LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = bloo)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = scr10)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = scr10)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10gASM)
(PROGRAM = extproc)
)
)
SCR9 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = bloo)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = scr9)
)
)
LISTENER_+ASM = (ADDRESS = (PROTOCOL = TCP)(HOST = bloo)(PORT = 1522))
create or replace procedure andy_file_test (
path in varchar2,
filename in varchar2,
text in varchar2
)
is
output_file utl_file.file_type;
begin
output_file := utl_file.fopen (path,filename, 'W');
utl_file.put_line (output_file, text);
utl_file.fclose(output_file);
end;
/
execute andy_file_test('/home/oracle/andy/file_test','andy.txt','hello world');
find ./ -name "*.ARC" -mtime +1 -exec ls -l {} \;
find ./ -name "*.arch" -mtime +1 -exec mv {} /u01/andy/;
rm -f `ls -tr|head -500`
find ./ -grep <what> {} \; -print 2>/dev/null
find ./ -exec grep -l "string" {} \;
lsof -p <pid>
ls -l |sort -k 5
cpio -idmv < <filename>
man -k <what> -displays the chapters containing the specified man <chapter> <what> -shows the page
mknod <name> p
:set noai nosm
set -o
set -o vi set -o vi-esccomplete
netstat -r
grep ^ORA- *log |cut -f2 -d"-"|cut -f1 -d:|awk '{print "ORA-" $1}'|sort -u
create directory my_dir as '/home/oracle/andyb';
create directory data_pump_dir as '/u01/ora_backup/datapump';
expdp system/****** schemas=andy dumpfile=andy.dp directory=my_dir
impdp system/****** schemas=andy dumpfile=andy.dp directory=my_dir
exec dbms_flashback.enable_at_time(to_date('2006-AUG-24 12:00:00', 'YYYY-MON-DD HH24:MI:SS'));
exec dbms_flashback.disable;
create table old_test_table tablespace andy as select * from test_table where rownum < 1 /
insert /*+ APPEND */ old_test_table
(select * from test_table as of timestamp
to_timestamp('24-AUG-06 12:00:00','DD-MON-YY HH24:MI:SS'))
/
exp / file=full_scr9.dmp log=full_scr9.log flashback_time=TO_TIMESTAMP('10-09-2006 09:00:00', 'DD-MM-YYYY HH24:MI:SS')
userid=system/****** file=full_scr9.dmp log=full_scr9.log flashback_time='2006-09-13 12:00:00'
select dbms_flashback.get_system_change_number from dual /
alter system set db_recovery_file_dest='<path>' scope=both / alter system set db_recovery_file_dest_size=<size> scope=both /
alter system set db_flashback_retention_target=<minutes> scope=both /
mknod andypipe p gzip < andypipe > filename.dmp.gz & exp ... file=andypipe ...
mknod andypipe p gunzip -c filename.dmp.gz > andypipe & imp ... file=andypipe ...
userid=/ file=/u03/scr9/oradata/expimp/exppipe log=/u03/scr9/oradata/expimp/imp_scr9.log rows=y constraints=n ignore=y indexes=n grants=n full=n feedback=10000 direct=y fromuser=andy,user2 touser=andy,user2
exp parfile=<file> imp parfile=<file>
#!/usr/bin/ksh export ORAENV_ASK=NO export ORACLE_SID=scr10 . /usr/local/bin/oraenv rm andypipe 2>/dev/null mknod andypipe p gzip < andypipe > full_scr10.dmp.gz & exp / full=y indexes=N grants=N statistics=none file=andypipe log=full_scr10.log rm andypipe 2>/dev/null
chmod 700 <file> nohup ./<file> &
resumable=y resumable_name=<name to apear in dba_resumable> resumable_timeout=<timeout in seconds>
grant resumable to <user>;
set lines 100 pages 999 col name format a20 select name , start_time , suspend_time , status from dba_resumable /
set lines 100 pages 999 select error_msg from dba_resumable where name like '&resumable_name' /
create a tablespace create a user grant connect, resource, recovery_catalog_owner to user
rman catalog user/pass@rmandb target user/pass@db
register database;
sqlplus user/pass@rmandb select * from rc_database; select db_key, db_id from db;
reset database;
resync catalog;
allocate channel... delete backuppiece <number>; release channel;
backup database plus archivelog format '/u01/ora_backup/rman/%d_%u_%s';
run {
allocate channel t1 type disk;
backup current controlfile format '/u01/ora_backup/rman/%d_%u_%s';
backup database format '/u01/ora_backup/rman/%d_%u_%s';
backup archivelog all delete input format '/u01/ora_backup/rman/arch_%d_%u_%s';
release channel t1;
}
run {
allocate channel t1 type disk;
backup archivelog all delete input format '/u01/ora_backup/rman/arch_%d_%u_%s';
release channel t1;
}
run {
allocate channel t1 type disk;
shutdown immediate;
startup mount;
backup database include current controlfile format '/u01/ora_backup/rman/%d_%u_%s';
alter database open;
}
startup nomount;
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
restore controlfile;
restore archivelog all;
alter database mount;
restore database;
recover database;
}
sql 'alter database open resetlogs';
startup nomount;
run {
set until time ="to_date('30/08/2006 12:00','dd/mm/yyyy hh24:mi')";
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
restore controlfile;
restore archivelog all;
alter database mount;
restore database;
recover database;
}
sql 'alter database open resetlogs';
startup mount;
run {
set until time ="to_date('08/02/2007 14:00','dd/mm/yyyy hh24:mi')";
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
restore database;
recover database;
}
sql 'alter database open resetlogs';
startup mount;
run {
allocate channel t1 type disk;
recover database;
}
set pages 999 lines 100 col name format a60 break on set_stamp skip 1 select set_stamp , to_char(ba.completion_time, 'HH24:MI DD/MM/YY') finish_time , df.name from v$datafile df , v$backup_datafile ba where df.file# = ba.file# and ba.file# != 0 order by set_stamp, ba.file# /
list backupset; list backup of database; list backup of archivelog all; report obsolete; report obsolete redundancy = 2; delete obsolete; - remove unneeded backups restore database validate; - check the backup report unrecoverable; report schema; - show current db files crosscheck backup; - make sure the backups in the catalog still physically exist delete expired backup; - delete epired backups found by crosscheck rman target sys/*****@scr10 catalog rman/rman@dbarep LIST BACKUPSET OF DATABASE; ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK; DELETE OBSOLETE REDUNDANCY = 4 device type disk; delete obsolete REDUNDANCY = 2 device type disk;
DELETE NOPROMPT ARCHIVELOG UNTIL TIME "SYSDATE-5"
change archivelog all crosscheck;
startup nomount alter database mount standby database; alter database recover managed standby database disconnect;
alter database recover managed standby database cancel; alter database recover managed standby database nodelay disconnect;
alter database recover managed standby database cancel;
alter database register physical logfile '<fullpath/filename>';
alter database register or replace physical logfile '<fullpath/filename>';
shutdown immediate startup nomount alter database mount standby database; alter database recover automatic standby database;
shutdown immediate startup nomount alter database mount standby database; alter database recover managed standby database disconnect;
select local.thread#
, local.sequence# from
(select thread#
, sequence#
from v$archived_log
where dest_id=1) local
where local.sequence# not in
(select sequence#
from v$archived_log
where dest_id=2 and
thread# = local.thread#)
/
alter system set log_archive_dest_state_2 = 'defer'; alter system set log_archive_dest_state_2 = 'enable';
alter system set LOG_ARCHIVE_TRACE = 128;
alter system set dg_broker_start=false /
select database_role from v$database /
alter database stop logical standby apply;
alter database start logical standby apply;
set numwidth 15 select max(sequence#) current_seq from v$log /
set numwidth 15 select max(applied_seq#) last_seq from v$archive_dest_status /
set lines 100 set numwidth 15 column ID format 99 column "SRLs" format 99 column active format 99 col type format a4 select ds.dest_id id , ad.status , ds.database_mode db_mode , ad.archiver type , ds.recovery_mode , ds.protection_mode , ds.standby_logfile_count "SRLs" , ds.standby_logfile_active active , ds.archived_seq# from v$archive_dest_status ds , v$archive_dest ad where ds.dest_id = ad.dest_id and ad.status != 'INACTIVE' order by ds.dest_id /
set numwidth 8 lines 100 column id format 99 select dest_id id , archiver , transmit_mode , affirm , async_blocks async , net_timeout net_time , delay_mins delay , reopen_secs reopen , register,binding from v$archive_dest order by dest_id /
set lines 100 pages 999 col member format a70 select st.group# , st.sequence# , ceil(st.bytes / 1048576) mb , lf.member from v$standby_log st , v$logfile lf where st.group# = lf.group# /
set lines 100 pages 999 select snap_id , snap_level , to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin from dba_hist_snapshot order by 1 /
@?/rdbms/admin/awrrpt.sql
col snap_interval format a30 col retention format a30 select snap_interval , retention from dba_hist_wr_control /
exec dbms_workload_repository.modify_snapshot_settings (interval => 30)
exec dbms_workload_repository.modify_snapshot_settings (retention => 14*24*60)
exec dbms_workload_repository.create_snapshot
set lines 100 col baseline_name format a40 select baseline_id , baseline_name , start_snap_id , end_snap_id from dba_hist_baseline order by 1 /
exec dbms_workload_repository.create_baseline (<start snap>, <endsnap>,'<name>')
exec dbms_workload_repository.drop_baseline('<baseline name>')
exec dbms_scheduler.enable('GATHER_STATS_JOB')
exec dbms_scheduler.disable('GATHER_STATS_JOB')
set lines 100 pages 999 select stat_name , value from v$sys_time_model order by value desc /
set lines 100 pages 999 select stat_name , value from v$sess_time_model where sid = '&sid' order by value desc /
@?/rdbms/admin/ashrpt.sql
exec statspack.snap;
exec statspack.snap(i_snap_level => 6, i_modify_parameter => 'true');
@?/rdbms/admin/sppurge;
@?/rdbms/admin/spreport.sql
col "Date/Time" format a30 select snap_id , snap_level , to_char(snap_time,'HH24:MI:SS DD-MM-YYYY') "Date/Time" from stats$snapshot , v$database order by snap_id /
@?/rdbms/admin/spcreate
@?/rdbms/admin/spdrop
@?/rdbms/admin/spauto.sql
select job , what from dba_jobs /
exec dbms_job.remove(<job number>);
set lines 100 pages 999 col bytes format 999,999,999 compute sum of bytes on pool break on pool skip 1 select pool , name , bytes from v$sgastat order by pool , name /
select st.sid "SID", sn.name "TYPE", ceil(st.value / 1024 / 1024) "MB" from v$sesstat st, v$statname sn where st.statistic# = sn.statistic# and sid in (select sid from v$session where username like '&user') and upper(sn.name) like '%PGA%' order by st.sid, st.value desc /
select name , sum(bytes) from v$sgastat where pool like 'shared pool' group by name /
@?/rdbms/admin/utlxplan.sql
column plan_plus_exp format a100 set autotrace on explain # Displays the execution plan only. set autotrace traceonly explain # dont run the query set autotrace on # Shows the execution plan as well as statistics of the statement. set autotrace on statistics # Displays the statistics only. set autotrace traceonly # Displays the execution plan and the statistics
set autotrace off
explain plan for select ...
explain plan set statement_id = 'bad1' for select...
set lines 100 pages 999 @?/rdbms/admin/utlxpls
select hash_value, sql_text from v$sqlarea where sql_text like '%TIMINGLINKS%FOLDERREF%' /
select sql_text from v$sqlarea where hash_value = '&hash' /
Look at a query's stats in the sql area
select executions , cpu_time , disk_reads , buffer_gets , rows_processed , buffer_gets / executions from v$sqlarea where hash_value = '&hash' /
execute dbms_stats.gather_database_stats;
execute dbms_stats.gather_database_stats( - estimate_percent => 1, - method_opt => 'FOR ALL COLUMNS SIZE 1',- cascade => TRUE);
execute dbms_stats.gather_schema_stats('SCOTT');
execute dbms_stats.gather_schema_stats( - ownname => 'SCOTT', - estimate_percent => 1, - method_opt => 'FOR ALL COLUMNS SIZE 1',- cascade => TRUE);
execute dbms_stats.gather_schema_stats( - ownname => 'SYS', - cascade => TRUE);
exec dbms_stats.gather_table_stats('<owner>', '<table_name>');
exec dbms_stats.delete_database_stats;
exec dbms_stats.delete_schema_stats('SCOTT');
execute dbms_stats.gather_system_stats('Start');
execute dbms_stats.gather_system_stats('Stop');
select pname , pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN' /
exec dbms_stats.create_stat_table(ownname => 'SYS',- stattab => 'prod_stats', tblspace => 'USERS');
exec dbms_stats.export_schema_stats(ownname => 'SCOTT',- statown=>'SYS', stattab=>'prod_stats');
exec dbms_stats.import_schema_stats(ownname => 'SCOTT',- statown=>'SYS', stattab=>'prod_stats');
analyze table employees compute statistics;
analyze index employees_ind compute statistics;
set pages 999 lines 100
select a.owner
, a.total_tables tables
, nvl(b.analyzed_tables,0) analyzed
from (select owner
, count(*) total_tables
from dba_tables
group by owner) a
, (select owner
, count(last_analyzed) analyzed_tables
from dba_tables
where last_analyzed is not null
group by owner) b
where a.owner = b.owner (+)
and a.owner not in ('SYS', 'SYSTEM')
order by a.total_tables - nvl(b.analyzed_tables,0) desc
/
select count(last_analyzed) left_to_do from dba_tables where owner = '&schema' and trunc(last_analyzed) < trunc(sysdate) order by 1 /
col owner format a20 trunc col object_name format a30 col touches format 9,999,999 select * from ( select count(*) , sum(tch) TOUCHES , u.name OWNER , o.name OBJECT_NAME from x$bh x , obj$ o , user$ u where x.obj = o.obj# and o.owner# = u.user# group by u.name, o.name order by 2 desc ) where rownum < 11 /
col name format a60 select name , count from x$kcbfwait , v$datafile where indx + 1 = file# order by 2 /
select object_name , obj# , statistic_name , value from v$segment_statistics where owner like '&owner' and statistic_name like '%waits%' and value > 0 order by statistic_name , value desc /
col event format a30 select event , time_waited , round(time_waited*100/ SUM (time_waited) OVER(),2) wait_pct from ( select event , time_waited from v$system_event where event not in ( 'Null event' ,'client message' ,'rdbms ipc reply' ,'smon timer' ,'rdbms ipc message' ,'PX Idle Wait' ,'PL/SQL lock timer' ,'file open' ,'pmon timer' ,'WMON goes to sleep' ,'virtual circuit status' ,'dispatcher timer' ,'SQL*Net message from client' ,'parallel query dequeue wait' ,'pipe get') union ( select name , value from v$sysstat where name like 'CPU used when call started' ) ) order by 2 desc /
col event format a30 select event , total_waits , time_waited from v$system_event where event like '%wait%' order by 2,3 /
select * from v$waitstat /
Then run this to identify the file, block and reason code...select p1 "File #" , p2 "Block #" , p3 "Reason Code" from v$session_wait where event = 'buffer busy waits' /Note. You might need to run this a few times before anything is displayed.Look at the performance stats for the instance
select n.name , s.value from v$statname n , v$sysstat s where n.statistic# = s.statistic# order by n.class , n.name /
select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY', 19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB', 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE', 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE', 32, 'INDEXTYPE', 33, 'OPERATOR', 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION', 40, 'LOB PARTITION', 41, 'LOB SUBPARTITION', 42, 'MATERIALIZED VIEW', 43, 'DIMENSION', 44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN', 48, 'CONSUMER GROUP', 51, 'SUBSCRIPTION', 52, 'LOCATION', 55, 'XML SCHEMA', 56, 'JAVA DATA', 57, 'SECURITY PROFILE', 59, 'RULE', 62, 'EVALUATION CONTEXT','UNDEFINED' ) object_type , lob.kglnaobj object_name , pn.kglpnmod lock_mode_held , pn.kglpnreq lock_mode_requested , ses.sid , ses.serial# , ses.username from v$session_wait vsw , x$kglob lob , x$kglpn pn , v$session ses where vsw.event = 'library cache lock' and vsw.p1raw = lob.kglhdadr and lob.kglhdadr = pn.kglpnhdl and pn.kglpnmod != 0 and pn.kglpnuse = ses.saddr /
select ses.username , ddl.session_id , ses.serial# , owner || '.' || ddl.name object , ddl.type , ddl.mode_held from dba_ddl_locks ddl , v$session ses where owner like '%userid%' and ddl.session_id = ses.sid /
select 'SID ' || l1.sid ||' is blocking ' || l2.sid blocking from v$lock l1, v$lock l2 where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2 /
set lines 100 pages 999
col username format a20
col sess_id format a10
col object format a25
col mode_held format a10
select oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id
, owner || '.' || object_name object
, object_type
, decode( l.block
, 0, 'Not Blocking'
, 1, 'Blocking'
, 2, 'Global') status
, decode(v.locked_mode
, 0, 'None'
, 1, 'Null'
, 2, 'Row-S (SS)'
, 3, 'Row-X (SX)'
, 4, 'Share'
, 5, 'S/Row-X (SSX)'
, 6, 'Exclusive', TO_CHAR(lmode)) mode_held
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
order by oracle_username
, session_id
/
select do.object_name
, row_wait_obj#
, row_wait_file#
, row_wait_block#
, row_wait_row#
, dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
from v$session s
, dba_objects do
where sid=&sid
and s.ROW_WAIT_OBJ# = do.OBJECT_ID
/
Then select the row with that rowid...select * from <table> where rowid=<rowid>;
List locks
column lock_type format a12
column mode_held format a10
column mode_requested format a10
column blocking_others format a20
column username format a10
SELECT session_id
, lock_type
, mode_held
, mode_requested
, blocking_others
, lock_id1
FROM dba_lock l
WHERE lock_type NOT IN ('Media Recovery', 'Redo Thread')
/
select sql_text from v$sqlarea where users_executing > 0 /
select sn.name , st.value from v$sesstat st , v$statname sn where st.STATISTIC# = sn.STATISTIC# and st.VALUE > 0 and st.SID = &SID order by value desc /
select sql_text , executions , to_char((((disk_reads+buffer_gets)/executions) * 8192)/1048576, '9,999,999,990.00') as total_gets_per_exec_mb , to_char((( disk_reads /executions) * 8192)/1048576, '9,999,999,990.00') as disk_reads_per_exec_mb , to_char((( buffer_gets /executions) * 8192)/1048576, '9,999,999,990.00') as buffer_gets_per_exec_mb , parsing_user_id from v$sqlarea where executions > 10 order by 6 desc /
set lines 80 pages 999 col fname heading "File Name" format a60 col sizemb heading "Size(Mb)" format 99,999 col phyrds heading "Reads" format 999,999,999 col readtim heading "Time" format 99.999 col phywrts heading "Writes" format 9,999,999 col writetim heading "Time" format 99.999 select lower(name) fname , (bytes / 1048576) sizemb , phyrds , readtim , phywrts , writetim from v$datafile df , v$filestat fs where df.file# = fs.file# order by 1 /
exec dbms_system.set_sql_trace_in_session (<sid>, <serial#>, true);
exec dbms_system.set_sql_trace_in_session (<sid>, <serial#>, false);
alter session set events '10046 trace name context forever, level 8';
alter session set events '10046 trace name context off';
select avg(row_count) avg , max(row_count) max , min(row_count) min from ( select count(*) row_count from &table_name group by substr(rowid, 1, 15) ) /
set lines 100 pages 999 col est_mb format 99,999 col estd_physical_reads format 999,999,999,999,999 select size_for_estimate est_mb , estd_physical_read_factor , estd_physical_reads from v$db_cache_advice where name = 'DEFAULT' order by size_for_estimate /
alter system set db_cache_advice=on;
set lines 100 pages 999 select trigger_name , trigger_type , table_name , status from dba_triggers where owner = '&owner' order by status, table_name /
set lines 100 pages 999 select trigger_name , trigger_type , status from dba_triggers where owner = '&owner' and table_name = '&table' order by status, trigger_name /
alter trigger <trigger_name> enable /
alter trigger <trigger_name> disable /
set pages 999 lines 100 col table_name format a40 select table_name , partitioning_type type , partition_count partitions from dba_part_tables where owner = '&owner' order by 1 /
set pages 999 lines 100 col high_value format a20 col tablespace_name format a20 select partition_name , tablespace_name , high_value from dba_tab_partitions where table_owner = '&owner' and table_name = '&table_name' order by partition_position /
set pages 999 lines 100 col tablespace_name format a20 col num_rows format 999,999,999 select p.partition_name , p.tablespace_name , p.num_rows , ceil(s.bytes / 1024 / 1204) mb from dba_tab_partitions p , dba_segments s where p.table_owner = s.owner and p.partition_name = s.partition_name and p.table_name = s.segment_name and p.table_owner = '&owner' and p.table_name = '&table_name' order by partition_position /
alter table <table_name> move partition <partition_name> tablespace <tablespace_name> nologging /
alter table <table_name> add partition <partition_name> values less than (<value>) tablespace <tablespace_name> /
alter table <table_name> add partition <partition_name> values (<value>) tablespace <tablespace_name> /
alter table <table_name> split partition <partition_name> at (<value>) into (partition <partition_name>, partition <partition_name>) update global indexes /
alter table <table_name> drop partition <partition_name> /
alter table <table_name> truncate partition <partition_name> /
create materialized view log on <table> /
create materialized view log on <table> tablespace <tablespace_name> /
select log_owner,log_table from dba_mview_logs /
create materialized view andy_mview refresh [fast | complete | force] start with sysdate next sysdate + 1/24 with primary key as select * from test_table /
set lines 100 pages 999 col last_refresh format a20 select owner , mview_name , to_char(last_refresh_date, 'dd/mm/yy hh24:mi') last_refresh from dba_mviews order by owner, last_refresh /Output:
set lines 100 col mview format a40 col master format a40 select owner || '.' || name mview , master_owner || '.' || master master from dba_mview_refresh_times /Output:
set lines 100 col job format 9999 col log_user format a15 col last format a15 col next format a15 col fail format 9999 col what format a20 select job , log_user , to_char(last_date, 'dd/mm/yy hh24:mi') last , to_char(next_date, 'dd/mm/yy hh24:mi') next , failures fail , replace(what, '"') what from dba_jobs where what like '%dbms_refresh.refresh%' /
col type format a10 col cons_name format a30 select decode(constraint_type, 'C', 'Check', 'O', 'R/O View', 'P', 'Primary', 'R', 'Foreign', 'U', 'Unique', 'V', 'Check view') type , constraint_name cons_name , status , last_change from dba_constraints where owner like '&owner' and table_name like '&table_name' order by 1 / Output:
Enter value for owner: ANDY old 12: where owner like '&owner' new 12: where owner like 'ANDY' Enter value for table_name: LOCATION old 13: and table_name like '&table_name' new 13: and table_name like 'LOCATION' TYPE CONS_NAME STATUS LAST_CHAN ---------- ------------------------------ -------- --------- Primary LOCATION_PK ENABLED 15-OCT-07 1 row selected.
set lines 100 pages 999 select a.owner , a.table_name , a.constraint_name from dba_constraints a , dba_constraints b where a.constraint_type = 'R' and a.r_constraint_name = b.constraint_name and a.r_owner = b.owner and b.owner = '&table_owner' and b.table_name = '&table_name' / Output:
Enter value for table_owner: ANDY old 9: and b.owner = '&table_owner' new 9: and b.owner = 'ANDY' Enter value for table_name: LOCATION old 10: and b.table_name = '&table_name' new 10: and b.table_name = 'LOCATION' OWNER TABLE_NAME CONSTRAINT_NAME --------------- ------------------------------ ------------------------------ ANDY BOX BOX_LOCATION_FK 1 row selected. Same as above, but produces 'disable constraint' statementsset lines 100 pages 999 col discon format a100 select 'alter table '||a.owner||'.'||a.table_name||' disable constraint '||a.constraint_name||';' discon from dba_constraints a , dba_constraints b where a.constraint_type = 'R' and a.r_constraint_name = b.constraint_name and a.r_owner = b.owner and b.owner = '&table_owner' and b.table_name = '&table_name' /Produce a list of disabled fk constraints
set lines 100 pages 999 col table format a60 col constraint_name format a30 select owner||'.'||table_name "table" , constraint_name from dba_constraints where status = 'DISABLED' and constraint_type = 'R' and owner not in ('SYS','SYSTEM') order by 1,2 /Produce enable statements all disabled fk constraints
set lines 100 pages 999 select 'alter table '||owner||'.'||table_name||' enable constraint '||constraint_name||';' "enable" from dba_constraints where status = 'DISABLED' and constraint_type = 'R' and owner not in ('SYS','SYSTEM') order by 1 /List parent tables that may need fixing/re-importing
select distinct r.owner || '.' || r.table_name "exp" from dba_constraints c , dba_constraints r where c.status = 'DISABLED' and c.constraint_type = 'R' and c.r_owner = r.owner and c.r_constraint_name = r.constraint_name and c.owner not in ('SYS','SYSTEM') order by 1 /List missing foriegn key values
select 'select '||cc.column_name- ||' from '||c.owner||'.'||c.table_name- ||' a where not exists (select ''x'' from '- ||r.owner||'.'||r.table_name- ||' where '||rc.column_name||' = a.'||cc.column_name||')' from dba_constraints c, dba_constraints r, dba_cons_columns cc, dba_cons_columns rc where c.constraint_type = 'R' and c.owner not in ('SYS','SYSTEM') and c.r_owner = r.owner and c.owner = cc.owner and r.owner = rc.owner and c.constraint_name = cc.constraint_name and r.constraint_name = rc.constraint_name and c.r_constraint_name = r.constraint_name and cc.position = rc.position and c.owner = '&table_owner' and c.table_name = '&table_name' and c.constraint_name = '&constraint_name' order by c.owner, c.table_name, c.constraint_name, cc.position /Show all table constraints for a user
set lines 100 pages 999 break on table_name select table_name , decode(constraint_type, 'C', 'Check', 'O', 'R/O View', 'P', 'Primary', 'R', 'Foreign', 'U', 'Unique', 'V', 'Check view') type , nvl(index_name, R_CONSTRAINT_NAME) "IDX" from dba_constraints where owner like '&user' order by table_name , decode(constraint_type, 'P','0','R','1','U','2','C','3','O','4','V','5') /Output:Enter value for user: ANDY old 11: where owner like '&user' new 11: where owner like 'ANDY' TABLE_NAME TYPE IDX ------------------------------ ---------- ------------------------------ BOX Primary BOX_PK Foreign LOCATION_PK CD Primary CD_PK Foreign BOX_PK LOCATION Primary LOCATION_PK LOTS_OF_ROWS Primary SYS_C0023467 TEST_TABLE Primary SYS_C0027234 TRACK Primary TRACK_PK Foreign CD_PK 9 rows selected.
select segment_name , status from dba_rollback_segs / Output:
alter system set undo_retention=500 scope=memory;
select tablespace_name , status , count(*) as HOW_MANY from dba_undo_extents group by tablespace_name , status / Output:
TABLESPACE_NAME STATUS HOW_MANY ---------------------------------------- -------- ---------- UNDOTBS2 EXPIRED 129 UNDOTBS2 UNEXPIRED 3 2 rows selected.
set lines 100 pages 999 col username format a15 col command format a20 select ses.username , substr(ses.program, 1, 19) command , tra.used_ublk from v$session ses , v$transaction tra where ses.saddr = tra.ses_addr / Output:
alter database drop logfile member '<filename>';
alter database add logfile member '<filename>' to group <group>;
col member format a60 col "Size MB" format 9,999,999 select lf.member , ceil(lg.bytes / 1024 / 1024) "Size MB" from v$logfile lf , v$log lg where lg.group# = lf.group# order by 1 / Output: