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
Performance Tuning 