oracle 常用语句
创建用户及授权
create temporary tablespace test_temp
tempfile 'C:\oracle\product\10.2.0\oradata\hszxdbtemp.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;
create tablespace hszxdb
logging
datafile 'C:\oracle\product\10.2.0\oradata\hszxdb.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;
create user hszx identified by hszx
default tablespace hszxdb
temporary tablespace test_temp;
grant connect,resource to hszx;
删除用户
drop user username cascade
查看表空间及使用情况
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;
删除表空间
DROP TABLESPACE HSZXDB INCLUDING CONTENTS AND DATAFILES;
-------------oracle创建自增列开始-------------------
-- Create sequence
create sequence ELUSER_SEQUENCE2
minvalue 1
maxvalue 999999999999999999999999999
start with 6480
increment by 1
cache 10;
--ELUSER_SEQUENCE2为名称
--创建一个触发器
CREATE OR REPLACE TRIGGER
"HSZX".auto_id_eluser2 before insert on ELUSER for each row
WHEN(new.id is null)
begin
select ELUSER_SEQUENCE2.nextval into :new.id from dual;
end;
--"HSZX"为表空间名称
-------------oracle创建自增列结束-------------------
--该语句是有时候用户权限不足的时候使用。
grant create any trigger to user_name;