Oracle Clone Tables and ALL TABLE OBJECTS (synonyms, grants, tablespace, indexspace...) into ddl Script containing a certain prefix in table name

Is there a way in Oracle to clone specific table objects ddl to a script. Not individual tables one at a time,  but specific tables that begin with a particular prefix all at one time?
iBincAsked:
Who is Participating?
 
Geert GOracle dbaCommented:
unless she was given a specific grant on dba_objects

if Toad gives an error then you are missing grants

i usually give select_catalog_role to all devs
otherwise i get a lot of work

there is no point in limiting a dev person in seeing the structure of the system
ask your dba to give your users the select_catalog_role
from inside a package ... it's still a little awkward ... i give select any table to solve that on test databases

to generate a list of items ... in the compare schemas, use a filter to select your items and then compare to an empty schema
0
 
slightwv (䄆 Netminder) Commented:
Use sqlplus or SQL Developer
set lines 500
set pages 0
set feedback off
spool my_ddl.sql
select dbms_metadata.get_ddl('TABLE',table_name)
from user_tables where table_name like 'MY_TAB%'
/
spool off

Open in new window


If you have a different tool, the SELECT should still work but you might need to alter how to capture the output.
0
 
slightwv (䄆 Netminder) Commented:
I suppose I should clarify that the script above will only get the tables themselves.

Are you wanting all related objects like indexes, etc???
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
iBincAuthor Commented:
yes, all related objects.... I'm using TOAD. Need full ddl for each table for indexes, tablespace, roles, synonym creations.
0
 
slightwv (䄆 Netminder) Commented:
So you are really asking how to generate an entire create script for only specific tables and ALL related objects?

What if there is a foreign key for another table that doesn't match your prefix?

Not sure how tablespaces,  roles and synonyms fall into your requirement for TABLES that have a certain prefix.

Can you clarify your actual requirements?
0
 
iBincAuthor Commented:
There will be no FK issues to other tables. I need ddl for all objects for all tables that begin with a certain prefix. That's it! There is nothing more to explain.
0
 
iBincAuthor Commented:
I clarified the question...
0
 
slightwv (䄆 Netminder) Commented:
I'm sorry but I don't understand what you want as it relates to tablespaces, roles and synonyms or ALL OBJECTS.

Tables and indexes are pretty easy using the SQL I posted above.  I would need to add the INDEX version if you want it.

I don't use Toad but from a quick Google there appears to be an Export DDL menu option.  That might be more flexible and give you what you want.
0
 
iBincAuthor Commented:
As it turns out, I do not have privileges to run dbms_metadata so that is not an option.
0
 
slightwv (䄆 Netminder) Commented:
You might also look at using expdp/impdp for this.  I've not done it to see what all it will do but the docs state it will also export dependent objects and allow wildcards:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/oracle-data-pump-export-utility.html#GUID-9D052182-6BD8-4167-B528-2E352C9CDBDB

Then you use impdp with the sqlfile parameters:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/datapump-import-utility.html#GUID-8453D1F2-FDA9-4658-B42E-9D5B75AFEED9
0
 
slightwv (䄆 Netminder) Commented:
>>As it turns out, I do not have privileges to run dbms_metadata so that is not an option.

Then forget about the export/import option.  That requires more privs than DBMS_METADATA.

I would ask for privs on DBMS_METADATA.  If Toad's Export doesn't work then you will likely have to write a LOT of code or find a script pre-written on the web.
0
 
iBincAuthor Commented:
I would ask for privs on DBMS_METADATA.  If Toad's Export doesn't work then you will likely have to write a LOT of code or find a script pre-written on the web.

Yeah that's not going to happen.

I found a way to do this in Toad...
0
 
iBincAuthor Commented:
For anyone interested, Toad generated the sql to do what I wanted using sys.DBA_OBJECTS.
0
 
iBincAuthor Commented:
clarifying...

dba_objects and dba_snapshots
0
 
slightwv (䄆 Netminder) Commented:
You can query DBA_OBJECTS and not DBMS_METADATA?  DBA_OBJECTS requires DBA level access which gives you access to just about everything.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.