select dbid,name,to_char(created,'YYYY-MM-DD') created,log_mode from gv$database;

select version,instance_name,to_char(startup_time,'YYYY-MM-DD') startup_time,status from gv$instance;

select instance_name,status from gv$instance;

$ORACLE_HOME/OPatch/opatch lspatches$ORACLE_HOME/OPatch/opatch lsinventory

set line 200col value for a50set pagesize 100select * from nls_database_parameters;

show parameter spfile

cat $ORACLE_HOME/dbs/init实例名.ora

set line 300col tablespace_name for a20select tablespace_name,block_size,initial_extent,next_extent,max_size,status,contents,logging,extent_management,segment_space_managementfrom dba_tablespaces;

set pagesize 9999set linesize 132col tablespace_name for a35selecta.tablespace_name,a.Total_mb,f.Free_mb,round(a.total_MB-f.free_mb,2) Used_mb,round((f.free_MB/a.total_MB)*100) "%_Free"from(select tablespace_name, sum(bytes/(1024*1024)) total_MB from dba_data_files group by tablespace_name) a,(select tablespace_name, round(sum(bytes/(1024*1024))) free_MB from dba_free_space group by tablespace_name) fWHERE a.tablespace_name = f.tablespace_name(+)order by "%_Free"/

selecta.tablespace_name,case when g.auto_free <0 then round((f.free_MB/a.total_MB)*100) else round(((f.free_MB+g.auto_free)/b.r_total_mb)*100) end "Free"from(select tablespace_name, sum(bytes/(1024*1024)) total_MB from dba_data_files group by tablespace_name) a,(select tablespace_name, round(sum(bytes/(1024*1024))) free_MB from dba_free_space group by tablespace_name) f,(select tablespace_name,trunc(sum(maxbytes)/1024/1024,2) as r_total_mb from dba_data_files group by tablespace_name) b,(select sum(free_MB) auto_free,tablespace_name from (select tablespace_name,trunc(maxbytes / 1024 / 1024 , 2) as MAX_MB,trunc((maxbytes-bytes)/1024/1024,2) FREE_MB from dba_data_files) a group by tablespace_name) gWHEREa.tablespace_name = f.tablespace_name(+)andg.tablespace_name = a.tablespace_nameandb.tablespace_name = a.tablespace_name;

select a.TABLESPACE_NAME,ROUND((1 - (a.Free_MB_1+ nvl(b.FREE_MB_2,0)) / a.total_mb) * 100, 2) Used_Prcfrom (select TABLESPACE_NAME,ROUND(sum(casewhen autoextensible = 'NO' thenBYTESwhen autoextensible = 'YES' thenMAXBYTESend) / 1024 / 1024,2) TOTAL_MB,ROUND(sum(casewhen MAXBYTES - BYTES >= 0 thenMAXBYTES - BYTESwhen MAXBYTES - BYTES < 0 then0end) / 1024 / 1024,2) Free_MB_1from dba_data_filesgroup by TABLESPACE_NAME) ainner join (SELECT dfs.TABLESPACE_NAME,SUM(dfs.bytes / 1024 / 1024) FREE_MB_2FROM dba_free_space dfsGROUP BY dfs.TABLESPACE_NAME) bon a.TABLESPACE_NAME = b.TABLESPACE_NAME(+);

set line 300col tablespace_name for a20col file_name for a45select tablespace_name,file_name,file_id,status,trunc(bytes / 1024 / 1024 / 1024, 2) as FILE_GB,autoextensible,trunc(maxbytes / 1024 / 1024 / 1024, 2) as MAX_GBfrom dba_data_filesorder by file_id;

select t.status,sum(t.blocks)*8/1024||'M' size_Mfrom dba_undo_extents tgroup by t.status;

select * from v$rollname;

set line 150col tablespace_name for a10set pagesize 100select owner, tablespace_name, segment_id, segment_name, statusfrom dba_rollback_segs order by 2,3;

set line 300col tablespace_name for a20col file_name for a40select tablespace_name,file_name,file_id,status,trunc(bytes / 1024 / 1024 / 1024, 2) as FILE_GB,autoextensible,trunc(maxbytes / 1024 / 1024 / 1024, 2) as MAX_GBfrom dba_temp_filesorder by file_id;

