1 查看数据字典名称
select table_name from dictionary where table_name like upper('%&table_name%');
2 查看表空间使用率
Select tablespace_name,sum_space/(1024*1024*1024) as total_space,sum_free_space/(1024*1024*1024) as free_space,(sum_space-sum_free_space)/1024/1024/1024 as used_space,to_char(100*(sum_space-sum_free_space)/sum_space,'99.99')||'%' as pct_used from (select tablespace_name,sum(bytes) as sum_space from dba_data_files group by tablespace_name) ,(select tablespace_name as fs_ts_name,sum(bytes) as sum_free_space from dba_free_space group by tablespace_name) where tablespace_name=fs_ts_name order by pct_used;
3 查看单个表空间使用率
select a.tablespace_name "tablespace_name",total "total GB",free "free GB",(total-free) "used GB",round ((total-free)/total,4)*100 "percent" from (select tablespace_name,sum(bytes/1024/1024/1024) free from dba_free_space group by tablespace_name) a,(select tablespace_name,sum(bytes/1024/1024/1024) total from dba_data_files group by tablespace_name) b where a.tablespace_name=b.tablespace_name and b.tablespace_name='&tablespace_name';
4 查看User表空间使用率最高的10个用户
SELECT * FROM (SELECT OWNER, SUM(BYTES) / 1024 / 1024 MB FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'USERS' GROUP BY OWNER ORDER BY 2 DESC) WHERE ROWNUM < 11;
5 tmp表空间使用率
SELECT a.tablespace_name "TABLESPACE_NAME", a.total "TOTAL(MB)", (a.total - nvl(b.used, 0)) "FREE(MB)" , nvl(b.used,0) "USED(MB)", round(nvl(b.used,0) * 100 / a.total, 3) "USED_PERCENT(%)" FROM (SELECT tablespace_name, SUM (bytes)/1024/1024 total FROM dba_temp_files GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM (bytes_cached)/1024/1024 used FROM v$temp_extent_pool GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name(+);
6 查看system表空间使用率最高的10个用户
SELECT * FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'SYSTEM' GROUP BY SEGMENT_NAME ORDER BY 2 DESC) WHERE ROWNUM < 11;
7 查看ASM DG的使用率
select NAME, TOTAL_MB, FREE_MB, (TOTAL_MB - FREE_MB) USED_MB, to_char((TOTAL_MB - FREE_MB) * 100 / TOTAL_MB,'99.99') || '%' USED_PRECENT from v$asm_diskgroup;
8 查看某个表空间使用的数据文件
select FILE_NAME,BYTES/1024/1024/1024 GB from dba_data_files where TABLESPACE_NAME='&tablespace_name' and rownum<10;
9 通过rman删除归档文件
delete archivelog all completed before 'sysdate-1';
10 查看用户的连接数
select username,count(username) from v$session where username is not null group by username order by 2;
11 查看行锁连接
select SID,SERIAL#,USERNAME,EVENT,PROGRAM from v$session where STATUS='ACTIVE' and EVENT like 'enq:%' order by username;
12 查看外部表信息1
select owner,object_name,object_type,status,to_char(created,'yyyy-mm-dd hh24:mi:ss') created,to_char(last_ddl_time,'yyyy-mm-dd hh24:mi:ss') last_ddl_time from dba_objects where object_name like 'ET$%';
13 查看外部表信息2
select owner,table_name,default_directory_name,access_type from dba_external_tables order by 1,2;
14 查看外部表信息3
select owner_name,job_name,rtrim(operation) "operation",rtrim(job_mode) "job_mode",state,attached_sessions from dba_datapump_jobs where job_name not like 'BIN$%' order by 1,2;
15 查看外部表信息4
select o.status,o.object_id,o.object_type,o.owner||','||object_name "owner.object" from dba_objects o,dba_datapump_jobs j where o.owner=j.owner_name and o.object_name=j.job_name and j.job_name not like 'BIN$%' order by 4,2;
16 根据sqlid查看执行计划
set linesize 200 pagesize 1000
select * from table(dbms_xplan.display_awr('&SQL_ID'));
17 查看某张表的索引信息
set pagesize 100
select t.*,i.index_type from dba_ind_columns t,dba_indexes i where t.index_name=i.index_name and t.table_name='&TABLE_NAME';
18 获取某张表的创建信息
set long 1000
select dbms_metadata.get_ddl('TABLE','&TABLE_NAME','&SCHAME') from dual;
19 查看用户表的大小
SELECT TABLESPACE_NAME,TO_CHAR(SUM(BYTES)/(1024*1024),'999G999D999') CNT_MB FROM DBA_EXTENTS WHERE OWNER='&OWNER' AND SEGMENT_NAME='&TABLE_NAME' AND SEGMENT_TYPE LIKE 'TABLE%' GROUP BY TABLESPACE_NAME;
20 按【backspace】经常出现^H
$ stty erase ^h
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31441616/viewspace-2141908/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31441616/viewspace-2141908/