Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.
Course Of The Month
11 days, 13 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Drop selected objects (demo tables) from user_tables as sys
Posted on 2011-03-08
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3-
3
7 Comments
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!
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!
Active today
You can easily write a script to generate the necessary drop commands. I would caution you before you do this: Automatically dropping a tables based on name can be a bad thing. What if I have a table called EMP but it is NOT the table you think it is...
You can generate the list of tables with:
select 'drop table ' || owner || '.' || table_name || ';' from dba_tables where table_name in ('DEMO_CUSTOMERS','EMP');
If all the referenced objects don't use dynamic SQL you can get a list of them from DBA_DEPENDENCIES.
Indexes should go away when you drop the tables.
Just as a double check: look for invalid objects before and after the drops. Anything new that is invalid should be manually checked.
You can generate the list of tables with:
select 'drop table ' || owner || '.' || table_name || ';' from dba_tables where table_name in ('DEMO_CUSTOMERS','EMP');
If all the referenced objects don't use dynamic SQL you can get a list of them from DBA_DEPENDENCIES.
Indexes should go away when you drop the tables.
Just as a double check: look for invalid objects before and after the drops. Anything new that is invalid should be manually checked.
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
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
Active today
>>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.
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';
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!
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!
Featured Post
For Any Use & On Any Platform
Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Introduction
A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle
Before we begin creating these things, what are user-defined aggregates? They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month11 days, 13 hours left to enroll
Earn Certification
Certified Information Systems Auditor (CISA)
$99.00$89.10
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.