SET PAGESIZE 400SET LINES 300COL D.TABLESPACE_NAME FORMAT A15COL D.TOT_GROOTTE_MB FORMAT A10COL TS-PER FORMAT A15SELECT d.tablespace_name "Name",TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999') "HWM (M)",TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " ,TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)",TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %"FROM sys.dba_tablespaces d,(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,(select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) tWHERE d.tablespace_name = a.tablespace_name(+)AND d.tablespace_name = t.tablespace_name(+)AND d.extent_management like 'LOCAL'AND d.contents like 'TEMPORARY';

col name for a50select status,name from v$controlfile;

SELECT * FROM v$CONTROLFILE_RECORD_SECTION;

set line 300col member for a50select a.group#,THREAD#,b.member,a.members,a.status,a.sequence#,bytes / 1024 / 1024 as file_mbfrom v$log a, v$logfile bwhere a.group# = b.group#order by 2,1;

archive log list;

set pagesize 100select a_date,a_count from (select to_char(first_time,'YYYY-MM-DD') a_date,count(*) a_count from gv$log_historygroup by to_char(first_time,'YYYY-MM-DD')order by 1 desc) where rownum<=31;

set line 300set pagesize 1000SELECT TRUNC(first_time) "Date",TO_CHAR(first_time, 'Dy') "Day",COUNT(1) "Total",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '00', 1, 0)) "h0",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '01', 1, 0)) "h1",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '02', 1, 0)) "h2",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '03', 1, 0)) "h3",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '04', 1, 0)) "h4",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '05', 1, 0)) "h5",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '06', 1, 0)) "h6",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '07', 1, 0)) "h7",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '08', 1, 0)) "h8",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '09', 1, 0)) "h9",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '10', 1, 0)) "h10",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '11', 1, 0)) "h11",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '12', 1, 0)) "h12",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '13', 1, 0)) "h13",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '14', 1, 0)) "h14",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '15', 1, 0)) "h15",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '16', 1, 0)) "h16",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '17', 1, 0)) "h17",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '18', 1, 0)) "h18",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '19', 1, 0)) "h19",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '20', 1, 0)) "h20",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '21', 1, 0)) "h21",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '22', 1, 0)) "h22",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '23', 1, 0)) "h23",ROUND(COUNT(1) / 24, 2) "Avg"FROM gv$log_historyWHERE thread# = inst_idGROUP BY TRUNC(first_time), TO_CHAR(first_time, 'Dy')ORDER BY 1 desc;

lsnrctl statuslsnrctl status listener_scan1

ls -lrth $ORACLE_HOME/network/log/listener.log

cd /oracle/grid/diag/tnslsnr/cjc-db-01/listener/trace

grep "HOST=.*establish.*\* 0" listener.log | awk -F'*' '{match($3,/[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+/); ip = substr($3,RSTART,RLENGTH);cnt[ip]+=1;last[ip]=$1;}END {for (i in cnt) printf "%-16s %9s %19s\n",i,cnt[i],last[i];}' | sort -k 1

set line 300col name for a10col compatibility for a10select group_number,name,block_size,total_mb,free_mb,(1-(free_mb/total_mb))*100 used,type,compatibility,voting_filesfrom v$asm_diskgroup;

set line 300col CREATE_DATE for a10col name for a15col path for a20set pagesize 300select GROUP_NUMBER,DISK_NUMBER,STATE,OS_MB,TOTAL_MB,FREE_MB,NAME,PATH,CREATE_DATE,VOTING_FILEfrom v$asm_disk order by 1,2;

ocrcheck -configocrcheck

ocrcheck -local

crsctl query css votedisk

oifcfg getif

srvctl config database -d dah

cemutlo -n

crsctl check cluster -all

crsctl stat res -t

crsctl check crs

crsctl check has

olsnodessrvctl status nodeapps -n cjc01srvctl status nodeapps -n cjc02

srvctl config databasesrvctl status database -d cjc

srvctl config scansrvctl status scan

srvctl config listener -a

srvctl status listener

column OWNER format a10 heading 'OWNER'column OBJECT_NAME format a80 heading 'OBJECT_NAME'column OBJECT_TYPE format a40 heading 'OBJECT_TYPE'select OWNER,OBJECT_NAME, OBJECT_TYPE from dba_objectswhere object_name in('DBMS_SUPPORT_INTERNAL','DBMS_SYSTEM_INTERNAL','DBMS_CORE_INTERNAL','DBMS_STANDARD_FUN9','DBMS_SUPPORT_INTERNAL','DBMS_SYSTEM_INTERNAL','DBMS_CORE_INTERNAL') or object_name like 'DBMS_SUPPORT_DBMONITOR%';

