Tablespace free space in  Database :

set numformat 9,999,999,999
col TblspNm for a28 heading ‘TABLESPACE NAME’
col curMb for 9,999,999 heading ‘CURRENT|TOTAL (MB)’
col MaxMb for 9,999,999 heading ‘MAX|TOTAL (MB)’
col TotalUsed for 9,999,999 heading ‘CURRENT|USED (MB)’
col TotalFree for 9,999,999 heading ‘CURRENT|FREE (MB)’
col UPercent for 999 heading ‘   CURR| % FULL’;
col UPercent for a7 heading ‘   CURR| % FULL’;

ttitle skip 1 right  ‘Page: ‘ format 99 sql.pno skip 1 –

center ‘TABLESPACE USAGE STATUS REPORT’ skip 2

select distinct
a.tablespace_name “TblspNm”,
SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) “MaxMb”, SUM(a.bytes)/1024/1024 “CurMb”,(SUM(a.bytes)/1024/1024 – round(c.”Free”/1024/1024)) “TotalUsed”,
(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) – (SUM(a.bytes)/1024/1024 – round(c.”Free”/1024/1024))) “TotalFree”,
lpad(round(100*(SUM(a.bytes)/1024/1024 – round(c.”Free”/1024/1024))/(SUM(decode(b.maxextend, null, a.bytes/1024/1024, b.maxextend*8192/1024/1024)))),5) || ‘ %’
“UPercent”
from dba_data_files a, sys.filext$ b,
(SELECT d.tablespace_name , sum(nvl(c.bytes,0)) “Free”
FROM dba_tablespaces d,dba_free_space c
where d.tablespace_name = c.tablespace_name(+)
group by d.tablespace_name) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
group by a.tablespace_name, c.”Free”/1024
order by round(100*(SUM(a.bytes)/1024/1024 – round(c.”Free”/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) desc
;

—————————————————————————————————————–

Session Details :

select ‘ Sid, Serial#,Aud sid : ‘|| s.sid||’ , ‘||s.serial#||’ ,  ‘||
s.audsid||chr(15)|| ‘     DB User / OS User : ‘||s.username||
‘   /   ‘||s.osuser||chr(10)|| ‘    Machine – Terminal : ‘||
s.machine||’  –  ‘|| s.terminal||chr(10)|| s.module||’-‘||s.action ||
‘        OS Process Ids : ‘||
s.process||’ (Client)  ‘||p.spid||’ – ‘||p.pid||’ (Server)’|| chr(10)||
‘   Client Program Name : ‘||s.program||chr(10)||
‘SESSION STATUS  : ‘||s.status||’   ‘||chr(10)||
‘MINUTES :    ‘||round(S.LAST_CALL_ET/60)   “Session Info”
from v$process p,v$session s
where p.addr = s.paddr
and s.sid = &sid;

———————————————————————————————————————–

Tracing :

alter session set events ‘10046 trace name context forever, level 12’;  – Session level
alter system set events ‘10046 trace name context forever, level 12’; – System level

alter system set events ‘10046 trace name context off’;

————————————————————————————————————————-

Patchset applied :

col ACTION format a15
col NAMESPACE format a15
col version format a10
col COMMENTS format a42
col BUNDLE_SERIES format a20
col action_time format a30
select * from sys.registry$history;

————————————————————————————————————————-

Locks :

set linesize 80
col sess for a15
SELECT DECODE(request,0,’Holder: ‘,’Waiter: ‘)||trim(sid) sess,
id1, id2, lmode, request, type
FROM gV$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM gV$LOCK WHERE request>0)
ORDER BY id1, request
/

————————————————————————————————————————–

Row getting blocked .

select
do.object_name,
row_wait_obj#,
row_wait_file#,
row_wait_block#,
row_wait_row#,
dbms_rowid.rowid_create ( 1, data_object_id, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from
v$session s,
dba_objects do
where sid=&sid and ROW_WAIT_OBJ# = do.OBJECT_ID;

 

select * from  <table_name> where rowid=’row id from above query’;

—————————————————————————————————————————

Using date to retrive data :

Using Timestamp works :

select * from v$active_session_history where sample_time between to_timestamp(’06-20-2014 3:00:41.911 PM’,’MM-DD-YYYY hh:mi:ss.ff PM’) and
to_timestamp(’06-20-2014 3:03:57.421 PM’,’MM-DD-YYYY hh:mi:ss.ff PM’)

—————————————————————————————————————————

Database Recovery :

recover database using backup controlfile until time ’01-OCT-2014 02:00:00′;

—————————————————————————————————————————

Datafile rename command generation :

select ‘alter database rename file ‘||””||name||””||’ to ‘||””||regexp_replace(name,’/u21′,’/u67′)||””||’;’from v$datafile where name like ‘%/u21%’;

select ‘alter database rename file ‘||””||name||””||’ to ‘||””||regexp_replace(name,’/u09′,’/ilmdu02′)||””||’;’from v$datafile where name like ‘%/u09%’;

—————————————————————————————————————————-

“Read by other session” wait event  :

To find the sessions waiting on blocks.

SELECT p1 “file#”, p2 “block#”, p3 “class#”
FROM v$session_wait WHERE event = ‘read by other session’;

After finding blocks object involved by running this query :

SELECT relative_fno, owner, segment_name, segment_type FROM dba_extents
WHERE file_id = &file
AND &block BETWEEN block_id AND block_id + blocks – 1;

 

Baselining Queries :

To create Sql SET :
___________________

BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => ‘2y4jv1yavr6gq_03222019’,
description => ‘SQL Tuning Set for loading plan into SQL Plan Baseline for sql id 2y4jv1yavr6gq’);
END;
To load the plans to the sql set :
__________________________________

DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE(
dbms_sqltune.select_workload_repository(begin_snap=>61600, end_snap=>61853,basic_filter=>’sql_id = ”2y4jv1yavr6gq”’,attribute_list=>’ALL’)
) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> ‘2y4jv1yavr6gq_03222019’, populate_cursor=>cur);
CLOSE cur;
END;
/

