Home » SQL & PL/SQL » SQL & PL/SQL » how to delete all data in all tables in one time ?!
icon5.gif  how to delete all data in all tables in one time ?! [message #304659] Thu, 06 March 2008 01:08 Go to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

  1  CREATE OR REPLACE PROCEDURE delete_all_data
  2  IS
  3     v_statement   VARCHAR2 (200);
  4  BEGIN
  5     FOR i IN (SELECT *
  6                 FROM user_tables)
  7     LOOP
  8        v_statement :=
  9                  'delete table ' || i.table_name ;
 10        EXECUTE IMMEDIATE v_statement;
 11     END LOOP;
 12  commit;
 13* END;
SQL> /

Procedure created.

SQL> exec delete_all_data
BEGIN delete_all_data; END;

*
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at "DE2.DELETE_ALL_DATA", line 10
ORA-06512: at line 1


I made the previous code , but it's didn't work with me .... any help for this problem please ?!

I'm just reminder ..... all what I need ,that delete all data in all tables in one time only .

I'm waiting for the answer ..... and thanks in advance
Re: how to delete all data in all tables in one time ?! [message #304660 is a reply to message #304659] Thu, 06 March 2008 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Try "select table_name from user_tables" and you will see you have tables with non standard name.

Regards
Michel

[Updated on: Thu, 06 March 2008 01:24]

Report message to a moderator

Re: how to delete all data in all tables in one time ?! [message #304664 is a reply to message #304659] Thu, 06 March 2008 01:17 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
you could also create a script file and then run that script file.

regards,
Re: how to delete all data in all tables in one time ?! [message #304669 is a reply to message #304664] Thu, 06 March 2008 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
you could also create a script file and then run that script file.

Which will lead to the same error.

Regards
Michel

[Updated on: Thu, 06 March 2008 01:25]

Report message to a moderator

Re: how to delete all data in all tables in one time ?! [message #304672 is a reply to message #304660] Thu, 06 March 2008 01:37 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Are you sure ?

SQL> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
TEST_OBJECT                    TABLE
TEST_ROLES                     TABLE
TEST_USER                      TABLE

SQL> delete table test_object;
delete table test_object
       *
ERROR at line 1:
ORA-00903: invalid table name

SQL> delete from test_object;

68000 rows deleted.

SQL> rollback;

Rollback complete.

SQL> delete test_object;

68000 rows deleted.



To be honest I thought for a while and said hang on it doesn't look like a right syntax.

Regards

Raj
Re: how to delete all data in all tables in one time ?! [message #304673 is a reply to message #304659] Thu, 06 March 2008 01:37 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
What happens if you execute your dynamic statement in sqlplus?
"delete table <table_name>" will ALWAYS return a "Invalid table name" error, because you have the basic syntax wrong!

[Edit: Raj spotted the same]

[Updated on: Thu, 06 March 2008 01:38]

Report message to a moderator

Re: how to delete all data in all tables in one time ?! [message #304674 is a reply to message #304659] Thu, 06 March 2008 01:38 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
For your question I wouldn't be using delete if you are sure you want to empty the table then use truncate because .....

Read the sql reference manual or search in this site for delete vs truncate, you will understand why ?

Regards

Raj

[Updated on: Thu, 06 March 2008 01:39]

Report message to a moderator

Re: how to delete all data in all tables in one time ?! [message #304676 is a reply to message #304672] Thu, 06 March 2008 01:41 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
infact even i overlooked OP's syntax.i just wanted to tell that instead of a PROC he could created a script to accomplish the task.

regards,
Re: how to delete all data in all tables in one time ?! [message #304679 is a reply to message #304672] Thu, 06 March 2008 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
delete table

Good grief! Confused I didn't even see that. Too must confident on posted basic statements.

Regards
Michel
icon1.gif  sorry for syntax error [message #304704 is a reply to message #304659] Thu, 06 March 2008 05:25 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

OKay , that's right .... I'm sorry .. I forget the true syntax in first post ........ OK but now there is a new problem :-

SQL> CREATE OR REPLACE PROCEDURE delete_all_data
2 IS
3 v_statement VARCHAR2 (200);
4 BEGIN
5 FOR i IN (SELECT *
6 FROM user_tables)
7 LOOP
8 v_statement :=
9 'delete ' || i.table_name ;
10 EXECUTE IMMEDIATE v_statement;
11 END LOOP;
12 commit;
13 END;
14 /

Procedure created.

SQL> exec delete_all_data
BEGIN delete_all_data; END;

*
ERROR at line 1:
ORA-02292: integrity constraint (DE2.EMP_DEPT_FK) violated - child record found
ORA-06512: at "DE2.DELETE_ALL_DATA", line 10
ORA-06512: at line 1


how can I avoid the constraints
that related between tables ( primary keys , foreign keys )
until can delete all data in one step ?!
Re: how to delete all data in all tables in one time ?! [message #304705 is a reply to message #304659] Thu, 06 March 2008 05:31 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
You have plenty of possible solutions, depending on what you want/can do or can't do:



  • Disable all constraints before starting
  • Generate a dependency tree of your tables based upon USER_CONSTRAINTS and process the tables bottom-up from that tree
  • other suggestions ...

Re: sorry for syntax error [message #304706 is a reply to message #304704] Thu, 06 March 2008 05:31 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You can't "avoid" constraints; you could, though, disable them.
Re: sorry for syntax error [message #304748 is a reply to message #304704] Thu, 06 March 2008 08:23 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Please, please stop using OraFAQ as your first option to turn to whenever you run into a problem.
The docs and Google should really be higher on your list. I am pretty sure that there are plenty of relevant hits to be found on this error.
Remember, we want to help you, but we won't do your work for you or solve your problems for you. You will have to do that yourself in the end.
Previous Topic: [HELP] SQL complex query
Next Topic: Basic Sql statement need advice on
Goto Forum:
  


Current Time: Fri Feb 07 15:45:00 CST 2025