Script to extract the Tablespace create script from your Database

This script will help you to extract the tablespace create script from your database. This create script will helpful for you while cloning the DB using export/import (logical backup) method.

select ‘create tablespace ‘ || df.tablespace_name || chr(10)
|| ‘ datafile ”’ || df.file_name || ”’ size ‘ || df.bytes
|| decode(autoextensible,’N’,null, chr(10) || ‘ autoextend on maxsize ‘
|| maxbytes)
|| chr(10)
|| ‘default storage ( initial ‘ || initial_extent
|| decode (next_extent, null, null, ‘ next ‘ || next_extent )
|| ‘ minextents ‘ || min_extents
|| ‘ maxextents ‘ || decode(max_extents,’2147483645′,’unlimited’,max_extents)
|| ‘) ;’
from dba_data_files df, dba_tablespaces t
where df.tablespace_name=t.tablespace_name
/

###########################################################################

select ‘create tablespace ‘ || df.tablespace_name || chr(10)
|| ‘ datafile ”’ || df.file_name || ”’ size ‘ || df.bytes
|| decode(autoextensible,’N’,null, chr(10) || ‘ autoextend on maxsize ‘
|| maxbytes)
|| chr(10)
|| ‘default storage ( initial ‘ || initial_extent
|| decode (next_extent, null, null, ‘ next ‘ || next_extent )
|| ‘ minextents ‘ || min_extents
|| ‘ maxextents ‘ || decode(max_extents,’2147483645′,’unlimited’,max_extents)
|| ‘) ;’ “Script To Recreate Tablespaces”
from dba_data_files df, dba_tablespaces t
where df.tablespace_name=t.tablespace_name;

Script to find the trigger associated with the table

set lines 100 pages 999
select trigger_name
, trigger_type
, status
from dba_triggers
where owner = ‘&owner’
and table_name = ‘&table’
order by status, trigger_name
/

Script to recompile the invalid objects

select ‘ALTER ‘ || OBJECT_TYPE || ‘ ‘ || OWNER || ‘.’ || OBJECT_NAME || ‘ COMPILE;’ from dba_objects where status = ‘INVALID’
and object_type in (‘FUNCTION’,’PROCEDURE’,’PACKAGE_BODY’) ;

Script to get the DB,Control,Log files location.

########################

CONTROL FILE

#########################

set pagesize 500

set linesize 130

Prompt

Prompt Control Files Location >>>>

col name format a60 heading “Control Files”

select name

from sys.v_$controlfile

/

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

LOG FILE

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

Prompt

Prompt Redo Log File Locations >>>>

Prompt

col Grp format 9999

col member format a50 heading “Online REDO Logs”

col File# format 9999

col name format a50 heading “Online REDO Logs”

break on Grp

select group#,member

from sys.v_$logfile

/

#######################################

DATA FILE

#####################################

Prompt Data Files Locations >>>>

col Tspace format a25

col status format a3 heading Sta

col Id format 9999

col Mbyte format 999999999

col name format a50 heading “Database Data Files”

col Reads format 99,999,999

col Writes format 99,999,999

break on report

compute sum label ‘Total(MB)’ of Mbyte on report

select F.file_id Id,

F.file_name name,

F.bytes/(1024*1024) Mbyte,

decode(F.status,’AVAILABLE’,’OK’,F.status) status,

F.tablespace_name Tspace

from sys.dba_data_files F

order by tablespace_name;

　

Script to List all tablespaces with free space < 10%

set pagesize 300

set linesize 100

column tablespace_name format a15 heading ‘Tablespace’

column sumb format 999,999,999

column extents format 9999

column bytes format 999,999,999,999

column largest format 999,999,999,999

column Tot_Size format 999,999 Heading ‘Total Size(Mb)’

column Tot_Free format 999,999,999 heading ‘Total Free(Kb)’

column Pct_Free format 999.99 heading ‘% Free’

column Max_Free format 999,999,999 heading ‘Max Free(Kb)’

column Min_Add format 999,999,999 heading ‘Min space add (MB)’

ttitle center ‘Tablespaces With Less Than 10% Free Space’ skip 2

set echo off

select a.tablespace_name,sum(a.tots/1048576) Tot_Size,

sum(a.sumb/1024) Tot_Free,

sum(a.sumb)*100/sum(a.tots) Pct_Free,

ceil((((sum(a.tots) * 15) – (sum(a.sumb)*100))/85 )/1048576) Min_Add

from

(

select tablespace_name,0 tots,sum(bytes) sumb

from dba_free_space a

group by tablespace_name

union

select tablespace_name,sum(bytes) tots,0 from

dba_data_files

group by tablespace_name) a

group by a.tablespace_name

having sum(a.sumb)*100/sum(a.tots) < 10

order by pct_free;

Script to find temporary tablespace free space

SELECT tablespace_name, SUM (bytes_used), SUM (bytes_free) FROM v$temp_space_header GROUP BY tablespace_name;

Script to find the Free space of tablespace (Above 90% usage)

set linesize 150

column tablespace_name format a20 heading ‘Tablespace’

column sumb format 999,999,999

column extents format 9999

column bytes format 999,999,999,999

column largest format 999,999,999,999

column Tot_Size format 999,999 Heading ‘Total| Size(Mb)’

column Tot_Free format 999,999,999 heading ‘Total Free(MB)’

column Pct_Free format 999.99 heading ‘% Free’

column Chunks_Free format 9999 heading ‘No Of Ext.’

column Max_Free format 999,999,999 heading ‘Max Free(Kb)’

set echo off

PROMPT FREE SPACE AVAILABLE IN TABLESPACES

select a.tablespace_name,sum(a.tots/1048576) Tot_Size,

sum(a.sumb/1048576) Tot_Free,

sum(a.sumb)*100/sum(a.tots) Pct_Free,

sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free

from

(

select tablespace_name,0 tots,sum(bytes) sumb,

max(bytes) largest,count(*) chunks

from dba_free_space a

group by tablespace_name

union

select tablespace_name,sum(bytes) tots,0,0,0 from

dba_data_files

group by tablespace_name) a

group by a.tablespace_name

order by pct_free;