Tablespace Commands

For more exciting blogs on Oracle, please click the LINK

1. Checking tablespace size

col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files 
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments 
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name;
**and tu.tablespace_name = 'tablespace_name';

OR

REM
REM Check for available free space
REM
TTITLE CENTER 'Tablespace free space' SKIP 1
COLUMN tsname        FORMAT a25
COLUMN allocation_type FORMAT A4 TRU HEADING ALLO
COLUMN contents      FORMAT A4 TRU HEADING MGMT
COLUMN Tot_Size      FORMAT 999,999 HEADING "TOTAL(M)"
COLUMN Tot_Free      FORMAT 999,999 HEADING "FREE(M)"
COLUMN Pct_Free      FORMAT 999 HEADING "FREE %"
COLUMN Fragments     FORMAT 99,999 HEADING "FRAGMTS"
COLUMN Large_Ext     FORMAT 999,999 HEADING "LARG(M)"
spool c:\log\freespace8i.lst
set feedback off pages 999 trims on 
SELECT a.tablespace_name TSNAME, SUM(a.tots)/1048576 Tot_Size,
       SUM(a.sumb)/1048576 Tot_Free,
       SUM(a.sumb)*100/sum(a.tots) Pct_Free,
       SUM(a.largest)/1048576 Large_Ext, SUM(a.chunks) Fragments,
       b.contents, b.allocation_type
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
        UNION
        SELECT tablespace_name, SUM(bytes) tots, 0, 0, 0
        FROM   dba_temp_files
        GROUP BY tablespace_name) a, dba_tablespaces b
WHERE b.tablespace_name = a.tablespace_name
GROUP BY a.tablespace_name, b.contents, b.allocation_type
Order by 4
/

OR

col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999

select df.tablespace_name "Tablespace",totalusedspace "Used MB",(df.totalspace - tu.totalusedspace) "Free MB",df.totalspace "Total MB",round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free" from (select tablespace_name,round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df,(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu where df.tablespace_name = tu.tablespace_name order by df.tablespace_name ;

2. Check Particular Tablespace size

col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(maxbytes) / 1048576) TotalSpace
from dba_data_files 
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments 
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
and df.tablespace_name ='' ;

3. Check Datafiles of a particular Tablespace

col file_name for a60
select file_name,sum(bytes)/1024/1024,autoextensible,sum(maxbytes)/1024/1024 from dba_data_files where tablespace_name='Tablespace_name' group by file_name,autoextensible order by 1;

4. Check DDL of a particular tablespace

set long 99999999
select dbms_metadata.get_ddl('TABLESPACE','Tablespace_name') from dual;

5. Add Datafile to a tablespace

alter tablespace Tablespace_name add datafile '/opt/ORACLE/DATA/datafile01.dbf' size 10G AUTOEXTEND ON NEXT 4194304 MAXSIZE 32767M;

6. To Online a tablespace

SQL> alter tablespace tablespace_name online;

Tablespace altered.

SQL> select STATUS from dba_tablespaces where tablespace_name = 'tablespace_name';

STATUS

---------

ONLINE

7. This script will tell you the usage of Temporary tablespace

select sysdate dtstamp,

s.tablespace_name,

d.tbspc_mb,

s.total_blocks*8192/1024/1024 temp_tot_mb,

s.used_blocks*8192/1024/1024 temp_used_mb,

s.free_blocks*8192/1024/1024 temp_free_mb

from v$sort_segment s,

(select tablespace_name,sum(bytes/1024/1024) tbspc_mb

from dba_data_files

group by tablespace_name

union

select tablespace_name,sum(bytes/1024/1024) tbspc_mb

from dba_temp_files

group by tablespace_name) d

where s.tablespace_name=d.tablespace_name;

8. To check Max size of tablespace

select TABLESPACE_NAME,BYTES/1024/1024/1024,STATUS,AUTOEXTENSIBLE,MAXBYTES,MAXBLOCKS,INCREMENT_BY,ONLINE_STATUS,FILE_NAME from dba_data_files where TABLESPACE_NAME like 'TABLESPACE_NAME';

9. To find Total Free space in Temp Tablespace

select 'FreeSpace  ' || (free_blocks*8)/1024/1024 ||' GB'  from v$sort_segment where tablespace_name='TEMP';

select tablespace_name , (free_blocks*8)/1024/1024 FreeSpaceInGB,

(used_blocks*8)/1024/1024 UsedSpaceInGB,

(total_blocks*8)/1024/1024 TotalSpaceInGB

from v$sort_segment where tablespace_name like '%TEMP%'

10. To find Total Space Allocated for Temp Tablespace

select 'TotalSpace ' || (sum(blocks)*8)/1024/1024 ||' GB'  from dba_temp_files where tablespace_name='TEMP';

11. Identifying WHO is currently using TEMP Segments

SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_used, c.sql_text

FROM v$session a, v$tempseg_usage b, v$sqlarea c,

(select block_size from dba_tablespaces where tablespace_name='TEMP') d

WHERE b.tablespace = 'TEMP'

and a.saddr = b.session_addr

AND c.address= a.sql_address

AND c.hash_value = a.sql_hash_value

AND (b.blocks*d.block_size)/1048576 > 1024

ORDER BY b.tablespace, 6 desc;

12. Check locked table

SELECT session_id,B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name

FROM V$Locked_Object A, All_Objects B

WHERE A.Object_ID = B.Object_ID;

select owner||'.'||object_name obj

,oracle_username||' ('||s.status||')' oruser

,os_user_name osuser

,machine computer

,l.process unix

,''''||s.sid||','||s.serial#||'''' ss

,r.name rs

,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time

from v$locked_object l

,dba_objects o

,v$session s

,v$transaction t

,v$rollname r

where l.object_id = o.object_id

and s.sid=l.session_id

and s.taddr=t.addr

and t.xidusn=r.usn

order by osuser, ss, obj

13. Check Table Fragmentation :

set pages 50000 lines 32767

select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size", round((num_rows*avg_row_len/1024),2)||'kb' "Actual size", round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||'kb',

((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 "reclaimable space % " from dba_tables where table_name ='&table_Name' AND OWNER LIKE '&schema_name'

14. Create table from a partition

create table INFRANEW.INCLUDE_TEST_PARTITIONS as select partition_name from dba_tab_partitions where partition_name in (
'SUBSCRIBER_2012Q4'

Submit a Comment Cancel reply