常用查询脚本

1.查看表大小，索引大小，获取表的DDL创建脚本

select bytes/1024/1024 from dba_segments where segment_name='table_name' and owner='username';

select bytes/1024/1024 from dba_segments where segment_name='index_name' and owner='username';

select dbms_metadata.get_ddl('TABLE','TABLE_NAME','USERNAME') from dual;

2.查看分区表大小，获取分区表的DDL创建脚本

select segment_name,PARTITION_NAME,bytes/1024/1024 from user_segments where segment_name='SALES_INTERVAL';

set pages 999 lines 180
set long 99999
select dbms_metadata.get_ddl('TABLE','SALES_INTERVAL','SH') from dual;

3.查看表的索引列，索引名，获取索引的DDL创建脚本

select a.table_name,b.index_name,b.column_name from user_indexes a ,user_ind_columns b where a.table_name=b.table_name and a.index_name = b.index_name and a.table_owner=upper('SCOTT') and a.table_name='EMP';
TABLE_NAME INDEX_NAME COLUMN_NAM
------------------------------ ------------------------------ ----------
EMP PK_EMP EMPNO

或者：
select table_name,index_name from user_indexes where table_name='EMP';
TABLE_NAME INDEX_NAME COLUMN_NAM
------------------------------ ------------------------------ ----------
EMP PK_EMP EMPNO

select index_name,column_name from user_ind_columns where index_name='PK_EMP';
INDEX_NAME COLUMN_NAM
------------------------------ ----------
PK_EMP EMPNO

SQL> CONN / AS SYSDBA
Connected.
SQL> set pages 1000 lines 180
SQL> set long 99999
SQL> select dbms_metadata.get_ddl('INDEX','PK_EMP','SCOTT') from dual;

DBMS_METADATA.GET_DDL('INDEX','PK_EMP','SCOTT')
--------------------------------------------------------------------------------

CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"

4.查看分区表的分区字段，各个区的大小

SQL> select * from dba_part_key_columns where name='SALES_INTERVAL' and owner='SH';

SELECT * FROM all_PART_KEY_COLUMNS;

SELECT * FROM all_PART_KEY_COLUMNS t where t.owner='数据库用户名' and t.name in（select table_name from dba_tables where partitioned='YES' and owner='数据库用户名' ）;

大小：
select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from dba_tab_partitions where table_name='SALES_INTERVAL';

5.查看数据库用户的系统权限，角色权限，表权限

select * from dba_sys_privs；

select * from dba_role_privs；

SELECT * FROM user_tab_privs_recd;

6.查看数据库各等待事件的个数

select wait_class#,wait_class_id,wait_class,count(*) as "count" from v$event_name group by wait_class#,wait_class_id,wait_class order by wait_class#;

7.查看用户和主机的连接数量

select machine,count(*) from v$session group by machine;

8.查看数据库的阻塞队列；如何kill 掉阻塞会话

select sid,serial#,username from v$session where sid in (select blocking_session from v$session);

alter system kill session 'sid,serial#';

9.查看数据库会话正在执行的SQL

select t2.sid, t2.SERIAL#, t1.SPID OS_PID,t3.SQL_ID, t2.EVENT,t2.P1TEXT, t2.P1, t2.p2TEXT, t2.P2,t2.p3,t3.SQL_TEXT,t2.P3, t3.SQL_FULLTEXT from v$process t1, v$session t2, v$sql t3 where t1.ADDR = t2.PADDR and t2.STATUS = 'ACTIVE' and t2.SQL_ID = t3.SQL_ID;

10.查看数据库正在执行的SQL的执行计划

执行SQL语句：
select * from hr.employees;

SELECT sql_id, hash_value, child_number, SUBSTR(sql_text,1,40) Text FROM v$sql WHERE sql_text LIKE 'select * from hr.employees;';

set linesize 200
set pagesize 50
select * from table(dbms_xplan.display_cursor('sql_id',0,'TYPICAL'));

11.查看表空间使用率（自动扩展的数据文件与非自动扩展的文件表空间的使用率)

SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
FREE_SPACE "FREE_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100) "USED_RATE(%)"
FROM
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES)/(1024*1024)) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES)/(1024*1024)) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY "USED_RATE(%)" desc;

12.查看ASM磁盘组的总大小，剩余空间，

select name,total_mb,free_mb from v$asm_diskgroup;