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.
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;
/
Oracle Database
Last Comment
Yehuda Tsadok
can you post the errors which your script gave when you ran them so that we can understand what error you are getting ?
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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....
ASKER
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
Thanks
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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
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
ASKER
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 ?
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks all