Avatar of skahlert2010
skahlert2010
 asked on

Drop selected objects (demo tables) from user_tables as sys

Dear experts and dbas,
I discovered that there is a vast number of users in our Oracle instance, that have the oracle demo tables (demo_customers, emp, salgrade etc) installed.

Since it takes a while to delete them all manually for each user, I'd like to know how I can drop these tables and correlated objects such as triggers, indizes and views at once.

Do you have a script to select a number of tables and delete all associated objects for these tables?

Please be so kind and share your knowledge with me!

Many thanks,

Sebastian
Oracle Database

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
kevb258

DROP USER user CASCADE;

that normally does the job for me
skahlert2010

ASKER
Thanks kevb258!

Well, I don't want to delete the user scheme itself! In fact I just want to delete the demo tables within all user schemes. I need to be able to specify the tables to delete in order to keep the custom user tables!

Any other ideas?

Brgds!

ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
skahlert2010

ASKER
Hello slightwv!

Thanks for your comment! I intend to delete only tables like "DEMO_CUSTOMERS", which I am sure is an Oracle demo table. I have checked on the dba_dependencies and dba_constraints views.

My final question is if correlated constraints, triggers are also deleted when I drop the tables via dba_tables using the CASCADE option! Do you have any experience with this? Or do I need to delete these objects all manually?

Why am I asking these questions? For curiosity! I am just looking for the most efficient way to delete any objects related to demo_tables in all my schemes.

Thanks for your help!

Regards,

Sebastian
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
slightwv (䄆 Netminder)

>>DEMO_CUSTOMERS", which I am sure is an Oracle demo table.

Not one I've ever heard of but Oracle has so many 'products'.  They just don't typically name sample tables 'DEMO'

>>Or do I need to delete these objects all manually?

Looks like triggers are automatically deleted as well even without cascade.

I have to verify this myself so I created a simple test.

You can expand this test to include any object types in question if needed.
drop table tab1 purge;
create table tab1 (col1 char(1));

create or replace trigger tab1_trig
before insert on tab1
for each row
begin
	null;
end;
/

show errors

select object_type from user_objects where object_name='TAB1_TRIG';

drop table tab1;

select object_type from user_objects where object_name='TAB1_TRIG';

Open in new window

skahlert2010

ASKER
Hello slightwv!

I just tested if triggers are deleted simultaneously with the table itself and can confirm it works!
However, the only objects that seem to be persistent are sequences. They are not deleted when the table is dropped and need to be deleted manually. The only difficulty is to find out,w hich respective sequence belongs to which table if names seq_names. If you have an idea, please don't hesitate to tell me!

I will close the question know and award you the points for your good help!

Thank you!
slightwv (䄆 Netminder)

The problem is sequences are not tied to tables at the database layer.  They are set up and used at the application layer.  They are independent objects.

If your shop set them up one-to-one then hopefully the sequence names are tied to table names in some way.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.