Verify the plans got loaded to sql Set :
_________________________________________

SELECT
first_load_time ,
executions as execs ,
parsing_schema_name ,
elapsed_time / 1000000 as elapsed_time_secs ,
cpu_time / 1000000 as cpu_time_secs ,
buffer_gets ,
disk_reads ,
direct_writes ,
rows_processed ,
fetches ,
optimizer_cost ,
sql_plan ,
plan_hash_value ,
sql_id ,
sql_text
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => ‘2y4jv1yavr6gq_03222019’)
);

SELECT
plan_hash_value
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => ‘2y4jv1yavr6gq_03222019’)
);
Create baseline for the sql to be forced :
_________________________________________
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => ‘2y4jv1yavr6gq_03222019′,
basic_filter=>’plan_hash_value = ”3086047068”’
);
END;
/
select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like ‘%2y4jv1yavr6gq%’;
exec DBMS_SHARED_POOL.PURGE (‘000000057AA369F8,3267995062′,’C’);


-- -----------------------------------------------------------------------------------
-- File Name    : https://oracle-base.com/dba/script_creation/table_indexes_ddl.sql
-- Author       : Tim Hall
-- Description  : Creates the index DDL for specified table, or all tables.
-- Call Syntax  : @table_indexes_ddl (schema-name) (table-name or all)
-- Last Modified: 16/03/2013 - Rewritten to use DBMS_METADATA
-- -----------------------------------------------------------------------------------
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
   -- Uncomment the following lines if you need them.
   --DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', false);
   --DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'STORAGE', false);
END;
/

SELECT DBMS_METADATA.get_ddl ('INDEX', index_name, owner)
FROM   all_indexes
WHERE  owner      = UPPER('&1')
AND    table_name = DECODE(UPPER('&2'), 'ALL', table_name, UPPER('&2'));

SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON