With hundreds of tables in the database, use stored procedures to empty table data script, do have a clear table data table, don't delete table

CodePudding user response:

Don't need to use stored procedures, directly in user_tables view spell delete a script, such as:
Select "truncate table '| | table_name | |'; 'the from user_tables
Perform the query can be generated script
Because you mentioned the "database", so if you want to clear the list of more than one user, so you may need to check more dba_tables, but there is a problem, need to pay attention to your best list need to clear the list of users, such as:
Select "truncate table '| | owner | |'. '| | table_name | |'; 'the from dba_tables where the owner in (user list);

CodePudding user response:

If use the stored procedure's purpose is to use multiple calls, you can use in the process of using dynamic SQL to perform these spell truncate statement,

CodePudding user response:

Three consecutive ~
Helpful hints: if there are exceptions, such as some don't want to delete the table, in the use of data dictionary user_tables or dba_tables, remember to use proper filter conditions will they sent,

CodePudding user response:

The CREATE OR REPLACE PROCEDURE Prc_Clear_Data IS
V_Sql VARCHAR2 (500);
The BEGIN
- the cursor, the current user query user_tables, all user query all_tables
LOOP FOR Rec IN (SELECT Table_Name FROM User_Tables)
- assembled SQL
V_Sql:='truncate table' | | Rec. Table_Name;

The immediate execution
-The EXECUTE IMMEDIATE v_Sql;
END LOOP;
END Prc_Clear_Data;

CodePudding user response:

Several upstairs, said very detailed,

CodePudding user response:

Keep table, data cleaning, use truncate command