Link to home
Create AccountLog in
Avatar of Yehuda Tsadok
Yehuda TsadokFlag for Israel

asked on

In oracle: How do I truncate all data from all user tables regardless if they are clustered and so forth?

Hi
First, sorry for the newbie question.

I want to truncate all data from all of my user tables.
I've ran the script (script 1) and it failed because I have clustered tables.

So I've also ran script (script 2) and it also failed..

As I said, I need to truncate all data from all user tables, regardless if they are clustered, sequenced or indexed, etc.


Script 1:
begin
   for c1 in (select table_name from user_tables) loop
         v_new_tab_name := '"' || C1.Table_Name|| '"';
         execute immediate (' truncate table '||v_new_tab_name) ;
   end loop;
end;
 
Script 2:
 
begin
   for c1 in (select table_name from user_clusters) loop
         v_new_tab_name := '"' || C1.Cluster_Name|| '"';
         execute immediate (' truncate cluster '||v_new_tab_name) ;
   end loop;
end;
 
 
/

Open in new window

Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

can you post the errors which your script gave when you ran them so that we can understand what error you are getting ?
Avatar of Yehuda Tsadok

ASKER

I forgot to mention why I want to truncate tables. The purpose is that I want to import a backup using the imp.exe
ASKER CERTIFIED SOLUTION
Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I dont have access to the server until sunday so I cant post the error now. What I need is a way to truncate all tables with a script so I can import a backup.
there are other updates which are already posted...please see them as well....
As i said I'm newbie to oracle so I apologize again. If dropping the tables will allow me to import, can you should I just change my script to drop instead of truncate if not, can you provide a script?

Thanks
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
1. If I drop and recreate the user, will it drop all associated tables even if they have forigen key relationship?
2. What is the best practice for dropping tables/etc in order to do an import?
3. My database has many tables - I am not fimiliar with them can you provide a script that will drop all tables and if needed the cascade constraints for all tables which have them?

Thanks
So if I do

begin
   for c1 in (select table_name from user_tables) loop
         v_new_tab_name := '"' || C1.Table_Name|| '"';
         execute immediate (' drop table '||v_new_tab_name ||' CASCADE CONSTRAINTS') ;
   end loop;
end;
\

will that work for all tables ?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks all