tablespace scripts

 

---------- Forwarded message ---------
From: Elakkiya N <elaks2111@gmail.com>
Date: Thu, 7 Jul 2022, 19:44
Subject: Tablespace queries
To: <rajyalaxmi.pabba@gmail.com>

TABLESPACE USAGE:
*****************

===================================================================================================================


SELECT A.TABLESPACE_NAME, A.GBYTES "ALLOCATED GB", B.GBYTES "FREE GB", A.GBYTES-B.GBYTES "USED GB"
FROM (SELECT TABLESPACE_NAME,((SUM(BYTES)/1024)/1024)/1024 "GBYTES" FROM SYS.DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME,((SUM(BYTES)/1024)/1024)/1024 "GBYTES"  FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME ORDER BY A.TABLESPACE_NAME;
/


SELECT A.TABLESPACE_NAME, A.GBYTES "ALLOCATED GB", B.GBYTES "FREE GB", A.GBYTES-B.GBYTES "USED GB"
FROM (SELECT TABLESPACE_NAME,((SUM(BYTES)/1024)/1024)/1024 "GBYTES"FROM SYS.DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME,((SUM(BYTES)/1024)/1024)/1024 "GBYTES"  FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME IN ('APPS_TS_TX_DATA', 'APPS_TS_TX_IDX',  'APPS_TS_TX_LG_DATA', 'APPS_TS_TX_LG_IDX') ORDER BY A.TABLESPACE_NAME;

=====================================================================================================================
Check the default tablespace for the user:
===========================================
SELECT distinct tablespace_name FROM dba_ts_quotas where username in ('APPLSYS','GL','HR','AR','INV','ONT');


SELECT DBMS_METADATA.GET_DDL('USER','APPS') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'APPS') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'APPS') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'APPS') FROM dual;


===================================================================================================================


select file_name, bytes/1024/1024/1024/1024,maxbytes/1024/1024/1024/1024,AUTOEXTENSIBLE from dba_data_files where tablespace_name='OBIDWH';

select file_name, bytes/1024/1024/1024,maxbytes/1024/1024/1024,AUTOEXTENSIBLE from dba_data_files where tablespace_name='DATA';

select file_id,file_name,bytes/1024/1024,maxbytes/1024/1024,AUTOEXTENSIBLE from dba_data_files where tablespace_name='DATA';

select file_name, (bytes)/1024/1024,(maxbytes)/1024/1024,AUTOEXTENSIBLE from dba_data_files where tablespace_name='DATA';

select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;



select name,total_mb/1024/1024,free_mb/1024/1024 from v$asm_diskgroup;

select name,total_mb/1024,free_mb/1024 from v$asm_diskgroup;

alter database datafile '+DATAC1/MOAP1/1F301CF7FCEA9797E053354D840AF738/DATAFILE/system.1028.930032153' autoextend on maxsize 32767M;

alter database datafile '+DATAC1/simdev/datafile/archive_data.3123.1077985245' resize 6g;


alter database datafile '+DATAC1/CHNSINET/23B495A271726812E053354D840A9EA2/DATAFILE/data_error.3980.89487' autoextend on maxsize 1900g

alter tablespace SYSTEM add datafile '+DATAC1' autoextend on maxsize 32767M;


alter tablespace CMX_DATA add datafile '+DATA' size 1g autoextend on maxsize 31g;

select bytes/1024/1024/1024 from sys.sm$ts_avail where tablespace_name='USERS';
select bytes/1024/1024/1024 from sys.sm$ts_free where tablespace_name='USERS';
select bytes/1024/1024/1024 from sys.sm$ts_used where tablespace_name='USERS';


select 'alter database datafile '||file_name||' '||' autoextend on;' from dba_data_files;

===================================================================================================================

COLUMN tablespace_name HEADING Tablespace|Name FORMAT a30
COLUMN totalspace HEADING Total|Space FORMAT 99999999
COLUMN free HEADING Free FORMAT 99999999.99
COLUMN Percent_free HEADING Percent|Free FORMAT 99999999.99
set lines 500
set pages 500
select a.tablespace_name ,c.bytes/1024/1024 TOTALSPACE , round((b.bytes/1024/1024),2) FREE,
100-round((b.bytes/c.bytes)*100,2)  Percent_Used, round((b.bytes/c.bytes)*100,2)  Percent_free from
dba_tablespaces a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_free_space GROUP BY tablespace_name ) b,
( select count(1) datafiles, SUM(bytes) bytes,
tablespace_name from dba_data_files GROUP BY tablespace_name ) c
where
b.tablespace_name (+) = a.tablespace_name
AND
c.tablespace_name (+) = a.tablespace_name
--and a.tablespace_name='
--and b.tablespace_name='
--and c.tablespace_name='
--and round((b.bytes/c.bytes)*100,2)<6
ORDER BY ((c.bytes-b.bytes)/c.bytes) DESC
/

