Here is the problem I'm running into. I need to resize the tablespaces because the ddl I generate from the production database will have very large sizes for the development environment. I have a smaller set of file systems in dev. I tried changing the size from the output generated from the ddl package and get this:

1 CREATE TABLESPACE "INDX" DATAFILE
2 '/uvxdata/oracle104/info/indx01.dbf' SIZE 26214400*.2 REUSE
3 AUTOEXTEND ON NEXT 1310720*.2 MAXSIZE UNLIMITED
4 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
5* EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
INFO:IDPRP> /
'/uvxdata/oracle104/info/indx01.dbf' SIZE 26214400*.2 REUSE
*
ERROR at line 2:
ORA-02180: invalid option for CREATE TABLESPACE

Then I tried to generate my own little script using the dba_tablespaces table but I was not able to find the uniform extent value for those tablespaces that have uniform extents. Where is it located?

Thanks

-----Original Message-----
From: MacGregor, Ian A. [mailto:[Email Address Removed] Tuesday, April 20, 2004 11:01 AM
To: LazyDBA.com Discussion
Subject: RE: dbms_metadata.get_ddl limitation


Why not capture the DDL for all tables, then for all unique and primary key constraints, then for all indexes not associated with PK's or UK's.
set linesize 100
set pagesize 0
set long 6000
set feeedback off
set termout off
column ddl_text format a99 word_wrapped
spool tables.sql
select
'select dbms_metadata.get_ddl(''TABLE'', '''||TABLE_NAME||''',''IEPM'')
from dual;'
FROM DBA_TABLES
/
spool off
SPOOL pksanduks.sql
select
'select dbms_metadata.get_ddl(''CONSTRAINT'',''' ||CONSTRAINT_NAME||''',''IEPM'') ddl_text FROM DUAL;'
FROM DBA_CONSTRAINTS
Where CONSTRAINT_TYPE IN ('P', 'U')
/
spool off
spool nonpk_and_nonuk_indexes.sql
select
'select dbms_metadata.get_ddl(''INDEX'', '''||INDEX_NAME||''',''IEPM'') FROM DUAL;'
FROM DBA_INDEXES i,
(SELECT OWNER, CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE
CONSTRAINT_TYPE IN ('P', 'U')) C
where I.OWNER = C.OWNER(+)
AND I.INDEX_NAME = C.CONSTRAINT_NAME(+)
AND C.OWNER IS NULL
AND C.CONSTRAINT_NAME IS NULL
/
spool off
spool create_objects.sql
@tables.sql
@pksanduks.sql
@non_pk_and_nonuk_indexes.sql
spool off
exit

You may want to exclude sys, system ctxsys etc. Also the above script doesn't produce an executable file because the
Create statements lack a ';' or a '/'. The above was changed from a script to get a user's ddl. It has not been tested as a way to get all "tablespace" ddl. The above may be incomplete, it was complete for the user from which the above was adapted. However, it should be pretty simple to add sections.

Ian MacGregor
Stanford Linear Accelerator Center
[Email Address Removed] today's cartoon: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: send a blank email to oracledba-[Email Address Removed] subscribe: send a blank email to oracledba-[Email Address Removed] using this list you agree to these terms:http://www.lazydba.com/legal.html

Oracle LazyDBA home page