IT Knowledge oracle
1. Find parent child tables
Find parent and child tables
​
select * from (
with pur as (
select table_name, constraint_type, constraint_name, r_constraint_name,
max(decode(constraint_type,'R',1,0)) over(partition by table_name) is_r
from all_constraints where owner='PRODUCT_OWNER'
and constraint_type in ('P', 'U', 'R')
)
, son_dad as (
select distinct s.table_name son, d.table_name dad, d.constraint_type
from (select * from pur where constraint_type = 'R' or is_r = 0) s
left join pur d
on s.r_constraint_name = d.constraint_name
and s.table_name != d.table_name
)
select son,level lvl, dad, constraint_type
from son_dad
start with dad is null
connect by dad = prior son
order siblings by dad, son
) order by son,lvl,dad;
with pur as (
select table_name, constraint_type, constraint_name, r_constraint_name,
max(decode(constraint_type,'R',1,0)) over(partition by table_name) is_r
from all_constraints where owner='OWNER'
and constraint_type in ('P', 'U', 'R')
)
, son_dad as (
select distinct s.table_name son, d.table_name dad, d.constraint_type
from (select * from pur where constraint_type = 'R' or is_r = 0) s
left join pur d
on s.r_constraint_name = d.constraint_name
and s.table_name != d.table_name
)
select level lvl, son, dad, constraint_type
from son_dad
start with dad is null
connect by dad = prior son
order siblings by dad, son;
SELECT
acc.owner AS table_owner, acc.table_name,acc.constraint_name, acc.column_name, atc.column_id
FROM
all_cons_columns acc
JOIN
all_tab_columns atc ON acc.owner = atc.owner AND acc.table_name = atc.table_name
AND acc.column_name = atc.column_name
WHERE
acc.constraint_name IN (
SELECT constraint_name
FROM all_constraints
WHERE constraint_type = 'P' -- 'P' for Primary Key
)
and lower(acc.table_name)='project_tbh_resource_allocation'
ORDER BY
acc.owner, acc.table_name, acc.constraint_name, atc.column_id;
2. Clean up user objects
Run by owner:
DECLARE
CURSOR c_lob_objects IS
SELECT object_name
FROM user_objects
WHERE object_type = 'LOB';
CURSOR c_user_objects IS
SELECT object_name, object_type
FROM user_objects
WHERE object_type IN ('TABLE', 'VIEW', 'INDEX', 'SEQUENCE', 'SYNONYM', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TRIGGER');
v_sql_stmt VARCHAR2(1000);
BEGIN
-- Log and attempt to drop LOB objects associated with user-defined types
FOR rec IN c_lob_objects LOOP
v_sql_stmt := 'DROP TYPE ' || rec.object_name || ' FORCE';
DBMS_OUTPUT.PUT_LINE('Attempting to drop LOB object ' || rec.object_name);
BEGIN
EXECUTE IMMEDIATE v_sql_stmt;
DBMS_OUTPUT.PUT_LINE('Dropped LOB object ' || rec.object_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Failed to drop LOB object ' || rec.object_name || ': ' || SQLERRM);
END;
END LOOP;
-- Log and attempt to drop all user objects
FOR rec IN c_user_objects LOOP
-- Construct the DROP statement based on the object type
IF rec.object_type = 'PACKAGE' THEN
v_sql_stmt := 'DROP PACKAGE ' || rec.object_name;
ELSIF rec.object_type = 'PACKAGE BODY' THEN
v_sql_stmt := 'DROP PACKAGE BODY ' || rec.object_name;
ELSE
v_sql_stmt := 'DROP ' || rec.object_type || ' ' || rec.object_name;
END IF;
-- Add CASCADE CONSTRAINTS for tables
IF rec.object_type = 'TABLE' THEN
v_sql_stmt := v_sql_stmt || ' CASCADE CONSTRAINTS';
END IF;
DBMS_OUTPUT.PUT_LINE('Attempting to drop ' || rec.object_type || ' ' || rec.object_name);
-- Execute the SQL statement
BEGIN
EXECUTE IMMEDIATE v_sql_stmt;
DBMS_OUTPUT.PUT_LINE('Dropped ' || rec.object_type || ' ' || rec.object_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Failed to drop ' || rec.object_type || ' ' || rec.object_name || ': ' || SQLERRM);
END;
END LOOP;
-- Log and attempt to drop all remaining tables (in case any still exist)
FOR rec IN (SELECT table_name FROM user_tables) LOOP
v_sql_stmt := 'DROP TABLE ' || rec.table_name || ' CASCADE CONSTRAINTS';
DBMS_OUTPUT.PUT_LINE('Attempting to drop table ' || rec.table_name);
BEGIN
EXECUTE IMMEDIATE v_sql_stmt;
DBMS_OUTPUT.PUT_LINE('Dropped table ' || rec.table_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Failed to drop table ' || rec.table_name || ': ' || SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('All user objects and LOB segments have been processed.');
END;
/
purge recyclebin;
Purge Index, table
DECLARE
v_sql VARCHAR2(4000);
BEGIN
FOR rec IN (SELECT owner, object_name, type FROM dba_recyclebin ) LOOP
BEGIN
IF rec.type = 'TABLE' THEN
v_sql := 'PURGE TABLE ' || rec.owner || '."' || rec.object_name || '"';
ELSIF rec.type = 'INDEX' THEN
v_sql := 'PURGE INDEX ' || rec.owner || '."' || rec.object_name || '"';
ELSIF rec.type = 'LOB' THEN
v_sql := 'PURGE LOB ' || rec.owner || '."' || rec.object_name || '"';
ELSIF rec.type = 'LOB INDEX' THEN
v_sql := 'PURGE LOBINDEX ' || rec.owner || '."' || rec.object_name || '"';
ELSE
v_sql := 'PURGE ' || rec.owner || '."' || rec.object_name || '"';
END IF;
EXECUTE IMMEDIATE v_sql;
EXCEPTION
WHEN OTHERS THEN
-- Ignore errors for non-purgable objects
NULL;
END;
END LOOP;
END;
/
select global_name,user,object_type,count(*)
from user_objects ,global_name
group by object_type, global_name,user;
​
Run by dba:
DECLARE
v_sql_stmt VARCHAR2(1000);
BEGIN
FOR v_schema IN (SELECT username FROM dba_users WHERE username IN ( select username from dba_users where oracle_maintained='N' and NOT REGEXP_LIKE(username, '[0-9]')
and username not in ('USER1','PERFDBA','USER2')) order by username) LOOP
DBMS_OUTPUT.PUT_LINE('Cleaning up objects for schema: ' || v_schema.username);
-- Cleanup LOB objects
FOR rec IN (SELECT object_name FROM all_objects WHERE owner = v_schema.username AND object_type = 'LOB') LOOP
v_sql_stmt := 'DROP TYPE ' || v_schema.username || '.' || rec.object_name || ' FORCE';
DBMS_OUTPUT.PUT_LINE('Attempting to drop LOB object ' || v_schema.username || '.' || rec.object_name);
BEGIN
EXECUTE IMMEDIATE v_sql_stmt;
DBMS_OUTPUT.PUT_LINE('Dropped LOB object ' || v_schema.username || '.' || rec.object_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Failed to drop LOB object ' || v_schema.username || '.' || rec.object_name || ': ' || SQLERRM);
END;
END LOOP;
-- Cleanup other user objects
FOR rec IN (SELECT object_name, object_type FROM all_objects WHERE owner = v_schema.username AND object_type IN (
'TABLE', 'VIEW', 'INDEX', 'SEQUENCE', 'SYNONYM', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TRIGGER'
)) LOOP
-- Construct the DROP statement based on the object type
IF rec.object_type = 'PACKAGE' THEN
v_sql_stmt := 'DROP PACKAGE ' || v_schema.username || '.' || rec.object_name;
ELSIF rec.object_type = 'PACKAGE BODY' THEN
v_sql_stmt := 'DROP PACKAGE BODY ' || v_schema.username || '.' || rec.object_name;
ELSE
v_sql_stmt := 'DROP ' || rec.object_type || ' ' || v_schema.username || '.' || rec.object_name;
END IF;
-- Add CASCADE CONSTRAINTS for tables
IF rec.object_type = 'TABLE' THEN
v_sql_stmt := v_sql_stmt || ' CASCADE CONSTRAINTS';
END IF;
DBMS_OUTPUT.PUT_LINE('Attempting to drop ' || rec.object_type || ' ' || v_schema.username || '.' || rec.object_name);
-- Execute the SQL statement
BEGIN
EXECUTE IMMEDIATE v_sql_stmt;
DBMS_OUTPUT.PUT_LINE('Dropped ' || rec.object_type || ' ' || v_schema.username || '.' || rec.object_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Failed to drop ' || rec.object_type || ' ' || v_schema.username || '.' || rec.object_name || ': ' || SQLERRM);
END;
END LOOP;
-- Cleanup remaining tables (if any)
FOR rec IN (SELECT table_name FROM all_tables WHERE owner = v_schema.username) LOOP
v_sql_stmt := 'DROP TABLE ' || v_schema.username || '.' || rec.table_name || ' CASCADE CONSTRAINTS';
DBMS_OUTPUT.PUT_LINE('Attempting to drop table ' || v_schema.username || '.' || rec.table_name);
BEGIN
EXECUTE IMMEDIATE v_sql_stmt;
DBMS_OUTPUT.PUT_LINE('Dropped table ' || v_schema.username || '.' || rec.table_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Failed to drop table ' || v_schema.username || '.' || rec.table_name || ': ' || SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Cleanup for schema ' || v_schema.username || ' completed.');
DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');
END LOOP;
DBMS_OUTPUT.PUT_LINE('All schemas have been processed.');
END;
/
​
​
2.4 Compile invald object
​
By DBA:
BEGIN
FOR rec IN (SELECT object_type, owner, object_name
FROM dba_objects
WHERE status = 'INVALID')
LOOP
BEGIN
IF rec.object_type = 'PACKAGE' THEN
EXECUTE IMMEDIATE 'ALTER PACKAGE ' || rec.owner || '.' || rec.object_name || ' COMPILE';
ELSIF rec.object_type = 'PACKAGE BODY' THEN
EXECUTE IMMEDIATE 'ALTER PACKAGE ' || rec.owner || '.' || rec.object_name || ' COMPILE BODY';
ELSIF rec.object_type = 'PROCEDURE' THEN
EXECUTE IMMEDIATE 'ALTER PROCEDURE ' || rec.owner || '.' || rec.object_name || ' COMPILE';
ELSIF rec.object_type = 'FUNCTION' THEN
EXECUTE IMMEDIATE 'ALTER FUNCTION ' || rec.owner || '.' || rec.object_name || ' COMPILE';
ELSIF rec.object_type = 'TRIGGER' THEN
EXECUTE IMMEDIATE 'ALTER TRIGGER ' || rec.owner || '.' || rec.object_name || ' COMPILE';
ELSIF rec.object_type = 'VIEW' THEN
EXECUTE IMMEDIATE 'ALTER VIEW ' || rec.owner || '.' || rec.object_name || ' COMPILE';
ELSIF rec.object_type = 'TYPE' THEN
EXECUTE IMMEDIATE 'ALTER TYPE ' || rec.owner || '.' || rec.object_name || ' COMPILE';
ELSIF rec.object_type = 'TYPE BODY' THEN
EXECUTE IMMEDIATE 'ALTER TYPE ' || rec.owner || '.' || rec.object_name || ' COMPILE BODY';
ELSIF rec.object_type = 'SYNONYM' THEN
EXECUTE IMMEDIATE 'ALTER SYNONYM ' || rec.owner || '.' || rec.object_name || ' COMPILE';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error compiling ' || rec.object_type || ' ' || rec.owner || '.' || rec.object_name || ': ' || SQLERRM);
END;
END LOOP;
END;
/
By schema:
​
purge recyclebin;
BEGIN
DBMS_UTILITY.COMPILE_SCHEMA(schema => user, compile_all => FALSE);
END;
/
3. Generate DDL
​
set heading off;
set echo off;
set pages 999;
set long 90000;
SET LONG 90000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
exec DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', TRUE);
exec DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', TRUE);
exec DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', FALSE);
exec DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'STORAGE', FALSE);
exec DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'EMIT_SCHEMA', FALSE);
select to_char(dbms_metadata.get_ddl('TABLE','TABLE_NAME','SCHEMA_NAME')) from dual;
select to_char(dbms_metadata.get_ddl('VIEW','VIEW_NAME','REPORT_ADMIN')) from dual;
select * from all_views where view_name = 'VIEW_NAME'; SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true); END;
/
​
​
​
​
​
​