select
tablespace_name
,tablespace_size/1024/1024/1024 gb_size
,allocated_space/1024/1024/1024 gb_alloc
,free_space/1024/1024/1024 gb_free
from dba_temp_free_space;

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;


select segment_name,
round(nvl(sum(act),0)/(1024*1024*1024),3 ) "ACT GB BYTES",
round(nvl(sum(unexp),0)/(1024*1024*1024),3) "UNEXP GB BYTES",
round(nvl(sum(exp),0)/(1024*1024*1024),3) "EXP GB BYTES",
NO_OF_EXTENTS
from ( select segment_name, nvl(sum(bytes),0) act,00 unexp, 00 exp, count(*) NO_OF_EXTENTS
from DBA_UNDO_EXTENTS
where status='ACTIVE' and tablespace_name = 'UNDOTBS1'
group by segment_name
union
select segment_name,00 act, nvl(sum(bytes),0) unexp, 00 exp , count(*) NO_OF_EXTENTS
from DBA_UNDO_EXTENTS
where status='UNEXPIRED' and tablespace_name = 'UNDOTBS1'
group by segment_name
union
select segment_name, 00 act, 00 unexp, nvl(sum(bytes),0) exp, count(*) NO_OF_EXTENTS
from DBA_UNDO_EXTENTS
where status='EXPIRED' and tablespace_name = 'UNDOTBS1'
group by segment_name
) group by segment_name, NO_OF_EXTENTS order by 5 desc;


select file_id,file_name,bytes/1024/1024,maxbytes/1024/1024,AUTOEXTENSIBLE from dba_temp_files where tablespace_name='WMWHSE1_TEMPDATA';

alter database tempfile'+DATAC1/wmprd/tempfile/wmwhse1_tempdata.1342.1054643185' autoextend on maxsize 31g;

alter tablespace WMWHSE1_TEMPDATA add tempfile'+DATAC1' size 10g autoextend on maxsize 31g;


===================================================================================================================
select owner, table_name, tablespace_name
from dba_tables
where table_name='S_EVT_ACT';


                                                              Tablespace
OWNER                          TABLE_NAME                     Name
------------------------------ ------------------------------ ------------------------------
OBIDWH                         S_EVT_ACT                      OBIDWH
NWCS_ODS                       S_EVT_ACT                      CDMR2
OBIRECON                       S_EVT_ACT                      OBIDWH




select SESSION_ID,INSTANCE_ID,STATUS,TIMEOUT,NAME,ERROR_MSG from dba_resumable where STATUS<>'NORMAL';
=====================================================================================================================

DISPLAY SPACE USAGE of EACH DATAFILE:
=====================================

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN "Tablespace Name" FORMAT A20
COLUMN "File Name" FORMAT A80

SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",
Substr(df.file_name,1,80) "File Name",
Round(df.bytes/1024/1024/1024,0) "Size (G)",
decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024/1024,0)) "Used (G)",
decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024/1024,0)) "Free (G)",
decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used"
FROM DBA_DATA_FILES DF,
(SELECT file_id,
sum(bytes) used_bytes
FROM dba_extents
GROUP by file_id) E,
(SELECT Max(bytes) free_bytes,
file_id
FROM dba_free_space
GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
AND df.file_id = f.file_id (+)
ORDER BY df.tablespace_name,
df.file_name
/



Datafile size:
==============

col file_name for a60;
set pagesize 500;
set linesize 500;
SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb, ((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

===================================================================================================================

clear columns
 column tablespace format a30
 column total_mb format 999,999,999.99
 column used_mb format 999,999,999,999.99
 column free_mb format 999,999,999.99
 column pct_used format 999.99
 column graph format a25 heading "GRAPH (X=5%)"
 column status format a10
 compute sum of total_mb on report
 compute sum of used_mb on report
 compute sum of free_mb on report
 break on report
 set lines 200 pages 100
 select total.ts tablespace,
 DECODE(total.mb,null,'OFFLINE',dbat.status) status,
total.mb total_mb,
 NVL(total.mb - free.mb,total.mb) used_mb,
 NVL(free.mb,0) free_mb,
 DECODE(total.mb,NULL,0,NVL(ROUND((total.mb -  free.mb)/(total.mb)*100,2),100))  pct_used,
 CASE WHEN (total.mb IS NULL) THEN '['||RPAD(LPAD('OFFLINE',13,'-'),20,'-')||']'
 ELSE '['|| DECODE(free.mb,
null,'XXXXXXXXXXXXXXXXXXXX',
 NVL(RPAD(LPAD('X',trunc((100-ROUND( (free.mb)/(total.mb) * 100, 2))/5),'X'),20,'-'),
 '--------------------'))||']'
 END as GRAPH
 from
 (select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_data_files group by tablespace_name) total,
 (select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name) free,
dba_tablespaces dbat
 where total.ts=free.ts(+) and
total.ts=dbat.tablespace_name and tablespace_name != 'UNDOTBS1'
 order by 6
 /
title off
 rem clear columns  
===================================================================================================================

clear columns
column tablespace format a20
column total_mb format 999,999,999,999.99
column used_mb format 999,999,999,999.99
column free_mb format 999,999,999.99
column pct_used format 999.99
column graph format a25 heading "GRAPH (X=5%)"
column status format a10
compute sum of total_mb on report
compute sum of used_mb on report
compute sum of free_mb on report
break on report
set lines 200 pages 100
select  total.ts tablespace,
        DECODE(total.mb,null,'OFFLINE',dbat.status) status,
total.mb total_mb,
NVL(total.mb - free.mb,total.mb) used_mb,
NVL(free.mb,0) free_mb,
        DECODE(total.mb,NULL,0,NVL(ROUND((total.mb - free.mb)/(total.mb)*100,2),100)) pct_used,
CASE WHEN (total.mb IS NULL) THEN '['||RPAD(LPAD('OFFLINE',13,'-'),20,'-')||']'
ELSE '['|| DECODE(free.mb,
                             null,'XXXXXXXXXXXXXXXXXXXX',
                             NVL(RPAD(LPAD('X',trunc((100-ROUND( (free.mb)/(total.mb) * 100, 2))/5),'X'),20,'-'),
'--------------------'))||']'
         END as GRAPH
from
(select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_data_files group by tablespace_name) total,
(select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name) free,
        dba_tablespaces dbat
where total.ts=free.ts(+) and
      total.ts=dbat.tablespace_name
UNION ALL
select  sh.tablespace_name,
        'TEMP',
SUM(sh.bytes_used+sh.bytes_free)/1024/1024 total_mb,
SUM(sh.bytes_used)/1024/1024 used_mb,
SUM(sh.bytes_free)/1024/1024 free_mb,
        ROUND(SUM(sh.bytes_used)/SUM(sh.bytes_used+sh.bytes_free)*100,2) pct_used,
        '['||DECODE(SUM(sh.bytes_free),0,'XXXXXXXXXXXXXXXXXXXX',
              NVL(RPAD(LPAD('X',(TRUNC(ROUND((SUM(sh.bytes_used)/SUM(sh.bytes_used+sh.bytes_free))*100,2)/5)),'X'),20,'-'),
                '--------------------'))||']'
FROM v$temp_space_header sh
GROUP BY tablespace_name
order by 1
/
ttitle off
rem clear columns  
 


===================================================================================================================

set lines 100
col name format a60


select name,floor(space_limit / 1024 / 1024) "Size MB",ceil(space_used  / 1024 / 1024) "Used MB"from v$recovery_file_dest order by name;


=====================================================================================================================

select file_name , TABLESPACE_NAME from DBA_TEMP_FILES;

To know temp table space:
************************************************************

select a.tablespace_name "TBL", a."Total SIZE", b."USED",round((b."USED"/a."Total SIZE")*100) "%USED",
round((1-(b."USED"/a."Total SIZE"))*100) "%FREE"  
from (select tablespace_name, sum(bytes)/1024/1024/1024 "Total SIZE" from dba_temp_files where tablespace_name='TEMP'
group by tablespace_name) a,(select tablespace_name, (total_blocks *(select value from v$parameter where name='db_block_size'))/1024/1024/1024 "USED"
from v$sort_segment) b where a.tablespace_name=b.tablespace_name;



SELECT a.tablespace_name,ROUND((c.total_blocks*b.block_size)/1024/1024/1024,2)
"Total Size [GB]",ROUND((a.used_blocks*b.block_size)/1024/1024/1024,2) "Used_size[GB]",
ROUND(((c.total_blocks-a.used_blocks)*b.block_size)/1024/1024/1024,2) "Free_size[GB]",
ROUND((a.max_blocks*b.block_size)/1024/1024/1024,2) "Max_Size_Ever_Used[GB]",            
ROUND((a.max_used_blocks*b.block_size)/1024/1024/1024,2) "MaxSize_ever_Used_by_Sorts[GB]" ,
ROUND((a.used_blocks/c.total_blocks)*100,2) "Used Percentage"
FROM V$sort_segment a,dba_tablespaces b,(SELECT tablespace_name,SUM(blocks)
total_blocks FROM dba_temp_files GROUP by tablespace_name) c
WHERE a.tablespace_name=b.tablespace_name AND a.tablespace_name=c.tablespace_name;



select
tablespace_name
,tablespace_size/1024/1024/1024 gb_size
,allocated_space/1024/1024/1024 gb_alloc
,free_space/1024/1024/1024 gb_free
from dba_temp_free_space;


To check the default temporary tablespace:
*********************************************

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
COLUMN description FORMAT A50
SET LINESIZE 200
SELECT *
FROM database_properties
WHERE property_name like '%TABLESPACE';


To view all the temporary tablespace:
**************************************

select TABLESPACE_NAME "TEMP TBS NAME",bytes/(1024*1024) "SIZE(MBs)",BYTES_USED/(1024*1024) "BYTES USED(MBs)",BYTES_FREE/(1024*1024) "BYTES FREE(MBs)" from sys. V_$TEMP_SPACE_HEADER,v$tempfile;

To get the DDL of temp tablespace:
**************************************
set heading off;
set echo off;
set pages 2000
set long 99999
spool tablespace_temp.sql
select dbms_metadata.get_ddl('TABLESPACE', 'tablespace_name') from dba_temp_files;


Tempfile:
************************************************************
SELECT file_id, file_name, tablespace_name, bytes/1024/1024 MB,autoextensible FROM dba_temp_files where tablespace_name='&a';


select file_name, bytes/1024/1024/1024,maxbytes/1024/1024/1024,AUTOEXTENSIBLE from dba_temp_files where tablespace_name='TEMP';

alter tablespace temp add tempfile '+DATAC1' size 31g;

alter database tempfile '+DATAC1/CODS/1F7D4164EEA249A2E053374D840AD6F3/TEMPFILE/temp.6891.952352201' autoextend on;


****************************************
'Tablespace Freespace Shortage Report':|
****************************************

Temporary Tablespace Sort Usage:
===================================

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
SELECT
   A.tablespace_name tablespace,
   D.mb_total,
   SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
   D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM
   v$sort_segment A,
(
SELECT
   B.name,
   C.block_size,
   SUM (C.bytes) / 1024 / 1024 mb_total
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.mb_total
/

==============================================================================================================

Temporary Tablespace Usage:
============================

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
COL TABLESPACE_SIZE FOR 999,999,999,999
COL ALLOCATED_SPACE FOR 999,999,999,999
COL FREE_SPACE FOR 999,999,999,999
 
SELECT *
FROM   dba_temp_free_space
/

================================================================================================================================

Sessionwise temp tablespace usage
======================================


col hash_value for a40
col tablespace for a10
col username for a15
set linesize 132 pagesize 1000
 

SELECT s.sid, s.username, u.tablespace, s.sql_hash_value||'/'||u.sqlhash hash_value, u.segtype, u.contents, u.blocks
FROM gv$session s, gv$tempseg_usage u
WHERE s.saddr=u.session_addr
order by u.blocks;

====================================================================================================
COLUMN tablespace FORMAT A25
COLUMN temp_size FORMAT A20
COLUMN sid_serial FORMAT A20
COLUMN username FORMAT A20
COLUMN program FORMAT A40
SET LINESIZE 200
SELECT
a.sid||','||a.serial# AS sid_serial,b.sql_id,b.segtype,
NVL(a.username, '(oracle)') AS username,
a.program, b.tablespace,ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size
FROM gv$session a,
gv$sort_usage b,
gv$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr and b.TABLESPACE='TEMP2'
ORDER BY b.tablespace, b.blocks;


=============================================================================================================
set lines 200
 col SID_SERIAL for a20
 col USERNAME for a20
 col OSUSER for a20
 col spid for a20
 col module for a40
 col program for a40
 col tablespace for a30

SELECT s.sid
  || ','
  || s.serial# sid_serial,
  s.username,
  s.osuser,
  p.spid,
  s.module,
  s.program,
  SUM (o.blocks) * t.block_size / 1024 / 1024 mb_used,
  o.tablespace,
  COUNT(*) sorts
FROM gv$sort_usage o,
  gv$session s,
  dba_tablespaces t,
  gv$process p
WHERE o.session_addr = s.saddr
AND s.paddr          = p.addr
AND o.tablespace     = t.tablespace_name
and s.username!='SYS'
GROUP BY s.sid,
  s.serial#,
  s.username,
  s.osuser,
  p.spid,
  s.module,
  s.program,
  t.block_size,
  o.tablespace
ORDER BY sid_serial;

=====================================================================================================================

select inst_id,sid,serial#,username,machine,to_char(logon_time,'DD-MON-YYYY HH24:MI:SS'),prev_sql_id,sql_id,status,machine from gv$session where sid=2884;