select grantee,granted_role from dba_role_privs where GRANTED_ROLE='DBA';

set line 300col profile for a10select * from dba_profiles where profile='DEFAULT' and resource_name in ('PASSWORD_LIFE_TIME','FAILED_LOGIN_ATTEMPTS');

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

select trunc(sum(bytes) / 1024 / 1024 / 1024,2) as db_GB from dba_segments;

col owner for a15select owner, trunc(sum(bytes) / 1024 / 1024,2) as db_MBfrom dba_segmentsgroup by ownerorder by 1;

set line 200set pagesize 100col ACCOUNT_STATUS for a20col default_tablespace for a15col username for a10select username,to_char(created,'YYYY-MM-DD') created,default_tablespace,ACCOUNT_STATUS from dba_users order by 2;

select grantee,granted_role from dba_role_privs where grantee in ('username') order by 1;Select grantee,privilege from dba_sys_privs where grantee in ('username') order by 1;Select grantee,privilege from dba_tab_privs where grantee in ('username') order by 1;Select role,privilege from role_sys_privs where role in ('CONNECT','RESOURCE') order by role;

---表数量col owner for a10select owner, count(*)from dba_tablesgroup by ownerorder by 1;
---临时表数量select owner, count(*)from dba_tableswhere temporary = 'Y'group by ownerorder by 1;
---索引信息select owner, count(*)from dba_indexesgroup by ownerorder by 1;
---视图数量select owner, count(*)from dba_viewsgroup by ownerorder by 1;
---触发器信息select owner, count(*)from dba_triggersgroup by ownerorder by 1;
---存储过程select owner, count(*)from dba_proceduresgroup by ownerorder by 1;
---无效的对象set line 100set pagesize 300col owner for a20col object_name for a30select owner,object_type,count(*)from dba_objectswhere status = 'INVALID' group by owner,object_type;select owner,object_name,object_typefrom dba_objectswhere status = 'INVALID';

SELECT OWNER,TABLE_NAME,PARTITIONING_TYPE FROM DBA_PART_TABLES ORDER BY 1;

set line 300set pagesize 1000col table_owner for a20col table_name for a20col PARTITION_NAME for a20col SUBPARTITION_COUNT for a20col MAX_SIZE for a20col HIGH_VALUE for a50select table_owner,table_name,PARTITION_NAME,HIGH_VALUE from dba_tab_PARTITIONS where table_owner not in ('SYS','SYSTEM') ORDER BY 3;

set line 150col INTERVAL for a35col SCHEMA_USER for a15col what for a30SELECT job,schema_user,broken,interval,what,last_date,last_sec,BROKEN from dba_jobs;

set line 100col start_date for a20select owner,job_name,job_type,start_date,state from dba_scheduler_jobs;

set line 300set pagesize 100col TABLE_OWNER for a15col TABLE_NAME for a30col COLUMN_NAME for a15col INDEX_NAME for a35select TABLE_OWNER,TABLE_NAME,COLUMN_NAME,INDEX_NAME from dba_ind_columns where TABLE_NAME in ('XXXXXX') order by 2,3;

set line 300col owner for a15select OWNER,TABLE_NAME,CONSTRAINT_TYPE,CONSTRAINT_NAME,INDEX_NAME,status from dba_constraints where table_name in('XXXXXX') and CONSTRAINT_TYPE='P';

set line 300set pagesize 150col in_size for a10col out_size for a10col input_type for a10col e for a20col s for a20selectsession_key,input_type,compression_ratio,INPUT_BYTES_DISPLAY in_size,output_bytes_display out_size,to_char(START_TIME,'YYYYMMDD HH24:MI:SS') S,to_char(END_TIME,'YYYYMMDD HH24:MI:SS') E,statusfrom v$rman_backup_job_details where INPUT_TYPE='DB FULL'order by S DESC;

