Create Schema Objects

Note: This will not work from 10g to 11g version......................procedure will be corrupted


set pagesize 0
set linesize 30000
set long 500000
set longchunksize 500000
set trimspool on
set feed off

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true);
exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );
exec  dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS',false);
exec  dbms_metadata.set_transform_param (dbms_metadata.session_transform,'REF_CONSTRAINTS', false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'CONSTRAINTS_AS_ALTER', false);

SPOOL C:\OPTABLE.SQL
select dbms_metadata.get_ddl('SYNONYM',synonym_name,'PUBLIC') metadata from dba_synonyms where owner = 'PUBLIC'   and table_owner = 'LDBO' ;
Select Dbms_Metadata.Get_Ddl('SEQUENCE',Sequence_Name)  from User_Sequences Where Sequence_Name Not Like 'SQFIN_%';
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE = 'TABLE';
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE IN 'INDEX' ;
Select Dbms_Metadata.Get_Ddl('CONSTRAINT', Constraint_Name) Ddl FROM USER_CONSTRAINTS WHERE Constraint_Type='C' And Constraint_Name Not Like 'BIN%'  order by table_name;
Select Dbms_Metadata.Get_Ddl(decode(constraint_type, 'R', 'REF_CONSTRAINT', 'CONSTRAINT'), constraint_name, owner)  CONSDDL From Dba_Constraints WHERE owner='LDBO' AND CONSTRAINT_NAME NOT LIKE 'BIN$%' and constraint_type IN('P', 'R', 'U') Order By Case When Constraint_Type In('P','U') Then 0 Else 1 End;
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE IN ('PROCEDURE','FUNCTION','TYPE');
Select Dbms_Metadata.Get_Ddl('PACKAGE_SPEC', Object_Name)  From User_Objects WHERE OBJECT_TYPE IN ('PACKAGE');
Select Dbms_Metadata.Get_Ddl('PACKAGE_BODY', Object_Name) From User_Objects WHERE OBJECT_TYPE IN ('PACKAGE BODY');
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE IN ('TRIGGER');

SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) FROM DBA_USERS;
SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', USERNAME) DDL FROM DBA_USERS where username in (select username from dba_ts_quotas);
SELECT DBMS_METADATA.GET_DDL('ROLE', role) FROM dba_roles;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', GRANTEE) FROM DBA_SYS_PRIVS;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', GRANTEE) FROM DBA_TAB_PRIVS WHERE OWNER='LDBO';
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', GRANTEE) FROM DBA_ROLE_PRIVS;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) DDL FROM DBA_USERS where username in (select grantee from dba_role_privs);
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) DDL FROM DBA_USERS where username in (select grantee from dba_sys_privs)
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) DDL FROM DBA_USERS where username in (select grantee from dba_tab_privs);
spool off



--------------------------------------------------------------objects---------------------------------------------------------

db_link
sequence
directory
synonym
type
table
index
constraint
package
function
procedure
compile invalid
view
ref_constra
package body
type body
trigger
MATERIALIZED_VIEW
MATERIALIZED_VIEW_LOG
JOB

--------------------------------------------checking------------------------------------------------------------------

Select Object_Type,Count(*) From User_Objects Group By Object_Type Order By 1;
select COUNT(*)  from user_objects where status='INVALID';
SELECT * FROM DBA_OBJECTS WHERE Owner='PUBLIC';
Select * From Dba_Synonyms Where Table_Owner='LDBO';
SELECT * FROM DBA_DB_LINKS;
SELECT * FROM DBA_DIRECTORIES;
select Constraint_Type,count(*) from User_Constraints group by Constraint_Type;
select table_name,count(*) from User_Constraints group by table_name order by 1;
select table_name,Search_Condition from User_Constraints where Constraint_Type='C' and table_name not like 'BIN%' ORDER BY 1;


---------------------------------------------------------------Directory Creation-------------------------------------------

select 'create directory '||OWNER||'.'||DIRECTORY_NAME||' as '||''''||DIRECTORY_PATH||''''||';' from dba_directories;
or
select dbms_metadata.get_ddl('DIRECTORY',directory_name) from dba_Directories;


get and change the path as you want


------------------------------------------------------------------Database link--------------------------------------------

SELECT 'CREATE ' || OWNER || ' DATABASE LINK ' || DB_LINK || ' CONNECT TO ' || USERNAME || ' IDENTIFIED BY ' || '<PLEASE PUT PASSWORD>' || ' USING ' || '<PLEASE PUT DATABASE STRING>' || ';' FROM DBA_DB_LINKS  WHERE USERNAME !=' ';

