Oracle 表信息收集

–收缩表空间
alter table 表名 shrink space cascade;
–无用索引
select * from user_indexes where generated=’Y’ or index_type=’LOB’
–无效索引
select * from user_indexes where status<>‘VALID’
–查询建表时间
AUDIT INSERT,SELECT,DELETE,UPDATE on 表名by ACCESS WHENEVER SUCCESSFUL
SELECT OBJ_NAME,ACTION_NAME ,to_char(timestamp,’dd/mm/yyyy , HH:MM:SS’) from sys.dba_audit_object
select * from sys.dba_audit_object where lower(owner)=’用户名’ and lower(obj_name)=’表名’
SELECT CREATED FROM USER_OBJECTS WHERE lower(OBJECT_NAME)=’表名’;
SELECT * FROM USER_OBJECTS WHERE lower(object_name)=’表名’
SELECT ACTION_NAME,to_char(timestamp,’dd/mm/yyyy , HH:MM:SS’) FROM sys.dba_audit_object WHERE lower(owner)=’用户名’ and lower(用户名)=’表名’
–查看所有表
select * from tabs;
select table_name from all_tables;
select table_name from user_tables;
–查询表占据的空间
select table_name,(blocks+empty_blocks)*8/1024 from user_tables where table_name=’表名’
select segment_name,bytes/1024/1024 from user_segments where segment_name=’表名’
select segment_name,partition_name,segment_type,tablespace_name,max(extent_id)+1,sum(bytes)/1024/1024,sum(blocks)*8/1024 from user_extents group by segment_name,partition_name,segment_type,tablespace_name
select owner,segment_name,partition_name,segment_type,tablespace_name,max(extent_id)+1,sum(bytes)/1024/1024,sum(blocks)*8/1024 from dba_extents group by owner,segment_name,partition_name,segment_type,tablespace_name having sum(bytes)/1024/102>1
–查看表结构：表名必须大写
–(1)查看列
select * from USER_COL_COMMENTS where table_name = ‘表名’;
–(2)查看主键
select a.constraint_name, a.column_name
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = ‘P’
and a.table_name = ‘表名’;
–(3)查看列属性
select * from dba_constraints where owner=’用户名’ and constraint_type=’P’
–生成查询表数据
select ‘select replace(wm_concat(‘||replace( wm_concat(‘”‘||column_name||'”‘),’,’,’||”|”||’)||’||”|”),”,”,CHR(10)) from 表名’ from user_tab_columns where table_name=’表名’;
select ‘select ‘||replace(wm_concat(‘”‘||column_name||'”‘),’,’,’||”|”||’)||’||”|” from 表名’ from user_tab_columns where table_name=’表名’;

–查询sql操作语句
–select * from v$sqlarea
select t.SQL_TEXT, t.FIRST_LOAD_TIME
from v$sqlarea t
where t.FIRST_LOAD_TIME like ‘2014-08-08%’
order by t.FIRST_LOAD_TIME desc
–查询建表和建索引的语句
select dbms_metadata.get_ddl(‘TABLE’,’GLS_GL_SUB’) from dual;　　
select dbms_metadata.get_ddl(‘INDEX’,’GLS_GL_SUB_IDX1′) from dual;

表空间信息查询