oracle常用(自用)

-- 查询所有sequence
select 'create sequence '||sequence_name||  
       ' minvalue '||min_value||  
       ' maxvalue '||max_value||  
       ' start with '||last_number||  
       ' increment by '||increment_by||  
       (case when cache_size=0 then ' nocache' else ' cache '||cache_size end) ||';'
from user_sequences

-- 索引
select * from all_indexes where table_name = 'exam_recrd';

create index index_examId on exam_recrd (exam_id);
create index index_userId on exam_recrd (user_id);
create index index_paperId on exam_recrd (paper_id);

-- 查看所有表空间
SELECT * FROM dba_tablespaces t, dba_data_files d 
WHERE t.tablespace_name = d.tablespace_name;
-- 创建表空间
create tablespace table_name datafile 'D:\ORACLE\ORADATA\ORCL\table_name .DBF' size 2000M;
--创建用户：
CREATE USER user_name  
IDENTIFIED BY user2022  -- 密码
DEFAULT TABLESPACE table_name
TEMPORARY TABLESPACE temp;

--给用赋权限
GRANT CREATE SESSION,CREATE TABLE,CREATE VIEW,CREATE SEQUENCE,UNLIMITED TABLESPACE TO user_name; 
GRANT CONNECT TO user_name; 
GRANT RESOURCE TO user_name;
GRANT DBA TO mycom; 

-- 查看oracle被锁住状态
select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time;
-- 解锁
alter system kill session '1076,421';      --1076是sid，421是serial#

-- group by 可与min max使用
select min(createtime),sfz from dual group by sfz;