Hi Masters,
I have 80 to 90 tmp tables in my schema. I wanted to delete all records from all tmp tables with one shot. Either pl/sql block or delete stmt? tmp means not GTTs. Just normal tables for temporary insert/delete purpose users created normal tables. But there is no primary keys, foreignkey references, common columns.
I wanted to delete all rows from all tmp tables ... Need your advise. It's very nice of you.
create table a_tmp(sno number(3));
create table b_tmp(no number(2));
create table c_tmp(n number(2));
insert all
into a_tmp values(1)
into a_tmp values(1)
into a_tmp values(1)
into b_tmp values(2)
into b_tmp values(2)
into b_tmp values(2)
into c_tmp values(3)
into c_tmp values(3)
into c_tmp values(3)
select * from dual;
SELECT * FROM A_TMP;
select * from user_tables where table_name like '%TMP%';
DELETE FROM user_tables where table_name like '%TMP%'; -- here I got error message Ora-02030 -- Can only select from fixed tables/views.
understood that we can't delete from user_tabels.
Is there any way? Please advise...
Regards
Ar