Database Administration (MOSC)

MOSC Banner

Gen DDL of tablespaces without using dbms_metadata.get_ddl

edited Aug 19, 2017 5:01AM in Database Administration (MOSC) 4 commentsAnswered

I don't want to use dbms_metadata.get_ddl as it doesn't give me option to change the original config

I am writing the following but don't know how to loop all tablespaces, and idea ?

select * from
(
(select 'Create tablespace ' || tablespace_name || ' ' || LOGGING ||' datafile ' from dba_tablespaces where tablespace_name='USERS')
union all
(select '''' || file_name || ''' size 100M AUTOEXTEND ' || DECODE(AUTOEXTENSIBLE,'YES','ON','NO','OFF') || ' NEXT 1M MAXSIZE ' || MAXBYTES/1024/1024 || 'M' from dba_data_files where tablespace_name='USERS')
)
union all
(select 'EXTENT MANAGEMENT ' || EXTENT_MANAGEMENT || ' SEGMENT SPACE MANAGEMENT '|| SEGMENT_SPACE_MANAGEMENT || ' ' || ALLOCATION_TYPE || ';' from dba_tablespaces where tablespace_name='USERS')

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center