DB script modifing
DB MONITORING
find current running sqls
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;
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;
Find active sessions in oracle database
Script to find active sessions in database
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
/
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
/
Find waitevents in database
Find waitevents in database
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 username is not null
order by 6
/
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 username is not null
order by 6
/
Find sessions generating undo
Find sessions generating undo
select a.sid, a.serial#, a.username, b.used_urec used_undo_record, b.used_ublk used_undo_blocks
from v$session a, v$transaction b
where a.saddr=b.ses_addr ;
from v$session a, v$transaction b
where a.saddr=b.ses_addr ;
Find the temp usage of the sessions
Find the temp usage of sessions
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 temp_size desc
/
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 temp_size desc
/
Find sessions generating lot of redo
Find sessions generating lot of redo
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,sn.SERIAL#,n.name, round(value/1024/1024,2) redo_mb, sn.username,sn.status,substr (sn.program,1,21) "program", sn.type, sn.module,sn.sql_id
from v$sesstat s join v$statname n on n.statistic# = s.statistic#
join v$session sn on sn.sid = s.sid where n.name like 'redo size' and s.value!=0 order by
redo_mb desc;
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,sn.SERIAL#,n.name, round(value/1024/1024,2) redo_mb, sn.username,sn.status,substr (sn.program,1,21) "program", sn.type, sn.module,sn.sql_id
from v$sesstat s join v$statname n on n.statistic# = s.statistic#
join v$session sn on sn.sid = s.sid where n.name like 'redo size' and s.value!=0 order by
redo_mb desc;
Monitor tablespace usage
Script to Monitor tablespace usage
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'
COLUMN max_size_of_tablespace format 99999999 heading 'ExtendUpto'
COLUM total_auto_used_pct format 999.99 heading 'Max%Used'
COLUMN total_auto_free_pct format 999.99 heading 'Max%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),
AUTOEXTEND AS
(SELECT tablespace_name, SUM (size_to_grow) total_growth_tbs
FROM (SELECT tablespace_name, SUM (maxbytes)/1024/1024 size_to_grow
FROM dba_data_files
WHERE autoextensible = 'YES'
GROUP BY tablespace_name
UNION
SELECT tablespace_name, SUM (BYTES)/1024/1024 size_to_grow
FROM dba_data_files
WHERE autoextensible = 'NO'
GROUP BY tablespace_name)
GROUP BY tablespace_name)
SELECT c.instance_name,a.tablespace_name Tablespace,
CASE tbs_auto.autoextensible
WHEN 'YES'
THEN 'YES'
ELSE 'NO'
END AS autoextensible,
files.tbs_files files_in_tablespace,
files.total_tbs_bytes total_tablespace_space,
(files.total_tbs_bytes - fragments.total_tbs_free_bytes
) total_used_space,
fragments.total_tbs_free_bytes total_tablespace_free_space,
round(( ( (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
/ files.total_tbs_bytes
)
* 100
)) total_used_pct,
round(((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100
)) total_free_pct
FROM dba_tablespaces a,v$instance c , files, fragments, AUTOEXTEND, tbs_auto
WHERE a.tablespace_name = files.tablespace_name
AND a.tablespace_name = fragments.tablespace_name
AND a.tablespace_name = AUTOEXTEND.tablespace_name
AND a.tablespace_name = tbs_auto.tablespace_name(+)
order by total_free_pct;
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'
COLUMN max_size_of_tablespace format 99999999 heading 'ExtendUpto'
COLUM total_auto_used_pct format 999.99 heading 'Max%Used'
COLUMN total_auto_free_pct format 999.99 heading 'Max%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),
AUTOEXTEND AS
(SELECT tablespace_name, SUM (size_to_grow) total_growth_tbs
FROM (SELECT tablespace_name, SUM (maxbytes)/1024/1024 size_to_grow
FROM dba_data_files
WHERE autoextensible = 'YES'
GROUP BY tablespace_name
UNION
SELECT tablespace_name, SUM (BYTES)/1024/1024 size_to_grow
FROM dba_data_files
WHERE autoextensible = 'NO'
GROUP BY tablespace_name)
GROUP BY tablespace_name)
SELECT c.instance_name,a.tablespace_name Tablespace,
CASE tbs_auto.autoextensible
WHEN 'YES'
THEN 'YES'
ELSE 'NO'
END AS autoextensible,
files.tbs_files files_in_tablespace,
files.total_tbs_bytes total_tablespace_space,
(files.total_tbs_bytes - fragments.total_tbs_free_bytes
) total_used_space,
fragments.total_tbs_free_bytes total_tablespace_free_space,
round(( ( (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
/ files.total_tbs_bytes
)
* 100
)) total_used_pct,
round(((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100
)) total_free_pct
FROM dba_tablespaces a,v$instance c , files, fragments, AUTOEXTEND, tbs_auto
WHERE a.tablespace_name = files.tablespace_name
AND a.tablespace_name = fragments.tablespace_name
AND a.tablespace_name = AUTOEXTEND.tablespace_name
AND a.tablespace_name = tbs_auto.tablespace_name(+)
order by total_free_pct;
Script to Monitor undo tablespace usage
Monitor undo tablespace usage
select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB
from (select sum(bytes) / 1024 / 1024 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 USAGEMB
from DBA_UNDO_EXTENTS c
where status <> 'EXPIRED'
group by c.tablespace_name) b
where a.tablespace_name = b.tablespace_name;
from (select sum(bytes) / 1024 / 1024 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 USAGEMB
from DBA_UNDO_EXTENTS c
where status <> 'EXPIRED'
group by c.tablespace_name) b
where a.tablespace_name = b.tablespace_name;
Monitor TEMP tablespace usage
Script to Monitor TEMP tablespace usage
select a.tablespace_name tablespace,
d.TEMP_TOTAL_MB,
sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_USED_MB,
d.TEMP_TOTAL_MB - sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_FREE_MB
from v$sort_segment a,
(
select b.name, c.block_size, sum (c.bytes) / 1024 / 1024 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;
d.TEMP_TOTAL_MB,
sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_USED_MB,
d.TEMP_TOTAL_MB - sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_FREE_MB
from v$sort_segment a,
(
select b.name, c.block_size, sum (c.bytes) / 1024 / 1024 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;
Find blocking sessions
Find blocking sessions
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;
s.inst_id,
s.blocking_session,
s.sid,
s.serial#,
s.seconds_in_wait
FROM
gv$session s
WHERE
blocking_session IS NOT NULL;
Find long running operations
Find long running operations
select sid,inst_id,opname,totalwork,sofar,start_time,time_remaining
from gv$session_longops
where totalwork<>sofar
/
from gv$session_longops
where totalwork<>sofar
/
Find locks present in database
Find locks present in database
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)) mode_held
from v$locked_object lo, dba_objects do
where lo.object_id = do.object_id
order by 1,5
/
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)) mode_held
from v$locked_object lo, dba_objects do
where lo.object_id = do.object_id
order by 1,5
/
Find queries triggered from a procedure
Find queries triggered from a procedure
-- Below script will provide the dependent queries getting triggered from a procedure.
SELECT s.sql_id, s.sql_text
FROM gv$sqlarea s JOIN dba_objects o ON s.program_id = o.object_id
and o.object_name = '&procedure_name';
SELECT s.sql_id, s.sql_text
FROM gv$sqlarea s JOIN dba_objects o ON s.program_id = o.object_id
and o.object_name = '&procedure_name';
Get sid from os pid
Get sid from os pid
- Get sid from os pid ( server process)
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, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;
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, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;
Kill all sessions of a sql_id
Kill all sessions of a sql_id
select 'alter system kill session ' ||''''||SID||','||SERIAL#||' immediate ;' from v$session
where sql_id='&sql_id';
--- FOR RAC
select 'alter system kill session ' ||''''||SID||','||SERIAL#||',@'||inst_id||''''||' immediate ;'
from gv$session where sql_id='&sql_id'
where sql_id='&sql_id';
--- FOR RAC
select 'alter system kill session ' ||''''||SID||','||SERIAL#||',@'||inst_id||''''||' immediate ;'
from gv$session where sql_id='&sql_id'
kill all session of a user
kill all session of a user
BEGIN
FOR r IN (select sid,serial# from v$session where username = 'TEST_ANB')
LOOP
EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid
|| ',' || r.serial# || '''';
END LOOP;
END;
/
FOR r IN (select sid,serial# from v$session where username = 'TEST_ANB')
LOOP
EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid
|| ',' || r.serial# || '''';
END LOOP;
END;
/
get parallel query detail
get parallel query detail
col username for a9
col sid 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) ) ) "Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "Slave Set",
to_char(s.sid) "SID",
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
px.req_degree "Requested DOP",
px.degree "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
/
col sid 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) ) ) "Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "Slave Set",
to_char(s.sid) "SID",
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
px.req_degree "Requested DOP",
px.degree "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
/
Top Query with high elapsed time
Top Query with high elapsed time
--- Queries in last 1 hour ( Run from Toad, for proper view)
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' ),executions, elapsed_time/executions/1000/1000,
rows_processed,sql_plan_baseline from gv$sql where last_active_time>sysdate-1/24
and executions <> 0 order by elapsed_time/executions desc
Monitor parallel queries
Monitor parallel queries
select
s.inst_id,
decode(px.qcinst_id,NULL,s.username,
' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "Slave Set",
to_char(s.sid) "SID",
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
px.req_degree "Requested DOP",
px.degree "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
s.inst_id,
decode(px.qcinst_id,NULL,s.username,
' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "Slave Set",
to_char(s.sid) "SID",
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
px.req_degree "Requested DOP",
px.degree "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
Find the locked objects
Find the locked objects
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) 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;
SET PAGESIZE 14
SET VERIFY ON
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) 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;
SET PAGESIZE 14
SET VERIFY ON
Check open cursors
Check open cursors
-- Current open cursor
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';
-- Max allowed open cursor and total open cursor
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 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';
-- Max allowed open cursor and total open cursor
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;
Session login history from ASH
Session login history from ASH
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, dba_hist_sqltext b, dba_users c
where a.SQL_ID = b.SQL_ID(+)
and a.user_id=c.user_id
and c.username='&username'
order by a.SQL_EXEC_START asc;
from DBA_HIST_ACTIVE_SESS_HISTORY a, dba_hist_sqltext b, dba_users c
where a.SQL_ID = b.SQL_ID(+)
and a.user_id=c.user_id
and c.username='&username'
order by a.SQL_EXEC_START asc;
DB MONITORING
xplain plan of sql_id from cursor
xplain plan of sql_id from cursor
--- First get the child number of the sql_id .One sql_id can have multiple child number( one for each plan_hash_value)
SQL> select sql_id,child_number,plan_hash_value from gv$sql where sql_id='9n2a2c8pvu6bm';
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE
------------- ------------ ---------------
9n2a2c8pvu6bm 1 13761463
--- Now get the explain plan for cursor:
SELECT * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sqlid',&child_number));
xplain plan of sql_id from AWR
xplain plan of sql_id from AWR
set lines 200
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));
Get sql_text from sid
Get sql_text from sid
col sql_text form 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
/
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
/
xplain plan of a sql statement
xplain plan of a sql statement
-- Generate explain plan
-- Syntax EXPLAIN PLAN FOR < SQL STATEMENT> ;
explain plan for
select count(*) from dbaclass;
-- View explain plan
select * from table(dbms_xplan.display);
-- Syntax EXPLAIN PLAN FOR < SQL STATEMENT> ;
explain plan for
select count(*) from dbaclass;
-- View explain plan
select * from table(dbms_xplan.display);
xplain plan of a sql baseline
xplain plan of a sql baseline
--- 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 * 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'));
Get bind values of a sql_id
Get bind values of a sql_id
SELECT
sql_id,
b. LAST_CAPTURED,
t.sql_text sql_text,
b.HASH_VALUE,
b.name bind_name,
b.value_string 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'
/
sql_id,
b. LAST_CAPTURED,
t.sql_text sql_text,
b.HASH_VALUE,
b.name bind_name,
b.value_string 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'
/
Flush a sql query from cursor
Flush a sql query from cursor
-- First get the address, hash_value of the sql_id
select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '5qd8a442c328k';
ADDRESS HASH_VALUE
--------------- ------------
C000007067F39FF0 4000666812
-- Now flush the query
SQL> exec DBMS_SHARED_POOL.PURGE ('C000007067F39FF0, 4000666812', 'C');
Note : For RAC, same need to be executed on all the nodes .
select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '5qd8a442c328k';
ADDRESS HASH_VALUE
--------------- ------------
C000007067F39FF0 4000666812
-- Now flush the query
SQL> exec DBMS_SHARED_POOL.PURGE ('C000007067F39FF0, 4000666812', 'C');
Note : For RAC, same need to be executed on all the nodes .
Enable trace for a sql_id
Enable trace for a sql_id
alter system set events 'sql_trace [sql:8krc88r46raff]';
10053 OPTIMIZER TRACE
10053 OPTIMIZER TRACE
begin
dbms_sqldiag.dump_trace(p_sql_id=>'dmx08r6ayx800',
p_child_number=>0,
p_component=>'Compiler',
p_file_id=>'TEST_OBJ3_TRC');
END;
/
dbms_sqldiag.dump_trace(p_sql_id=>'dmx08r6ayx800',
p_child_number=>0,
p_component=>'Compiler',
p_file_id=>'TEST_OBJ3_TRC');
END;
/
Enable trace for a session
Enable trace for a session
EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>321, serial#=>1234, sql_trace=>FALSE);
--- Get the trace file name
SELECT p.tracefile FROM v$session s JOIN v$process p ON s.paddr = p.addr WHERE s.sid = 321;
--- Get the trace file name
SELECT p.tracefile FROM v$session s JOIN v$process p ON s.paddr = p.addr WHERE s.sid = 321;
Tracing all session of a user
Tracing all session of a user
--- CREATE THE BELOW TRIGGER TO ENABLE TRACE ALL SESSION OF USER ( SCOTT)
CREATE OR REPLACE TRIGGER USER_TRACE_TRG
AFTER LOGON ON DATABASE
BEGIN
IF USER = 'SCOTT'
THEN
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
CREATE OR REPLACE TRIGGER USER_TRACE_TRG
AFTER LOGON ON DATABASE
BEGIN
IF USER = 'SCOTT'
THEN
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
Enable tracing for a listener
Enable tracing for a listener
- Set to the listener you want to trace
LSNRCTL> set cur LISTENER_TEST
-- Enable Trace:
LSNRCTL> set trc_level ADMIN
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_TEST)))
LISTENER_TEST parameter "trc_level" set to admin
The command completed successfully
LSNRCTL> set cur LISTENER_TEST
-- Enable Trace:
LSNRCTL> set trc_level ADMIN
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_TEST)))
LISTENER_TEST parameter "trc_level" set to admin
The command completed successfully
execution detail of a sql_id in cursor
execution detail of a sql_id in cursor
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' ),sql_plan_baseline,executions,
elapsed_time/executions/1000/1000,rows_processed from gv$sql
where sql_id in ('&sql_id');
to_char(last_active_time,'DD/MM/YY HH24:MI:SS' ),sql_plan_baseline,executions,
elapsed_time/executions/1000/1000,rows_processed from gv$sql
where sql_id in ('&sql_id');
Pga usage by sessions
Pga usage by sessions
set lines 2000
SELECT SID, b.NAME, ROUND(a.VALUE/(1024*1024),2) MB FROM
v$sesstat a, v$statname b
WHERE (NAME LIKE '%session uga memory%' OR NAME LIKE '%session pga memory%')
AND a.statistic# = b.statistic# order by ROUND(a.VALUE/(1024*1024),2) desc
SELECT SID, b.NAME, ROUND(a.VALUE/(1024*1024),2) MB FROM
v$sesstat a, v$statname b
WHERE (NAME LIKE '%session uga memory%' OR NAME LIKE '%session pga memory%')
AND a.statistic# = b.statistic# order by ROUND(a.VALUE/(1024*1024),2) desc
segments with high physical read
segments with high physical read
set pagesize 200
setlinesize 120
col segment_name format a20
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 in ('physical reads')
order by total_physical_reads desc)
where rownum <=10;
setlinesize 120
col segment_name format a20
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 in ('physical reads')
order by total_physical_reads desc)
where rownum <=10;
I/O usage of each tempfile
I/O usage of each tempfile
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 f.phyblkrd + f.phyblkwrt DESC;
select * from (SELECT u.tablespace, s.username, s.sid, s.serial#, s.logon_time, program, u.extents, ((u.blocks*8)/1024) as MB,
i.inst_id,i.host_name
FROM gv$session s, gv$sort_usage u ,gv$instance i
WHERE s.saddr=u.session_addr and u.inst_id=i.inst_id order by MB DESC) a where rownum<10;
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 f.phyblkrd + f.phyblkwrt DESC;
select * from (SELECT u.tablespace, s.username, s.sid, s.serial#, s.logon_time, program, u.extents, ((u.blocks*8)/1024) as MB,
i.inst_id,i.host_name
FROM gv$session s, gv$sort_usage u ,gv$instance i
WHERE s.saddr=u.session_addr and u.inst_id=i.inst_id order by MB DESC) a where rownum<10;
Current SGA usage
Current SGA usage
select round(used.bytes /1024/1024 ,2) used_mb
, round(free.bytes /1024/1024 ,2) free_mb
, round(tot.bytes /1024/1024 ,2) 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
/
, round(free.bytes /1024/1024 ,2) free_mb
, round(tot.bytes /1024/1024 ,2) 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
/
Top running queries from ASH
Top running queries from ASH
--Query to get list of top running sqls in PAST between sysdate-1 to sysdate-23/34 . You can change accordingly
SELECT active_session_history.user_id,
dba_users.username,
sqlarea.sql_text,
SUM(active_session_history.wait_time +
active_session_history.time_waited)/1000000 ttl_wait_time_in_seconds
FROM v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE-23/24
AND active_session_history.sql_id = sqlarea.sql_id
AND active_session_history.user_id = dba_users.user_id
and dba_users.username not in ('SYS','DBSNMP')
GROUP BY active_session_history.user_id,sqlarea.sql_text, dba_users.username
ORDER BY 4 DESC
/
SELECT active_session_history.user_id,
dba_users.username,
sqlarea.sql_text,
SUM(active_session_history.wait_time +
active_session_history.time_waited)/1000000 ttl_wait_time_in_seconds
FROM v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE-23/24
AND active_session_history.sql_id = sqlarea.sql_id
AND active_session_history.user_id = dba_users.user_id
and dba_users.username not in ('SYS','DBSNMP')
GROUP BY active_session_history.user_id,sqlarea.sql_text, dba_users.username
ORDER BY 4 DESC
/
Find blocking sessions from ASH
Find blocking sessions from ASH
--- Query will list the blocking session details between SYSDATE - 1 AND SYSDATE-23/24 ( PAST)
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 blocker_ses,
a.blocking_session_serial# blocker_ser,
a.user_id,
s.sql_text,
a.module,a.sample_time
FROM GV$ACTIVE_SESSION_HISTORY a,
gv$sql s
where a.sql_id=s.sql_id
and blocking_session is not null
and a.user_id <> 0 -- exclude SYS user
and a.sample_time BETWEEN SYSDATE - 1 AND SYSDATE-23/24
/
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 blocker_ses,
a.blocking_session_serial# blocker_ser,
a.user_id,
s.sql_text,
a.module,a.sample_time
FROM GV$ACTIVE_SESSION_HISTORY a,
gv$sql s
where a.sql_id=s.sql_id
and blocking_session is not null
and a.user_id <> 0 -- exclude SYS user
and a.sample_time BETWEEN SYSDATE - 1 AND SYSDATE-23/24
/
Top cpu consuming sessions
Top cpu consuming sessions
col program form a30 heading "Program"
col CPUMins form 99990 heading "CPU in Mins"
select rownum as rank, a.*
from (
SELECT v.sid, program, v.value / (100 * 60) CPUMins
FROM v$statname s , v$sesstat v, v$session sess
WHERE s.name = 'CPU used by this session'
and sess.sid = v.sid
and v.statistic#=s.statistic#
and v.value>0
ORDER BY v.value DESC) a
where rownum < 11;
col CPUMins form 99990 heading "CPU in Mins"
select rownum as rank, a.*
from (
SELECT v.sid, program, v.value / (100 * 60) CPUMins
FROM v$statname s , v$sesstat v, v$session sess
WHERE s.name = 'CPU used by this session'
and sess.sid = v.sid
and v.statistic#=s.statistic#
and v.value>0
ORDER BY v.value DESC) a
where rownum < 11;
Sessions holding library cache lock
Sessions holding library cache lock
-- For standalone db:
select sid Waiter, p1raw,
substr(rawtohex(p1),1,30) Handle,
substr(rawtohex(p2),1,30) Pin_addr
from v$session_wait where wait_time=0 and event like '%library cache%';
-- For RAC DB:
select a.sid Waiter,b.SERIAL#,a.event,a.p1raw,
substr(rawtohex(a.p1),1,30) Handle,
substr(rawtohex(a.p2),1,30) 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%';
or
set lines 152
col sid for a9999999999999
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 name like '%library cache%';
select sid Waiter, p1raw,
substr(rawtohex(p1),1,30) Handle,
substr(rawtohex(p2),1,30) Pin_addr
from v$session_wait where wait_time=0 and event like '%library cache%';
-- For RAC DB:
select a.sid Waiter,b.SERIAL#,a.event,a.p1raw,
substr(rawtohex(a.p1),1,30) Handle,
substr(rawtohex(a.p2),1,30) 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%';
or
set lines 152
col sid for a9999999999999
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 name like '%library cache%';
Objects locked by library cache
Objects locked by library cache
select to_char(SESSION_ID,'999') sid ,
substr(LOCK_TYPE,1,30) Type,
substr(lock_id1,1,23) Object_Name,
substr(mode_held,1,4) HELD, substr(mode_requested,1,4) REQ,
lock_id2 Lock_addr
from dba_lock_internal
where
mode_requested'None'
and mode_requestedmode_held
and session_id in ( select sid
from v$session_wait where wait_time=0
and event like '%library cache%') ;
substr(LOCK_TYPE,1,30) Type,
substr(lock_id1,1,23) Object_Name,
substr(mode_held,1,4) HELD, substr(mode_requested,1,4) REQ,
lock_id2 Lock_addr
from dba_lock_internal
where
mode_requested'None'
and mode_requestedmode_held
and session_id in ( select sid
from v$session_wait where wait_time=0
and event like '%library cache%') ;
RAC & ASM
Get asm disk info
set pagesize 2000
set lines 2000
set long 999
col path for a54
select name, path, header_status, total_mb free_mb, trunc(bytes_read/1024/1024) read_mb, trunc(bytes_written/1024/1024) write_mb from v$asm_disk;
set lines 2000
set long 999
col path for a54
select name, path, header_status, total_mb free_mb, trunc(bytes_read/1024/1024) read_mb, trunc(bytes_written/1024/1024) write_mb from v$asm_disk;
Get ASM diskgroup details
Get ASM diskgroup details
SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage
FROM v$asm_diskgroup;
FROM v$asm_diskgroup;
drop an asm disk
drop an asm disk
-----Dropping one disk:
alter diskgroup data drop disk DATA_ASM0001;
-----Dropping multiple disk:
alter diskgroup data drop disk DATA_ASM0001,DATA_ASM00002, DATA_ASM0003 rebalance power 100;
---- Monitoring the rebalance operation:
select * from v$asm_operation;
alter diskgroup data drop disk DATA_ASM0001;
-----Dropping multiple disk:
alter diskgroup data drop disk DATA_ASM0001,DATA_ASM00002, DATA_ASM0003 rebalance power 100;
---- Monitoring the rebalance operation:
select * from v$asm_operation;
Monitor ASM disk rebalance
Monitor ASM disk rebalance
set pagesize 299
set lines 2999
select GROUP_NUMBER,OPERATION,STATE,POWER,
ACTUAL,ACTUAL,EST_MINUTES from gv$asm_operation;
set lines 2999
select GROUP_NUMBER,OPERATION,STATE,POWER,
ACTUAL,ACTUAL,EST_MINUTES from gv$asm_operation;
execute runcluvfy.sh for RAC precheck
execute runcluvfy.sh for RAC precheck
-- Runcluvfy.sh script is available after unzipping the grid software.
syntax - ./runcluvfy.sh stage -pre crsinst -n host1,host2,host3 -verbose
./runcluvfy.sh stage -pre crsinst -n classpredb1,classpredb2 -verbose
syntax - ./runcluvfy.sh stage -pre crsinst -n host1,host2,host3 -verbose
./runcluvfy.sh stage -pre crsinst -n classpredb1,classpredb2 -verbose
copy asm file to remote asm instance
copy asm file to remote asm instance
--- ASM file can be copied to remote asm instance(diskgroup) using asmcmd command.
SYNTAX - asmcmd> cp - -port asm_port file_name remote_asm_user/remote_asm_pwd@remote_host:Instancce_name:TARGET_ASM_PATH
ASMCMD> cp --port 1521 s_srv_new21.dbf sys/[email protected].+ASM1:+ARCL/s_srv_new21.dbf
SYNTAX - asmcmd> cp - -port asm_port file_name remote_asm_user/remote_asm_pwd@remote_host:Instancce_name:TARGET_ASM_PATH
ASMCMD> cp --port 1521 s_srv_new21.dbf sys/[email protected].+ASM1:+ARCL/s_srv_new21.dbf
Mount/dismount ASM diskgroups
Mount/dismount ASM diskgroups
-- For mount a diskgroup,(This is instance specific, for mounting on all nodes, run the same on all nodes)
SQL>alter diskgroup DATA mount;
or
asmcmd>mount DATA
-- For umount a diskgroup,(This is instance specific, for unmounting on all nodes, run the same on all nodes)
SQL>alter diskgroup DATA dismount;
or
asmcmd>umount DATA
-- To mount/Dismount all the diskgroups
SQL>alter diskgroup ALL mount;
SQL>alter diskgroup ALL dismount;
SQL>alter diskgroup DATA mount;
or
asmcmd>mount DATA
-- For umount a diskgroup,(This is instance specific, for unmounting on all nodes, run the same on all nodes)
SQL>alter diskgroup DATA dismount;
or
asmcmd>umount DATA
-- To mount/Dismount all the diskgroups
SQL>alter diskgroup ALL mount;
SQL>alter diskgroup ALL dismount;
Drop ASM diskgroup
Drop ASM diskgroup
-- To drop a diskgroup, make sure the diskgroup has been dismounted from all the remote nodes, It should be mounted only on the local nodes, where we will run the drop command.
drop diskgroup NSMREDOA including contents;
drop diskgroup NSMREDOA including contents;
Clock Synchronization status in RAC
Clock Synchronization status in RAC
-- Clock Synchronization across the cluster nodes
cd $GRID_HOME/bin
cluvfy comp clocksync -n all
- Check whether ctss or ntp is running
crsctl check ctss
CRS-4700: The Cluster Time Synchronization Service is in Observer mode.
Observer means - Time sync between nodes are taken care by NTP
Active means - Time sync between nodes are taken care by CTSS
cd $GRID_HOME/bin
cluvfy comp clocksync -n all
- Check whether ctss or ntp is running
crsctl check ctss
CRS-4700: The Cluster Time Synchronization Service is in Observer mode.
Observer means - Time sync between nodes are taken care by NTP
Active means - Time sync between nodes are taken care by CTSS
Create ASM disk in Linux using oracleasm
Create ASM disk in Linux using oracleasm
-- Check the asm disk labelling
#/etc/init.d/oracleasm querydisk /dev/sdn1
Device "/dev/sdn" is not marked as an ASM disk
-- Create asm disk
# /etc/init.d/oracleasm createdisk ARCDATA /dev/sdn1
Marking disk "ARCDATA" as an ASM disk: [ OK ]
-- Check the asm disk labelling
# /etc/init.d/oracleasm querydisk /dev/sdn1
Device "/dev/sdn1" is marked an ASM disk with the label "ARCDATA"
-- List the asm disks present
# /etc/init.d/oracleasm listdisks
ARCDATA
#/etc/init.d/oracleasm querydisk /dev/sdn1
Device "/dev/sdn" is not marked as an ASM disk
-- Create asm disk
# /etc/init.d/oracleasm createdisk ARCDATA /dev/sdn1
Marking disk "ARCDATA" as an ASM disk: [ OK ]
-- Check the asm disk labelling
# /etc/init.d/oracleasm querydisk /dev/sdn1
Device "/dev/sdn1" is marked an ASM disk with the label "ARCDATA"
-- List the asm disks present
# /etc/init.d/oracleasm listdisks
ARCDATA
REPORTING SCRIPTS
Get redo log member info
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 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
/
Get DDL of all tablespaces
Get DDL of all tablespaces
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 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
Get DDL of all privileges granted to user
Get DDL of privileges granted to user
set feedback off pages 0 long 900000 lines 20000 pagesize 20000 serveroutput on
accept USERNAME prompt "Enter username :"
--This line add 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;
accept USERNAME prompt "Enter username :"
--This line add 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;
Get size of the database
Get size of the database
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
/
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
/
View hidden parameter setting
View hidden parameter setting
Set lines 2000
col NAME for a45
col DESCRIPTION for a100
SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\'
/
col NAME for a45
col DESCRIPTION for a100
SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\'
/
Get ACL details in database
Get ACL details in database
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;
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;
Archive generation per hour
Archive generation per hour
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
/
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
/
Find active transactions in db
Find active transactions in db
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
/
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
/
Find who locked your account
Find who locked your account
-- 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);
-- 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);
Find duplicate rows in table
Find duplicate rows in table
--- Reference metalink id - 332494.1
-- Save as duplicate.sql and run as @duplicate.sql
REM This is an example SQL*Plus Script to detect duplicate rows from
REM a table.
REM
set echo off
set verify off heading off
undefine t
undefine c
prompt
prompt
prompt Enter name of table with duplicate rows
prompt
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.
prompt
accept c prompt 'Column(s): '
prompt
select &&c from &&t
where rowid not in (select min(rowid) from &&t group by &&c)
/
-- Save as duplicate.sql and run as @duplicate.sql
REM This is an example SQL*Plus Script to detect duplicate rows from
REM a table.
REM
set echo off
set verify off heading off
undefine t
undefine c
prompt
prompt
prompt Enter name of table with duplicate rows
prompt
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.
prompt
accept c prompt 'Column(s): '
prompt
select &&c from &&t
where rowid not in (select min(rowid) from &&t group by &&c)
/
Database growth per month
Database growth per month
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');
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');
generate resize datafile script without ORA-03297 error
generate resize datafile script
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 /*+ 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);
Get database uptime
Get database uptime
select to_char(startup_time, 'DD-MM-YYYY HH24:MI:SS'),floor(sysdate-startup_time) DAYS from v$Instance;
Scn to timestamp and viceversa
Scn to timestamp and viceversa
-- 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;
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;
Disable/enable all triggers of schema
Disable/enable all triggers of schema
----- 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;
/
BEGIN
FOR i IN (SELECT trigger_name
FROM user_triggers) LOOP
EXECUTE IMMEDIATE 'ALTER TRIGGER ' || i.trigger_name || ' DISABLE';
END LOOP;
END;
/
Ger row_count of all the tables of a schema
Ger row_count of all the tables of a schema
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';
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';
Spool sql query output to HTML
Spool sql query output to HTML
-- 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
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
Monitor index usage
Monitor index usage
---Index monitoring is required, to find whether indexes are really in use or not. Unused can be dropped to avoid overhead.
-- 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;
-- 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;
Get installed sqlpatches in db
Get installed sqlpatches in db
--- 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;
set lines 2000
select patch_id,status,description from dba_registry_sqlpatch;
--- For 11g and below:
set lines 2000
select * from dba_registry_history;
SRVCTL COMMANDS
Stop and start db using srvctl
Stop and start db using srvctl
-- SYNTAX FOR STOP DB
--- srvctl stop database -d db_name [-o stop_options] where stop_options is normal/immediate(default)/transactional/abort
e.g
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
-- SYNTAX FOR START DB
-- srvctl start database -d db_name [-o start_options] where start_option is nomount/mount/open(default)
e.g
srvctl start database -d PRODB -o nomount
srvctl start database -d PRODB -o mount
srvctl start database -d PRODB -o open
--- srvctl stop database -d db_name [-o stop_options] where stop_options is normal/immediate(default)/transactional/abort
e.g
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
-- SYNTAX FOR START DB
-- srvctl start database -d db_name [-o start_options] where start_option is nomount/mount/open(default)
e.g
srvctl start database -d PRODB -o nomount
srvctl start database -d PRODB -o mount
srvctl start database -d PRODB -o open
add/remove db using srvctl
add/remove db using srvctl
--- SYNTAX FOR REMOVING DB SERVICE:
---srvctl remove database -d db_unique_name [-f] [-y] [-v]
e.g:
srvctl remove database -d PRODB -f -y
--- SYNTAX FOR ADDING DB SERVICE :
-- srvctl add database -d db_unique_name -o ORACLE_HOME [-p spfile]
e.g:
srvctl add database -d PRODB -o /u01/app/oracle/product/12.1.0.2/dbhome_1 -p +DATA/PRODDB/parameterfile/spfilePRODB.ora
---srvctl remove database -d db_unique_name [-f] [-y] [-v]
e.g:
srvctl remove database -d PRODB -f -y
--- SYNTAX FOR ADDING DB SERVICE :
-- srvctl add database -d db_unique_name -o ORACLE_HOME [-p spfile]
e.g:
srvctl add database -d PRODB -o /u01/app/oracle/product/12.1.0.2/dbhome_1 -p +DATA/PRODDB/parameterfile/spfilePRODB.ora
Add/remove instance using srvctl
Add/remove instance using srvctl
-- SYNTAX FOR REMOVING INSTANCE
---srvctl remove instance -d DB_UNIQUE_NAME -i INSTANCE_NAME
e.g
srvctl remove instance -d PRODB - I PRODB1
-- SYNTAX FOR ADDING INSTANCE
--- srvctl add instance –d db_unique_name –i inst_name -n node_name
e.g
srvctl add instance -d PRODB - i PRODB1 -n rachost1
---srvctl remove instance -d DB_UNIQUE_NAME -i INSTANCE_NAME
e.g
srvctl remove instance -d PRODB - I PRODB1
-- SYNTAX FOR ADDING INSTANCE
--- srvctl add instance –d db_unique_name –i inst_name -n node_name
e.g
srvctl add instance -d PRODB - i PRODB1 -n rachost1
Stop and start instance using srvctl
Stop and start instance using srvctl
--SYNTAX FOR STOPPING INSTANCE
-- srvctl stop instance -d db_unique_name [-i "instance_name_list"]} [-o stop_options] [-f]
e.g
srvctl stop instance -d PRODB -i PRODB1
--SYNTAX FOR STARTING INSTANCE
-- srvctl start instance -d db_unique_name [-i "instance_name_list"} [-o start_options]
e.g
srvctl start instance -d PRODB -i PRODB1
-- srvctl stop instance -d db_unique_name [-i "instance_name_list"]} [-o stop_options] [-f]
e.g
srvctl stop instance -d PRODB -i PRODB1
--SYNTAX FOR STARTING INSTANCE
-- srvctl start instance -d db_unique_name [-i "instance_name_list"} [-o start_options]
e.g
srvctl start instance -d PRODB -i PRODB1
Enable/disable db/instance using srvctl
Enable/disable db/instance using srvctl
-- ENABLE - Reenables management by Oracle Restart for a component.
-- DISABLE - Disables management by Oracle Restart for a component.
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
-- DISABLE - Disables management by Oracle Restart for a component.
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
CRSCTL COMMANDS
Enable/Disable autorestart of crs
Enable/Disable autorestart of crs
-- Run as root user(
$GRID_HOME/bin/crsctl enable crs
CRS-4622: Oracle High Availability Services autostart is enabled.
$GRID_HOME/bin/crsctl disable crs
CRS-4621: Oracle High Availability Services autostart is disabled.
$GRID_HOME/bin/crsctl enable crs
CRS-4622: Oracle High Availability Services autostart is enabled.
$GRID_HOME/bin/crsctl disable crs
CRS-4621: Oracle High Availability Services autostart is disabled.
Find the cluster name in RAC
Find the cluster name in RAC
$GRID_HOME/bin/cemutlo -n
or
$GRID_HOME/bin/olsnodes -c
or
$GRID_HOME/bin/olsnodes -c
Stop and start CRS
Stop and start CRS
-- stop crs ( run from root)
$GRID_HOME/bin/crsctl stop crs
-- start crs( run from root)
$GRID_HOME/bin/crsctl start crs
$GRID_HOME/bin/crsctl stop crs
-- start crs( run from root)
$GRID_HOME/bin/crsctl start crs
Find OCR and VD location
Find OCR and VD location
-- Find voting disk location
$GRID_HOME/bin/crsctl query css votedisk
-- Find OCR location.
$GRID_HOME/bin/ocrcheck
$GRID_HOME/bin/crsctl query css votedisk
-- Find OCR location.
$GRID_HOME/bin/ocrcheck
Find the grid version
Find the grid version
SYNTAX - $GRID_HOME/bin/crsctl query crs softwareversion
$GRID_HOME/bin/crsctl query crs softwareversion host-dbaclass1
$GRID_HOME/bin/crsctl query crs softwareversion host-dbaclass1
check cluster component status
check cluster component status
$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/crsctl check crs
$GRID_HOME/bin/crsctl check cssd
$GRID_HOME/bin/crsctl check crsd
$GRID_HOME/bin/crsctl check evmd
Get cluster_interconnect details
Get cluster_interconnect details
$GRID_HOME/bin/oifcfg getif
app-ipmp0 172.21.39.128 global public
loypredbib0 172.16.3.192 global cluster_interconnect
loypredbib1 172.16.4.0 global cluster_interconnect
select NAME,IP_ADDRESS from v$cluster_interconnects;
NAME IP_ADDRESS
--------------- ----------------
loypredbib0 172.16.3.193
loypredbib1 172.16.4.1
app-ipmp0 172.21.39.128 global public
loypredbib0 172.16.3.192 global cluster_interconnect
loypredbib1 172.16.4.0 global cluster_interconnect
select NAME,IP_ADDRESS from v$cluster_interconnects;
NAME IP_ADDRESS
--------------- ----------------
loypredbib0 172.16.3.193
loypredbib1 172.16.4.1
Manual backup of ocr and list backups
Manual backup of ocr and list backups
-- List down the backups of OCR
$GRID_HOME/bin/ocrconfig -showbackup
-- Take manual OCR backup
$GRID_HOME/bin/ocrconfig -manualbackup
$GRID_HOME/bin/ocrconfig -showbackup
-- Take manual OCR backup
$GRID_HOME/bin/ocrconfig -manualbackup
Move voting disk to new diskgroup
Move voting disk to new diskgroup
$GRID_HOME/bin/crsctl replace votedisk +NEW_DG
Check the status using below command.
$GRID_HOME/bin/crsctl query css votedisk
SQL PROF/BASELINE
create baselines for all sqls of a schema
create baselines for all sqls of a schema
DECLARE
nRet NUMBER;
BEGIN
nRet := dbms_spm.load_plans_from_cursor_cache(
attribute_name => 'PARSING_SCHEMA_NAME',
attribute_value => '&schema_name'
);
END;
nRet NUMBER;
BEGIN
nRet := dbms_spm.load_plans_from_cursor_cache(
attribute_name => 'PARSING_SCHEMA_NAME',
attribute_value => '&schema_name'
);
END;
Create sql baseline from cursor cache
Create sql baseline from cursor cache
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => '&sql_id');
END;
/
-- Create baseline with a particular hash value
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => '&sql_id', plan_hash_value => '&plan_hash_value');
END;
/
drop a sql baseline
drop a sql baseline
declare
drop_result pls_integer;
begin
drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
plan_name => '&sql_plan_baseline_name');
dbms_output.put_line(drop_result);
end;
/
You can get the sql baseline from a sql_id from below command:
SELECT sql_handle, plan_name FROM dba_sql_plan_baselines WHERE signature IN
( SELECT exact_matching_signature FROM gv$sql WHERE sql_id='&SQL_ID');
drop_result pls_integer;
begin
drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
plan_name => '&sql_plan_baseline_name');
dbms_output.put_line(drop_result);
end;
/
You can get the sql baseline from a sql_id from below command:
SELECT sql_handle, plan_name FROM dba_sql_plan_baselines WHERE signature IN
( SELECT exact_matching_signature FROM gv$sql WHERE sql_id='&SQL_ID');
drop a sql profile
drop a sql profile
BEGIN
DBMS_SQLTUNE.drop_sql_profile (
name => '&sql_profile',
ignore => TRUE);
END;
/
You can get the respective sql_profile of a sql_id from below:
select distinct
p.name sql_profile_name,
s.sql_id
from
dba_sql_profiles p,
DBA_HIST_SQLSTAT s
where
p.name=s.sql_profile and s.sql_id='&sql_id';
DBMS_SQLTUNE.drop_sql_profile (
name => '&sql_profile',
ignore => TRUE);
END;
/
You can get the respective sql_profile of a sql_id from below:
select distinct
p.name sql_profile_name,
s.sql_id
from
dba_sql_profiles p,
DBA_HIST_SQLSTAT s
where
p.name=s.sql_profile and s.sql_id='&sql_id';
Get sql_profile of a sql_id
Get sql_profile of a sql_id
-- Script for getting sql_profile created for a sql_id
select distinct
p.name sql_profile_name,
s.sql_id
from
dba_sql_profiles p,
DBA_HIST_SQLSTAT s
where
p.name=s.sql_profile and s.sql_id='&sql_id';
select distinct
p.name sql_profile_name,
s.sql_id
from
dba_sql_profiles p,
DBA_HIST_SQLSTAT s
where
p.name=s.sql_profile and s.sql_id='&sql_id';
Disable/enable sql profile
Disable/enable sql profile
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('&sql_profile_name','STATUS','DISABLED');
PARTITIONING
Adding partitions 11g/12c
-- SYNTAX : ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> ADD PARTITION < PARTITION_NAME> VALUES LESS THAN < HIGH_VALUE> TABLESPACE <TABLESPACE_NAME > < UPDATE GLOBAL INDEXES(optional)>;
-- NOTE: UPDATE GLOBAL INDEXES is required if GLOBAL INDEX is present
ALTER TABLE CMADMIN.DBACLASS ADD PARTITION DBACLASS_JAN VALUES
LESS THAN (TO_DATE('01-FEB-2016','DD-MON-YYYY')) TABLESPACE USERS UPDATE GLOBAL INDEXES;
-- In oracle 12c(new feature), we can add multiple partition 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;
-- NOTE: UPDATE GLOBAL INDEXES is required if GLOBAL INDEX is present
ALTER TABLE CMADMIN.DBACLASS ADD PARTITION DBACLASS_JAN VALUES
LESS THAN (TO_DATE('01-FEB-2016','DD-MON-YYYY')) TABLESPACE USERS UPDATE GLOBAL INDEXES;
-- In oracle 12c(new feature), we can add multiple partition 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;
Dropping partition 11g/12c
Dropping partition 11g/12c
-- SYNTAX : ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> DROP PARTITION < PARTITION_NAME> < UPDATE GLOBAL INDEXES(optional)>;
--- NOTE: UPDATE GLOBAL INDEXES is required if GLOBAL INDEX is present
ALTER TABLE CMADMIN.DBACLASS DROP PARTITION DBACLASS_JAN UPDATE GLOBAL INDEXES;
--- In oracle 12c, we can drop multiple partitions in one command
ALTER TABLE CMADMIN.DBACLASS DROP PARTITIONS DBACLASS_JAN, DBACLASS_FEB, DBACLASS_MAR UPDATE GLOBAL INDEXES;
--- NOTE: UPDATE GLOBAL INDEXES is required if GLOBAL INDEX is present
ALTER TABLE CMADMIN.DBACLASS DROP PARTITION DBACLASS_JAN UPDATE GLOBAL INDEXES;
--- In oracle 12c, we can drop multiple partitions in one command
ALTER TABLE CMADMIN.DBACLASS DROP PARTITIONS DBACLASS_JAN, DBACLASS_FEB, DBACLASS_MAR UPDATE GLOBAL INDEXES;
Truncate partitions
Truncate partitions
- SYNTAX : ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> TRUNCATE PARTITION < PARTITION_NAME> < UPDATE GLOBAL INDEXES(optional)>;
--- NOTE: UPDATE GLOBAL INDEXES is required if GLOBAL INDEX is present
ALTER TABLE CMADMIN.DBACLASS TRUNCATE PARTITION DBACLASS_JAN UPDATE GLOBAL INDEXES;
--- In oracle 12c, we can truncate multiple partitions in one command
ALTER TABLE CMADMIN.DBACLASS TRUNCATE PARTITIONS DBACLASS_JAN, DBACLASS_FEB, DBACLASS_MAR UPDATE GLOBAL INDEXES;
--- NOTE: UPDATE GLOBAL INDEXES is required if GLOBAL INDEX is present
ALTER TABLE CMADMIN.DBACLASS TRUNCATE PARTITION DBACLASS_JAN UPDATE GLOBAL INDEXES;
--- In oracle 12c, we can truncate multiple partitions in one command
ALTER TABLE CMADMIN.DBACLASS TRUNCATE PARTITIONS DBACLASS_JAN, DBACLASS_FEB, DBACLASS_MAR UPDATE GLOBAL INDEXES;
merge partition
merge partition
-- MERGE PARTITION - FOR COMBINING MULTIPLE PARTITIONS TO A NEW ONE ( 12C ONWARS)
-- SYNTAX : ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> MERGE PARTITIONS < PARTITION1,PARTITION2,...> < UPDATE GLOBAL INDEXES(optional)>;
--- NOTE: UPDATE GLOBAL INDEXES is required if GLOBAL INDEX is present
ALTER TABLE CMADMIN.DBACLASS MERGE PARTITIONS DBACLASS_JAN, DBACLASS_FEB, DBACLASS_MAR INTO partition DBACLASS_Q1;
-- SYNTAX : ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> MERGE PARTITIONS < PARTITION1,PARTITION2,...> < UPDATE GLOBAL INDEXES(optional)>;
--- NOTE: UPDATE GLOBAL INDEXES is required if GLOBAL INDEX is present
ALTER TABLE CMADMIN.DBACLASS MERGE PARTITIONS DBACLASS_JAN, DBACLASS_FEB, DBACLASS_MAR INTO partition DBACLASS_Q1;
Make a partition ready only(12CR2)
Make a partition ready only(12CR2)
-- From oracle 12.2.0.1 Relase, we can make few partitions of a table read only.
SQL> alter table dbatest.ORDER_TAB modify partition CREATED_2105_P10 read only;
Table altered.
SQL> select partition_name,read_only from dba_tab_partitions where table_name='ORDER_TAB';
PARTITION_NAME READ
-------------------------------- ----
CREATED_2105_P10 YES
CREATED_2105_P11 NO
CREATED_2105_P12 NO
CREATED_2105_P8 NO
CREATED_2105_P9 NO
CREATED_MX NO
6 rows selected.
SQL> alter table dbatest.ORDER_TAB modify partition CREATED_2105_P10 read only;
Table altered.
SQL> select partition_name,read_only from dba_tab_partitions where table_name='ORDER_TAB';
PARTITION_NAME READ
-------------------------------- ----
CREATED_2105_P10 YES
CREATED_2105_P11 NO
CREATED_2105_P12 NO
CREATED_2105_P8 NO
CREATED_2105_P9 NO
CREATED_MX NO
6 rows selected.
Split partition online(12cR2 only)
Split partition online(12cR2 only)
SQL> 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;
Table altered.
SQL> select partition_name,read_only,high_value from dba_tab_partitions where table_name='ORDER_TAB';
(partition CREATED_2106_P2 VALUES LESS THAN (TO_DATE('01/03/2016', 'DD/MM/YYYY')),PARTITION CREATED_MX) ONLINE;
Table altered.
SQL> select partition_name,read_only,high_value from dba_tab_partitions where table_name='ORDER_TAB';
Non-partitioned to partitioned online(12CR2 only)
Non-partitioned to partitioned online(12CR2 only)
-- In Oracle 12cR2, we can convert non partitioned table to partitioned online using alter table command.
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 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;
Rename a partition
Rename a partition
ALTER TABLE employee RENAME PARTITION TAB3 TO TAB4;
Get row_count of partitions of a table
Get row_count of partitions of a table
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');
dbms_output.put_line('Log file to numrows_part_&&TABLE_OWNER.lst ....');
dbms_output.put_line('....');
for get_tab_rec in get_tab loop
BEGIN
sql_stmt := 'select count(*) from &&TABLE_OWNER..'||get_tab_rec.table_name
||' partition ( '||get_tab_rec.partition_name||' )';
EXECUTE IMMEDIATE sql_stmt INTO row_count;
dbms_output.put_line('Table '||rpad(get_tab_rec.table_name
||'('||get_tab_rec.partition_name||')',50)
||' '||TO_CHAR(row_count)||' rows.');
exception when others then
dbms_output.put_line
('Error counting rows for table '||get_tab_rec.table_name);
END;
end loop;
end;
/
set verify on
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');
dbms_output.put_line('Log file to numrows_part_&&TABLE_OWNER.lst ....');
dbms_output.put_line('....');
for get_tab_rec in get_tab loop
BEGIN
sql_stmt := 'select count(*) from &&TABLE_OWNER..'||get_tab_rec.table_name
||' partition ( '||get_tab_rec.partition_name||' )';
EXECUTE IMMEDIATE sql_stmt INTO row_count;
dbms_output.put_line('Table '||rpad(get_tab_rec.table_name
||'('||get_tab_rec.partition_name||')',50)
||' '||TO_CHAR(row_count)||' rows.');
exception when others then
dbms_output.put_line
('Error counting rows for table '||get_tab_rec.table_name);
END;
end loop;
end;
/
set verify on
STATISTICS
Gather stats for schema
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;
/
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;
/
Gather stats for a table
Gather stats for a table
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SCOTT',
tabname => 'TEST',
cascade => true, ---- For collecting stats for respective indexes
method_opt=>'for all indexed columns size 1',
granularity => 'ALL',
estimate_percent =>dbms_stats.auto_sample_size,
degree => 8);
END;
/
-- For a single table partition
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SCOTT',
tabname => 'TEST', --- TABLE NAME
partname => 'TEST_JAN2016' --- PARTITOIN NAME
method_opt=>'for all indexed columns size 1',
GRANULARITY => 'APPROX_GLOBAL AND PARTITION',
degree => 8);
END;
/
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SCOTT',
tabname => 'TEST',
cascade => true, ---- For collecting stats for respective indexes
method_opt=>'for all indexed columns size 1',
granularity => 'ALL',
estimate_percent =>dbms_stats.auto_sample_size,
degree => 8);
END;
/
-- For a single table partition
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SCOTT',
tabname => 'TEST', --- TABLE NAME
partname => 'TEST_JAN2016' --- PARTITOIN NAME
method_opt=>'for all indexed columns size 1',
GRANULARITY => 'APPROX_GLOBAL AND PARTITION',
degree => 8);
END;
/
Lock/unlock statistics
Lock/unlock statistics
--- Lock statistics
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 statistics
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 stats status:
SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';
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 statistics
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 stats status:
SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';
Export import statistics
Export import statistics
--- Create staging table to store the statistics data
exec dbms_stats.create_stat_table(ownname => 'SCOTT', stattab => 'STAT_BACKUP',tblspace=>'USERS');
-- Export stats
exec dbms_stats.export_table_stats(ownname=>'SCOTT', tabname=>'EMP', stattab=>'STAT_BACKUP', cascade=>true);
-- Import stats
exec dbms_stats.import_table_stats(ownname=>'SCOTT', tabname=>'EMP', stattab=>'STAT_BACKUP', cascade=>true);
exec dbms_stats.create_stat_table(ownname => 'SCOTT', stattab => 'STAT_BACKUP',tblspace=>'USERS');
-- Export stats
exec dbms_stats.export_table_stats(ownname=>'SCOTT', tabname=>'EMP', stattab=>'STAT_BACKUP', cascade=>true);
-- Import stats
exec dbms_stats.import_table_stats(ownname=>'SCOTT', tabname=>'EMP', stattab=>'STAT_BACKUP', cascade=>true);
Check stale stats
Check stale stats
--- STALE STATS FOR TABLE
select owner,table_name,STALE_STATS from dba_tab_statistics where owner='&SCHEMA_NAME' and table_name='&TABLE_NAME';
-- FOR INDEX
select owner,INDEX_NAME,TABLE_NAME from DBA_IND_STATISTICS where owner='&SCHEMA_NAME' and index_name='&INDEX_NAME';
select owner,table_name,STALE_STATS from dba_tab_statistics where owner='&SCHEMA_NAME' and table_name='&TABLE_NAME';
-- FOR INDEX
select owner,INDEX_NAME,TABLE_NAME from DBA_IND_STATISTICS where owner='&SCHEMA_NAME' and index_name='&INDEX_NAME';
Table statistics history
Table statistics history
-- For getting history of TABLE statistics
setlines 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';
setlines 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';
Publish Pending stats
Publish Pending stats
-- Publish Pending stats for table
EXEC DBMS_STATS.PUBLISH_PENDING_STATS ('SCHEMA_NAME,'TABLE_NAME');
-- Publish pending stats for a schema
exec dbms_stats.publish_pending_stats('SCHEMA_NAME',null);
EXEC DBMS_STATS.PUBLISH_PENDING_STATS ('SCHEMA_NAME,'TABLE_NAME');
-- Publish pending stats for a schema
exec dbms_stats.publish_pending_stats('SCHEMA_NAME',null);
Get statistics preference setting
Get statistics preference setting
-- Setting Publish preference
exec dbms_stats.set_table_prefs('SCOTT','EMP','PUBLISH','FALSE');
--- Check the publish preference status
select dbms_stats.get_prefs('PUBLISH', 'SCOTT','EMP') FROM DUAL;
Similarly for schema also use as below:
select dbms_stats.get_prefs('PUBLISH', 'SCOTT') from dual
exec dbms_stats.SET_SCHEMA_PREFS('DBATEST','PUBLISH','FALSE');
--- FOR INDEX
SET_INDEX_STATS
GET_INDEX_STATS
-- FOR DATABASE
SET_DATABASE_PREFS
exec dbms_stats.set_table_prefs('SCOTT','EMP','PUBLISH','FALSE');
--- Check the publish preference status
select dbms_stats.get_prefs('PUBLISH', 'SCOTT','EMP') FROM DUAL;
Similarly for schema also use as below:
select dbms_stats.get_prefs('PUBLISH', 'SCOTT') from dual
exec dbms_stats.SET_SCHEMA_PREFS('DBATEST','PUBLISH','FALSE');
--- FOR INDEX
SET_INDEX_STATS
GET_INDEX_STATS
-- FOR DATABASE
SET_DATABASE_PREFS
FLASHBACK TECH
Flashback a table to point in time
Flashback a table to point in time
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 TIMESTAMP
TO_TIMESTAMP('2017-01-10 09:00:00', `YYYY-MM-DD HH24:MI:SS');
Recover a dropped table
Recover a dropped table
Flashback table DBACLASS.EMP to before drop;
-- Restore the dropped table with a new name
Flashback table DBACLASS.EMP to before drop rename to EMP_BACKUP;
Note - To recover the table, table should be present in recyclebin:
select * from dba_recyclebin;
-- Restore the dropped table with a new name
Flashback table DBACLASS.EMP to before drop rename to EMP_BACKUP;
Note - To recover the table, table should be present in recyclebin:
select * from dba_recyclebin;
Flashback query as of timestamp
Flashback query as of timestamp
SELECT * FROM DBACLASS.EMP AS OF TIMESTAMP
TO_TIMESTAMP('2017-01-07 10:00:00', 'YYYY-MM-DD HH:MI:SS');
SELECT * FROM DBACLASS.EMP AS OF TIMESTAMP SYSDATE -1/24;
TO_TIMESTAMP('2017-01-07 10:00:00', 'YYYY-MM-DD HH:MI:SS');
SELECT * FROM DBACLASS.EMP AS OF TIMESTAMP SYSDATE -1/24;
Enable flashback for database
Enable flashback for database
-- Make sure database is in 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;
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;
Create/drop flashback restore point
Create/drop flashback restore point
-- To create a guarantee flashback restore point;
create restore point BEFORE_UPG guarantee flashback database;
-- Check the restore_points present in database
select * from v$restore_point;
-- Drop restore point;
drop restore point BEFORE_UPG;
create restore point BEFORE_UPG guarantee flashback database;
-- Check the restore_points present in database
select * from v$restore_point;
-- Drop restore point;
drop restore point BEFORE_UPG;
Flashback db using restore point
Flashback db using restore point
--- Below are the steps for flashback database to a guaranteed restore point;
1. Get the restore point name:
SQL> select NAME,time from v$restore_point;
NAME TIME
-------------------------------- -----------------------------------------------
GRP_1490100093811 21-MAR-17 03.41.33.000000000 PM
2. Shutdown database and start db in Mount stage:
shutdown immediate;
startup mount;
3. flashback db to restore point:
flashback database to restore point GRP_1490100093811;
4. Open with resetlog:
alter database open resetlogs:
1. Get the restore point name:
SQL> select NAME,time from v$restore_point;
NAME TIME
-------------------------------- -----------------------------------------------
GRP_1490100093811 21-MAR-17 03.41.33.000000000 PM
2. Shutdown database and start db in Mount stage:
shutdown immediate;
startup mount;
3. flashback db to restore point:
flashback database to restore point GRP_1490100093811;
4. Open with resetlog:
alter database open resetlogs:
RMAN SCRIPTS
rman full db backup run block script
rman full database backup script
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 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 ;
}
RMAN INCR db backup run block
RMAN incremental db backup run block script
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;
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 ;
}
rman tablespace backup run block
rman tablespace backup run block
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 tablespace USERS,TOOLS;
release channel c1 ;
release channel c2 ;
}
RMAN datafile(s) backup run block
RMAN datafile(s) backup run block
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 datafile 3,4;
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 archive older than 1 day
delete archive older than 1 day
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'sysdate-1';
CROSSCHECK ARCHIVELOG ALL;
DELETE EXPIRED ARCHIVELOG ALL;
CROSSCHECK ARCHIVELOG ALL;
DELETE EXPIRED ARCHIVELOG ALL;
backup archivelogs using RMAN
backup archivelogs using RMAN
--- Backup all archivelogs known to controlfile
backup archivelog all;
-- Backup all archivelogs known to controlfile and delete them once backed up
backup archivelog all delete input ;
-- Backup archivlogs known to controlfile and the logs which haven't backed up once also
backup archivelog all not backed up 1 times;
backup archivelog all;
-- Backup all archivelogs known to controlfile and delete them once backed up
backup archivelog all delete input ;
-- Backup archivlogs known to controlfile and the logs which haven't backed up once also
backup archivelog all not backed up 1 times;
Copy archive from ASM to File system
Copy archive from ASM to Mount point
--- Copy archive log from ASM to regular mount point using RMAN:
--- Connect to RMAN in RAC db
RMAN> copy archivelog '+B2BSTARC/thread_2_seq_34.933' to '/data/thread_2_seq_34.933';
--- Connect to RMAN in RAC db
RMAN> copy archivelog '+B2BSTARC/thread_2_seq_34.933' to '/data/thread_2_seq_34.933';
backup archive b/w 2 sequence
backup archive between 2 sequence number
--- For taking backup of archivelog between seq number 1000 to 1050
RMAN> backup format '/archive/%d_%s_%p_%c_%t.arc.bkp'
archivelog from sequence 1000 until sequence 1050;
-- For RAC ,need to mention the thread number also
RMAN> backup format '/archive/%d_%s_%p_%c_%t.arc.bkp'
archivelog from sequence 1000 until sequence 1050 thread 2;
RMAN> backup format '/archive/%d_%s_%p_%c_%t.arc.bkp'
archivelog from sequence 1000 until sequence 1050;
-- For RAC ,need to mention the thread number also
RMAN> backup format '/archive/%d_%s_%p_%c_%t.arc.bkp'
archivelog from sequence 1000 until sequence 1050 thread 2;
Enable trace for RMAN
Enable trace for RMAN
-- To diagnose rman script, use trace as below.
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;
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 dropped table with RMAN 12c
Recover dropped table with RMAN 12c
RMAN>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 – Location where all the related files for auxiliary instance will be placed
datapump destination – Location where the export dump of the table will be placed
NOTE - This feature is available only in oracle 12c and later.
auxiliary destination ‘/u03/arch/TEST/BACKUP’
datapump destination ‘/u03/arch/TEST/BACKUP';
auxiliary destination – Location where all the related files for auxiliary instance will be placed
datapump destination – Location where the export dump of the table will be placed
NOTE - This feature is available only in oracle 12c and later.
Monitor rman backup progress
Monitor rman backup progress
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK;
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK;
Restore archivelog from rman tape
Restore archivelog from rman tape
-----Below script will restore the archive sequences from 7630 to 7640 to /dumparea location
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)’connect sys/****@CRM_DB;
set archivelog destination to ‘/dumparea/';
restore archivelog from sequence 7630 until sequence 7640;
release channel t1;
}
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)’connect sys/****@CRM_DB;
set archivelog destination to ‘/dumparea/';
restore archivelog from sequence 7630 until sequence 7640;
release channel t1;
}
USER MANAGEMENT
Create user in oracle
SYNTAX :
create user <USER_NAME> identified by <PASSWORD>
default tablespace <TABLESPACE_NAME>
temporary tablespace <TEMP_TABLESPACE>;
Eg:
create user SCOTT identified by oracle#41234
default tablespace users
temporary tablespace TEMP;
-To create an user, which will prompt for new password upon login:
create user SCOTT identified by oracle#41234
default tablespace users
temporary tablespace TEMP
password expire;
create user <USER_NAME> identified by <PASSWORD>
default tablespace <TABLESPACE_NAME>
temporary tablespace <TEMP_TABLESPACE>;
Eg:
create user SCOTT identified by oracle#41234
default tablespace users
temporary tablespace TEMP;
-To create an user, which will prompt for new password upon login:
create user SCOTT identified by oracle#41234
default tablespace users
temporary tablespace TEMP
password expire;
Alter an user
Alter an user
-- Change password of an user
ALTER USER SCOTT identified by NEW_PWD;
-- Change user profile;
ALTER USER SCOTT PROFILE SIEBEL_PROFILE;
-- Unlock/lock a user
ALTER USER SCOTT account unlock;
ALTER USER SCOTT account lock;
-- Make sure account expiry, so upon login, it will ask for new one
ALTER USER SCOTT password expire;
ALTER USER SCOTT identified by NEW_PWD;
-- Change user profile;
ALTER USER SCOTT PROFILE SIEBEL_PROFILE;
-- Unlock/lock a user
ALTER USER SCOTT account unlock;
ALTER USER SCOTT account lock;
-- Make sure account expiry, so upon login, it will ask for new one
ALTER USER SCOTT password expire;
Change default tablespace of user
Change default tablespace of user
-- Get default tablespace of a user:
set lines 200
col username for a23
select username,DEFAULT_TABLESPACE from dba_users where username='SCOTT';
USERNAME DEFAULT_TABLESPACE
----------------------- ------------------------------
SCOTT USERS
-- Change default tablespace of a user:
ALTER USER SCOTT DEFAULT TABLESPACE DATATS;
select username,DEFAULT_TABLESPACE from dba_users where username='SCOTT';
USERNAME DEFAULT_TABLESPACE
----------------------- ------------------------------
SCOTT DATATS
set lines 200
col username for a23
select username,DEFAULT_TABLESPACE from dba_users where username='SCOTT';
USERNAME DEFAULT_TABLESPACE
----------------------- ------------------------------
SCOTT USERS
-- Change default tablespace of a user:
ALTER USER SCOTT DEFAULT TABLESPACE DATATS;
select username,DEFAULT_TABLESPACE from dba_users where username='SCOTT';
USERNAME DEFAULT_TABLESPACE
----------------------- ------------------------------
SCOTT DATATS
Tablespace quota for a user
Tablespace quota for a user
-- Get the current tablespace quota information of an user
set lines 299
select TABLESPACE_NAME,BYTES/1024/1024 "UTILIZIED_SPACE" ,MAX_BYTES/1024/1024 "QUOTA_ALLOCATED" from dba_ts_quotas where username='&USER_NAME';
TABLESPACE_NAME UTILIZIED_SPACE QUOTA_ALLOCATED
------------------------------ --------------------------- --------------------------
USERS .0625 1024
--- Change the tablespace quota for the user to 5G
ALTER USER SCOTT QUOTA 5G ON USERS;
--- Grant unlimited tablespace quota:
ALTER USER SCOTT QUOTA UNLIMITED ON USERS;
set lines 299
select TABLESPACE_NAME,BYTES/1024/1024 "UTILIZIED_SPACE" ,MAX_BYTES/1024/1024 "QUOTA_ALLOCATED" from dba_ts_quotas where username='&USER_NAME';
TABLESPACE_NAME UTILIZIED_SPACE QUOTA_ALLOCATED
------------------------------ --------------------------- --------------------------
USERS .0625 1024
--- Change the tablespace quota for the user to 5G
ALTER USER SCOTT QUOTA 5G ON USERS;
--- Grant unlimited tablespace quota:
ALTER USER SCOTT QUOTA UNLIMITED ON USERS;
View Privileges granted to an user
View Privileges granted to an user
-- System privileges granted to an user ( scott)
SELECT * FROM DBA_SYS_PRIVS where grantee='SCOTT';
-- Roles granted to an user ( scott)
SELECT * FROM DBA_ROLE_PRIVS where grantee='SCOTT';
-- Object privileges granted to an user ( SCOTT)
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE='SCOTT';
-- Column specific privileges granted
SELECT * FROM DBA_COL_PRIVS WHERE WHERE GRANTEE='SCOTT';
SELECT * FROM DBA_SYS_PRIVS where grantee='SCOTT';
-- Roles granted to an user ( scott)
SELECT * FROM DBA_ROLE_PRIVS where grantee='SCOTT';
-- Object privileges granted to an user ( SCOTT)
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE='SCOTT';
-- Column specific privileges granted
SELECT * FROM DBA_COL_PRIVS WHERE WHERE GRANTEE='SCOTT';
grant table/column privilege to user
grant table/column privilege to user
-- Table privileges
GRANT READ ANY TABLE TO SCOTT;
GRANT SELECT ANY TABLE TO SCOTT;
GRANT INSERT, UPDATE, DELETE ON TESTUSER1.EMPTABL on SCOTT;
GRANT ALL ON TESTUSER1.EMPTABL on SCOTT;
-- Grant privilege on few columns of a table
--Only INSERT,UPDATE can be granted at COLUMN level.
GRANT insert (emp_id) ON TESTUSER1.EMPTABL TO SCOTT;
GRANT UPDATE(emp_id) ON TESTUSER1.EMPTABL TO SCOTT;
GRANT READ ANY TABLE TO SCOTT;
GRANT SELECT ANY TABLE TO SCOTT;
GRANT INSERT, UPDATE, DELETE ON TESTUSER1.EMPTABL on SCOTT;
GRANT ALL ON TESTUSER1.EMPTABL on SCOTT;
-- Grant privilege on few columns of a table
--Only INSERT,UPDATE can be granted at COLUMN level.
GRANT insert (emp_id) ON TESTUSER1.EMPTABL TO SCOTT;
GRANT UPDATE(emp_id) ON TESTUSER1.EMPTABL TO SCOTT;
Connect to user without knowing password
Connect to user without knowing password
--- You can connect to another user without knowing the password, with grant connect through privilege
--- Suppose a user TEST1 wants to connect to TEST2 user and create a table and we don’t know the password of TEST2.
Conn / as sysdba
SQL >alter user TEST2 grant connect through TEST1;
User altered.
SQL >conn TEST1[TEST2]
Enter password:< Give password for TEST1>
SQL >show user
USER is "TEST2"
SQL >create table emp_test as select * from emp;
Table created.
SQL > conn / as sysdba
connected
SQL > select owner from dba_tables where table_name='EMP_TEST';
OWNER
------
TEST2
--- Suppose a user TEST1 wants to connect to TEST2 user and create a table and we don’t know the password of TEST2.
Conn / as sysdba
SQL >alter user TEST2 grant connect through TEST1;
User altered.
SQL >conn TEST1[TEST2]
Enter password:< Give password for TEST1>
SQL >show user
USER is "TEST2"
SQL >create table emp_test as select * from emp;
Table created.
SQL > conn / as sysdba
connected
SQL > select owner from dba_tables where table_name='EMP_TEST';
OWNER
------
TEST2
TABLESPACE MANAGEMENT
Create tablespace in oracle db
Create tablespace in oracle db
-- Create New tablespace
Create tablespace DATA datafile '/u01/dbaclass/oradata/data01.dbf' size 5G autoextend on next 500M;
-- Create tablespace on ASM diskgroup
Create tablespace DATA datafile '+DATAG' size 5G autoextend on next 500M;
-- Create big tablespace:
CREATE BIGFILE TABLESPACE BIGTS datafile '/u01/dbaclass/oradata/bigts01.dbf' size 100G autoextend on NEXT 1G;
Create tablespace DATA datafile '/u01/dbaclass/oradata/data01.dbf' size 5G autoextend on next 500M;
-- Create tablespace on ASM diskgroup
Create tablespace DATA datafile '+DATAG' size 5G autoextend on next 500M;
-- Create big tablespace:
CREATE BIGFILE TABLESPACE BIGTS datafile '/u01/dbaclass/oradata/bigts01.dbf' size 100G autoextend on NEXT 1G;
Rename tablespace in oracle db
Rename tablespace in oracle db
SQL❯ select file_id,file_name,tablespace_name from dba_data_files where file_id=37;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------------- ------------------------------
37 /home/oracle/app/oracle/oradata/cdb1/testin1.dbf TESTING
--- Rename the tablespace_name from TESTING to PRODUCING;
SQL❯ alter tablespace TESTING rename to PRODUCING;
Tablespace altered.
SQL❯ select file_id,file_name,tablespace_name from dba_data_files where file_id=37;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------------- ------------------------------
37 /home/oracle/app/oracle/oradata/cdb1/testin1.dbf PRODUCING
Drop tablespace in oracle db
Drop tablespace in oracle db
-- Drop a tablespace without removing the physical database files.
SQL❯ drop tablespace TESTING;
Tablespace dropped.
SQL❯ select file_name from dba_data_files where tablespace_name='TESTING';
no rows selected
-- Drop tablespace including the physical datafiles.
SQL❯ drop tablespace TESTING including contents and datafiles;
Tablespace dropped.
Add/alter datafile
Add/Drop/Alter datafile
-- Add a datafile to a tablespace
Alter tablespace USERS add datafile '/u01/data/users02.dbf' size 5G;
-- Enable autoextend on for a datafile;
Alter database datafile '/u01/data/users02.dbf' autoextend on;
-- Resize a datafile
alter database datafile '/u01/data/users02.dbf' resize 10G;
-- Make a datafile offline/online
Alter database datafile '/u01/data/users02.dbf' offline;
Alter database datafile '/u01/data/users02.dbf' online;
-- Drop a datafile:
Alter tablespace USERS drop datafile '/u01/data/users02.dbf';
Alter tablespace USERS add datafile '/u01/data/users02.dbf' size 5G;
-- Enable autoextend on for a datafile;
Alter database datafile '/u01/data/users02.dbf' autoextend on;
-- Resize a datafile
alter database datafile '/u01/data/users02.dbf' resize 10G;
-- Make a datafile offline/online
Alter database datafile '/u01/data/users02.dbf' offline;
Alter database datafile '/u01/data/users02.dbf' online;
-- Drop a datafile:
Alter tablespace USERS drop datafile '/u01/data/users02.dbf';
Add/drop Tempfile
Add/drop Tempfile
-- Add tempfile to temp tablespace:
alter tablespace TEMP1 add tempfile '/u01/dbaclass/tempfile/temp02.dbf' size 1G autoextend on next 200M;
-- Resize temp file:
alter database tempfile '/u01/dbaclass/tempfile/temp02.dbf' resize 2G;
-- Drop tempfile:
ALTER DATABASE TEMPFILE '/u01/dbaclass/tempfile/temp02.dbf' DROP INCLUDING DATAFILES;
alter tablespace TEMP1 add tempfile '/u01/dbaclass/tempfile/temp02.dbf' size 1G autoextend on next 200M;
-- Resize temp file:
alter database tempfile '/u01/dbaclass/tempfile/temp02.dbf' resize 2G;
-- Drop tempfile:
ALTER DATABASE TEMPFILE '/u01/dbaclass/tempfile/temp02.dbf' DROP INCLUDING DATAFILES;
Rename/move a datafile
Rename/move a datafile
--------------- For oracle 12c, move or rename of datafile can be done online with one line:
SQL> alter database move datafile '/home/oracle/producing1.dbf' to '/home/oracle/app/oracle/oradata/cdb1/testin1.dbf';
-- -------------For 11g, u have to follow below steps:( It needs downtime for the datafile)
--Make the tablespace offline:
alter database datafile '/home/oracle/app/oracle/oradata/cdb1/testin1.dbf' offline;
-- Move the file physically to a new location.
mv /home/oracle/app/oracle/oradata/cdb1/testin1.dbf /home/oracle/producing1.dbf
-- Rename at db level
alter database rename file '/home/oracle/app/oracle/oradata/cdb1/testin1.dbf' to '/home/oracle/producing1.dbf';
-- Recover the datafile:
recover datafile 37;
-- Make the datafile online:
alter database datafile '/home/oracle/producing1.dbf' online;
SQL> alter database move datafile '/home/oracle/producing1.dbf' to '/home/oracle/app/oracle/oradata/cdb1/testin1.dbf';
-- -------------For 11g, u have to follow below steps:( It needs downtime for the datafile)
--Make the tablespace offline:
alter database datafile '/home/oracle/app/oracle/oradata/cdb1/testin1.dbf' offline;
-- Move the file physically to a new location.
mv /home/oracle/app/oracle/oradata/cdb1/testin1.dbf /home/oracle/producing1.dbf
-- Rename at db level
alter database rename file '/home/oracle/app/oracle/oradata/cdb1/testin1.dbf' to '/home/oracle/producing1.dbf';
-- Recover the datafile:
recover datafile 37;
-- Make the datafile online:
alter database datafile '/home/oracle/producing1.dbf' online;
MULTITENANT(CDB-PDB)
Status of PDBS in multitenant
SQL> select dbid,name,open_mode,TOTAL_SIZE/1024/1024 from v$pdbs;
DBID NAME OPEN_MODE TOTAL_SIZE/1024/1024
---------- ------------------------------ ---------- --------------------
3987628790 PDB$SEED READ ONLY 830
1360187792 PDB1 READ WRITE 905
3819422575 PDB2 MOUNTED 0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
DBID NAME OPEN_MODE TOTAL_SIZE/1024/1024
---------- ------------------------------ ---------- --------------------
3987628790 PDB$SEED READ ONLY 830
1360187792 PDB1 READ WRITE 905
3819422575 PDB2 MOUNTED 0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
Tablespace info in Multitenant
Tablespace info in Multitenant
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."
--
COMPUTE SUM OF fsm apm ON REPORT
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;
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."
--
COMPUTE SUM OF fsm apm ON REPORT
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;
Temp tablespace details in Multitenant
Temp tablespace details in Multitenant
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;
show History of PDBS
show History of PDBS
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;
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;
currently connected PDB name
currently connected PDB name
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> select sys_context('USERENV','CON_NAME') FROM DUAL;
SYS_CONTEXT('USERENV','CON_NAME')
-----------------------------------
PDB1
CON_NAME
------------------------------
PDB1
SQL> select sys_context('USERENV','CON_NAME') FROM DUAL;
SYS_CONTEXT('USERENV','CON_NAME')
-----------------------------------
PDB1
stop and start pluggable db:
stop and start pluggable db:
-- Open/close all the pluggable db:
-- Connect to root container:
alter pluggable database all open;
alter pluggable database all close immediate;
-- Stop/start a pluggable db:
SQL> alter session set container=PDB1;
Session altered.
SQL> startup
Pluggable Database opened.
SQL> shutdown
Pluggable Database closed.
-- Connect to root container:
alter pluggable database all open;
alter pluggable database all close immediate;
-- Stop/start a pluggable db:
SQL> alter session set container=PDB1;
Session altered.
SQL> startup
Pluggable Database opened.
SQL> shutdown
Pluggable Database closed.
Drop a pluggable database
Drop a pluggable database
-- Need to run from root container;
SQL> show con_name
CON_NAME
------------------------
CDB$ROOT
ALTER PLUGGABLE DATABASE PDB1 CLOSE IMMEDIATE;
DROP PLUGGABLE DATABASE PDB1 INCLUDING DATAFILE;
SQL> show con_name
CON_NAME
------------------------
CDB$ROOT
ALTER PLUGGABLE DATABASE PDB1 CLOSE IMMEDIATE;
DROP PLUGGABLE DATABASE PDB1 INCLUDING DATAFILE;
SCHEDULER & JOBS
Manage dbms_schedulerjobs
--- Enable a job
EXECUTE DBMS_SCHEDULER.ENABLE('SCOTT.MONTHLYBILLING');
--- Disable a job
EXECUTE DBMS_SCHEDULER.DISABLE('SCOTT.MONTHLYBILLING');
-- Stop a running job
EXECUTE DBMS_SCHEDULER.STOP_JOB('SCOTT.MONTHLYBILLING');
--- Drop a running job
EXECUTE DBMS_SCHEDULER.DROP_JOB('SCOTT.MONTHLYBILLING');
-- Run a job immediately
EXECUTE DBMS_SCHEDULER.RUN_JOB('SCOTT.MONTHLYBILLING');
EXECUTE DBMS_SCHEDULER.ENABLE('SCOTT.MONTHLYBILLING');
--- Disable a job
EXECUTE DBMS_SCHEDULER.DISABLE('SCOTT.MONTHLYBILLING');
-- Stop a running job
EXECUTE DBMS_SCHEDULER.STOP_JOB('SCOTT.MONTHLYBILLING');
--- Drop a running job
EXECUTE DBMS_SCHEDULER.DROP_JOB('SCOTT.MONTHLYBILLING');
-- Run a job immediately
EXECUTE DBMS_SCHEDULER.RUN_JOB('SCOTT.MONTHLYBILLING');
Create and scheduler a scheduler job
Create and scheduler a scheduler job
-- TO schedule a job, first create a schedule, then a program and then a job
--Create a schedule
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
Schedule_name => 'DAILYBILLINGJOB',
Start_date => SYSTIMESTAMP,
Repeat_interval =>'FREQ=DAILY;BYHOUR=11; BYMINUTE=30',
Comments => 'DAILY BILLING JOB'
);
END;
-- Create a 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;
-- Now create the job:
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'DAILYBILLINGJOB_RUN',
program_name => 'DAILYBILLINGJOB',
schedule_name => 'DAILYBILLINGJOB_SCHED',
enabled => FLASE,
comments => 'daily billing job'
);
END;
-- ENABLE THE JOB
DBMS_SCHEDULER.ENABLE('DAILYBILLINGJOB_RUN');
--Create a schedule
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
Schedule_name => 'DAILYBILLINGJOB',
Start_date => SYSTIMESTAMP,
Repeat_interval =>'FREQ=DAILY;BYHOUR=11; BYMINUTE=30',
Comments => 'DAILY BILLING JOB'
);
END;
-- Create a 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;
-- Now create the job:
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'DAILYBILLINGJOB_RUN',
program_name => 'DAILYBILLINGJOB',
schedule_name => 'DAILYBILLINGJOB_SCHED',
enabled => FLASE,
comments => 'daily billing job'
);
END;
-- ENABLE THE JOB
DBMS_SCHEDULER.ENABLE('DAILYBILLINGJOB_RUN');
Drop a schedule
Drop a schedule
BEGIN
DBMS_SCHEDULER.DROP_SCHEDULE(
schedule_name => 'DAILYBILLINGJOB_SCHED',
force => TRUE
);
END;
DBMS_SCHEDULER.DROP_SCHEDULE(
schedule_name => 'DAILYBILLINGJOB_SCHED',
force => TRUE
);
END;
scheduler shell script in dbms_scheduler
scheduler shell script in dbms_scheduler
-- This feature in available from oracle 12c onward
-- Create an credential store:
BEGIN
dbms_credential.create_credential (
CREDENTIAL_NAME => 'ORACLEOSUSER',
USERNAME => 'oracle',
PASSWORD => 'oracle@98765',
DATABASE_ROLE => NULL,
WINDOWS_DOMAIN => NULL,
COMMENTS => 'Oracle OS User',
ENABLED => true
);
END;
/
-- Create the job:
exec 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');
-- Create an credential store:
BEGIN
dbms_credential.create_credential (
CREDENTIAL_NAME => 'ORACLEOSUSER',
USERNAME => 'oracle',
PASSWORD => 'oracle@98765',
DATABASE_ROLE => NULL,
WINDOWS_DOMAIN => NULL,
COMMENTS => 'Oracle OS User',
ENABLED => true
);
END;
/
-- Create the job:
exec 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');
Monitor scheduler jobs
Monitor scheduler jobs
-- Monitor currently running jobs
SELECT job_name, session_id, running_instance, elapsed_time, FROM dba_scheduler_running_jobs;
-- View the job run details
select * from DBA_SCHEDULER_JOB_RUN_DETAILS;
-- View the job related logs:
select * from DBA_SCHEDULER_JOB_LOG;
SELECT job_name, session_id, running_instance, elapsed_time, FROM dba_scheduler_running_jobs;
-- View the job run details
select * from DBA_SCHEDULER_JOB_RUN_DETAILS;
-- View the job related logs:
select * from DBA_SCHEDULER_JOB_LOG;