88 lines
2.4 KiB
SQL
88 lines
2.4 KiB
SQL
WHENEVER SQLERROR EXIT SQL.SQLCODE
|
|
|
|
-- Disable statspack job.
|
|
BEGIN
|
|
FOR i in (select job from user_jobs where lower(what) like '%snap%' )
|
|
LOOP
|
|
sys.dbms_job.broken(i.job,TRUE);
|
|
END LOOP;
|
|
COMMIT;
|
|
END;
|
|
/
|
|
|
|
-- Export Statspack schema
|
|
host expdp directory=export_dump schemas=PERFSTAT dumpfile=PERFSTAT.dmp logfile=PERFSTAT.log
|
|
|
|
-- Disable all constraints
|
|
BEGIN
|
|
FOR i in (select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME
|
|
|| ' MODIFY CONSTRAINT ' || CONSTRAINT_NAME
|
|
|| ' DISABLE' sqltext
|
|
from dba_constraints
|
|
where owner = 'PERFSTAT'
|
|
and status <> 'DISABLED'
|
|
and constraint_type = 'R')
|
|
LOOP
|
|
execute immediate i.sqltext;
|
|
END LOOP;
|
|
FOR i in (select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME
|
|
|| ' MODIFY CONSTRAINT ' || CONSTRAINT_NAME
|
|
|| ' DISABLE' sqltext
|
|
from dba_constraints
|
|
where owner = 'PERFSTAT'
|
|
and status <> 'DISABLED')
|
|
LOOP
|
|
execute immediate i.sqltext;
|
|
END LOOP;
|
|
END;
|
|
/
|
|
|
|
-- Truncate All tables
|
|
BEGIN
|
|
FOR i in (select 'TRUNCATE TABLE ' || OWNER || '.' || TABLE_NAME sqltext
|
|
from dba_tables where owner = 'PERFSTAT')
|
|
LOOP
|
|
execute immediate i.sqltext;
|
|
END LOOP;
|
|
END;
|
|
/
|
|
|
|
-- Import Statspack schema
|
|
host impdp directory=export_dump schemas=PERFSTAT content=data_only dumpfile=PERFSTAT.dmp logfile=PERFSTAT.log
|
|
|
|
|
|
-- Enable all constraints
|
|
BEGIN
|
|
FOR i in (select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME
|
|
|| ' MODIFY CONSTRAINT ' || CONSTRAINT_NAME
|
|
|| ' ENABLE' sqltext
|
|
from dba_constraints
|
|
where owner = 'PERFSTAT'
|
|
AND constraint_type IN ('P','U')
|
|
and status = 'DISABLED'
|
|
)
|
|
LOOP
|
|
execute immediate i.sqltext;
|
|
END LOOP;
|
|
FOR i in (select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME
|
|
|| ' MODIFY CONSTRAINT ' || CONSTRAINT_NAME
|
|
|| ' ENABLE' sqltext
|
|
from dba_constraints where owner = 'PERFSTAT'
|
|
and status = 'DISABLED')
|
|
LOOP
|
|
execute immediate i.sqltext;
|
|
END LOOP;
|
|
END;
|
|
/
|
|
|
|
|
|
-- Enable statspack job.
|
|
BEGIN
|
|
FOR i in (select job from user_jobs where lower(what) like '%snap%' )
|
|
LOOP
|
|
sys.dbms_job.broken(i.job,FALSE);
|
|
END LOOP;
|
|
COMMIT;
|
|
END;
|
|
/
|