SQL查询技巧汇总：数据表、权限与索引操作

常用sql语句查询
select * from dictionary where instr(comments,'index')>0;
select column_name,comments from dict_columns where table_name='USER_INDEXES';
--用户
select username,default_tablespace from user_users;--当前用户缺省表空间
select * from user_role_privs;--当前用户角色
select * from user_sys_privs; --当前用户系统权限
select * from user_tab_privs; --当前用户表权限
------表
select * from user_tables;--用户所有表
select object_name,object_id from user_objects where instr(object_name,'LOG')>0; -- 查看名称包含log字符的表
select object_name,created from user_objects where object_name=upper('&table_name');--查看某表的创建时间
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&table_name'); --查看某表的大小
select table_name,cache from user_tables where instr(cache,'Y')>0; --查看放在ORACLE的内存区里的表
------索引
select index_name,index_type,table_name from user_indexes order by table_name;--查看索引个数和类别
select * from user_ind_columns where index_name=upper('&index_name');--查看索引被索引的字段
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&index_name');--查看索引的大小
------序列号
select * from user_sequences;--查看序列号，last_number是当前值
------视图
select view_name from user_views;--查看视图的名称
select text from user_views where view_name=upper('&view_name');--查看创建视图的select语句?????
------同义词
select * from user_synonyms;--查看同义词的名称
------约束条件
select constraint_name, constraint_type,search_condition, r_constraint_name from user_constraints where table_name = upper('&table_name');--查看某表的约束条件
select c.constraint_name,c.constraint_type,cc.column_name from user_constraints c,user_cons_columns cc where c.owner = upper('&table_owner') and c.table_name = upper('&table_name') and c.owner = cc.owner and c.constraint_name = cc.constraint_name order by cc.position;
--存储函数和过程
select object_name,status from user_objects where object_type='FUNCTION'; --查看函数和过程的状态

select object_name,status from user_objects where object_type='PROCEDURE';--查看函数和过程的状态 
select text from all_source where owner=user and name=upper('&plsql_name');--查看函数和过程的源码
--查看表空间的名称及大小
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;
--查看表空间物理文件的名称及大小
select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;
--查看回滚段名称及大小
SELECT v.usn, SEGMENT_NAME,TABLESPACE_NAME,R.STATUS,(INITIAL_EXTENT / 1024) INITIALEXTENT,(NEXT_EXTENT / 1024) NEXTEXTENT,MAX_EXTENTS,V.CUREXT CUREXTENT FROM DBA_ROLLBACK_SEGS R, V$ROLLSTAT V WHERE R.SEGMENT_ID = V.USN(+) ORDER BY SEGMENT_NAME;
--查看某个回滚段里面，跑的什么事物或者正在执行什么sql语句
SELECT D.SQL_TEXT, A.NAME FROM V$ROLLNAME A, V$TRANSACTION B, V$SESSION C, V$SQLTEXT D WHERE A.USN = B.XIDUSN AND B.ADDR = C.TADDR AND C.SQL_ADDRESS = D.ADDRESS AND C.SQL_HASH_VALUE = D.HASH_VALUE AND A.USN = 39;--你要看哪个，就把usn=?写成几就行了
--查看控制文件
SELECT * FROM V$CONTROLFILE;
--查看日志文件
SELECT * FROM V$LOGFILE;
--如何查看当前SQL*PLUS用户的sid和serial#
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE AUDSID=USERENV('SESSIONID');
--查看当前数据库的字符集
SELECT USERENV('LANGUAGE') FROM DUAL;
SELECT USERENV('LANG') FROM DUAL;
--查看客户端登陆的IP地址
SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM DUAL;
--在生产数据库中创建一个追踪客户端IP地址的触发器
CREATE OR REPLACE TRIGGER ON_LOGON_TRIGGER AFTER LOGON ON DATABASE
BEGIN
 DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
END;
 
REM 记录登陆信息的触发器
CREATE OR REPLACE TRIGGER LOGON_HISTORY
AFTER LOGON ON DATABASE --WHEN (USER='WACOS') --ONLY FOR USER 'WACOS'