Handy SQLPlus scripts and PL/SQL blocks for common DBA tasks. Copy/paste into SQLPlus as needed.
col member for a56
set pagesize 299
set lines 299
select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) "Size (MB)"
from v$log l, v$logfile f
where f.group# = l.group#
order by 1,2 ;
set heading off;
set echo off;
Set pages 999;
set long 90000;
spool ddl_tablespace.sql
select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;
spool off
set feedback off pages 0 long 900000 lines 20000 pagesize 20000 serveroutput on
accept USERNAME prompt "Enter username :"
-- This line adds a semicolon at the end of each statement
execute dbms_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
-- This will generate the DDL for the user and add his objects, system and role grants
SELECT DBMS_METADATA.GET_DDL('USER',username) as script from DBA_USERS where username='&username'
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',grantee) as script from DBA_SYS_PRIVS
where grantee='&username' and rownum=1
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',grantee) as script from DBA_ROLE_PRIVS
where grantee='&username' and rownum=1
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',grantee) as script from DBA_TAB_PRIVS
where grantee='&username' and rownum=1;
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) - round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes from v$datafile
union all
select bytes from v$tempfile
union all
select bytes from v$log) used
, (select sum(bytes) as p from dba_free_space) free
group by free.p
/
Set lines 2000
col NAME for a45
col DESCRIPTION for a100
SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\'
/
set lines 200
COL ACL_OWNER FOR A12
COL ACL FOR A67
COL HOST FOR A34
col PRINCIPAL for a20
col PRIVILEGE for a13
select ACL_OWNER,ACL,HOST,LOWER_PORT,UPPER_PORT FROM dba_network_acls;
select ACL_OWNER,ACL,PRINCIPAL,PRIVILEGE from dba_network_acl_privileges;
set lines 299
SELECT TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') "DG Date",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') "12AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') "01AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') "02AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') "03AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') "04AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') "05AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') "06AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') "07AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') "08AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') "09AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') "10AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') "11AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') "12PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') "1PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') "2PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') "3PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') "4PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') "5PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') "6PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') "7PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') "8PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') "9PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') "10PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') "11PM"
FROM V$LOG_HISTORY
GROUP BY TRUNC(FIRST_TIME)
ORDER BY TRUNC(FIRST_TIME) DESC
/
col name format a10
col username format a8
col osuser format a8
col start_time format a17
col status format a12
tti 'Active transactions'
select s.sid,username,t.start_time, r.name, t.used_ublk "USED BLKS",
decode(t.space, 'YES', 'SPACE TX',
decode(t.recursive, 'YES', 'RECURSIVE TX',
decode(t.noundo, 'YES', 'NO UNDO TX', t.status))) status
from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
where t.xidusn = r.usn
and t.ses_addr = s.saddr
/
-- Return code 1017 ( INVALID LOGIN ATTEMPT)
-- Return code 28000 ( ACCOUNT LOCKED)
set pagesize 1299
set lines 299
col username for a15
col userhost for a13
col timestamp for a39
col terminal for a23
SELECT username,userhost,terminal,timestamp,returncode
FROM dba_audit_session
WHERE username='&USER_NAME' and returncode in (1017,28000);
Reference: My Oracle Support Doc ID 332494.1
-- Save as duplicate.sql and run as @duplicate.sql
set echo off
set verify off heading off
undefine t
undefine c
prompt
prompt Enter name of table with duplicate rows
accept t prompt 'Table: '
prompt
select 'Table '||upper('&&t') from dual;
describe &&t
prompt
prompt Enter name(s) of column(s) which should be unique. If more than
prompt one column is specified, you MUST separate with commas.
accept c prompt 'Column(s): '
prompt
select &&c from &&t
where rowid not in (select min(rowid) from &&t group by &&c)
/
select to_char(creation_time, 'MM-RRRR') "Month",
sum(bytes)/1024/1024/1024 "Growth in GB
from sys.v_$datafile
where to_char(creation_time,'RRRR')='&YEAR_IN_YYYY_FORMAT'
group by to_char(creation_time, 'MM-RRRR')
order by to_char(creation_time, 'MM-RRRR');
select 'alter database datafile'||' '''||file_name||''''||' resize '||round(highwater+2)||' '||'m'||';' from (
select /*+ rule */
a.tablespace_name,
a.file_name,
a.bytes/1024/1024 file_size_MB,
(b.maximum+c.blocks-1)*d.db_block_size/1024/1024 highwater
from dba_data_files a ,
(select file_id,max(block_id) maximum from dba_extents group by file_id) b,
dba_extents c,
(select value db_block_size from v$parameter where name='db_block_size') d
where a.file_id= b.file_id
and c.file_id = b.file_id
and c.block_id = b.maximum
order by a.tablespace_name,a.file_name);
select to_char(startup_time, 'DD-MM-YYYY HH24:MI:SS'), floor(sysdate-startup_time) DAYS from v$Instance;
-- Get current scn value:
select current_scn from v$database;
-- Get scn value at particular time:
select timestamp_to_scn('19-JAN-08:22:00:10') from dual;
-- Get timestamp from scn:
select scn_to_timestamp(224292) from dual;
---- Connect to the user and run this.
BEGIN
FOR i IN (SELECT trigger_name FROM user_triggers) LOOP
EXECUTE IMMEDIATE 'ALTER TRIGGER ' || i.trigger_name || ' DISABLE';
END LOOP;
END;
/
select table_name,
to_number(extractvalue(dbms_xmlgen.getXMLtype('select /*+ PARALLEL(8) */ count(*) cnt from "&&SCHEMA_NAME".'||table_name),'/ROWSET/ROW/CNT')) rows_in_table
from dba_TABLES
where owner='&&SCHEMA_NAME';
-- We can spool output of an sql query to html format:
set pages 5000
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>EMPLOYEE REPORT</TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: #FFFFC6} --> -
</STYLE>" -
BODY "TEXT='#FF00Ff'" -
TABLE "WIDTH='90%' BORDER='5'"
spool report.html
Select * from scott.emp;
spool off
Exit
-- First enable monitoring usage for the indexes.
alter index siebel.S_ASSET_TEST monitoring usage;
-- Below query to find the index usage:
select * from v$object_usage;
--- From 12c onward
set lines 2000
select patch_id,status,description from dba_registry_sqlpatch;
--- For 11g and below:
set lines 2000
select * from dba_registry_history;
-- Find the orphaned DataPump jobs:
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$%' and state='NOT RUNNING'
ORDER BY 1,2;
-- Drop the tables
SELECT 'drop table ' || owner_name || '.' || job_name || ';'
FROM dba_datapump_jobs WHERE state='NOT RUNNING' and job_name NOT LIKE 'BIN$%'
set pagesize 299
set lines 299
col value for a65
select * from v$diag_info where NAME='Diag Trace';
col comp_id for a10
col comp_name for a56
col version for a12
col status for a10
set pagesize 200
set lines 200
set long 999
select comp_id,comp_name,version,status from dba_registry;
set pagesize 200
set lines 200
select parameter,value from v$nls_parameters where parameter like 'NLS_%CHAR%';
-- View current AWR retention period
select retention from dba_hist_wr_control;
-- Modify retention period to 7 days and interval to 30 min
select dbms_workload_repository.modify_snapshot_settings (interval => 30, retention => 10080);
-- NOTE - 7 DAYS = 7*24*3600= 10080 minutes
SELECT d.undo_size / (1024 * 1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value, 1, 25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) / (1024 * 1024) "NEEDED UNDO SIZE [MByte]"
FROM (SELECT SUM(a.bytes) undo_size
FROM gv$datafile a, gv$tablespace b, dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#) d,
gv$parameter e,
gv$parameter f,
(SELECT MAX(undoblks / ((end_time - begin_time) * 3600 * 24)) undo_block_per_sec FROM v$undostat) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size';
-- Find the AWR snapshot details.
select snap_id,begin_interval_time,end_interval_time from sys.wrm$_snapshot order by snap_id;
-- Purge snapshot between snapid 612 to 700
execute dbms_workload_repository.drop_snapshot_range(low_snap_id =>612 , high_snap_id =>700);
-- Verify again
select snap_id,begin_interval_time,end_interval_time from sys.wrm$_snapshot order by snap_id;
-- Check the current moving window baseline size:
select BASELINE_TYPE,MOVING_WINDOW_SIZE from dba_hist_baseline;
-- Modify window_size to (7 days):
execute dbms_workload_repository.modify_baseline_window_size(window_size=> 7);
set pagesize 200
set lines 200
col db_link for a19
set long 999
SELECT db_link,
owner_id,
logged_on,
heterogeneous,
open_cursors,
in_transaction,
update_sent
FROM gv$dblink
ORDER BY db_link;
SELECT le.leseq "Current log sequence No",
100*cp.cpodr_bno/le.lesiz "Percent Full",
cp.cpodr_bno "Current Block No",
le.lesiz "Size of Log in Blocks"
FROM x$kcccp cp, x$kccle le
WHERE le.leseq = CP.cpodr_seq
AND bitand(le.leflg,24) = 8
/
-- where trunc(BEGIN_INTERVAL_TIME)=trunc(sysdate-&no)
-- order by 1;
CREATE OR REPLACE DIRECTORY awr_reports_dir AS '/tmp/awrreports';
DECLARE
-- Adjust before use.
l_snap_start NUMBER := 4884; -- Specify Initial Snap ID
l_snap_end NUMBER := 4892; -- Specify End Snap ID
l_dir VARCHAR2(50) := 'AWR_REPORTS_DIR';
l_last_snap NUMBER := NULL;
l_dbid v$database.dbid%TYPE;
l_instance_number v$instance.instance_number%TYPE;
l_file UTL_FILE.file_type;
l_file_name VARCHAR(50);
BEGIN
SELECT dbid INTO l_dbid FROM v$database;
SELECT instance_number INTO l_instance_number FROM v$instance;
FOR cur_snap IN (SELECT snap_id
FROM dba_hist_snapshot
WHERE instance_number = l_instance_number
AND snap_id BETWEEN l_snap_start AND l_snap_end
ORDER BY snap_id)
LOOP
IF l_last_snap IS NOT NULL THEN
l_file := UTL_FILE.fopen(l_dir, 'awr_' || l_last_snap || '_' || cur_snap.snap_id || '.html', 'w', 32767);
FOR cur_rep IN (SELECT output
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(l_dbid, l_instance_number, l_last_snap, cur_snap.snap_id)))
LOOP
UTL_FILE.put_line(l_file, cur_rep.output);
END LOOP;
UTL_FILE.fclose(l_file);
END IF;
l_last_snap := cur_snap.snap_id;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/
select * from (
select c.table_name, co.column_name, co.position column_position
from user_constraints c, user_cons_columns co
where c.constraint_name = co.constraint_name
and c.constraint_type = 'R'
minus
select ui.table_name, uic.column_name, uic.column_position
from user_indexes ui, user_ind_columns uic
where ui.index_name = uic.index_name
)
order by table_name, column_position;
select
a.constraint_name cons_name
,a.table_name tab_name
,b.column_name cons_column
,nvl(c.column_name,'***No Index***') ind_column
from user_constraints a
join user_cons_columns b on a.constraint_name = b.constraint_name
left outer join user_ind_columns c on b.column_name = c.column_name
and b.table_name = c.table_name
where constraint_type = 'R'
order by 2,1;
set pagesize 200
set lines 200
col name for a21
col stat_name for a25
col value for a13
col comments for a56
select STAT_NAME,to_char(VALUE) as VALUE ,COMMENTS from v$osstat
where stat_name IN ('NUM_CPUS','NUM_CPU_CORES','NUM_CPU_SOCKETS')
union
select STAT_NAME,VALUE/1024/1024/1024 || ' GB' ,COMMENTS from v$osstat
where stat_name IN ('PHYSICAL_MEMORY_BYTES');
set heading off
set feedback off
select 'Incarnation Destination Configuration' from dual;
select '*************************************' from dual;
set heading on
set feedback on
select INCARNATION# INC#, RESETLOGS_CHANGE# RS_CHANGE#, RESETLOGS_TIME,
PRIOR_RESETLOGS_CHANGE# PRIOR_RS_CHANGE#, STATUS,
FLASHBACK_DATABASE_ALLOWED FB_OK
from v$database_incarnation;
SELECT version FROM v$timezone_file;
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
SELECT DATABASE_ROLE,
DB_UNIQUE_NAME INSTANCE,
OPEN_MODE,
PROTECTION_MODE,
PROTECTION_LEVEL,
SWITCHOVER_STATUS
FROM V$DATABASE;
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS
FROM V$MANAGED_STANDBY;
SELECT MESSAGE FROM V$DATAGUARD_STATUS;
SELECT 'Last Log applied : ' AS Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') AS Time
FROM v$archived_log
WHERE sequence# = (SELECT MAX(sequence#)
FROM v$archived_log
WHERE applied='YES')
UNION
SELECT 'Last Log received : ' AS Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') AS Time
FROM v$archived_log
WHERE sequence# = (SELECT MAX(sequence#) FROM v$archived_log);
SET LINES 100 PAGES 999
COL member FORMAT a70
SELECT st.group#,
st.sequence#,
CEIL(st.bytes / 1048576) AS mb,
lf.member
FROM v$standby_log st,
v$logfile lf
WHERE st.group# = lf.group#
/
COLUMN applied_time FOR a30
SET LINESIZE 140
SELECT TO_CHAR(SYSDATE,'mm-dd-yyyy hh24:mi:ss') AS "Current Time" FROM dual;
SELECT DB_NAME, APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED AS LOG_GAP,
(CASE WHEN ((APPLIED_TIME IS NOT NULL AND (LOG_ARCHIVED-LOG_APPLIED) IS NULL) OR
(APPLIED_TIME IS NULL AND (LOG_ARCHIVED-LOG_APPLIED) IS NOT NULL) OR
((LOG_ARCHIVED-LOG_APPLIED) > 5))
THEN 'Error! Log Gap is '
ELSE 'OK!'
END) AS Status
FROM
( SELECT INSTANCE_NAME DB_NAME FROM GV$INSTANCE WHERE INST_ID = 1 ),
( SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG
WHERE DEST_ID=1 AND ARCHIVED='YES' AND THREAD#=1 ),
( SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG
WHERE DEST_ID=2 AND APPLIED='YES' AND THREAD#=1 ),
( SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' AND THREAD#=1 )
UNION
SELECT DB_NAME, APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED AS LOG_GAP,
(CASE WHEN ((APPLIED_TIME IS NOT NULL AND (LOG_ARCHIVED-LOG_APPLIED) IS NULL) OR
(APPLIED_TIME IS NULL AND (LOG_ARCHIVED-LOG_APPLIED) IS NOT NULL) OR
((LOG_ARCHIVED-LOG_APPLIED) > 5))
THEN 'Error! Log Gap is '
ELSE 'OK!'
END) AS Status
FROM
( SELECT INSTANCE_NAME DB_NAME FROM GV$INSTANCE WHERE INST_ID = 2 ),
( SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG
WHERE DEST_ID=1 AND ARCHIVED='YES' AND THREAD#=2 ),
( SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG
WHERE DEST_ID=2 AND APPLIED='YES' AND THREAD#=2 ),
( SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' AND THREAD#=2 )
/
SELECT TO_CHAR(START_TIME,'DD-MON-YYYY HH24:MI:SS') AS "Recovery Start Time",
TO_CHAR(item)||' = '||TO_CHAR(sofar)||CHR(10)||TO_CHAR(units) AS "Progress"
FROM v$recovery_progress
WHERE start_time = (SELECT MAX(start_time) FROM v$recovery_progress);
-- Cancel MRP (media recovery) on standby:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
-- Start MRP (media recovery):
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
-- Start real-time apply:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
-- 1) Check child_number and plan_hash_value
SELECT sql_id, child_number, plan_hash_value
FROM gv$sql
WHERE sql_id='9n2a2c8pvu6bm';
-- 2) Display cursor plan for specific child
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sqlid', &child_number));
SET LINES 200
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));
COL sql_text FOR a80
SET LINES 120
SELECT sql_text
FROM gv$sqltext
WHERE hash_value = (SELECT sql_hash_value
FROM gv$session
WHERE sid=&1 AND inst_id=&inst_id)
ORDER BY piece;
-- Generate explain plan
EXPLAIN PLAN FOR
SELECT COUNT(*) FROM dbaclass;
-- View explain plan
SELECT * FROM TABLE(dbms_xplan.display);
-- SYNTAX:
-- SELECT * FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'<SQL BASELINE NAME>'));
SELECT * FROM TABLE(
DBMS_XPLAN.display_sql_plan_baseline(
plan_name=>'SQL_PLAN_gbhrw1v44209a5b2f7514'
)
);
SELECT
sql_id,
b.last_captured,
t.sql_text,
b.hash_value,
b.name AS bind_name,
b.value_string AS bind_value
FROM gv$sql t
JOIN gv$sql_bind_capture b USING (sql_id)
WHERE b.value_string IS NOT NULL
AND sql_id='&sqlid'
/
-- Find address & hash_value
SELECT address, hash_value
FROM v$sqlarea
WHERE sql_id LIKE '5qd8a442c328k';
-- Purge from shared pool
EXEC DBMS_SHARED_POOL.PURGE('C000007067F39FF0, 4000666812', 'C');
-- Note: On RAC, execute on all instances.
ALTER SYSTEM SET events 'sql_trace [sql:8krc88r46raff]';
BEGIN
dbms_sqldiag.dump_trace(
p_sql_id => 'dmx08r6ayx800',
p_child_number => 0,
p_component => 'Compiler',
p_file_id => 'TEST_OBJ3_TRC'
);
END;
/
EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>321, serial#=>1234, sql_trace=>FALSE);
-- Get tracefile name
SELECT p.tracefile
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.sid = 321;
SELECT module,
parsing_schema_name,
inst_id,
sql_id,
plan_hash_value,
child_number,
sql_fulltext,
TO_CHAR(last_active_time,'DD/MM/YY HH24:MI:SS') AS last_active_time,
sql_plan_baseline,
executions,
elapsed_time/executions/1000/1000 AS avg_elapsed_s,
rows_processed
FROM gv$sql
WHERE sql_id IN ('&sql_id');
SET LINES 2000
SELECT SID, b.NAME, ROUND(a.VALUE/(1024*1024),2) MB
FROM v$sesstat a, v$statname b
WHERE (b.NAME LIKE '%session uga memory%' OR b.NAME LIKE '%session pga memory%')
AND a.statistic# = b.statistic#
ORDER BY MB DESC;
SET PAGESIZE 200
SET LINESIZE 120
COL segment_name FORMAT a30
COL owner FORMAT a10
SELECT segment_name, object_type, total_physical_reads
FROM (
SELECT owner||'.'||object_name AS segment_name,
object_type,
value AS total_physical_reads
FROM v$segment_statistics
WHERE statistic_name = 'physical reads'
ORDER BY total_physical_reads DESC
)
WHERE ROWNUM <= 10;
SELECT SUBSTR(t.name,1,50) AS file_name,
f.phyblkrd AS blocks_read,
f.phyblkwrt AS blocks_written,
f.phyblkrd + f.phyblkwrt AS total_io
FROM v$tempstat f,
v$tempfile t
WHERE t.file# = f.file#
ORDER BY total_io DESC;
SELECT * FROM (
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
a.inst_id AS Instance,
a.sid||','||a.serial# AS sid_serial,
NVL(a.username, '(oracle)') AS username,
a.program,
a.status,
a.sql_id
FROM gv$session a,
gv$sort_usage b,
gv$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
AND a.inst_id = b.inst_id
AND a.inst_id = p.inst_id
ORDER BY ((b.blocks*p.value)/1024/1024) DESC
) WHERE ROWNUM < 10;
SELECT ROUND(used.bytes /1024/1024 ,2) AS used_mb,
ROUND(free.bytes /1024/1024 ,2) AS free_mb,
ROUND(tot.bytes /1024/1024 ,2) AS total_mb
FROM (SELECT SUM(bytes) bytes FROM v$sgastat WHERE name != 'free memory') used,
(SELECT SUM(bytes) bytes FROM v$sgastat WHERE name = 'free memory') free,
(SELECT SUM(bytes) bytes FROM v$sgastat) tot
/
-- Window: SYSDATE-1 to SYSDATE-23/24 (adjust as needed)
SELECT ash.user_id,
u.username,
sa.sql_text,
SUM(ash.wait_time + ash.time_waited)/1000000 AS ttl_wait_time_in_seconds
FROM v$active_session_history ash,
v$sqlarea sa,
dba_users u
WHERE ash.sample_time BETWEEN SYSDATE - 1 AND SYSDATE - 23/24
AND ash.sql_id = sa.sql_id
AND ash.user_id = u.user_id
AND u.username NOT IN ('SYS','DBSNMP')
GROUP BY ash.user_id, sa.sql_text, u.username
ORDER BY 4 DESC
/
SET PAGESIZE 50
SET LINESIZE 120
COL sql_id FORMAT a15
COL inst_id FORMAT 9
COL sql_text FORMAT a50
COL module FORMAT a10
COL blocker_ses FORMAT '999999'
COL blocker_ser FORMAT '999999'
SELECT DISTINCT
a.sql_id,
a.inst_id,
a.blocking_session AS blocker_ses,
a.blocking_session_serial# AS blocker_ser,
a.user_id,
s.sql_text,
a.module,
a.sample_time
FROM gv$active_session_history a
JOIN gv$sql s ON a.sql_id = s.sql_id
WHERE a.blocking_session IS NOT NULL
AND a.user_id <> 0 -- exclude SYS user
AND a.sample_time BETWEEN SYSDATE - 1 AND SYSDATE - 23/24
/
COL program FORM a30 HEADING "Program"
COL CPUMins FORM 99990 HEADING "CPU in Mins"
SELECT ROWNUM AS rank, a.*
FROM (
SELECT v.sid,
sess.program,
v.value / (100 * 60) AS CPUMins
FROM v$statname s
JOIN v$sesstat v ON v.statistic# = s.statistic#
JOIN v$session sess ON sess.sid = v.sid
WHERE s.name = 'CPU used by this session'
AND v.value > 0
ORDER BY v.value DESC
) a
WHERE ROWNUM < 11;
-- Standalone DB:
SELECT sid AS Waiter, p1raw,
SUBSTR(RAWTOHEX(p1),1,30) AS Handle,
SUBSTR(RAWTOHEX(p2),1,30) AS Pin_addr
FROM v$session_wait
WHERE wait_time = 0
AND event LIKE '%library cache%';
-- RAC DB:
SELECT a.sid AS Waiter, b.serial#, a.event, a.p1raw,
SUBSTR(RAWTOHEX(a.p1),1,30) AS Handle,
SUBSTR(RAWTOHEX(a.p2),1,30) AS Pin_addr
FROM v$session_wait a, v$session b
WHERE a.sid = b.sid
AND a.wait_time = 0
AND a.event LIKE 'library cache%';
-- Statistics by session for library cache
SET LINES 152
COL sid FOR a15
COL name FOR a40
SELECT a.sid, b.name, a.value, b.class
FROM gv$sesstat a, gv$statname b
WHERE a.statistic# = b.statistic#
AND b.name LIKE '%library cache%';
SELECT TO_CHAR(SESSION_ID,'999') AS sid,
SUBSTR(LOCK_TYPE,1,30) AS Type,
SUBSTR(lock_id1,1,23) AS Object_Name,
SUBSTR(mode_held,1,4) AS HELD,
SUBSTR(mode_requested,1,4) AS REQ,
lock_id2 AS Lock_addr
FROM dba_lock_internal
WHERE mode_requested <> 'None'
AND mode_requested <> mode_held
AND session_id IN (
SELECT sid
FROM v$session_wait
WHERE wait_time = 0
AND event LIKE '%library cache%'
);
SET LINES 299
COLUMN object FORMAT a30
COLUMN owner FORMAT a10
SELECT * FROM v$access
WHERE owner = '&OWNER'
AND object = '&object_name'
/
SELECT sql_id, object_owner, object_name
FROM V$SQL_PLAN
WHERE operation='TABLE ACCESS'
AND options='FULL'
AND object_owner NOT IN ('SYS','SYSTEM','DBSNMP');
SELECT SUM(gets) AS "Gets",
SUM(getmisses) AS "Misses",
(1-(SUM(getmisses)/SUM(gets)))*100 AS "CACHE HIT RATIO"
FROM gv$rowcache;
-- NOTE: Cache hit ratio should be > 95%.
SELECT * FROM (
SELECT plan_hash_value,
COUNT(DISTINCT(hash_value)) AS hv_cnt,
SUM(executions) AS execs,
SUM(parse_calls) AS parses
FROM gv$sql
GROUP BY plan_hash_value
HAVING COUNT(DISTINCT(hash_value)) > 10
ORDER BY hv_cnt DESC
) WHERE ROWNUM < 21;
SET LINES 160 PAGES 100
SELECT * FROM x$ksmlru ORDER BY ksmlrnum;
SELECT schema, sql_text, disk_reads, ROUND(cpu,2)
FROM (
SELECT s.parsing_schema_name AS schema,
t.sql_id,
t.sql_text,
t.disk_reads,
t.sorts,
t.cpu_time/1000000 AS cpu,
t.rows_processed,
t.elapsed_time
FROM v$sqlstats t
JOIN v$sql s ON (t.sql_id = s.sql_id)
WHERE s.parsing_schema_name = 'SCOTT'
ORDER BY t.disk_reads DESC
)
WHERE ROWNUM <= 5;
COLUMN mux FORMAT a18 HEADING 'Mutex Type' TRUNC
COLUMN loc FORMAT a32 HEADING 'Location' TRUNC
COLUMN sleeps FORMAT 9,999,999,990 HEADING 'Sleeps'
COLUMN wt FORMAT 9,999,990.9 HEADING 'Wait Time (s)'
SELECT e.mutex_type AS mux,
e.location AS loc,
e.sleeps - NVL(b.sleeps, 0) AS sleeps,
(e.wait_time - NVL(b.wait_time, 0))/1000000 AS wt
FROM DBA_HIST_MUTEX_SLEEP b,
DBA_HIST_MUTEX_SLEEP e
WHERE b.snap_id(+) = &bid
AND e.snap_id = &eid
AND b.dbid(+) = e.dbid
AND b.instance_number(+)= e.instance_number
AND b.mutex_type(+) = e.mutex_type
AND b.location(+) = e.location
AND e.sleeps - NVL(b.sleeps, 0) > 0
ORDER BY (e.wait_time - NVL(b.wait_time, 0)) DESC;
-- Create tuning task
SET LONG 1000000000
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => 'apwfwjhgc9sk8',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit=> 500,
task_name => 'apwfwjhgc9sk8_tuning_task_1',
description => 'Tuning task for statement apwfwjhgc9sk8'
);
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
-- Execute tuning task
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'apwfwjhgc9sk8_tuning_task_1');
-- Generate report
SET LONG 10000000
SET PAGESIZE 100000000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('apwfwjhgc9sk8_tuning_task_1') AS recommendations
FROM dual;
SET PAGESIZE 24
-- STATISTICS_LEVEL should be TYPICAL/ALL.
SHOW PARAMETER statistics_level
SELECT * FROM v$sga_target_advice ORDER BY sga_size;
SELECT shared_pool_size_for_estimate AS "Size MB",
shared_pool_size_factor AS "Size Factor",
estd_lc_time_saved AS "Time Saved (s)"
FROM v$shared_pool_advice;
-- From $ORACLE_HOME/rdbms/admin
@addmrpt.sql
-- Then provide begin & end snapshot IDs when prompted.
SELECT sesion.sid,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
FROM v$sqlarea sqlarea,
v$session sesion
WHERE sesion.sql_hash_value = sqlarea.hash_value
AND sesion.sql_address = sqlarea.address
AND sesion.username IS NOT NULL;
SET ECHO OFF
SET LINESIZE 95
SET HEAD ON
SET FEEDBACK ON
COL sid HEAD "Sid" FORM 9999 TRUNC
COL serial# FORM 99999 TRUNC HEAD "Ser#"
COL username FORM a8 TRUNC
COL osuser FORM a7 TRUNC
COL machine FORM a20 TRUNC HEAD "Client|Machine"
COL program FORM a15 TRUNC HEAD "Client|Program"
COL login FORM a11
COL "last call" FORM 9999999 TRUNC HEAD "Last Call|In Secs"
COL status FORM a6 TRUNC
SELECT sid, serial#,
SUBSTR(username,1,10) username,
SUBSTR(osuser,1,10) osuser,
SUBSTR(program||module,1,15) program,
SUBSTR(machine,1,22) machine,
TO_CHAR(logon_time,'ddMon hh24:mi') login,
last_call_et "last call",
status
FROM gv$session
WHERE status='ACTIVE'
ORDER BY 1
/
SELECT a.sid,
SUBSTR(b.username,1,10) username,
SUBSTR(b.osuser,1,10) osuser,
SUBSTR(b.program||b.module,1,15) program,
SUBSTR(b.machine,1,22) machine,
a.event,
a.p1,
b.sql_hash_value
FROM v$session_wait a,
v$session b
WHERE b.sid = a.sid
AND a.event NOT IN ('SQL*Net message from client','SQL*Net message to client',
'smon timer','pmon timer')
AND b.username IS NOT NULL
ORDER BY a.event;
SELECT a.sid, a.serial#, a.username,
b.used_urec AS used_undo_record,
b.used_ublk AS used_undo_blocks
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr;
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
a.inst_id AS Instance,
a.sid||','||a.serial# AS sid_serial,
NVL(a.username, '(oracle)') AS username,
a.program,
a.status,
a.sql_id
FROM gv$session a,
gv$sort_usage b,
gv$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
AND a.inst_id = b.inst_id
AND a.inst_id = p.inst_id
ORDER BY ((b.blocks*p.value)/1024/1024) DESC
/
SET LINES 2000
SET PAGES 1000
COL sid FOR 99999
COL name FOR a09
COL username FOR a14
COL program FOR a21
COL module FOR a25
SELECT s.sid,
sess.serial#,
n.name,
ROUND(s.value/1024/1024,2) AS redo_mb,
sess.username,
sess.status,
SUBSTR(sess.program,1,21) AS program,
sess.type,
sess.module,
sess.sql_id
FROM v$sesstat s
JOIN v$statname n ON n.statistic# = s.statistic#
JOIN v$session sess ON sess.sid = s.sid
WHERE n.name = 'redo size'
AND s.value <> 0
ORDER BY redo_mb DESC;
SET FEEDBACK OFF
SET PAGESIZE 70
SET LINESIZE 2000
SET HEAD ON
COLUMN Tablespace FORMAT a25 HEADING 'Tablespace Name'
COLUMN autoextensible FORMAT a11 HEADING 'AutoExtend'
COLUMN files_in_tablespace FORMAT 999 HEADING 'Files'
COLUMN total_tablespace_space FORMAT 99999999 HEADING 'TotalSpace'
COLUMN total_used_space FORMAT 99999999 HEADING 'UsedSpace'
COLUMN total_tablespace_free_space FORMAT 99999999 HEADING 'FreeSpace'
COLUMN total_used_pct FORMAT 9999 HEADING '%Used'
COLUMN total_free_pct FORMAT 9999 HEADING '%Free'
WITH tbs_auto AS (
SELECT DISTINCT tablespace_name, autoextensible
FROM dba_data_files
WHERE autoextensible = 'YES'
),
files AS (
SELECT tablespace_name, COUNT(*) tbs_files,
SUM(bytes/1024/1024) total_tbs_bytes
FROM dba_data_files
GROUP BY tablespace_name
),
fragments AS (
SELECT tablespace_name, COUNT(*) tbs_fragments,
SUM(bytes)/1024/1024 total_tbs_free_bytes,
MAX(bytes)/1024/1024 max_free_chunk_bytes
FROM dba_free_space
GROUP BY tablespace_name
)
SELECT i.instance_name,
t.tablespace_name AS Tablespace,
CASE WHEN a.autoextensible = 'YES' THEN 'YES' ELSE 'NO' END AS autoextensible,
f.tbs_files AS files_in_tablespace,
f.total_tbs_bytes AS total_tablespace_space,
(f.total_tbs_bytes - fr.total_tbs_free_bytes) AS total_used_space,
fr.total_tbs_free_bytes AS total_tablespace_free_space,
ROUND(((f.total_tbs_bytes - fr.total_tbs_free_bytes)/f.total_tbs_bytes)*100) AS total_used_pct,
ROUND((fr.total_tbs_free_bytes/f.total_tbs_bytes)*100) AS total_free_pct
FROM dba_tablespaces t
JOIN files f ON t.tablespace_name = f.tablespace_name
JOIN fragments fr ON t.tablespace_name = fr.tablespace_name
LEFT JOIN tbs_auto a ON t.tablespace_name = a.tablespace_name
CROSS JOIN v$instance i
ORDER BY total_free_pct;
SELECT a.tablespace_name,
sizemb,
usagemb,
(sizemb - usagemb) AS freemb
FROM (
SELECT SUM(bytes)/1024/1024 AS sizemb, b.tablespace_name
FROM dba_data_files a, dba_tablespaces b
WHERE a.tablespace_name = b.tablespace_name
AND b.contents = 'UNDO'
GROUP BY b.tablespace_name
) a,
(
SELECT c.tablespace_name, SUM(bytes)/1024/1024 AS usagemb
FROM dba_undo_extents c
WHERE status <> 'EXPIRED'
GROUP BY c.tablespace_name
) b
WHERE a.tablespace_name = b.tablespace_name;
SELECT a.tablespace_name AS tablespace,
d.temp_total_mb,
SUM(a.used_blocks * d.block_size) / 1024 / 1024 AS temp_used_mb,
d.temp_total_mb - SUM(a.used_blocks * d.block_size) / 1024 / 1024 AS temp_free_mb
FROM v$sort_segment a,
(
SELECT b.name, c.block_size, SUM(c.bytes) / 1024 / 1024 AS temp_total_mb
FROM v$tablespace b, v$tempfile c
WHERE b.ts# = c.ts#
GROUP BY b.name, c.block_size
) d
WHERE a.tablespace_name = d.name
GROUP BY a.tablespace_name, d.temp_total_mb;
SELECT
s.inst_id,
s.blocking_session,
s.sid,
s.serial#,
s.seconds_in_wait
FROM gv$session s
WHERE blocking_session IS NOT NULL;
SELECT sid, inst_id, opname, totalwork, sofar, start_time, time_remaining
FROM gv$session_longops
WHERE totalwork <> sofar
/
COL session_id HEAD 'Sid' FORM 9999
COL object_name HEAD "Table|Locked" FORM a30
COL oracle_username HEAD "Oracle|Username" FORM a10 TRUNCATE
COL os_user_name HEAD "OS|Username" FORM a10 TRUNCATE
COL process HEAD "Client|Process|ID" FORM 99999999
COL mode_held FORM a15
SELECT lo.session_id,
lo.oracle_username,
lo.os_user_name,
lo.process,
do.object_name,
DECODE(lo.locked_mode,
0, 'None',
1, 'Null',
2, 'Row Share (SS)',
3, 'Row Excl (SX)',
4, 'Share',
5, 'Share Row Excl (SSX)',
6, 'Exclusive',
TO_CHAR(lo.locked_mode)) AS mode_held
FROM v$locked_object lo
JOIN dba_objects do ON lo.object_id = do.object_id
ORDER BY 1,5
/
-- Dependent SQL areas associated with a PLSQL procedure
SELECT s.sql_id, s.sql_text
FROM gv$sqlarea s
JOIN dba_objects o ON s.program_id = o.object_id
WHERE o.object_name = '&procedure_name';
COL sid FORMAT 999999
COL username FORMAT a20
COL osuser FORMAT a15
SELECT b.spid, a.sid, a.serial#, a.username, a.osuser
FROM v$session a
JOIN v$process b ON a.paddr = b.addr
WHERE b.spid = '&spid'
ORDER BY b.spid;
SELECT 'alter system kill session ' || ''''||sid||','||serial#||' immediate;' AS kill_stmt
FROM v$session
WHERE sql_id = '&sql_id';
-- For RAC:
SELECT 'alter system kill session ' || ''''||sid||','||serial#||',@'||inst_id||''''||' immediate;' AS kill_stmt
FROM gv$session
WHERE sql_id = '&sql_id';
SELECT 'alter system kill session '''||sid||','||serial#||''' immediate;' AS kill_stmt
FROM v$session
WHERE status = 'SNIPED';
COL username FOR a12
COL "QC/Slave" FOR a8
SET LINES 299
SELECT
s.inst_id,
DECODE(px.qcinst_id, NULL, s.username,
' - '||LOWER(SUBSTR(s.program, LENGTH(s.program)-4, 4))) AS "Username",
DECODE(px.qcinst_id, NULL, 'QC', '(Slave)') AS "QC/Slave",
TO_CHAR(px.server_set) AS "Slave Set",
TO_CHAR(s.sid) AS "SID",
DECODE(px.qcinst_id, NULL, TO_CHAR(s.sid), px.qcsid) AS "QC SID",
px.req_degree AS "Requested DOP",
px.degree AS "Actual DOP",
p.spid
FROM gv$px_session px,
gv$session s,
gv$process p
WHERE px.sid = s.sid (+)
AND px.serial# = s.serial#
AND px.inst_id = s.inst_id
AND p.inst_id = s.inst_id
AND p.addr = s.paddr
ORDER BY 5, 1 DESC
/
-- Same as above; useful to rerun periodically to monitor PX usage.
<use the previous query>
SELECT
module, parsing_schema_name, inst_id, sql_id, child_number, sql_plan_baseline,
sql_profile, plan_hash_value, sql_fulltext,
TO_CHAR(last_active_time,'DD/MM/YY HH24:MI:SS') AS last_active,
executions, elapsed_time/executions/1000/1000 AS avg_elapsed_s,
rows_processed, sql_plan_baseline
FROM gv$sql
WHERE last_active_time > SYSDATE - 1/24
AND executions <> 0
ORDER BY elapsed_time/executions DESC;
-- Current open cursors by session
SELECT a.value, s.username, s.sid, s.serial#
FROM v$sesstat a, v$statname b, v$session s
WHERE a.statistic# = b.statistic#
AND s.sid = a.sid
AND b.name = 'opened cursors current';
-- Highest observed vs max allowed
SELECT MAX(a.value) AS highest_open_cur, p.value AS max_open_cur
FROM v$sesstat a, v$statname b, v$parameter p
WHERE a.statistic# = b.statistic#
AND b.name = 'opened cursors current'
AND p.name = 'open_cursors'
GROUP BY p.value;
SELECT c.username, a.sample_time, a.sql_opname, a.sql_exec_start,
a.program, a.module, a.machine, b.sql_text
FROM dba_hist_active_sess_history a
LEFT JOIN dba_hist_sqltext b ON a.sql_id = b.sql_id
JOIN dba_users c ON a.user_id = c.user_id
WHERE c.username = '&username'
ORDER BY a.sql_exec_start ASC;
SELECT ROUND((1 - (phy.value / (cur.value + con.value))) * 100, 2) AS "Cache Hit Ratio"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'
/
SELECT username AS users,
ROUND(disk_reads/executions) AS DReadsExec,
executions AS Exec,
disk_reads AS DReads,
sql_text
FROM gv$sqlarea a
JOIN dba_users b ON a.parsing_user_id = b.user_id
WHERE executions > 0
AND disk_reads > 100000
ORDER BY 2 DESC;
SET LINES 123
COL username FOR a15
COL osuser FOR a12
COL machine FOR a18
COL program FOR a20
SELECT b.spid,
a.username,
a.program,
a.osuser,
a.machine,
a.sid,
a.serial#,
a.status
FROM gv$session a
JOIN gv$process b ON b.addr = a.paddr
WHERE a.sid = &sid;
SELECT sid, sql_id, serial#, status, username, program
FROM v$session
WHERE plsql_entry_object_id IN (
SELECT object_id
FROM dba_objects
WHERE object_name IN ('&PROCEDURE_NAME')
);
COL object_name FORMAT a30
COL to_total FORMAT 999.99
SELECT owner, object_name, object_type, cnt AS count,
(cnt / value) * 100 AS to_total
FROM (
SELECT a.owner, a.object_name, a.object_type, COUNT(*) AS cnt
FROM dba_objects a, x$bh b
WHERE a.object_id = b.obj
AND a.owner NOT IN ('SYS','SYSTEM')
GROUP BY a.owner, a.object_name, a.object_type
ORDER BY 4
), v$parameter
WHERE name = 'db_cache_size'
AND (cnt / value) * 100 > .005
ORDER BY to_total DESC
/
SELECT state, undoblocksdone, undoblockstotal,
undoblocksdone/undoblockstotal*100 AS pct_done
FROM gv$fast_start_transactions;
ALTER SESSION SET nls_date_format='dd-mon-yyyy hh24:mi:ss';
SELECT usn, state,
undoblockstotal AS "Total",
undoblocksdone AS "Done",
undoblockstotal - undoblocksdone AS "ToDo",
DECODE(cputime, 0, 'unknown',
SYSDATE + (((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)
) AS "Estimated time to complete"
FROM v$fast_start_transactions;
SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr
ORDER BY 5 DESC;
SET LINES 150
SET PAGES 500
COL table_name FOR a20
COL column_name FOR a20
SELECT a.object_name AS table_name,
c.column_name,
equality_preds, equijoin_preds, range_preds, like_preds
FROM dba_objects a, col_usage$ b, dba_tab_columns c
WHERE a.object_id = b.obj#
AND c.column_id = b.intcol#
AND a.object_name = c.table_name
AND b.obj# = a.object_id
AND a.object_name = '&table_name'
AND a.object_type = 'TABLE'
AND a.owner = '&owner'
ORDER BY 3 DESC, 4 DESC, 5 DESC;
COL ksbddidn FOR a15
COL ksmfsnam FOR a20
COL ksbdddsc FOR a60
SET LINES 150 PAGES 5000
SELECT ksbdd.ksbddidn, ksmfsv.ksmfsnam, ksbdd.ksbdddsc
FROM x$ksbdd ksbdd, x$ksbdp ksbdp, x$ksmfsv ksmfsv
WHERE ksbdd.indx = ksbdp.indx
AND ksbdp.addr = ksmfsv.ksmfsadr
ORDER BY ksbdd.ksbddidn;
SELECT LENGTH(addr)*4 || '-bits' AS word_length
FROM v$process
WHERE ROWNUM = 1;
SELECT
samp.dbid,
fu.name,
samp.version,
detected_usages,
total_samples,
DECODE(TO_CHAR(last_usage_date, 'MM/DD/YYYY, HH:MI:SS'),
NULL, 'FALSE',
TO_CHAR(last_sample_date, 'MM/DD/YYYY, HH:MI:SS'), 'TRUE',
'FALSE') AS currently_used,
first_usage_date,
last_usage_date,
aux_count,
feature_info,
last_sample_date,
last_sample_period,
sample_interval,
mt.description
FROM wri$_dbu_usage_sample samp,
wri$_dbu_feature_usage fu,
wri$_dbu_feature_metadata mt
WHERE samp.dbid = fu.dbid
AND samp.version= fu.version
AND fu.name = mt.name
AND fu.name NOT LIKE '_DBFUS_TEST%'
AND BITAND(mt.usg_det_method, 4) != 4;
SELECT operation_name, default_value
FROM V$OPTIMIZER_PROCESSING_RATE
WHERE operation_name IN ('IO_BYTES_PER_SEC','CPU_BYTES_PER_SEC','CPU_ROWS_PER_SEC');
-- Counts before
SELECT COUNT(*) FROM dba_recyclebin;
PURGE RECYCLEBIN;
-- Counts after
SELECT COUNT(*) FROM dba_recyclebin;
-- Create directory (once)
CREATE DIRECTORY EXPDIR AS '/export/home/oracle/ORADUMP';
-- Parfile: compressed.par
-- FULL DB export with compression
dumpfile=schema.dmp
logfile=tables.log
directory=EXPDIR
FULL=Y
compression=ALL
-- Run:
expdp parfile=compressed.par
CREATE DIRECTORY EXPDIR AS '/export/home/oracle/ORADUMP';
-- parallel.par
dumpfile=parallel_%U.dmp
logfile=tables.log
directory=EXPDIR
schemas=PROD_DATA
parallel=4 -- set as per CPU cores
-- Run:
expdp parfile=parallel.par
-- For import, use impdp with similar parfile.
CREATE DIRECTORY EXPDIR AS '/export/home/oracle/ORADUMP';
-- schema.par
dumpfile=schema.dmp
logfile=tables.log
directory=EXPDIR
schemas=PROD_DATA,DEV_DATA
-- Run:
expdp parfile=schema.par
CREATE DIRECTORY EXPDIR AS '/export/home/oracle/ORADUMP';
-- tables.par
dumpfile=tables.dmp
logfile=tables.log
directory=EXPDIR
tables=PROD_DATA.EMPLOYEE,PROD_DATA.DEPT,DEV_DATA.STAGING
-- Run:
expdp parfile=tables.par
-- expdp_query.par
dumpfile=test.dmp
logfile=test1.log
directory=TEST
tables=DBACLASS.EMP_TAB
QUERY=DBACLASS.EMP_TAB:"WHERE created > sysdate -40"
-- sqlfile.par
dumpfile=test.dmp
logfile=test1.log
directory=TEST
tables=DBACLASS.DEP_TAB
sqlfile=emp_tab.sql
-- DDLs will be written to emp_tab.sql
Valid: APPEND | REPLACE | SKIP | TRUNCATE
-- APPEND example
impdp dumpfile=emp_tab.dmp logfile=emp_tab.log directory=VEN table_exists_action=APPEND
-- TRUNCATE example
impdp dumpfile=emp_tab.dmp logfile=emp_tab.log directory=VEN table_exists_action=TRUNCATE
-- Exclude some tables
dumpfile=test.dmp
logfile=test1.log
directory=TEST
exclude=TABLE:"IN ('EMP_TAB','DEPT')"
schemas=DBACLASS
-- Exclude some schemas on import
dumpfile=test.dmp
logfile=test1.log
directory=TEST
EXCLUDE=SCHEMA:"IN ('WMSYS','OUTLN')"
dumpfile=FULL.dmp
logfile=full.log
directory=DBATEST
INCLUDE=TABLE,INDEX
CREATE DIRECTORY DIR1 AS '/home/oracle/DIR1';
CREATE DIRECTORY DIR2 AS '/home/oracle/DIR2';
-- parfile
dumpfile=DIR1:test_%U.dmp,DIR2:test_%U.dmp
logfile=test.log
directory=DIR1
parallel=2
tables=raj.test
-- Dump files to ASM
CREATE DIRECTORY SOURCE_DUMP AS '+NEWTST/TESTDB2/TEMPFILE';
-- Logs to filesystem
CREATE DIRECTORY EXPLOG AS '/export/home/oracle';
-- parfile
dumpfile=test.dmp
logfile=EXPLOG:test.log
directory=SOURCE_DUMP
tables=dbatest.EMPTAB
exclude=statistics
-- If datapump directory is not shared across nodes, set cluster=N
-- parfile
dumpfile=asset_%U.dmp
logfile=asset.log
directory=VEN
parallel=32
cluster=N
ALTER TABLE DBACLASS.EMP ENABLE ROW MOVEMENT;
FLASHBACK TABLE DBACLASS.EMP
TO TIMESTAMP TO_TIMESTAMP('2017-01-10 09:00:00','YYYY-MM-DD HH24:MI:SS');
FLASHBACK TABLE DBACLASS.EMP TO BEFORE DROP;
-- Restore with a new name
FLASHBACK TABLE DBACLASS.EMP TO BEFORE DROP RENAME TO EMP_BACKUP;
-- Verify in recyclebin
SELECT * FROM dba_recyclebin;
SELECT * FROM DBACLASS.EMP
AS OF TIMESTAMP TO_TIMESTAMP('2017-01-07 10:00:00','YYYY-MM-DD HH24:MI:SS');
SELECT * FROM DBACLASS.EMP AS OF TIMESTAMP SYSDATE - 1/24;
-- Ensure ARCHIVELOG mode
ALTER SYSTEM SET db_recovery_file_dest_size = 10G SCOPE=BOTH;
ALTER SYSTEM SET db_recovery_file_dest = '/dumparea/FRA/B2BRBMT3' SCOPE=BOTH;
ALTER DATABASE FLASHBACK ON;
-- Guaranteed restore point
CREATE RESTORE POINT BEFORE_UPG GUARANTEE FLASHBACK DATABASE;
-- List restore points
SELECT * FROM v$restore_point;
-- Drop
DROP RESTORE POINT BEFORE_UPG;
-- 1) Identify restore point
SELECT name, time FROM v$restore_point;
-- 2) Mount mode
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
-- 3) Flashback
FLASHBACK DATABASE TO RESTORE POINT GRP_1490100093811;
-- 4) Open with resetlogs
ALTER DATABASE OPEN RESETLOGS;
-- Get object_id first
SELECT object_id
FROM dba_objects
WHERE owner='DBACLASS' AND object_name='VOL_DISCOUNT_INSERT';
-- Fetch source from timestamp
SELECT source
FROM sys.source$ AS OF TIMESTAMP TO_TIMESTAMP('23-Apr-2017 10:00:20','DD-Mon-YYYY hh24:MI:SS')
WHERE obj# = 2201943;
-- Time
SELECT TO_CHAR(oldest_flashback_time,'dd-mon-yyyy hh24:mi:ss') AS "Oldest Flashback Time"
FROM v$flashback_database_log;
-- SCN
COL oldest_flashback_scn FORMAT 99999999999999999999999999
SELECT oldest_flashback_scn
FROM v$flashback_database_log;
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/uv1249/arch/PROD' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
-- SQL*Plus
SELECT * FROM v$restore_point;
-- RMAN
-- RMAN> LIST RESTORE POINT ALL;
SELECT dbid, name, open_mode, total_size/1024/1024 AS mb FROM v$pdbs;
SHOW PDBS
SET LINES 132 PAGES 100
COL con_name FORM A15 HEAD "Container|Name"
COL tablespace_name FORM A15
COL fsm FORM 999,999,999,999 HEAD "Free|Space Meg."
COL apm FORM 999,999,999,999 HEAD "Alloc|Space Meg."
BREAK ON REPORT ON con_id ON con_name ON tablespace_name
WITH x AS (
SELECT c1.con_id, cf1.tablespace_name, SUM(cf1.bytes)/1024/1024 fsm
FROM cdb_free_space cf1, v$containers c1
WHERE cf1.con_id = c1.con_id
GROUP BY c1.con_id, cf1.tablespace_name
),
y AS (
SELECT c2.con_id, cd.tablespace_name, SUM(cd.bytes)/1024/1024 apm
FROM cdb_data_files cd, v$containers c2
WHERE cd.con_id = c2.con_id
GROUP BY c2.con_id, cd.tablespace_name
)
SELECT x.con_id, v.name con_name, x.tablespace_name, x.fsm, y.apm
FROM x, y, v$containers v
WHERE x.con_id = y.con_id
AND x.tablespace_name = y.tablespace_name
AND v.con_id = y.con_id
UNION
SELECT vc2.con_id, vc2.name, tf.tablespace_name, NULL, SUM(tf.bytes)/1024/1024
FROM v$containers vc2, cdb_temp_files tf
WHERE vc2.con_id = tf.con_id
GROUP BY vc2.con_id, vc2.name, tf.tablespace_name
ORDER BY 1,2;
SELECT a.name, b.file_id, b.tablespace_name, b.file_name
FROM v$containers a, cdb_temp_files b
WHERE a.con_id = b.con_id;
SET LINES 299
SET PAGESIZE 299
COL db_name FOR a10
COL CLONED_FROM_PDB_NAME FOR a12
COL pdb_name FOR a18
SELECT db_name, con_id, pdb_name, operation, op_timestamp, cloned_from_pdb_name
FROM cdb_pdb_history;
SHOW CON_NAME
SELECT sys_context('USERENV','CON_NAME') FROM dual;
-- From CDB$ROOT
ALTER PLUGGABLE DATABASE ALL OPEN;
ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;
-- Specific PDB
ALTER SESSION SET container = PDB1;
STARTUP; -- opens the PDB
SHUTDOWN; -- closes the PDB
-- Run from CDB$ROOT
ALTER PLUGGABLE DATABASE PDB1 CLOSE IMMEDIATE;
DROP PLUGGABLE DATABASE PDB1 INCLUDING DATAFILES;
SELECT * FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED';
-- TRUE => Local Undo; FALSE => Shared Undo
SELECT CDB FROM v$database; -- YES => multitenant
COL network_name FOR a34
COL pdb FOR a15
COL con_id FOR 999
SELECT pdb, network_name, con_id
FROM cdb_services
WHERE pdb IS NOT NULL AND con_id > 2
ORDER BY pdb;
COL name FORM a8
SELECT name, con_id, dbid, con_uid, guid FROM v$containers;
LSNRCTL> set cur LISTENER_TEST
LSNRCTL> set trc_level ADMIN
-- Verify output:
-- "LISTENER_TEST parameter "trc_level" set to admin"
-- PUBLIC DB link
CREATE PUBLIC DATABASE LINK link_pub
CONNECT TO system IDENTIFIED BY oracle
USING 'PRODB'; -- entry from tnsnames.ora
-- PRIVATE DB link under SCOTT
CONNECT scott/tiger
CREATE DATABASE LINK link_priv
CONNECT TO system IDENTIFIED BY oracle
USING 'PRODB';
-- Drops
DROP PUBLIC DATABASE LINK TEST_LINK;
CONNECT scott/tiger
DROP DATABASE LINK link_priv;
-- Note: Private DB link can be dropped only by its owner.
CREATE PUBLIC DATABASE LINK imfp CONNECT TO iwf IDENTIFIED BY thr3iwf USING
'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=testoracle.com)(PORT=1522)))
(CONNECT_DATA=(SERVICE_NAME=IMFP)))';
srvctl modify scan_listener -p 1523
$GRID_HOME/bin/srvctl stop scan_listener
$GRID_HOME/bin/srvctl start scan_listener
# Update in DB:
SQL> ALTER SYSTEM SET remote_listener='orcl-scan.stc.com.sa:1523' SCOPE=BOTH SID='*';
LISTENER_DBACLASS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.20.211.236)(PORT = 1527))
)
SID_LIST_LISTENER_DBACLASS =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /oracle/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = DBACLASS)
)
)
lsnrctl start LISTENER_DBACLASS
lsnrctl stop LISTENER_DBACLASS
lsnrctl reload LISTENER_DBACLASS
lsnrctl status LISTENER_DBACLASS
lsnrctl version LISTENER_DBACLASS
lsnrctl services LISTENER_DBACLASS
lsnrctl servacls LISTENER_DBACLASS
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
SELECT b.inst_id,
b.session_id AS sid,
NVL(b.oracle_username,'(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
DECODE(b.locked_mode, 0, 'None', 1, 'Null (NULL)', 2, 'Row-S (SS)',
3, 'Row-X (SX)', 4, 'Share (S)', 5, 'S/Row-X (SSX)',
6, 'Exclusive (X)', b.locked_mode) AS locked_mode,
b.os_user_name
FROM dba_objects a,
gv$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1,2,3,4;
-- Create ACL
exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('scott_utl_mail.xml','Allow mail to be send','SCOTT', TRUE,'connect');
-- Assign ACL to network
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('scott_utl_mail.xml','*',25);
-- Grant privileges to user
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('scott_utl_mail.xml','SCOTT', TRUE, 'connect');
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('scott_utl_mail.xml','SCOTT', TRUE, 'resolve');
-- Unassign network from ACL
exec DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL('scott_utl_mail.xml','*',25);
-- Remove privilege from a user
exec DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE('scott_utl_mail.xml','SCOTT', TRUE, 'connect');
-- Drop ACL
exec DBMS_NETWORK_ACL_ADMIN.DROP_ACL('scott_utl_mail.xml');
-- Shows all registered services for the database
COL NETWORK_NAME FOR a25
SET PAGESIZE 299
SET LINES 299
SELECT NAME, INST_ID, NETWORK_NAME, CREATION_DATE, GOAL, GLOBAL
FROM GV$ACTIVE_SERVICES
WHERE name NOT LIKE 'SYS$%';
-- Make sure a listener is already running with the port (e.g., 1524)
ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS = (PROTOCOL = TCP)(HOST = 162.20.217.15)(PORT = 1524))' SCOPE=BOTH;
ALTER SYSTEM REGISTER;
SELECT type, value FROM v$listener_network WHERE TYPE='LOCAL LISTENER';
SET LINES 200
COL ACL_OWNER FOR A12
COL ACL FOR A67
COL HOST FOR A34
COL PRINCIPAL FOR A20
COL PRIVILEGE FOR A13
SELECT ACL_OWNER, ACL, HOST, LOWER_PORT, UPPER_PORT FROM dba_network_acls;
SELECT ACL_OWNER, ACL, PRINCIPAL, PRIVILEGE FROM dba_network_acl_privileges;
-- Find LOB segment details
SELECT table_name, column_name, segment_name, tablespace_name
FROM dba_lobs
WHERE owner='DBACLASS';
-- Move LOB to new tablespace
ALTER TABLE DBACLASS.LOB_SEG1
MOVE LOB (PAYLOAD) STORE AS SYS_LOB0000100201C00011$$ ( TABLESPACE USERS );
SET PAGESIZE 200
SET LINES 200
SET LONG 999
COL owner FOR a15
COL table_name FOR a20
COL column_name FOR a21
SELECT a.owner, a.table_name, a.column_name, b.data_type
FROM dba_lobs a, dba_tab_columns b
WHERE a.column_name = b.column_name
AND a.table_name = b.table_name
AND a.owner = b.owner
AND b.owner NOT IN ('SYS','SYSTEM','DBSNMP','WMSYS');
-- Segment bytes:
SELECT s.bytes
FROM dba_segments s
JOIN dba_lobs l USING (owner, segment_name)
WHERE l.table_name = '&table_name';
-- Actual data bytes in the LOB column:
SELECT NVL(SUM(DBMS_LOB.getlength(&lob_column)),0) AS bytes FROM &table_name;
ANALYZE TABLE SCOTT.EMPTABLE LIST CHAINED ROWS;
SELECT COUNT(*) FROM chained_rows WHERE table_name='EMPTABLE';
-- Output is number of chained rows in that table.
SET LINES 132 PAGES 1000
COL object_name FORMAT a30 HEADING "Object Name"
COL object_type FORMAT a10 HEADING "Object|Type"
COL created FORMAT a30 HEADING "Created"
COL status FORMAT a30 HEADING "Status"
SELECT owner, object_name, object_type, created, status
FROM dba_objects
WHERE (object_name = LOWER(object_name) OR object_name = INITCAP(LOWER(object_name)))
AND object_name != UPPER(object_name);
SET PAGESIZE 200
SET LINES 200
SET LONG 999
COL owner FOR a18
COL table_name FOR a20
COL table_type_owner FOR a20
COL table_type_name FOR a20
COL parent_table_name FOR a20
COL parent_table_column FOR a20
SELECT owner, table_name, table_type_owner, table_type_name,
parent_table_name, parent_table_column,
LTRIM(storage_spec) storage_spec, LTRIM(return_type) return_type
FROM dba_nested_tables
WHERE owner='&SCHEMA_NAME'
AND UPPER(table_name) LIKE '&&TABLE_NAME'
ORDER BY owner;
-- Create PRIVATE database link under SCOTT
CONNECT scott/tiger
CREATE DATABASE LINK LINK_PRIV
CONNECT TO system IDENTIFIED BY oracle USING 'PRODB'; -- 'PRODB' is a tnsnames.ora entry
-- Drop PUBLIC database link
DROP PUBLIC DATABASE LINK TEST_LINK;
-- Drop PRIVATE database link
CONNECT scott/tiger
DROP DATABASE LINK LINK_PRIV;
-- Note: Private DB link can be dropped only by its owner.
-- For a table
SELECT idx.table_name, bytes/1024/1024/1024 AS gb
FROM dba_segments seg, dba_indexes idx
WHERE idx.table_name = '&TABLE_NAME'
AND idx.index_name = seg.segment_name
GROUP BY idx.table_name
ORDER BY 1;
-- Total index size per table in a schema
SELECT idx.table_name, SUM(bytes/1024/1024/1024) AS gb
FROM dba_segments seg, dba_indexes idx
WHERE idx.table_owner = 'SIEBEL'
AND idx.owner = seg.owner
AND idx.index_name = seg.segment_name
GROUP BY idx.table_name
ORDER BY 1;
-- Add column(s)
ALTER TABLE scott.emp ADD (empname VARCHAR2(20));
ALTER TABLE scott.emp ADD (empid NUMBER, deptid NUMBER);
-- Drop column(s)
ALTER TABLE scott.emp DROP (empname);
ALTER TABLE scott.emp DROP (empid, deptid);
-- Rename column
ALTER TABLE scott.emp RENAME COLUMN empname TO asocname;
-- Set column unused
ALTER TABLE scott.emp SET UNUSED (empname);
-- Drop all unused columns
ALTER TABLE scott.emp DROP UNUSED COLUMNS;
-- Public synonym
CREATE PUBLIC SYNONYM emp_view FOR scott.emp;
-- Private synonym
CREATE SYNONYM priv_view FOR scott.emp;
-- Drops
DROP PUBLIC SYNONYM emp_view;
DROP SYNONYM priv_view;
-- View synonyms
SELECT * FROM dba_synonyms;
SET LINES 150
SET PAGES 500
COL table_name FOR a20
COL column_name FOR a20
SELECT a.object_name AS table_name,
c.column_name,
equality_preds, equijoin_preds, range_preds, like_preds
FROM dba_objects a, col_usage$ b, dba_tab_columns c
WHERE a.object_id = b.obj#
AND c.column_id = b.intcol#
AND a.object_name = c.table_name
AND b.obj# = a.object_id
AND a.object_name = '&table_name'
AND a.object_type = 'TABLE'
AND a.owner = '&owner'
ORDER BY 3 DESC, 4 DESC, 5 DESC;
SET SERVEROUTPUT ON
DECLARE
v_used_bytes NUMBER(10);
v_allocated_bytes NUMBER(10);
BEGIN
DBMS_SPACE.CREATE_INDEX_COST(
'create index DBACLASS.INDEX1 on DBACLASS.EMP(EMPNO)',
v_used_bytes,
v_allocated_bytes
);
DBMS_OUTPUT.PUT_LINE('Used Bytes MB: ' || ROUND(v_used_bytes/1024/1024));
DBMS_OUTPUT.PUT_LINE('Allocated Bytes MB: ' || ROUND(v_allocated_bytes/1024/1024));
END;
/
-- All invalids
@${ORACLE_HOME}/rdbms/admin/utlrp.sql
-- A single schema
EXEC DBMS_UTILITY.compile_schema(schema => 'APPS');
-- Specific objects
ALTER PACKAGE APPS.DAIL_REPORT COMPILE;
ALTER PACKAGE APPS.DAIL_REPORT COMPILE BODY;
ALTER PROCEDURE APPS.REPORT_PROC COMPILE;
ALTER VIEW APPS.USERSTATUS_VW COMPILE;
ALTER FUNCTION APPS.SYNC_FUN COMPILE;
-- Disable all triggers of a schema
SELECT 'ALTER TRIGGER '||owner||'.'||trigger_name||' DISABLE;' AS ddl
FROM dba_triggers
WHERE owner='&SCHEMA_NAME';
-- Disable triggers for a specific table
SELECT 'ALTER TRIGGER '||owner||'.'||trigger_name||' DISABLE;' AS ddl
FROM dba_triggers
WHERE table_name='&TABLE_NAME' AND owner='&SCHEMA_NAME';
-- Enable equivalents
SELECT 'ALTER TRIGGER '||owner||'.'||trigger_name||' ENABLE;' AS ddl
FROM dba_triggers
WHERE owner='&SCHEMA_NAME';
SELECT 'ALTER TRIGGER '||owner||'.'||trigger_name||' ENABLE;' AS ddl
FROM dba_triggers
WHERE table_name='&TABLE_NAME' AND owner='&SCHEMA_NAME';
SELECT * FROM dba_dependencies WHERE owner='&SCHEMA_NAME' AND name='&OBJECT_NAME';
SELECT * FROM dba_dependencies WHERE referenced_owner='USER_NAME' AND referenced_name='OBJECT_NAME';
ALTER INDEX TEST_INDX REBUILD ONLINE;
-- Fast rebuild
ALTER INDEX TEST_INDX REBUILD ONLINE PARALLEL 8 NOLOGGING;
ALTER INDEX TEST_INDX NOPARALLEL;
ALTER INDEX TEST_INDX LOGGING;
# Stop / start OMS (12c/13c)
cd $ORACLE_HOME/bin
emctl stop oms
emctl start oms
emctl status oms
# Stop / start Agent
cd $AGENT_HOME/bin
./emctl start agent
./emctl stop agent
./emctl status agent
# OMS repository details
cd $OMS_HOME/bin
./emctl config oms -list_repos_details
# OMS / Agent URL details
cd $OMS_HOME/bin
./emctl status oms -details
cd $AGENT_HOME/bin
./emctl status agent –details
# Targets
./emcli get_targets
./emcli get_target_types
./emcli get_targets -targets="oracle_database"
# Plugins
./emcli list_plugins_on_server
./emcli list_plugins_on_agent
./emcli list_plugins_on_agent -agent_names="172.15.36.93"
# Change SYSMAN password
./emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd <current_sys_pwd> -new_pwd <new_sysman_pwd>
emctl stop oms; emctl start oms
-- 0 means disabled
SELECT dbms_xdb.getHttpPort() FROM dual;
SELECT dbms_xdb_config.getHttpsPort() FROM dual;
-- Enable
EXEC dbms_xdb_config.sethttpsport(5500);
EXEC dbms_xdb_config.sethttpport(8080);
-- Disable
EXEC dbms_xdb_config.sethttpsport(0);
EXEC dbms_xdb_config.sethttpport(0);
-- Syntax:
-- ALTER TABLE <SCHEMA>.<TABLE> ADD PARTITION <PARTITION_NAME>
-- VALUES LESS THAN <HIGH_VALUE> TABLESPACE <TBS> [UPDATE GLOBAL INDEXES];
ALTER TABLE CMADMIN.DBACLASS
ADD PARTITION DBACLASS_JAN VALUES LESS THAN (TO_DATE('01-FEB-2016','DD-MON-YYYY'))
TABLESPACE USERS UPDATE GLOBAL INDEXES;
-- 12c: add multiple partitions in one command
ALTER TABLE CMADMIN.DBACLASS ADD
PARTITION DBACLASS_JAN VALUES LESS THAN (TO_DATE('01-FEB-2016','DD-MON-YYYY')) TABLESPACE USERS,
PARTITION DBACLASS_FEB VALUES LESS THAN (TO_DATE('01-MAR-2016','DD-MON-YYYY')) TABLESPACE USERS,
PARTITION DBACLASS_MAR VALUES LESS THAN (TO_DATE('01-APR-2016','DD-MON-YYYY')) TABLESPACE USERS
UPDATE GLOBAL INDEXES;
ALTER TABLE CMADMIN.DBACLASS DROP PARTITION DBACLASS_JAN UPDATE GLOBAL INDEXES;
-- 12c: drop multiple
ALTER TABLE CMADMIN.DBACLASS DROP PARTITIONS DBACLASS_JAN, DBACLASS_FEB, DBACLASS_MAR
UPDATE GLOBAL INDEXES;
ALTER TABLE CMADMIN.DBACLASS TRUNCATE PARTITION DBACLASS_JAN UPDATE GLOBAL INDEXES;
-- 12c: multiple
ALTER TABLE CMADMIN.DBACLASS TRUNCATE PARTITIONS DBACLASS_JAN, DBACLASS_FEB, DBACLASS_MAR
UPDATE GLOBAL INDEXES;
ALTER TABLE CMADMIN.DBACLASS
MERGE PARTITIONS DBACLASS_JAN, DBACLASS_FEB, DBACLASS_MAR INTO PARTITION DBACLASS_Q1;
ALTER TABLE dbatest.ORDER_TAB MODIFY PARTITION CREATED_2105_P10 READ ONLY;
SELECT partition_name, read_only
FROM dba_tab_partitions
WHERE table_name='ORDER_TAB';
ALTER TABLE order_tab SPLIT PARTITION CREATED_MX INTO
(PARTITION CREATED_2106_P2 VALUES LESS THAN (TO_DATE('01/03/2016','DD/MM/YYYY')),
PARTITION CREATED_MX) ONLINE;
ALTER TABLE BSSTDBA.ORDER_TAB MODIFY
PARTITION BY RANGE (CREATED)
( PARTITION created_2105_p8 VALUES LESS THAN (TO_DATE('01/09/2015','DD/MM/YYYY')),
PARTITION created_2105_p9 VALUES LESS THAN (TO_DATE('01/10/2015','DD/MM/YYYY')),
PARTITION created_2105_p10 VALUES LESS THAN (TO_DATE('01/11/2015','DD/MM/YYYY')),
PARTITION created_2105_p11 VALUES LESS THAN (TO_DATE('01/12/2015','DD/MM/YYYY')),
PARTITION created_2105_p12 VALUES LESS THAN (TO_DATE('01/01/2016','DD/MM/YYYY')),
PARTITION created_mx VALUES LESS THAN (MAXVALUE)
) ONLINE;
ALTER TABLE employee RENAME PARTITION TAB3 TO TAB4;
SET SERVEROUT ON SIZE 1000000
SET VERIFY OFF
DECLARE
sql_stmt VARCHAR2(1024);
row_count NUMBER;
CURSOR get_tab IS
SELECT table_name, partition_name
FROM dba_tab_partitions
WHERE table_owner=UPPER('&&TABLE_OWNER')
AND table_name='&&TABLE_NAME';
BEGIN
dbms_output.put_line('Checking Record Counts for table_name');
FOR r IN get_tab LOOP
BEGIN
sql_stmt := 'select count(*) from '||'&&TABLE_OWNER'||'.'||r.table_name||' partition ('||r.partition_name||')';
EXECUTE IMMEDIATE sql_stmt INTO row_count;
dbms_output.put_line('Table '||RPAD(r.table_name||'('||r.partition_name||')',50)||' '||TO_CHAR(row_count)||' rows.');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error counting rows for table '||r.table_name);
END;
END LOOP;
END;
/
SET VERIFY ON
SET PAGESIZE 200
SET LINES 200
SET LONG 999
COL owner FOR a12
COL name FOR a20
COL object_type FOR a20
COL column_name FOR a32
SELECT owner, name, object_type, column_name
FROM dba_part_key_columns
WHERE owner='&OWNER'
ORDER BY owner, name;
-- Single partition
ALTER TABLE SCOTT.EMP MOVE PARTITION EMP_Q1 TABLESPACE TS_USERS;
-- With parallel
ALTER TABLE SCOTT.EMP MOVE PARTITION EMP_Q1 TABLESPACE TS_USERS PARALLEL(DEGREE 4) NOLOGGING;
-- Dynamic script (all partitions)
SELECT 'ALTER TABLE '||table_owner||'.'||table_name||' MOVE PARTITION '||partition_name||
' TABLESPACE TS_USERS PARALLEL(DEGREE 4) NOLOGGING;' AS ddl
FROM dba_tab_partitions
WHERE table_name='&TABLE_NAME' AND table_owner='&SCHEMA_NAME';
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/archiva/backup/%F';
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
RUN {
ALLOCATE CHANNEL c1 TYPE DISK FORMAT '/archiva/backup/%I-%Y%M%D-%U' MAXPIECESIZE 3G;
ALLOCATE CHANNEL c2 TYPE DISK FORMAT '/archiva/backup/%I-%Y%M%D-%U' MAXPIECESIZE 3G;
ALLOCATE CHANNEL c3 TYPE DISK FORMAT '/archiva/backup/%I-%Y%M%D-%U' MAXPIECESIZE 3G;
ALLOCATE CHANNEL c4 TYPE DISK FORMAT '/archiva/backup/%I-%Y%M%D-%U' MAXPIECESIZE 3G;
BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 CHECK LOGICAL DATABASE PLUS ARCHIVELOG;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
}
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/archiva/backup/%F';
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
RUN {
ALLOCATE CHANNEL c1 TYPE DISK FORMAT '/archiva/backup/%I-%Y%M%D-%U' MAXPIECESIZE 3G;
ALLOCATE CHANNEL c2 TYPE DISK FORMAT '/archiva/backup/%I-%Y%M%D-%U' MAXPIECESIZE 3G;
ALLOCATE CHANNEL c3 TYPE DISK FORMAT '/archiva/backup/%I-%Y%M%D-%U' MAXPIECESIZE 3G;
ALLOCATE CHANNEL c4 TYPE DISK FORMAT '/archiva/backup/%I-%Y%M%D-%U' MAXPIECESIZE 3G;
BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 CHECK LOGICAL DATABASE PLUS ARCHIVELOG;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
}
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/archiva/backup/%F';
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
RUN {
ALLOCATE CHANNEL c1 TYPE DISK FORMAT '/archiva/backup/%I-%Y%M%D-%U' MAXPIECESIZE 3G;
ALLOCATE CHANNEL c2 TYPE DISK FORMAT '/archiva/backup/%I-%Y%M%D-%U' MAXPIECESIZE 3G;
BACKUP TABLESPACE USERS, TOOLS;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
}
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/archiva/backup/%F';
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
RUN {
ALLOCATE CHANNEL c1 TYPE DISK FORMAT '/archiva/backup/%I-%Y%M%D-%U' MAXPIECESIZE 3G;
ALLOCATE CHANNEL c2 TYPE DISK FORMAT '/archiva/backup/%I-%Y%M%D-%U' MAXPIECESIZE 3G;
BACKUP DATAFILE 3,4;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
}
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'sysdate-1';
CROSSCHECK ARCHIVELOG ALL;
DELETE EXPIRED ARCHIVELOG ALL;
BACKUP ARCHIVELOG ALL;
BACKUP ARCHIVELOG ALL DELETE INPUT;
BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES;
-- In RAC
COPY ARCHIVELOG '+B2BSTARC/thread_2_seq_34.933' TO '/data/thread_2_seq_34.933';
BACKUP FORMAT '/archive/%d_%s_%p_%c_%t.arc.bkp'
ARCHIVELOG FROM SEQUENCE 1000 UNTIL SEQUENCE 1050;
-- RAC (specify thread)
BACKUP FORMAT '/archive/%d_%s_%p_%c_%t.arc.bkp'
ARCHIVELOG FROM SEQUENCE 1000 UNTIL SEQUENCE 1050 THREAD 2;
SPOOL TRACE TO '/tmp/rman_trace.out';
REPORT SCHEMA;
LIST BACKUP SUMMARY;
LIST BACKUP OF DATAFILE 1;
LIST COPY OF DATAFILE 1;
SPOOL TRACE OFF;
RECOVER TABLE SCOTT.SALGRADE UNTIL TIME "to_date('08/09/2016 18:49:40','mm/dd/yyyy hh24:mi:ss')"
AUXILIARY DESTINATION '/u03/arch/TEST/BACKUP'
DATAPUMP DESTINATION '/u03/arch/TEST/BACKUP';
-- 'auxiliary destination' hosts auxiliary instance files
-- 'datapump destination' hosts exported table dump
SELECT sid, serial#, context, sofar, totalwork,
ROUND(sofar/totalwork*100,2) AS "%_complete"
FROM v$session_longops
WHERE opname LIKE 'RMAN%'
AND opname NOT LIKE '%aggregate%'
AND totalwork != 0
AND sofar <> totalwork;
CONNECT TARGET sys/******@CRM_DB
CONNECT CATALOG RMAN_tst/*****@catdb
RUN {
ALLOCATE CHANNEL t1 TYPE SBT_TAPE PARMS
"ENV=(NSR_SERVER=nwwerpw,NSR_CLIENT=tsc_test01,NSR_DATA_VOLUME_POOL=DD086A1)";
SQL 'ALTER SYSTEM SET log_archive_dest_1=''LOCATION=/dumparea/''';
RESTORE ARCHIVELOG FROM SEQUENCE 7630 UNTIL SEQUENCE 7640;
RELEASE CHANNEL t1;
}
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE
'/export/home/oracle/RMAN/TESTDB/TRACKING_FILE/block_change_TESTDB.log';
SELECT filename, status FROM v$block_change_tracking;
rman checksyntax
RMAN> backup database;
-- "The command has no syntax errors"
EXEC DBMS_SCHEDULER.ENABLE('SCOTT.MONTHLYBILLING');
EXEC DBMS_SCHEDULER.DISABLE('SCOTT.MONTHLYBILLING');
EXEC DBMS_SCHEDULER.STOP_JOB('SCOTT.MONTHLYBILLING');
EXEC DBMS_SCHEDULER.DROP_JOB('SCOTT.MONTHLYBILLING');
EXEC DBMS_SCHEDULER.RUN_JOB('SCOTT.MONTHLYBILLING');
-- 1) Schedule
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'DAILYBILLINGJOB_SCHED',
start_date => SYSTIMESTAMP,
repeat_interval=> 'FREQ=DAILY;BYHOUR=11;BYMINUTE=30',
comments => 'DAILY BILLING JOB'
);
END;
/
-- 2) Program
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name => 'DAILYBILLINGJOB',
program_type => 'STORED_PROCEDURE',
program_action => 'DAILYJOB.BILLINGPROC',
number_of_arguments => 0,
enabled => TRUE,
comments => 'DAILY BILLING JOB'
);
END;
/
-- 3) Job
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'DAILYBILLINGJOB_RUN',
program_name => 'DAILYBILLINGJOB',
schedule_name => 'DAILYBILLINGJOB_SCHED',
enabled => FALSE,
comments => 'daily billing job'
);
END;
/
-- Enable
EXEC DBMS_SCHEDULER.ENABLE('DAILYBILLINGJOB_RUN');
BEGIN
DBMS_SCHEDULER.DROP_SCHEDULE(
schedule_name => 'DAILYBILLINGJOB_SCHED',
force => TRUE
);
END;
/
-- Create credential
BEGIN
dbms_credential.create_credential(
credential_name => 'ORACLEOSUSER',
username => 'oracle',
password => 'oracle@98765',
comments => 'Oracle OS User',
enabled => TRUE
);
END;
/
-- External script job
BEGIN
dbms_scheduler.create_job(
job_name => 'myscript4',
job_type => 'EXTERNAL_SCRIPT',
job_action => '/export/home/oracle/ttest.2.sh',
enabled => TRUE,
start_date => SYSDATE,
repeat_interval => 'FREQ=MINUTELY;BYMINUTE=1',
auto_drop => FALSE,
credential_name => 'ORACLEOSUSER'
);
END;
/
-- Currently running
SELECT job_name, session_id, running_instance, elapsed_time
FROM dba_scheduler_running_jobs;
-- Run details
SELECT * FROM dba_scheduler_job_run_details;
-- Job logs
SELECT * FROM dba_scheduler_job_log;
SET PAGESIZE 300 LINESIZE 200
SELECT * FROM dba_scheduler_windows;
SET PAGESIZE 200
SET LINES 299
COL start_date FOR a45
COL repeat_interval FOR a45
COL schedule_name FOR a34
SELECT schedule_name, schedule_type, start_date, repeat_interval
FROM dba_scheduler_schedules;
SET PAGESIZE 299
SET LINES 299
COL job_name FOR a24
COL actual_start_date FOR a56
COL run_duration FOR a34
SELECT job_name, status, actual_start_date, run_duration
FROM dba_scheduler_job_run_details
ORDER BY actual_start_date DESC;
SET PAGESIZE 299
SET LINES 299
COL job_name FOR a24
COL log_date FOR a40
COL operation FOR a19
COL additional_info FOR a79
SELECT job_name, log_date, status, operation, additional_info
FROM dba_scheduler_job_log
ORDER BY log_date DESC;
SELECT dbms_metadata.get_ddl('PROCOBJ','<JOB_NAME>','<JOB_OWNER>') FROM dual;
-- e.g.
SELECT dbms_metadata.get_ddl('PROCOBJ','DUP_ACC','SCOTT') FROM dual;
SELECT con_id, job_name, job_type, enabled, state, next_run_date, repeat_interval
FROM cdb_scheduler_jobs;
EXEC dbms_scheduler.copy_job('SCOTT.MY_JOB_2','DBACLASS.MY_JOB_2');
SELECT job, log_user, schema_user FROM dba_jobs;
ALTER SESSION SET current_schema=DBATEST;
SET SERVEROUTPUT ON
DECLARE
callstr VARCHAR2(500);
BEGIN
dbms_job.user_export(743, callstr);
dbms_output.put_line(callstr);
END;
/
-- List jobs
SELECT job AS jobno, schema_user, what FROM dba_jobs;
-- Disable
EXEC DBMS_IJOB.BROKEN(:jobno, TRUE);
-- Enable
EXEC DBMS_IJOB.BROKEN(:jobno, FALSE);
-- Remove
EXEC DBMS_IJOB.REMOVE(:jobno);
# stop options: normal | immediate (default) | transactional | abort
srvctl stop database -d PRODB -o normal
srvctl stop database -d PRODB -o immediate
srvctl stop database -d PRODB -o transactional
srvctl stop database -d PRODB -o abort
# start options: nomount | mount | open (default)
srvctl start database -d PRODB -o nomount
srvctl start database -d PRODB -o mount
srvctl start database -d PRODB -o open
srvctl remove database -d PRODB -f -y
srvctl add database -d PRODB -o /u01/app/oracle/product/12.1.0.2/dbhome_1 -p +DATA/PRODDB/parameterfile/spfilePRODB.ora
srvctl remove instance -d PRODB -i PRODB1
srvctl add instance -d PRODB -i PRODB1 -n rachost1
srvctl stop instance -d PRODB -i PRODB1
srvctl start instance -d PRODB -i PRODB1
srvctl enable instance -d DB_UNIQUE_NAME -i INSTANCE_NAME
srvctl disable instance -d DB_UNIQUE_NAME -i INSTANCE_NAME
srvctl enable database -d DB_UNIQUE_NAME
srvctl disable database -d DB_UNIQUE_NAME
srvctl relocate service -d PREDB -s PRDB_SVC -i PREDB2 -t PREDB1
srvctl status service -d PREDB -s PRDB_SVC
srvctl add service -d PREDB -s PRDB_SRV -r "PREDB1,PREDB2" -a "PREDB2" -P BASIC
srvctl remove service -d PREDB -s PRDB_SRV
srvctl start service -d PREDB -s PRDB_SRV
srvctl stop service -d PREDB -s PRDB_SRV
srvctl status mgmtdb
srvctl stop mgmtdb
srvctl start mgmtdb
# Set this to enable trace at OS level, then run srvctl
export SRVM_TRACE=true
srvctl status database -d ORACL
BEGIN
dbms_stats.gather_schema_stats(
ownname => 'SCOTT', -- schema name
options => 'GATHER AUTO',
estimate_percent=> dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 24
);
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SCOTT',
tabname => 'TEST',
cascade => TRUE, -- also collect index stats
method_opt => 'for all indexed columns size 1',
granularity => 'ALL',
estimate_percent => dbms_stats.auto_sample_size,
degree => 8
);
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SCOTT',
tabname => 'TEST', -- table name
partname => 'TEST_JAN2016', -- partition name
method_opt => 'for all indexed columns size 1',
granularity => 'APPROX_GLOBAL AND PARTITION',
degree => 8
);
END;
/
-- Lock
EXEC DBMS_STATS.lock_schema_stats('SCOTT');
EXEC DBMS_STATS.lock_table_stats('SCOTT', 'TEST');
EXEC DBMS_STATS.lock_partition_stats('SCOTT', 'TEST', 'TEST_JAN2016');
-- Unlock
EXEC DBMS_STATS.unlock_schema_stats('SCOTT');
EXEC DBMS_STATS.unlock_table_stats('SCOTT', 'TEST');
EXEC DBMS_STATS.unlock_partition_stats('SCOTT', 'TEST', 'TEST_JAN2016');
-- Check status
SELECT stattype_locked
FROM dba_tab_statistics
WHERE table_name='TEST' AND owner='SCOTT';
-- Create staging table
EXEC dbms_stats.create_stat_table(ownname=>'SCOTT', stattab=>'STAT_BACKUP', tblspace=>'USERS');
-- Export
EXEC dbms_stats.export_table_stats(ownname=>'SCOTT', tabname=>'EMP', stattab=>'STAT_BACKUP', cascade=>TRUE);
-- Import
EXEC dbms_stats.import_table_stats(ownname=>'SCOTT', tabname=>'EMP', stattab=>'STAT_BACKUP', cascade=>TRUE);
-- Table
SELECT owner, table_name, stale_stats
FROM dba_tab_statistics
WHERE owner='&SCHEMA_NAME' AND table_name='&TABLE_NAME';
-- Index
SELECT owner, index_name, table_name
FROM dba_ind_statistics
WHERE owner='&SCHEMA_NAME' AND index_name='&INDEX_NAME';
SET LINES 200
COL owner FOR a12
COL table_name FOR a21
SELECT owner, table_name, stats_update_time
FROM dba_tab_stats_history
WHERE table_name='&TABLE_NAME';
-- Table
EXEC DBMS_STATS.PUBLISH_PENDING_STATS('SCHEMA_NAME','TABLE_NAME');
-- Schema
EXEC dbms_stats.publish_pending_stats('SCHEMA_NAME', NULL);
-- Set publish preference at table level
EXEC dbms_stats.set_table_prefs('SCOTT','EMP','PUBLISH','FALSE');
-- Check publish preference
SELECT dbms_stats.get_prefs('PUBLISH','SCOTT','EMP') FROM dual;
-- Schema-level examples
SELECT dbms_stats.get_prefs('PUBLISH','SCOTT') FROM dual;
EXEC dbms_stats.set_schema_prefs('DBATEST','PUBLISH','FALSE');
-- Index / Database helpers (names only)
-- SET_INDEX_STATS / GET_INDEX_STATS
-- SET_DATABASE_PREFS
-- View
SELECT dbms_stats.get_stats_history_retention FROM dual;
-- Modify (days)
EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(60);
SELECT occupant_desc, space_usage_kbytes
FROM v$sysaux_occupants
WHERE occupant_desc LIKE '%Statistics%';
-- Check
SELECT dbms_stats.get_prefs('INCREMENTAL', tabname=>'EMPLOYEE', ownname=>'SCOTT') FROM dual;
-- Enable
EXEC DBMS_STATS.SET_TABLE_PREFS('SCOTT','EMPLOYEE','INCREMENTAL','TRUE');
-- Verify again
SELECT dbms_stats.get_prefs('INCREMENTAL', tabname=>'EMPLOYEE', ownname=>'SCOTT') FROM dual;
EXEC DBMS_STATS.delete_database_stats;
EXEC DBMS_STATS.delete_schema_stats('DBACLASS');
EXEC DBMS_STATS.delete_table_stats('DBACLASS','DEPT');
EXEC DBMS_STATS.delete_column_stats('DBACLASS','DEPT','CLASS');
EXEC DBMS_STATS.delete_index_stats('DBACLASS','CLASS_IDX');
EXEC DBMS_STATS.delete_dictionary_stats;
EXEC DBMS_STATS.UPGRADE_STAT_TABLE(ownname=>'RAJ', stattab=>'STAT_TEST');
-- Regular FS
CREATE TABLESPACE DATA DATAFILE '/u01/dbaclass/oradata/data01.dbf' SIZE 5G AUTOEXTEND ON NEXT 500M;
-- ASM diskgroup
CREATE TABLESPACE DATA DATAFILE '+DATAG' SIZE 5G AUTOEXTEND ON NEXT 500M;
-- BIGFILE tablespace
CREATE BIGFILE TABLESPACE BIGTS DATAFILE '/u01/dbaclass/oradata/bigts01.dbf' SIZE 100G AUTOEXTEND ON NEXT 1G;
SELECT file_id, file_name, tablespace_name FROM dba_data_files WHERE file_id=37;
ALTER TABLESPACE TESTING RENAME TO PRODUCING;
-- Keep datafiles
DROP TABLESPACE TESTING;
-- Include datafiles
DROP TABLESPACE TESTING INCLUDING CONTENTS AND DATAFILES;
-- Add
ALTER TABLESPACE USERS ADD DATAFILE '/u01/data/users02.dbf' SIZE 5G;
-- Autoextend
ALTER DATABASE DATAFILE '/u01/data/users02.dbf' AUTOEXTEND ON;
-- Resize
ALTER DATABASE DATAFILE '/u01/data/users02.dbf' RESIZE 10G;
-- Offline / online
ALTER DATABASE DATAFILE '/u01/data/users02.dbf' OFFLINE;
ALTER DATABASE DATAFILE '/u01/data/users02.dbf' ONLINE;
-- Drop datafile
ALTER TABLESPACE USERS DROP DATAFILE '/u01/data/users02.dbf';
-- Add tempfile
ALTER TABLESPACE TEMP1 ADD TEMPFILE '/u01/dbaclass/tempfile/temp02.dbf' SIZE 1G AUTOEXTEND ON NEXT 200M;
-- Resize tempfile
ALTER DATABASE TEMPFILE '/u01/dbaclass/tempfile/temp02.dbf' RESIZE 2G;
-- Drop tempfile
ALTER DATABASE TEMPFILE '/u01/dbaclass/tempfile/temp02.dbf' DROP INCLUDING DATAFILES;
-- 12c: online move/rename
ALTER DATABASE MOVE DATAFILE '/home/oracle/producing1.dbf'
TO '/home/oracle/app/oracle/oradata/cdb1/testin1.dbf';
-- 11g: requires downtime
ALTER DATABASE DATAFILE '/home/oracle/app/oracle/oradata/cdb1/testin1.dbf' OFFLINE;
! mv /home/oracle/app/oracle/oradata/cdb1/testin1.dbf /home/oracle/producing1.dbf
ALTER DATABASE RENAME FILE '/home/oracle/app/oracle/oradata/cdb1/testin1.dbf'
TO '/home/oracle/producing1.dbf';
RECOVER DATAFILE 37;
ALTER DATABASE DATAFILE '/home/oracle/producing1.dbf' ONLINE;
SET FEED OFF
SET PAGESIZE 10000
SET LINESIZE 500
COL file_nr FORMAT 999999 HEADING 'File#'
COL checkpoint_time FORMAT A20 HEADING 'Checkpoint|Time'
COL file_name FORMAT A59 HEADING 'Filename'
SELECT file# AS file_nr,
TO_CHAR(checkpoint_time,'DD.MM.YYYY:HH24:MI:SS') AS checkpoint_time,
name AS file_name
FROM v$datafile_header;
SELECT occupant_name, occupant_desc, space_usage_kbytes
FROM v$sysaux_occupants;
-- Syntax
-- create user <USER_NAME> identified by <PASSWORD> default tablespace <TBS> temporary tablespace <TEMP_TBS>;
CREATE USER SCOTT IDENTIFIED BY "oracle#41234"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
-- Force password change on first login
CREATE USER SCOTT IDENTIFIED BY "oracle#41234"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
PASSWORD EXPIRE;
ALTER USER SCOTT IDENTIFIED BY NEW_PWD; -- change password
ALTER USER SCOTT PROFILE SIEBEL_PROFILE; -- change profile
ALTER USER SCOTT ACCOUNT UNLOCK; -- unlock
ALTER USER SCOTT ACCOUNT LOCK; -- lock
ALTER USER SCOTT PASSWORD EXPIRE; -- expire now
SET LINES 200
COL username FOR a23
SELECT username, default_tablespace FROM dba_users WHERE username='SCOTT';
ALTER USER SCOTT DEFAULT TABLESPACE DATATS;
SELECT username, default_tablespace FROM dba_users WHERE username='SCOTT';
SET LINES 299
SELECT tablespace_name,
bytes/1024/1024 AS utilizied_space,
max_bytes/1024/1024 AS quota_allocated
FROM dba_ts_quotas
WHERE username='&USER_NAME';
-- Update quota
ALTER USER SCOTT QUOTA 5G ON USERS;
ALTER USER SCOTT QUOTA UNLIMITED ON USERS;
SELECT * FROM dba_sys_privs WHERE grantee='SCOTT'; -- system privs
SELECT * FROM dba_role_privs WHERE grantee='SCOTT'; -- roles
SELECT * FROM dba_tab_privs WHERE grantee='SCOTT'; -- object privs
SELECT * FROM dba_col_privs WHERE grantee='SCOTT'; -- column privs
GRANT READ ANY TABLE TO SCOTT;
GRANT SELECT ANY TABLE TO SCOTT;
GRANT INSERT, UPDATE, DELETE ON TESTUSER1.EMPTABL TO SCOTT;
GRANT ALL ON TESTUSER1.EMPTABL TO SCOTT;
-- Column-level (INSERT/UPDATE only)
GRANT INSERT(emp_id) ON TESTUSER1.EMPTABL TO SCOTT;
GRANT UPDATE(emp_id) ON TESTUSER1.EMPTABL TO SCOTT;
-- Connect TEST1 through TEST2 (without TEST2 password)
-- As SYSDBA:
ALTER USER TEST2 GRANT CONNECT THROUGH TEST1;
-- Connect:
-- sqlplus TEST1[TEST2]@db
-- show user => TEST2
-- Create common user (from CDB$ROOT)
CREATE USER c##dbaclass IDENTIFIED BY dbaclass CONTAINER=ALL;
-- Common role
CREATE ROLE c##dbarole;
SELECT name, type#, ctime, ptime, exptime, ltime
FROM sys.user$
WHERE name IN ('SYS','SYSTEM')
ORDER BY name;
-- ctime: creation, ptime: last password change, exptime: expiry, ltime: lock time
CREATE PROFILE app_profile LIMIT
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME 90
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED;
ALTER PROFILE app_profile LIMIT PASSWORD_LIFE_TIME UNLIMITED;
SELECT username FROM dba_users WHERE oracle_maintained='Y';
SET PAGESIZE 2000 LINES 2000 LONG 999
COL path FOR a54
SELECT name, path, header_status, total_mb, free_mb,
TRUNC(bytes_read/1024/1024) AS read_mb,
TRUNC(bytes_written/1024/1024) AS write_mb
FROM v$asm_disk;
SELECT name, free_mb, total_mb, free_mb/total_mb*100 AS percentage
FROM v$asm_diskgroup;
ALTER DISKGROUP DATA DROP DISK DATA_ASM0001;
ALTER DISKGROUP DATA DROP DISK DATA_ASM0001, DATA_ASM00002, DATA_ASM0003 REBALANCE POWER 100;
-- Monitor rebalance
SELECT * FROM v$asm_operation;
SET PAGESIZE 299 LINES 2999
SELECT group_number, operation, state, power, actual, est_minutes
FROM gv$asm_operation;
./runcluvfy.sh stage -pre crsinst -n host1,host2,host3 -verbose
./runcluvfy.sh stage -pre crsinst -n classpredb1,classpredb2 -verbose
# asmcmd
asmcmd> cp --port 1521 s_srv_new21.dbf sys/oracle@172.20.17.69:+ASM1:+ARCL/s_srv_new21.dbf
-- Instance-specific
ALTER DISKGROUP DATA MOUNT;
ALTER DISKGROUP DATA DISMOUNT;
-- All
ALTER DISKGROUP ALL MOUNT;
ALTER DISKGROUP ALL DISMOUNT;
DROP DISKGROUP NSMREDOA INCLUDING CONTENTS;
$GRID_HOME/bin/cluvfy comp clocksync -n all
# CTSS vs NTP
crsctl check ctss
# "Observer" => using NTP, "Active" => CTSS handles time sync
/etc/init.d/oracleasm querydisk /dev/sdn1
/etc/init.d/oracleasm createdisk ARCDATA /dev/sdn1
/etc/init.d/oracleasm querydisk /dev/sdn1
/etc/init.d/oracleasm listdisks
SHOW PARAMETER asm_power_limit; -- default 1
SELECT inst_id, group_number, operation, state, power, est_rate, est_minutes
FROM gv$asm_operation;
ALTER DISKGROUP SALDATA REBALANCE POWER 4;
# 12c+ (asmcmd)
ASMCMD> pwcreate --dbuniquename PRDPRE +DATA/PWDFILE/pwdPRDPRE oracle
# All versions
orapwd file='+DATA/orapwPRODPRE' ENTRIES=10 DBUNIQUENAME='PRODPRE'
crsctl stop has
crsctl start has
ASMCMD> lspwusr
ASMCMD> orapwusr --modify asmsnmp
SELECT * FROM v$asm_disk_iostat;
-- Enable tracing for asmcmd (reference placeholder; configure tracing via environment or diag_dest as needed)
export ORACLE_SID=+ASM
asmcmd
ASMCMD> orapwusr --modify --password sys
# Or recreate with orapwd
SHOW PARAMETER audit_trail
-- Enable (requires restart)
ALTER SYSTEM SET audit_trail='DB' SCOPE=SPFILE;
-- or
ALTER SYSTEM SET audit_trail='DB, EXTENDED' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
COL user_name FOR a12 HEADING "User name"
COL audit_option FOR a30 HEADING "Audit Option"
SET PAGES 1000
SELECT user_name, audit_option, success, failure
FROM sys.dba_stmt_audit_opts
ORDER BY user_name, proxy_name, audit_option;
COL user_name FOR a12 HEADING "User name"
COL privilege FOR a30 HEADING "Privilege"
SET PAGES 1000
SELECT user_name, privilege, success, failure
FROM dba_priv_audit_opts
ORDER BY user_name, proxy_name, privilege;
COL user_name FOR a12 HEADING "User name"
COL timest FOR a13
COL userid FOR a8 TRUNC
COL obn FOR a10 TRUNC
COL name FOR a13 TRUNC
COL object_name FOR a10
COL object_type FOR a6
COL priv_used FOR a15 TRUNC
SET VERIFY OFF PAGES 1000 LINES 299
SELECT username AS userid,
TO_CHAR(timestamp,'dd-mon hh24:mi') AS timest,
action_name AS acname, priv_used, obj_name AS obn, ses_actions
FROM sys.dba_audit_trail
WHERE timestamp > SYSDATE - &HOURS*(1/24)
AND username='&USER_NAME'
ORDER BY timestamp;
ALTER SYSTEM SET audit_sys_operations=TRUE SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
SHOW PARAMETER audit_sys_operations;
-- Check (FALSE => mixed auditing)
SELECT value FROM v$option WHERE parameter='Unified Auditing';
# Enable (requires relink)
shutdown immediate
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk unaiaud_on ioracle
startup
SELECT value FROM v$option WHERE parameter='Unified Auditing';
SELECT DISTINCT policy_name FROM audit_unified_policies;
SELECT DISTINCT policy_name FROM audit_unified_enabled_policies;
SELECT audit_option
FROM audit_unified_policies
WHERE policy_name='ORA_SECURECONFIG';
SET LINES 299
COL sql_text FOR a23
COL action_name FOR a18
COL unified_audit_policies FOR a23
SELECT action_name, sql_text, unified_audit_policies, event_timestamp
FROM unified_audit_trail
WHERE event_timestamp > SYSDATE - 1/24;
CREATE AUDIT POLICY test_case2
ACTIONS CREATE TABLE,
INSERT ON bsstdba.emp_tab,
TRUNCATE TABLE,
SELECT ON bsstdba.prod_tab;
SELECT policy_name, audit_option, audit_condition, object_schema, object_name
FROM audit_unified_policies
WHERE policy_name='TEST_CASE2';
AUDIT POLICY test_case2;
SELECT DISTINCT policy_name
FROM audit_unified_enabled_policies
WHERE policy_name='TEST_CASE2';
CREATE AUDIT POLICY expdp_aduit ACTIONS COMPONENT=DATAPUMP EXPORT;
AUDIT POLICY expdp_aduit;
SELECT dbusername, dp_text_parameters1
FROM unified_audit_trail
WHERE dp_text_parameters1 IS NOT NULL;
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'AUDIT_DATA');
END;
/
SELECT owner, segment_name, segment_type, tablespace_name, bytes/1024/1024 MB
FROM dba_segments
WHERE segment_name='AUD$';
SELECT * FROM gv$encryption_wallet;
$GRID_HOME/bin/crsctl enable crs
$GRID_HOME/bin/crsctl disable crs
$GRID_HOME/bin/cemutlo -n
$GRID_HOME/bin/olsnodes -c
$GRID_HOME/bin/crsctl stop crs
$GRID_HOME/bin/crsctl start crs
$GRID_HOME/bin/crsctl query css votedisk # voting disks
$GRID_HOME/bin/ocrcheck # OCR
$GRID_HOME/bin/crsctl query crs softwareversion
$GRID_HOME/bin/crsctl query crs softwareversion host-dbaclass1
$GRID_HOME/bin/crsctl stat res -t
$GRID_HOME/bin/crsctl check crs
$GRID_HOME/bin/crsctl check cssd
$GRID_HOME/bin/crsctl check crsd
$GRID_HOME/bin/crsctl check evmd
$GRID_HOME/bin/oifcfg getif
SELECT name, ip_address FROM v$cluster_interconnects;
$GRID_HOME/bin/ocrconfig -showbackup
$GRID_HOME/bin/ocrconfig -manualbackup
$GRID_HOME/bin/crsctl replace votedisk +NEW_DG
$GRID_HOME/bin/crsctl query css votedisk
crsctl get css disktimeout
crsctl get css misscount
olsnodes # list nodes
olsnodes -n # with node number
olsnodes -i # with VIP
olsnodes -s -t # status / type
olsnodes -a # leaf or hub
olsnodes -l -p # private IP of local node
olsnodes -c # cluster name
oifcfg iflist -p -n
# sample output:
# backup0 172.21.56.0 PRIVATE 255.255.254.0
# cdnet0 162.168.1.0 PRIVATE 255.255.255.0
# ...
$GRID_HOME/bin/ocrcheck -local
$GRID_HOME/bin/ocrconfig -local -showbackup
$GRID_HOME/bin/ocrconfig -local -manualbackup