各种按钮

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/