SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
select tablespace_name,dbms_metadata.get_ddl(object_type => 'TABLESPACE', name=> tablespace_name) metadata from dba_tablespaces;
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
TABLESPACE_NAME METADATA
------------------------------ ----------------------------------------------------------------------------------------------------
SYSTEM
CREATE TABLESPACE "SYSTEM" DATAFILE
'/opt/app/oracle/oradata/orcl/system01.dbf' SIZE 524288000
AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT MANUAL
ALTER DATABASE DATAFILE
'/opt/app/oracle/oradata/orcl/system01.dbf' RESIZE 880803840
SYSAUX
CREATE TABLESPACE "SYSAUX" DATAFILE
'/opt/app/oracle/oradata/orcl/sysaux01.dbf' SIZE 419430400
AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
ALTER DATABASE DATAFILE
'/opt/app/oracle/oradata/orcl/sysaux01.dbf' RESIZE 870318080
TEMP
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
'/opt/app/oracle/oradata/orcl/temp01.dbf' SIZE 341835776
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576
USERS
CREATE TABLESPACE "USERS" DATAFILE
'/opt/app/oracle/oradata/orcl/users01.dbf' SIZE 5242880
AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
ALTER DATABASE DATAFILE
'/opt/app/oracle/oradata/orcl/users01.dbf' RESIZE 672399360
EXAMPLE
CREATE TABLESPACE "EXAMPLE" DATAFILE
'/opt/app/oracle/oradata/orcl/example01.dbf' SIZE 104857600
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
ANUJTEST
CREATE TABLESPACE "ANUJTEST" DATAFILE
'/opt/app/oracle/oradata/orcl/anujtest.dbf' SIZE 10485760
AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
TSAPEXF
CREATE TABLESPACE "TSAPEXF" DATAFILE
'/opt/app/oracle/oradata/orcl/tsapexf01.dbf' SIZE 52428800
AUTOEXTEND ON NEXT 10485760 MAXSIZE 314572800
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
TSAPEXU
CREATE TABLESPACE "TSAPEXU" DATAFILE
'/opt/app/oracle/oradata/orcl/tsapexu01.dbf' SIZE 52428800
AUTOEXTEND ON NEXT 10485760 MAXSIZE 314572800
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
ALTER DATABASE DATAFILE
'/opt/app/oracle/oradata/orcl/tsapexu01.dbf' RESIZE 115343360
TEST
CREATE TABLESPACE "TEST" DATAFILE
'/opt/app/oracle/oradata/orcl/test.dbf' SIZE 20971520
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
RMAN
CREATE TABLESPACE "RMAN" DATAFILE
'/opt/app/oracle/oradata/orcl/rman.dbf' SIZE 52428800
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
DROP1
CREATE TABLESPACE "DROP1" DATAFILE
'/opt/app/oracle/oradata/orcl/drop.dbf' SIZE 2097152
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
PERFSTAT
CREATE TABLESPACE "PERFSTAT" DATAFILE
'/opt/app/oracle/oradata/orcl/anuj_perfstat.dbf' SIZE 1048576000
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 524288 DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
UNDOTBR
CREATE UNDO TABLESPACE "UNDOTBR" DATAFILE
'/opt/app/oracle/oradata/orcl/undotbR.dbf' SIZE 524288000
AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
CREATE TABLESPACE tbs_anuj DATAFILE '+DATA' SIZE 1M;
alter tablespace TBS_ANUJ add datafile '+DATA' size 32767m;
FILE_ID BLOCK_SIZE TABLESPACE_NAME File Name SIZE_GB MAX_GB AUTOEXTENSIBLE BIGFILE ENCRYPTED COMPRESS_F CONTENTS STATUS
---------- ---------- --------------------------- ------------------------------------------------- ---------- ---------- ------------------ --------------- ---------- ---------- --------------------- ---------
95 8192 TBS_ANUJ +DATA/VIHCDBD8/DATAFILE/tbs_anuj.3779.1202914077 31.99 0 NO NO NO PERMANENT AVAILABLE
94 8192 TBS_ANUJ +DATA/VIHCDBD8/DATAFILE/tbs_anuj.3713.1202913527 0 0 NO NO NO PERMANENT AVAILABLE
alter database datafile '+DATA/VIHCDBD8/DATAFILE/tbs_anuj.3713.1202913527' autoextend on maxsize unlimited;
FILE_ID BLOCK_SIZE TABLESPACE_NAME File Name SIZE_GB MAX_GB AUTOEXTENSIBLE BIGFILE ENCRYPTED COMPRESS_F CONTENTS STATUS
---------- ---------- --------------------------- ------------------------------------------------- ---------- ---------- ------------------ --------------- ---------- ---------- --------------------- ---------
95 8192 TBS_ANUJ +DATA/VIHCDBD8/DATAFILE/tbs_anuj.3779.1202914077 31.99 0 NO NO NO PERMANENT AVAILABLE
94 8192 TBS_ANUJ +DATA/VIHCDBD8/DATAFILE/tbs_anuj.3713.1202913527 0 31.99 YES NO NO PERMANENT AVAILABLE
undefine tablespace_name
define tablespace_name='TBS_ANUJ'
set pages 500 lines 400 term off
col fn new_value fname
select 'a'||(max(length(FILE_NAME))+1) fn from DBA_DATA_FILES;
col file_name for a60
col AUTOEXTENSIBLE for a18
col BIGFILE for a15
col ENCRYPTED for a10
col TABLESPACE_NAME for a27
col COMPRESS_FOR for a10
col file_name for &fname heading "File Name"
SELECT FILE_ID, b.BLOCK_SIZE, a.TABLESPACE_NAME,a.FILE_NAME, trunc(a.BYTES/(1024*1024*1024),2) SIZE_GB, trunc(a.MAXBYTES/(1024*1024*1024),2) Max_GB , a.AUTOEXTENSIBLE,BIGFILE,ENCRYPTED,COMPRESS_FOR,CONTENTS,a.STATUS from DBA_DATA_FILES a ,DBA_TABLESPACES b
where 1=1
and a.TABLESPACE_NAME=b.TABLESPACE_NAME
--and a.TABLESPACE_NAME in ('ENCRYPT_256')
and a.tablespace_name = upper( decode('&&tablespace_name',null,a.tablespace_name,'&&tablespace_name'))
--and FILE_ID=31
order by 2;
FILE_ID BLOCK_SIZE TABLESPACE_NAME File Name SIZE_GB MAX_GB AUTOEXTENSIBLE BIGFILE ENCRYPTED COMPRESS_F CONTENTS STATUS
---------- ---------- --------------------------- ------------------------------------------------- ---------- ---------- ------------------ --------------- ---------- ---------- --------------------- ---------
94 8192 TBS_ANUJ +DATA/VIHCDBD8/DATAFILE/tbs_anuj.3713.1202913527 0 0 NO NO NO PERMANENT AVAILABLE
