Tablespaces in oracle database | Tablespaces script

In this blog, I will explain the script of tablespace to check the utilization. Tablespace is the collection of datafiles. Oracle store the data physically in datafiles and store the data logically in tablespace.

Script for Tablespace current utilization

set linesize 1234 pages 1234
 col tablespace format a24
 col free heading 'Free(Mb)' format 99999999.9
 col total heading 'Total(Mb)' format 999999999.9
 col used heading 'Used(Mb)' format 99999999.9
 col pct_free heading 'Pct|Free' format 99999.9
 col largest heading 'Largest(Mb)' format 99999.9
 compute sum of total on report
 compute sum of free on report
 compute sum of used on report
 break on report
 select substr(a.tablespace_name,1,24) tablespace,
 round(sum(a.total1)/1024/1024, 1) Total,
 round(sum(a.total1)/1024/1024, 1)-round(sum(a.sum1)/1024/1024, 1) used,
 round(sum(a.sum1)/1024/1024, 1) free,
 round(sum(a.sum1)/1024/1024, 1)*100/round(sum(a.total1)/1024/1024, 1) pct_free,
 round(sum(a.maxb)/1024/1024, 1) largest,
 max(a.cnt) fragments
 from
 (select tablespace_name, 0 total1, sum(bytes) sum1,
 max(bytes) MAXB,
 count(bytes) cnt
 from dba_free_space
 group by tablespace_name
 union
 select tablespace_name, sum(bytes) total1, 0, 0, 0 from dba_data_files
 group by tablespace_name
 union
 select tablespace_name, sum(bytes) total1, 0, 0, 0 from dba_temp_files
 group by tablespace_name) a
 group by a.tablespace_name
 /

Script to check datafiles assigned to tablespace.

col file_name for a50;
select file_name,tablespace_name,bytes/1024/1024,status from dba_data_files where tablespace_name='&TABLESPACE_NAME';

Script to check tablespace utilization for specific tablespace.

select*from dba_tablespace_usage_metrics where tablespace_name='&TABLESPACE_NAME';

Script to add 1000000 record in table 

BEGIN
FOR id IN 1..1000000 loop INSERT INTO C##TESTUSER.testtable(id) VALUES(id); END loop; END; /

Script to add datafiles in tablespace.

alter tablespace PSAPB31 add datafile '/oracle/B31/sapdata18/b31_214/b31.data214.dbf' size 30720M;

Script to resize datafiles.

ALTER DATABASE datafile '/oracle/NXD/sapdata10/sr3db_3/sr3db.data3' resize 15000M;

Script to Create tablespace

CREATE TABLESPACE tbspdb DATAFILE '/data/oracle/app/oracle/oradata/cdb/tbs_perm_pdb.dat'   SIZE 10M ;

Script to check temp tablespace utilization

select file#, name, round(bytes/(1024*1024),2) "Temp file SIZE IN MB's" from v$tempfile;

Script to add tempfile space in temp tablespace.

alter tablespace TEMP add tempfile '/home/oracle/app/oracle/oradata/testdb/temp02.dbf' size 10M;

Auto Extend DATAFILE

select TABLESPACE_NAME, FILE_NAME,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 from dba_Data_files;

Filename Tablespace Current Size (MB) Aut Autoextend Size (MB) Max Size (MB)
-------------------------------------------------- ------------------------------ ----------------- --- -------------------- -------------
/data/oracle/app/oradata/prim/system01.dbf SYSTEM 820 YES 10 32767.98
/data/oracle/app/oradata/prim/sysaux01.dbf SYSAUX 600 YES 10 32767.98
/data/oracle/app/oradata/prim/undotbs01.dbf UNDOTBS1 70 YES 5 32767.98
/data/oracle/app/oradata/prim/users01.dbf USERS 5 YES 1.25 32767.98
/data/oracle/app/oradata/prim/tbs_perm_cdb.dat TBSCDB 10 NO 0 0


Alter datafile auto extend on

alter database datafile
'/data/app/oracle/ts_32k.dbf'
autoextend on
maxsize unlimited;


Disable autoextend of datafile

alter database datafile '/data/oracle/app/oracle/oradata/prim/pdbprim/users01.dbf' autoextend off;


Create tabelspace with Autoextend on

CREATE TABLESPACE tbs_perm_02
DATAFILE 'tbs_perm_02.dat'
SIZE 10M
REUSE
AUTOEXTEND ON NEXT 10M MAXSIZE 200M;


Create Big file tablespace

CREATE BIGFILE TABLESPACE BIGTBS1 DATAFILE '/data/app/oracle/oradata/bigtbs01.dbf' SIZE 10M;

CREATE BIGFILE TABLESPACE BIGTBS2 DATAFILE '/data/app/oracle/oradata/bigtbs02.dbf' SIZE 10M
AUTOEXTEND ON NEXT 20M MAXSIZE 100G;

 

Thanks for visiting this post. Please contact us if you are looking for training.
Subscribe to our channel for unlimited learning. Thank you!!!

Share and Enjoy !

0Shares
0 0 0

Recommended For You

ankushthavali

About the Author: ankushthavali

Leave a Reply

Your email address will not be published. Required fields are marked *