- 07 Jun, 2019
- 0 Comments
- 2 Mins Read
Tablespaces in oracle database | Tablespaces script
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 /
col file_name for a50; select file_name,tablespace_name,bytes/1024/1024,status from dba_data_files where tablespace_name='&TABLESPACE_NAME';
BEGIN FOR id IN 1..10000 loop INSERT INTO SYS.testtable(id,name) VALUES(id,'dba'); 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;
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 AUTOEXTEND ON NEXT 20M MAXSIZE 100G;
Check BigFile tablespace and Small File tablespace
SQL> select TABLESPACE_NAME, BIGFILE from DBA_TABLESPACES; TABLESPACE_NAME BIG ------------------------------ --- SYSTEM NO SYSAUX NO UNDOTBS1 NO TEMP NO USERS NO BIGTBS1 YES
select name,total_mb,free_mb, (free_mb/total_mb)*100 "%Free" from v$asm_diskgroup;