1、sqlplus ps -ef | grep smon \n echo $ORACLE_SID \n sqlplus / as sysdba \n set timing on time on \n 复制 复制 2、查看用户状态 复制 set line 240 \n set pages 999 \n col ACCOUNT_STATUS for a18 \n select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users; \n 复制 3、数据库启动时间 复制 复制 set line 240 \n col HOST_NAME for a30 \n select INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS from gv$instance; \n 复制 4、查看ASM使用率 复制 复制 set lin 1000 pagesize 999 \n col PATH for a33 \n col NAME for a15 \n col FAILGROUP for a15 \n col path for a15 \n select GROUP_NUMBER,DISK_NUMBER,TOTAL_MB/1024,FREE_MB/1024,NAME,FAILGROUP,PATH,FAILGROUP_TYPE from v$asm_disk order by 1; \n select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB/1024,FREE_MB/1024,USABLE_FILE_MB/1024,HOT_USED_MB,COLD_USED_MB/1024 from v$asm_diskgroup; \n 复制 5、查看用户状态 复制 复制 set line 240 \n col profile for a20 \n set pages 999 \n col username for a25 \n col ACCOUNT_STATUS for a18 \n select USERNAME,ACCOUNT_STATUS,CREATED,LOCK_DATE,PROFILE,PASSWORD_VERSIONS,DEFAULT_TABLESPACE from dba_users where account_status='OPEN' order by CREATED asc; \n 复制 6、查看 ASM 使用率 复制 复制 set line 240 \n col HOST_NAME for a30 \n select INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS from gv$instance; \n 复制 7、查看数据量 复制 复制 select sum(bytes)/1024/1024/1024 Gb from dba_segments where segment_name not like 'BIN$%';\n 复制 8、查看 LOCAL=NO 会话 复制 复制 ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}' |wc -l \n echo "ps -ef | grep LOCAL=NO | grep -v grep | awk '{print \$2}' | xargs kill -9" \n 复制 9、查看等待事件 复制 set lines 345 pages 345 \n select event, sql_id,sid,serial#,SECONDS_IN_WAIT from gv$session \n where type <> 'BACKGROUND' and STATUS='ACTIVE' \n group by event,sql_id,sid,serial#,SECONDS_IN_WAIT \n order by SECONDS_IN_WAIT desc; \n 复制 复制 10、查看索引列 复制 SET LINE 234 \n COL INDEX_OWNER FOR A20 \n COL TABLE_OWNER FOR A20 \n COL TABLE_NAME FOR A25 \n COL INDEX_NAME FOR A30 \n COL COLUMN_NAME FOR A25 \n SELECT INDEX_OWNER,TABLE_OWNER,TABLE_NAME,INDEX_NAME,COLUMN_NAME FROM DBA_IND_COLUMNS WHERE TABLE_NAME='&tablename' and table_owner='&tb_owner' ORDER BY INDEX_NAME; \n 复制 11、连接会话总数 复制 复制 --select CON_ID,inst_id,count(*),status from gv$session where type<>'BACKGROUND' group by con_id,inst_id,status order by 1;\n select inst_id,count(*),status from gv$session where type<>'BACKGROUND' group by inst_id,status order by 1; \n 复制 12、表空间使用率 复制 --purge dba_recyclebin; \n col TABLESPACE_NAME for a30 \n set pages 456 \n SELECT a.tablespace_name,round(total/1024/1024/1024) "Total g", \n round(free/1024/1024/1024) "Free g",ROUND((total-free)/total,4)*100 "USED%" \n FROM (SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACE \n GROUP BY tablespace_name ) a, \n (SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES \n GROUP BY tablespace_name) b \n WHERE a.tablespace_name=b.tablespace_name \n ORDER BY 4; \n 复制 复制 13、查看有哪些 IP 连接数据库 复制 netstat -anop | grep ESTABLISHED | awk '$4 ~/:1521/' |awk '{print $5}' \n 复制 14、查数据文件大小 复制 复制 select file_id,file_name,bytes/1024/1024,AUTOEXTENSIBLE from dba_data_files where tablespace_name='&tabs_name'; \n 复制 15、查表空间内前十大表 复制 复制 col TABLE_NAME for a30 \n col OWNER for a30 \n set pagesize 200 \n set linesize 200 \n col TABLE_NAME for a30 \n select * from (select TABLESPACE_NAME,OWNER,SEGMENT_NAME "TABLE_NAME",to_number(decode(substr(BYTES/1024/1024,1,1),'.','0'||BYTES/1024/1024,BYTES/1024/1024)) total_MB \n from dba_segments where TABLESPACE_NAME ='&tablespacename' and SEGMENT_TYPE='TABLE' order by total_MB desc ) where rownum<=10; \n 复制 16、查看用户下 Lob 字段大小 复制 SET LINE 345 PAGES 456 \n COL OWNER FOR a20 \n COL TABLE_NAME FOR A40 \n COL SEGMENT_TYPE FOR A20 \n col COLUMN_NAME FOR A35 \n SELECT \n A.OWNER, \n B.TABLE_NAME, \n B.COLUMN_NAME, \n a.SEGMENT_TYPE, \n ROUND(SUM(A.BYTES/1024/1024/1024),2) G \n FROM DBA_SEGMENTS A \n LEFT JOIN DBA_LOBS B \n ON A.OWNER = B.OWNER \n AND A.SEGMENT_NAME = B.SEGMENT_NAME \n WHERE A.SEGMENT_TYPE='LOBSEGMENT' \n AND A.OWNER in('&OWNER') \n GROUP BY A.OWNER,B.TABLE_NAME,B.COLUMN_NAME,a.SEGMENT_TYPE \n ORDER BY 5 DESC; \n 复制 复制 17、查隐含参数 复制 set line 345 \n col name for a50 \n col value for a30 \n col description for a80 \n select a.ksppinm name, b.ksppstvl value,a.ksppdesc description from x$ksppi a, x$ksppcv b where a.indx = b.indx and a.ksppinm like '%&name%'; \n 复制 18、查看 ADG 延迟 复制 复制 set linesize 150; \n set pagesize 9999; \n column name format a13; \n column value format a20; \n column unit format a30; \n column TIME_COMPUTED format a30; \n select name,value,unit,datum_time,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');\n 复制 19、外键没创建索引的表 复制 复制 set linesize 250 \n set pagesize 9999 \n col CONSTRAINT_NAME for a40 \n with cons as \n (select /*+materialize*/ \n owner, table_name, constraint_name \n from dba_constraints \n where owner = '&OWNER' \n and constraint_type = 'R'), \n idx as \n (select /*+materialize*/ \n table_owner, table_name, column_name \n from dba_ind_columns \n where table_owner = '&TABLE_OWNER') \n select owner, table_name, constraint_name, column_name \n from dba_cons_columns \n where (owner, table_name, constraint_name) in (select * from cons) \n and (owner, table_name, constraint_name) not in (select * from idx); \n 复制 20、需要收集直方图的列 复制 --最后一个纯属凑数，注意这个没有 \n 换行，可直接使用-- select a.owner, a.table_name, a.column_name, b.NUM_ROWS, a.num_distinct Cardinality, round(a.num_distinct / b.NUM_ROWS * 100, 2) selectivity from dba_tab_col_statistics a, dba_tables b where a.owner = b.OWNER and a.table_name = b.TABLE_NAME and a.owner = '&OWNER' and round(a.num_distinct / b.NUM_ROWS * 100, 2) < 5 and num_rows > 50000 and (a.table_name, a.column_name) in (select o.name, c.name from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r where o.obj# = u.obj# and c.obj# = u.obj# and c.col# = u.intcol# and r.name = '&OWNER') order by 2,6;