& DBA_TABLESPACES
& V$TABLESPACE
& DBA_DATA_FILES
& V$DATAFILE
& DBA_FREE_SPACE
[oracle@dbserver ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 -
Production on Mon Jan 29 23:25:42 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019,
Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise
Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set
container=maxpdb;
Session altered.
q CREATE A NEW TABLESPACE
v SQL> create tablespace veridos datafile '/u01/app/oracle/oradata/MAXCDB/maxpdb/veridos01.dbf'
size 50m autoextend on;
Tablespace
created.
q ADD NEW DATAFILE IN TABLESPACE
v SQL> alter tablespace veridos add datafile
'/u01/app/oracle/oradata/MAXCDB/maxpdb/veridos02.dbf' size 50m;
Tablespace
altered.
q DROP TABLESPACE
v SQL> drop tablespace veridos including contents and datafiles;
Tablespace dropped.
q TABLESPACE RENAME
v SQL> alter tablespace veridos rename to epp;
Tablespace altered.
q DATA FILE RESIZE
v SQL> alter database datafile
'/u01/app/oracle/oradata/ORCL/orclpdb/tb2.dbf' resize 200m;
Database altered.
q RELOCATE DATAFILE
v SQL> alter database move datafile '/u01/app/oracle/oradata/ORCL/orclpdb/tb2.dbf'
to '/u01/tb2.dbf';
Database altered.
q ADD ORPHAN DATAFILE TO TABLESPACE
v SQL> create tablespace tbs2 datafile
'/u01/app/oracle/oradata/ORCL/orclpdb/test01.dbf' reuse;
Tablespace created.
v ALTER TABLESPACE tbs2 ADD DATAFILE '/u01/app/oracle/oradata/ORCL/orclpdb/test02.dbf ' REUSE;
Tablespace
created.
q CREATE A BIG FILE TABLESPACE
v SQL> create bigfile tablespace bigtbs datafile
'/u01/app/oracle/oradata/ORCL/orclpdb/bigtbs01.dbf' size 100m autoextend on;
q NOLOG TABLESPACE
v SQL> create tablespace nologtbs datafile
'/u01/app/oracle/oradata/ORCL/orclpdb/nologtbs01.dbf' size 100m autoextend on
nologging;
q NON-STANDARD BLOCK TABLESPACE
v SQL> alter systems set db_16k_cache_size=50m scope=both;
v SQL> create tablespce nonstd datafile
'/u01/app/oracle/oradata/ORCL/orclpdb/nologtbs01.dbf' size 100m autoextend on
blocksize 16k;
q CREATE TEMPORARY TABLESPACE
v SQL> create temporary tablespace temptbs tempfile
'/u01/app/oracle/oradata/ORCL/orclpdb/temp01.dbf' size 100m autoextend on;
q TEMPORARY TABLESPACE GROUP
v DBA_TABLESPACE_GROUPS
v SQL> create temporary tablespace temp2 tempfile
'/u01/app/oracle/oradata/ORCL/orclpdb/temp01.dbf' size 100m tablespce ts_group;
v SQL> alter tablespace temp1 tablespace group ts_group;
q DATABASE_PROPERTIES [TO
CHECK DEFAULT TEMPORARY TABLESPACE]
v SQL> alter database default temporary tablespace ts_group;
q CREATE UNDO TABLESPACE
v SQL> create undo tablespace undotbs datafile
'/u01/app/oracle/oradata/ORCL/undotbs01.dbf' size 100m autoextend on next 10m;
r
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';
r Script to check temp tablespace utilization
select file#, name,
round(bytes/(1024*1024),2) "Temp file SIZE IN MB's" from v$tempfile;
r Auto Extend DATAFILE
select tablespace_name, file_name,autoextensible,maxbytes/1024/1024/1024
from dba_data_files;
r Check BigFile tablespace and Small File
tablespace
select TABLESPACE_NAME, BIGFILE
from DBA_TABLESPACES;
select name,total_mb,free_mb,
(free_mb/total_mb)*100 "%Free" from v$asm_diskgroup;