Posts Tagged ‘Tabtype’

Oracle: Delete all rows from all tables etc…

Quick method to do multiple actions across multiple tables.

Simply spool a script with your commands in for each table – this way you can avoid dropping your tables or user and still keep all your schema structure.

So for instance to delete all data from all tables you could do the following:

set heading off;
set feedback off;
spool C:my_file_name.sql
select 'delete from ' || tname ||';' from tab where tabtype = 'TABLE';
spool off;

This will produce an SQL file with a series of delete commands for each table and then you can just run this script which will delete all rows in all tables. The || is the shortcut for the concatenation function in Oracle and this basically works the same as a select script using Dual.

Tab like user_tables refers to a system table that describes the users tables in the schema. Tab has only a few columns and I’ve used this because it has the type of table as a column (just in case). You can see this for yourself by doing:

select * from tab;

And if you do:

select * from user_tables;

You’ll see the difference, user_tables contains alot more data about each table.

You can change the SQL command to ‘truncate table’ or whatever else you want – even drop all tables by querying user_tables instead of tab:

set heading off;
set feedback off;
spool C:my_file_name.sql
select 'drop table ' || table_name || 'cascade constraints;' from user_tables;
spool off;

Using this method you can do the same action across multiple tables and save it for later use.