set line 300set pagesize 150col in_size for a10col out_size for a10col input_type for a20col e for a20col s for a20selectsession_key,input_type,compression_ratio,INPUT_BYTES_DISPLAY in_size,output_bytes_display out_size,to_char(START_TIME,'YYYYMMDD HH24:MI:SS') S,to_char(END_TIME,'YYYYMMDD HH24:MI:SS') E,statusfrom v$rman_backup_job_details where INPUT_TYPE='DB INCR'order by S DESC;

SELECT DISTINCT INCREMENTAL_LEVEL FROM V$BACKUP_SET;

set line 300set pagesize 150col in_size for a10col out_size for a10col input_type for a20col e for a20col s for a20selectsession_key,input_type,compression_ratio,INPUT_BYTES_DISPLAY in_size,output_bytes_display out_size,to_char(START_TIME,'YYYYMMDD HH24:MI:SS') S,to_char(END_TIME,'YYYYMMDD HH24:MI:SS') E,statusfrom v$rman_backup_job_details where INPUT_TYPE='ARCHIVELOG'order by S DESC;

col username for a15select inst_id, username, count(*)from gv$sessiongroup by inst_id, usernameorder by 1;

set line 300set pagesize 100col RESOURCE_NAME for a10col INITIAL_ALLOCATION for a15select * from v$resource_limit;

@?/rdbms/admin/awrrpt.sql@?/rdbms/admin/awrgrpt.sql

set linesize 150set pagesize 2000select * from TABLE(dbms_xplan.display_cursor('&SQL_ID'));或select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD));select * from table(dbms_xplan.display_awr('&sql_id')) ;

select dbms_metadata.get_ddl('TABLE','TABLE_NAME','TABLE_OWNER') from dual;select dbms_metadata.get_ddl('INDEX','INDEX_NAME','INDEX_OWNER') from dual; select dbms_metadata.get_ddl('PROCEDURE','NAME','OWNER') from dual;

col CLIENT_NAME for a35select client_name,status from dba_autotask_client;

sqlplus / as sysdbashow parameter memshow parameter sgashow parameter pga

---v$parameter ---session---v$system_parameter ---system

set line 100set pagesize 100col name for a30col value for a20col display_value for a20col isdefault for a20select name, value, display_value, isdefaultfrom v$system_parameterwhere name in ('audit_trail','audit_sys_operations','cluster_database_instances','cpu_count','cursor_sharing','db_recovery_file_dest_size','deferred_segment_creation','disk_asynch_io','event','enable_ddl_logging','filesystemio_options','job_queue_processes','log_archive_dest_1','log_archive_format','memory_max_target','memory_target','nls_language','optimizer_dynamic_sampling','optimizer_index_cost_adj','processes','parallel_force_local','parallel_max_servers','pga_aggregate_target','query_rewrite_enabled','sec_case_sensitive_logon','sessions','sga_max_size','sga_target','utl_file_dir','undo_management','undo_retention','undo_tablespace','large_pool_size','resource_limit','resource_manager_plan','max_dump_file_size','control_file_record_keep_time','result_cache_max_size','local_listener','resource_limit') order by 1;

set line 200col name for a40col describ for a50SELECT x.ksppinm as name,y.ksppstvl as value,y.ksppstdf as isdefault,x.ksppdesc describFROM SYS.x$ksppi x, SYS.x$ksppcv yWHERE x.inst_id = USERENV('Instance')AND y.inst_id = USERENV('Instance')AND x.indx = y.indxAND x.ksppinm in ('_allow_resetlogs_corruption','_b_tree_bitmap_plans','_corrupted_rollback_segments','_datafile_write_errors_crash_instance','_gc_policy_time','_gc_undo_affinity','_gc_defer_time','_hash_join_enabled','_offline_rollback_segments','_px_use_large_pool','_memory_imm_mode_without_autosga','_partition_large_extents','_optimizer_null_aware_antijoin','_optim_peek_user_binds','_optimizer_mjc_enabled','_optimizer_use_feedback','_optimizer_join_elimination_enabled','_optimizer_ads_use_result_cache','_optimizer_adaptive_plans','_optimizer_adaptive_cursor_sharing','_optimizer_extended_cursor_sharing','_optimizer_extended_cursor_sharing_rel','_optimizer_aggr_groupby_elim','_optimizer_reduce_groupby_key','_optimizer_cost_based_transformation','_use_adaptive_log_file_sync','_undo_autotune')order by 1;

col owner for a40col name for a30set lines 200 pages 1000col pname for a10col current_value for a85col check_result for a42select nam.inst_id,nam.ksppinm pname,val.ksppstvl current_value,caseWHEN nam.ksppinm = 'event' andval.ksppstvl ='28401 trace name context forever,level 1, 10949 trace name context forever,level 1' then'28401,10949 event is OK! Manual check NAS event (10298)'else'INCORRECT'end as check_resultfrom x$ksppi nam, x$ksppsv valwhere nam.indx = val.indxand nam.ksppinm IN ('event')/

select name,total,round(total-free,2) used,round(free,2) free,round((total-free)/total*100,2) pctused from(select 'SGA' name,(select sum(value)/1024/1024 from v$sga) total,(select sum(bytes/1024/1024) from v$sgastat where name='free memory') free from dual)unionselect name,total,round(used,2) used,round(total-used,2) free,round(used/total*100,2) pctused from(select 'PGA' name,(select value/1024/1024 from v$pgastat where name='aggregate PGA target parameter') total,(select value/1024/1024 used from v$pgastat where name='total PGA allocated') used from dual);

set line 300set pagesize 1000select SID,SERIAL#,SQL_ID,LAST_CALL_ET,status,event from v$session where STATUS='ACTIVE' and username is not null and event not in ('SQL*Net message to client','SQL*Net message from client') order by LAST_CALL_ET;

select * from v$sql where address in (select sql_address from v$session);

set linesize 150set pagesize 2000select * from TABLE(dbms_xplan.display_cursor('&SQL_ID'));或select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD));select * from table(dbms_xplan.display_awr('&sql_id')) ;

select v.last_call_et,v.username,v.machine,v.program,v.module,v.sid,sql.sql_text,sql.sql_fulltext,sql.sql_id,sql.disk_reads,v.eventfrom v$session v, v$sql sqlwhere v.sql_address = sql.addressand v.last_call_et > 0and v.status = 'ACTIVE'and v.username is not null;

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'cjc',tabname => 't1',estimate_percent=>100,method_opt=> 'FOR ALL INDEXED COLUMNS',CASCADE=> TRUE,no_invalidate=> FALSE,degree=>);EXEC DBMS_STATS.GATHER_TABLE_STATS('OWNER','TABLE',estimate_percent=>30,CASCADE=> TRUE);dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);EXEC DBMS_STATS.GATHER_TABLE_STATS('OWNER','TABLE',estimate_percent=>30,CASCADE=> TRUE);

set colsep' ';　　　 //域输出分隔符set echo off;　　　　//显示start启动的脚本中的每个sql命令，缺省为onset feedback off;　　//回显本次sql命令处理的记录条数，缺省为onset heading off;　　 //输出域标题，缺省为onset pagesize 0;　　　//输出每页行数，缺省为24,为了避免分页，可设定为0。set termout off;　　 //显示脚本中的命令的执行结果，缺省为onset trimout on;　　　//去除标准输出每行的拖尾空格，缺省为offset trimspool on;　　//去除重定向（spool）输出每行的拖尾空格，缺省为off

SQL> set timing on; //设置显示“已用时间：XXXX”SQL> set autotrace on-; //设置允许对执行的sql进行分析SQL> set trimout on; //去除标准输出每行的拖尾空格，缺省为offSQL> set trimspool on; //去除重定向（spool）输出每行的拖尾空格，缺省为offSQL> set echo on //设置运行命令是是否显示语句SQL> set echo off; //显示start启动的脚本中的每个sql命令，缺省为onSQL> set feedback on; //设置显示“已选择XX行”SQL> set feedback off; //回显本次sql命令处理的记录条数，缺省为onSQL> set colsep''; //输出分隔符SQL> set heading off; //输出域标题，缺省为onSQL> set pagesize 0; //输出每页行数，缺省为24,为了避免分页，可设定为0。SQL> set linesize 80; //输出一行字符个数，缺省为80SQL> set numwidth 12; //输出number类型域长度，缺省为10SQL> set termout off; //显示脚本中的命令的执行结果，缺省为onSQL> set serveroutput on; //设置允许显示输出类似dbms_outputSQL> set verify off //可以关闭和打开提示确认信息old 1和new 1的显示.

