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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
iBincAuthor Commented:
yes, all related objects.... I'm using TOAD. Need full ddl for each table for indexes, tablespace, roles, synonym creations.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.