Oracle Errors and Solutions
05:13:00
Error
,
Errors
,
Forms Errors
,
Oracle Errors
,
Oracle Errors.SQL error
,
Pl/SQL Errors
,
SQL error
Edit
Hello There,
ReplyDeleteYour writing shines! There is no room for gibberish here clearly you have explained about Oracle Errors and Solutions. Keep writing!
I have a number of tables with partitions by month
My first script where I input the actual table works fine - as in it will drop the partitions which meet the criteria (see below)
Code (SQL):
DECLARE
v_sql varchar2(500);
v_date DATE;
v_partition_name user_tab_partitions.partition_name%TYPE;
v_high_value user_tab_partitions.high_value%TYPE;
CURSOR c1 IS SELECT PARTITION_NAME, HIGH_VALUE
FROM user_tab_partitions WHERE TABLE_NAME='TEST_YFS_BAT_LOC_H' AND PARTITION_NAME!='P0';
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_partition_name, v_high_value;
EXIT WHEN c1%NOTFOUND;
v_date := to_date(SUBSTR(v_high_value,11,19),'YYYY-MM-DD HH24:MI:SS');
IF v_date < (sysdate - 30) THEN
v_sql := 'alter table TEST_YFS_BAT_LOC_H drop partition ' || v_partition_name;
EXECUTE immediate v_sql;
END IF;
END LOOP;
CLOSE c1;
END;
/
But I want to be able to drop all partitions from all tables belonging to this schema. So how can I do something similar which goes through all the tables and partitions and does the same thing?
I am executing this script as the schema owner with 100+ tables
But great job man, do keep posted with the new updates.
Kind Regards,
preethi
Hey,
ReplyDeleteI learnt so much in such little time about Oracle Errors and Solutions. Even a toddler could become smart reading of your amazing articles.
I have requirement like
select COL1,Col2 from A
minus
Select COL1,COL2 from B;
both tables have 50+ millions record so I want to automate a job which will execute this query for 1millions records for one time and then again 1 millions for second time...like loop and store output in record type.
But nice Article Mate! Great Information! Keep up the good work!
Kind Regards,
Radhey