select /*+ full(t1) */ * from t1;--全表扫描select /*+ index(t1 idx_name) */ * from t1 where object_id>2;使用指定索引select /*+ no_index(t1 idx_name) */ * from t1 where object_id>2; --不使用指定索引select /*+ index_desc(t1 idx_name) */ * from t1 where object_id=2; --按索引降序顺序访问数据select /*+ index_combine(t1 idx_name) */ * from t1;--选择位图索引select /*+ index_ffs(t1 idx_name) */ from t1 where object_id <100; --索引快速全表扫描（把索引当作一个表看待）select /*+ index_join(t1 idx_name1 idx_name2) */ * from t1 where object_id=5 and status='VALID'; --同时使用条件列上的相关索引select /*+ index_ss(t1 index_name) */ * from t1 where object_id=99; --跳跃式扫描select /*+ leading(t1,t) */ t.* from t,t1 where t1.object_id=t.object_id; --指定t1为驱动作，优化器先访问此表select /*+ ordered */ t.* from t,t1 where t1.id=t.id; --指定按from 后面表的顺序选择驱表，t作为驱动表select /*+ use_nl(t1,t) */ t.* from t1,t where t1.object_id=t.object_id;--使用NEST LOOP表连接，适合含有小表数据关联，如一大一小(有别名，必须用别名)select /*+ use_hash(t1,t) */ t.* from t1,t where t1.object_id=t.object_id;--使用HASH表连接，适合两个大表关联select /*+ use_merge(t1,t) */t.* from t1,t where t1.object_id=t.object_id;--使用合并排序表连接select /*+ no_use_nl(t1,t) */ t.* from t1,t where t1.object_id=t.object_id; --不使用NEST LOOP表连接select /*+ no_use_hash(t1,t) */ t.* from t1,t where t1.object_id=t.object_id;--不使用HASH表连接select /*+ no_use_merge(t1,t) */t.* from t1,t where t1.object_id=t.object_id; --不使用合并排序表连接/*+OPTIMIZER_FEATURES_ENABLE('10.2.0.4')*/SELECT /*+ PUSH_PRED (v1) */ t1.c1,v1.c1/*+no_push_pred()*//*+ no_unnest *//*+ unnest */

select value from v$sysmetricwhere metric_name in ('Executions Per Sec')and group_id = 2;

select value from v$sysmetric where metric_name in ('Executions Per Sec') and group_id = 3;

select (select VALUE from v$sysmetric where metric_name in ('User Commits Per Sec'))+ (select VALUE from v$sysmetric where metric_name in ('User Rollbacks Per Sec')) as TPS FROM DUAL;

alter table XXX enable row movement;alter table XXX shrink space;alter table XXX disable row movement;

select owner,table_name,status from dba_indexes where table_name='XXX';

set line 300col owner for a15col segment_name for a30set pagesize 100select * from(select owner,segment_name,segment_type,BYTES/1024/1024/1024 GB from dba_segments order by 4 desc)where rownum<=20;

select sum(GB) from(select owner,segment_name,segment_type,BYTES/1024/1024/1024 GB from dba_segments order by 4 desc)where rownum<=20;

消耗内存前10名ps auxw|head -1;ps auxw|sort -rn -k4|head -10
消耗CPU前10名ps auxw|head -1;ps auxw|sort -rn -k3|head -10

usedMem1=`free -k|grep "cache:" |awk '{print $3}'`shmem=`cat /proc/meminfo |grep -w Shmem|awk '{print $2}'`usedMem=`expr $usedMem1 + $shmem`

usedMem1=`free -k|grep "Mem:" |awk '{print $3}'`shmem=`cat /proc/meminfo |grep -w Shmem|awk '{print $2}'`usedMem=`expr $usedMem1 + $shmem`

cat db_startup.shdatename="`date +%Y%m%d%s`"su - oracle -c"lsnrctl start;sqlplus / as sysdba <<EOFspool /home/oracle/shell/outlog/$datename.txtstartup;prompt database startup;select STATUS from v\\\$instance;prompt alter system register;alter system register;EOFlsnrctl status;exit;"

cat db_shutdown.shdatename="`date +%Y%m%d%s`"su - oracle -c"lsnrctl stop;sqlplus / as sysdba <<EOFspool /home/oracle/shell/outlog/$datename.txtshow user;select STATUS from v\\\$instance;shutdown immediate;prompt database shutdown;exitEOF"