Select 'DROP '||Decode(U.Name,'PUBLIC','public ')||'database link '||Chr(10)
||Decode(U.Name,'PUBLIC',Null, U.Name||'.')|| L.Name||Chr(10) ||';' TEXT
From Sys.Link$ L, Sys.User$ U
Where L.Owner# = U.User#;

SELECT 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||Decode(U.Name,'PUBLIC',Null, U.Name||'.')|| L.Name||Chr(10)
||' connect to ' || L.Userid || ' identified by values '''
||L.PASSWORDX||''' using ''' || L.host || ''''
||chr(10)||';' TEXT
From Sys.Link$ L, Sys.User$ U
WHERE L.OWNER# = U.USER# AND L.USERID !=' ';



CREATE PUBLIC DATABASE LINK "LNK_RAKSHAK"
  CONNECT TO "LDBO" IDENTIFIED BY LDBO USING 'APX1314SRV';

 CREATE PUBLIC DATABASE LINK "LNK_CCM"
  CONNECT TO "LDBO" IDENTIFIED BY LDBO USING 'APX1314SRV';

CREATE DATABASE LINK "LNK_DIGITAL"
  CONNECT TO "LDBO" IDENTIFIED BY ldbo USING 'APX1314SRV';

create database link LNK_PREVIOUSYEARBALANCE connect to LDBO identified by ldbo using 'APX1314SRV';

CREATE PUBLIC DATABASE LINK "CMLDLINK"
  CONNECT TO ldbo IDENTIFIED BY ldbo USING 'APX1314SRV';


--------------------------------------------------------------------------------------------------------------

set pagesize 0
set linesize 30000
set long 500000
set longchunksize 500000
set trimspool on
set feed off

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true);
exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );
exec  dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS',false);
exec  dbms_metadata.set_transform_param (dbms_metadata.session_transform,'REF_CONSTRAINTS', false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'CONSTRAINTS_AS_ALTER', false);


SPOOL C:\SEQ.SQL
Select Dbms_Metadata.Get_Ddl('SEQUENCE',Sequence_Name)  from User_Sequences Where Sequence_Name Not Like 'SQFIN_%';
SPOOL OFF

spool c:\tables.sql
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE = 'TABLE';
spool off

spool c:\index.sql
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE IN 'INDEX' ;
spool off

spool c:\syn.sql
select dbms_metadata.get_ddl('SYNONYM',synonym_name,'PUBLIC') metadata from dba_synonyms where owner = 'PUBLIC'   and table_owner = 'LDBO' ;
spool off

spool c:\consc.sql
Select Dbms_Metadata.Get_Ddl('CONSTRAINT', Constraint_Name) Ddl FROM USER_CONSTRAINTS WHERE Constraint_Type='C' And Constraint_Name Not Like 'BIN%'  order by table_name;
spool off


spool c:\consp.sql
Select Dbms_Metadata.Get_Ddl(
decode(constraint_type, 'R', 'REF_CONSTRAINT', 'CONSTRAINT'), constraint_name, owner)  CONSDDL
From Dba_Constraints
WHERE owner='LDBO' AND CONSTRAINT_NAME NOT LIKE 'BIN$%'
and constraint_type IN('P', 'R', 'U')
Order By Case When Constraint_Type In('P','U') Then 0 Else 1 End;
spool off


spool c:\proc.sql
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE IN ('PROCEDURE','FUNCTION','TYPE');
spool off

spool c:\pack.sql
Select Dbms_Metadata.Get_Ddl('PACKAGE_SPEC', Object_Name)  From User_Objects WHERE OBJECT_TYPE IN ('PACKAGE');
spool off

spool c:\packb.sql
Select Dbms_Metadata.Get_Ddl('PACKAGE_BODY', Object_Name) From User_Objects WHERE OBJECT_TYPE IN ('PACKAGE BODY');
spool off

spool c:\trigger.sql
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE IN ('TRIGGER');
spool off


------------------------------------------------------------------User Creation and its role--------------------------------------------
set head off
set pages 0
set long 9999999
spool c:\user_script.sql
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) || '/' DDL
FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select username from dba_ts_quotas)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_role_privs)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_sys_privs)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_tab_privs);
spool off;



--------------------------------------------------------------Role Creation------------------------------------------------------------------

SELECT 'CREATE ROLE ' ||ROLE || ';' FROM DBA_ROLES;
Select 'GRANT ' || Privilege || ' ON ' ||Table_Name || ' TO "' || Grantee || '";' From Dba_Tab_Privs Where  OWNER='LDBO' ORDER BY GRANTEE,TABLE_NAME;
Select 'GRANT ' || Privilege || ' TO "' || Grantee || '";' From Dba_sys_Privs ORDER BY GRANTEE;
select 'GRANT ' || Privilege || ' (' || COLUMN_NAME ||  ') ON ' ||Table_Name || ' TO "' || Grantee || '";' from Dba_Col_Privs;
Select 'GRANT ' || Granted_Role || ' TO "' || Grantee || '";' From Dba_role_Privs ORDER BY GRANTEE;


-----------------------------------------------------------------------------------User Role Profile


select dbms_metadata.get_ddl('USER', u.username) AS ddl from   dba_users u
union all
select dbms_metadata.get_ddl('ROLE', role) AS ddl FROM dba_roles
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl from   dba_ts_quotas tq
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl from   dba_role_privs rp
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl from dba_sys_privs sp
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl from   dba_tab_privs tp
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl from   dba_role_privs rp
union all
select to_clob('/* Start profile creation script in case they are missing') AS ddl from dba_users u where u.profile <> 'DEFAULT'
union all
select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl from   dba_users u where   u.profile <> 'DEFAULT'
union all
select to_clob('End profile creation script */') AS ddl from   dba_users u where u.profile <> 'DEFAULT'
;

-----------------------------------------------------------------------------------

set define off
Turns off substitution variables.

---------------------------------------------------------compile invalid--------------------------------------------------------------------------------------------
EXEC sys.UTL_RECOMP.recomp_serial;



---------------------------------------------------------------Analyze-----------------------------------------------------------------------------------
EXEC DBMS_STATS.gather_database_stats(degree => DBMS_STATS.DEFAULT_DEGREE);




SET HEADING OFF

spool c:\temp\invalid.sql ;
select OBJECT_NAME from dba_objects where STATUS='INVALID';

select
'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '."' || OBJECT_NAME || '" COMPILE;' from dba_objects where status = 'INVALID'
and object_type in ('PACKAGE','FUNCTION','PROCEDURE','VIEW','TRIGGER');

Select decode( object_type, 'PACKAGE BODY', 'ALTER PACKAGE ' ||  OWNER || '.' || OBJECT_NAME || ' COMPILE BODY;')
from dba_objects
where status = 'INVALID' and object_type in ('PACKAGE BODY') order by object_type;

select 'ALTER ' || OWNER || ' ' || OBJECT_TYPE || ' "' || OBJECT_NAME || '" COMPILE;' from dba_objects where status = 'INVALID'
and object_type in ('SYNONYM');

Select decode( object_type, 'TYPE', 'ALTER TYPE ' ||  OWNER || '.' || OBJECT_NAME || ' COMPILE;')
from dba_objects where status = 'INVALID' and object_type in ('TYPE') order by object_type;

Select decode( object_type, 'TYPE BODY', 'ALTER TYPE ' ||  OWNER || '.' || OBJECT_NAME || ' COMPILE BODY;')
from dba_objects
where status = 'INVALID' and object_type in ('TYPE BODY') order by object_type;

spool out ;
@ c:\temp\invalid.sql ;


SELECT * FROM USER_ERRORS;


select 'SELECT INSTANCE_NAME,HOST_NAME FROM V$INSTANCE@' ||Db_Link || ';' from dba_db_links;


















-------------------------------Drop objects

BEGIN
 FOR cur_rec IN (SELECT object_name, object_type
                  FROM   user_objects where object_type='TRIGGER') LOOP
    BEGIN
      EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' ' || cur_rec.object_name;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
  FOR cur_rec IN (SELECT table_name, constraint_name
                  FROM   user_constraints
                  WHERE  constraint_type = 'R') LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE ' || cur_rec.table_name || ' DROP CONSTRAINT ' || cur_rec.constraint_name;
  END LOOP;
FOR cur_rec IN (SELECT object_name, object_type
                  FROM   user_objects) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' ' || cur_rec.object_name;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
FOR cur_rec IN (SELECT owner,synonym_name,table_owner FROM  dba_synonyms where table_owner not in ('DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) LOOP
BEGIN
      EXECUTE IMMEDIATE 'DROP ' || cur_rec.owner || ' SYNONYM ' || cur_rec.table_owner || '.' || cur_rec.synonym_name || ' FORCE';
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/
purge recyclebin ;