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
3 comments:
select 'create tablespace '||tablespace_name ||' datafile ''+DATA'' size 1G autoextend on ;' from dba_tablespaces where tablespace_name like 'ANUJ%';
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
set heading off echo off pages 1000 long 100000
select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;
Tablespace report
https://anuj-singh.blogspot.com/2011/10/oracle-space-used-in-tablespace.html
Post a Comment