Manage objects

search object_name or procedure

select owner , object_name , object_type from dba_objects where lower(object_name) like '%idappel%'

union

select owner , procedure_name , 'PROCEDURE' from dba_procedures where lower(procedure_name) like '%idappel%'

1] Triggers

-- find every logon triggers

select * from dba_triggers where triggering_event like '%LOGON%' and status = 'ENABLED' order by 1,2

Code example

CREATE OR REPLACE TRIGGER OWNER.ReInit_CTXT_WS_Ventes

BEFORE INSERT ON T_CTXT_SDD

FOR EACH ROW

BEGIN

  IF :new.STATUT = 0 AND (:new.ID_TYPEEQUIPEMENT=50 OR :new.ID_TYPEEQUIPEMENT=60) then

     :new.STATUT := 1 ;

  END IF ;

END ;

/

-- update APP.GP_ACCID_SDIS set ADRESSE = replace(ADRESSE,'\','/');

CREATE OR REPLACE TRIGGER APP.GP_ACCID_SDIS_T01

BEFORE UPDATE OR INSERT ON APP.GP_ACCID_SDIS

FOR EACH ROW

BEGIN

  select replace(:new.ADRESSE,'\','/') into :new.ADRESSE from dual ;

END ;

/

2] Compile objects

Schema Level : cautious, doesn’t check for grant on synonym

EXEC DBMS_UTILITY.compile_schema(schema => '&user_c');

Object Level : checks for grant on synonym

select 'alter '||decode(object_type,'PACKAGE BODY','PACKAGE',object_type)||' '||owner||'.'||object_name|| ' compile ' || decode(object_type,'PACKAGE BODY','body ;',';')

from dba_objects where owner = '${USc}' and object_type in ('FUNCTION','PACKAGE','PACKAGE BODY','PROCEDURE','TRIGGER','VIEW','MATERIALIZED VIEW','SYNONYM') and status = 'INVALID' ;

The result SQL script should be run as the Target user instead of SYS or SYSTEM.

The Proxy method helps us here to « become » temporarily this user without ever knowing its password !

SQLPLUS as SYSTEM

SQL> conn system/<psw>

Connected.

SQL> alter SYNONYM <SCHEMA>_2_BO.TEST compile ;

alter SYNONYM <SCHEMA>_2_BO.TEST compile

*

ERROR at line 1:

ORA-01031: insufficient privileges

Then

SQL> create user su identified by secret123;

SQL> alter user <SCHEMA>_2_BO grant connect through su;

SQL> connect su[<SCHEMA>_2_BO]/secret123

Connected.

SQL> alter SYNONYM <SCHEMA>_2_BO.TEST compile ;

Synonym altered.

SQL> conn system/<psw>

Connected.

SQL> alter user <SCHEMA>_2_BO revoke connect through su;

SQL> drop user su ;

User dropped.

3] Grants

3.1 Grant Select/Execute on every Objects

• Complete SQL

spool /tmp/gr.sql

select 'grant select on &&compte..'||table_name||'  to '|| '&&compte._BO ;'

         from dba_tables where owner=upper('&&compte') and table_name not like 'SYS_EXPORT%' ;

select 'grant select on &&compte..' ||view_name||'  to '|| '&&compte._BO ;'

         from dba_views where owner=upper('&&compte');

select 'grant execute on &&compte..'||object_name||' to '|| '&&compte._BO ;'

        from dba_objects where object_type ='FUNCTION' and owner=upper('&&compte');

select 'grant execute on &&compte..'||object_name||' to '|| '&&compte._BO ;'

        from dba_objects where object_type ='PROCEDURE' and owner=upper('&&compte');

select 'grant execute on &&compte..'||object_name||' to '|| '&&compte._BO ;'

        from dba_objects where object_type ='PACKAGE' and owner=upper('&&compte');

spool off

@/tmp/gr.sql

Simplified SQL

select 'grant '||decode(object_type,'TABLE','select',(decode(object_type,'VIEW','select','execute')))||' on &&compte..'||object_name||'  to '|| '&&compte._BO ;'

from dba_objects where owner=upper('&&compte') and object_type in ('TABLE','VIEW','FUNCTION','PROCEDURE','PACKAGE') and object_name not like 'SYS_EXPORT%'

order by 1 ;

• PL Loop : Grant to Role SCHEMA_READ TABLE / VIEW / FUNCTION / PROCEDURE / PACKAGE

set serveroutput on

begin

--dbms_output.enable(100000);

 for i in ( select decode(object_type,'TABLE','select',(decode(object_type,'VIEW','select','execute'))) gr_type , owner , object_name

                  from dba_objects where owner='<SCHEMA>' and object_type in ('TABLE','VIEW','FUNCTION','PROCEDURE','PACKAGE') and object_name not like 'SYS_EXPORT%' and upper(object_name) != 'LOCK' order by object_name )

 loop

        --DBMS_OUTPUT.PUT_LINE( 'grant '||i.gr_type||' on '||i.owner||'.'||i.object_name|| ' to <SCHEMA>_R ; ' ) ;

    EXECUTE IMMEDIATE 'grant '||i.gr_type||' on '||i.owner||'.'||i.object_name|| ' to <SCHEMA>_R' ;

 end loop;

end;

/

In a procedure :

CREATE OR REPLACE procedure GR_SEL_DBADWH_CONS (RETOUR OUT NUMBER) authid current_user

AS

my_sqlerrm       varchar2(100);

begin

 for i in ( select decode(object_type,'TABLE','select',(decode(object_type,'VIEW','select','execute'))) gr_type , object_name

                  from user_objects where object_type in ('TABLE','VIEW','FUNCTION','PROCEDURE','PACKAGE') and object_name not like 'SYS_EXPORT%' and upper(object_name) != 'LOCK' order by object_name )

 loop

    EXECUTE IMMEDIATE 'grant '||i.gr_type||' on '||i.object_name|| ' to DBADWH_CONS' ;

 end loop;

 retour:=0;

 insert into SCHEMA.SCH_TRT_LOG (DTE,RET,MSG) values (sysdate,retour,'GR_SEL_DBADWH_CONS traitement: OK');

 commit ;

-- gestion des erreurs

EXCEPTION WHEN OTHERS THEN

 retour :=2;

 my_sqlerrm:=substr(sqlerrm,1,100);

--    dbms_output.put_line ('incohérence traitement: '||v_rec.num_class||' - '||' erreur : '||my_sqlerrm);

 insert into SCHEMA.SCH_TRT_LOG (DTE,RET,MSG) values (sysdate,retour,'GR_SEL_DBADWH_CONS traitement: KO - '||' erreur : '||my_sqlerrm);

 commit;

end;

/

Call it (Invoker's Rights )

set serveroutput on

DECLARE NUM number ;

BEGIN SCHEMA.GR_SEL_DBADWH_CONS(NUM);

dbms_output.put_line ('Retour: '||NUM ) ;

END;

/

• PL to Grant just the SELECT on TABLE/VIEW

set serveroutput on

begin

--dbms_output.enable(100000);

 for i in ( select 'select' gr_type , owner , object_name

                  from dba_objects where owner='SCHEMA' and object_type in ('TABLE','VIEW') and object_name not like 'SYS_EXPORT%' and upper(object_name) != 'LOCK' order by object_name )

 loop

    EXECUTE IMMEDIATE 'grant '||i.gr_type||' on '||i.owner||'.'||i.object_name|| ' to SCHEMA_CONS' ;

 end loop;

end;

/

• Drop public Synonym and Grant

select 'revoke '||privilege||' on ROC.'||table_name||' from PUBLIC ; ' from dba_tab_privs where owner = 'ROC' and grantee = 'PUBLIC' ;

select 'drop public synonym '||synonym_name||' ; ' from dba_synonyms where owner = 'PUBLIC' and table_owner = 'SCH' ;

• Find Grants from one Schema to Another

select distinct 'grant '||privilege||' on "'||owner||'".'||TABLE_NAME||' to "'||GRANTEE||'" ;' from dba_tab_privs

where grantee = 'APP' and owner = 'APP02'

and grantable = 'NO'

union

select distinct 'grant '||privilege||' on "'||owner||'".'||TABLE_NAME||' to "'||GRANTEE||'" with GRANT OPTION ;' from dba_tab_privs

where grantee = 'APP' and owner = 'APP02'

and grantable = 'YES'

order by 1

3.2 Reverse Enginnering of Grants

-- Ref IPL_PRO

select distinct 'grant '||privilege||' on "'||owner||'".'||TABLE_NAME||' to "'||GRANTEE||'" ;' from dba_tab_privs

where grantee in ( 'SCH1', 'SCH2' ) and owner not like '%SCH3%' and grantable = 'NO'

union

select distinct 'grant '||privilege||' on "'||owner||'".'||TABLE_NAME||' to "'||GRANTEE||'" with GRANT OPTION ;' from dba_tab_privs

where grantee in ('SCH1', 'SCH2' ) and owner not like '%SCH3%' and grantable = 'YES'

order by 1

3.3 which users doesn't have "alter default role ALL" ?

select distinct 'alter user '||grantee||' default role all ; ' from dba_role_privs where default_role != 'YES' order by 1

Also, remove the unused granted role to SYSTEM.

select 'revoke '||granted_role||' from '||grantee||' ; ' from dba_role_privs where default_role != 'YES' and grantee = 'SYSTEM' order by 1

4] Constraints

4.1 Clean orphaned rows when enabling FK constraint fails :

Try to enable constraint

select distinct 'alter table '||owner||'.'||table_name||' enable constraint '||constraint_name||' ; ' from dba_constraints

where r_constraint_name in ( select constraint_name from dba_constraints ) and r_owner = 'HR' and status = 'DISABLED' order by 1 ;

alter table HR.GE15 enable constraint FGE15  --> FAILS ON HR.GE10 parent

ORA-02298: cannot validate (HR.FGE15) - parent keys not found

Generate SQL to delete rows in Child table not existing in Parent.

select distinct 'DELETE FROM '||a.table_name|| ' a '||CHR(10) ||

'WHERE NOT EXISTS '||CHR(10)||

'( SELECT 1 FROM ' ||CHR(10)

,b.table_name ||' b ' ||CHR(10)||

'WHERE a.'||a.column_name||'='||'b.'||b.column_name||');'

from dba_cons_columns a,dba_cons_columns b,dba_constraints c

where c.constraint_name=a.constraint_name

and c.r_constraint_name=b.constraint_name

and a.owner = 'HR' and a.table_name = 'GE15' –- CHILD

and b.table_name = 'GE10' –- PARENT

and a.column_name = b.column_name

To get

DELETE FROM HR.GE15 a

WHERE NOT EXISTS

( SELECT 1 FROM

HR.GE10 b

WHERE a.CDSTDO=b.CDSTDO and a.CDSEGI=b.CDSEGI and a.CDINFO=b.CDINFO and a.CDMACI=b.CDMACI and a.CDPLLO=b.CDPLLO and a.TIMODI=b.TIMODI);

commit ;

Another one

select distinct 'DELETE FROM '||a.owner||'.'||a.table_name||' a WHERE NOT EXISTS ( SELECT 1 FROM '||b.owner||'.'||b.table_name||' b WHERE a.'||a.column_name||'='||'b.'||b.column_name||' ) ;'

from dba_cons_columns a,dba_cons_columns b,dba_constraints c

where c.constraint_name=a.constraint_name

and c.r_constraint_name=b.constraint_name

and a.owner = 'HF' and a.table_name = 'MX40' -- CHILD

and b.owner = 'HF' and b.table_name = 'MX10' -- PARENT

and a.column_name = b.column_name

DELETE FROM APP.MX20 a WHERE NOT EXISTS ( SELECT 1 FROM APP.MX10 b WHERE a.VSESID=b.VSESID ) -- 33 rows deleted

alter table APP.MX20 enable constraint FMX20 -- -> APP.MX10

DELETE FROM APP.MX30 a WHERE NOT EXISTS ( SELECT 1 FROM APP.MX10 b WHERE a.VSESID=b.VSESID ) ;

alter table APP.MX30 enable constraint FMX30 -- -> MX10

DELETE FROM APP.MX40 a WHERE NOT EXISTS ( SELECT 1 FROM APP.MX10 b WHERE a.VSESID=b.VSESID ) ;

alter table APP.MX40 enable constraint FMX40 -- -> MX10

4.2 Drop FK constraint to allow Table Removal :

drop table <SCH>.TABLE_01 ;

ORA-02449: unique/primary keys in table referenced by foreign keys

select distinct 'alter table '||owner||'.'||table_name||' drop constraint '||constraint_name||' ; ' from dba_constraints

where r_constraint_name in ( select constraint_name from dba_constraints where owner = '<SCH>'

and table_name in ('TAB1')

)

and r_owner = '<SCH>' and status = 'ENABLED'

order by 1 ;

alter table <SCH>.ACTIONS drop constraint CONT01_FK ;

alter table <SCH>.COM_GRP drop constraint CONT02_FK ;

alter table <SCH>.COM_GRP drop constraint CONT03_FK ;

drop table <SCH>.TABLE_01 ;

drop table <SCH>.DOSSIER ;

4.3 Cross-Schema Foreign keys

drop table auc.tab1

drop table auc2.taba

create table auc.tab1 ( a int )

create table auc2.taba ( a int )

insert into auc.tab1 values ('10')

insert into auc2.taba values ('10')

ALTER TABLE SCH.tab1 ADD (

  CONSTRAINT my_pk

    PRIMARY KEY (a)

)

ALTER TABLE SCH2.taba ADD (

  CONSTRAINT my_pk

    PRIMARY KEY (a)

)

grant references on auc2.taba to SCH ;

ALTER TABLE SCH.tab1 ADD (

  CONSTRAINT my_fk

    FOREIGN KEY (a)

    REFERENCES auc2.taba(a)

)

5] Synonyms

5.1 Identify & Drop Invalid

-- syno to non existing objects

with A as

( select b.synonym_name , b.table_owner , b.table_name , a.status , a.created

from dba_objects a , dba_synonyms b

where b.owner = 'SCH_BO' and a.owner=b.owner and a.object_name = b.synonym_name

)

select A.synonym_name , A.table_owner , A.table_name , A.status , A.created , nvl2(c.object_name,c.object_name,'not-exist') target_owner

from A LEFT OUTER JOIN dba_objects c

on ( A.table_owner = c.owner and A.table_name = c.object_name)

order by status,synonym_name

SYNONYM_NAME	TABLE_OWNER	TABLE_NAME	STATUS	CREATED	TARGET_OWNER
AB10DEV	HR	AB10DEV	INVALID	06/01/2011 15:59	not-exist
EM10SAV	HR	EM10SAV	INVALID	24/10/2012 20:30	not-exist
EM30SAV	HR	EM30SAV	INVALID	24/10/2012 20:30	not-exist
ZX370912	HR	ZX370912	INVALID	05/09/2012 20:30	not-exist
ZX5W201111	HR	ZX5W201111	INVALID	01/02/2012 20:32	not-exist
ZYTD12JANIER	HR	ZYTD12JANIER	INVALID	13/01/2012 20:32	not-exist
ZY90SAV28032012	HR	ZY90SAV28032012	INVALID	28/03/2012 20:32	not-exist
ABXGDM71	HR	ABXGDM71	VALID	19/04/2010 14:36	ABXGDM71
ABXGDM81	HR	ABXGDM81	VALID	19/04/2010 14:36	ABXGDM81

Remove Them

-- syno to non existing objects

select 'drop synonym '||B.owner||'.'||B.synonym_name||' ;' from (

with A as

( select b.owner , b.synonym_name , b.table_owner , b.table_name , a.status , a.created

from dba_objects a , dba_synonyms b

where b.owner = 'SCH_BO' and a.owner=b.owner and a.object_name = b.synonym_name

)

select A.owner,A.synonym_name,c.owner own_ex

from A LEFT OUTER JOIN dba_objects c

on ( A.table_owner = c.owner and A.table_name = c.object_name)

) B

where B.own_ex is null order by 1

Be careful when there are DB_LINKS !!!

5.2 « synonym translation is no longer valid » only in sql developer at connection

The trace shows the errors on Reference to not-existing object ( APEX ) :

select 1 from apex_release where 1=2

Error encountered: ORA-00980

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

select  version_no from apex_release

Error encountered: ORA-00980

Drop those PUBLIC Synonyms

-- synonym to non existing objects , to drop & keeping the synonym ddl

select 'drop public synonym '||B.synonym_name||' ;'

--, B.owner,B.synonym_name,Target_Owner,Target_Object

from (

with A as

( select b.owner , b.synonym_name , b.table_owner , b.table_name , a.status , a.created

from dba_objects a , dba_synonyms b

where b.owner = 'PUBLIC' and a.owner=b.owner and a.object_name = b.synonym_name

)

select A.owner,A.synonym_name,c.owner own_ex,A.table_owner Target_Owner,A.table_name Target_Object

from A LEFT OUTER JOIN dba_objects c

on ( A.table_owner = c.owner and A.table_name = c.object_name)

) B

where B.own_ex is null and Target_Object not like '/%' order by 1

6] Materialized Views

6.1 Atomic Refresh = FALSE ( TRUNCATE ) :

SQL> execute dbms_mview.refresh('<SCH>.VM_JOUR','C',atomic_refresh=>FALSE) ;

BEGIN dbms_mview.refresh('<SCH>.VM_JOUR','C',atomic_refresh=>FALSE) ; END;

*

ERROR at line 1:

ORA-23538: cannot explicitly refresh a NEVER REFRESH materialized view

("VM_JOUR")

alter materialized view <SCH>.VM_JOUR REFRESH FORCE ON DEMAND

6.2 Fast Refresh

CREATE MATERIALIZED VIEW LOG ON APP.SITE WITH ROWID;

CREATE MATERIALIZED VIEW APP.MV_VAL

  NOLOGGING

  CACHE

  BUILD IMMEDIATE

  REFRESH FAST WITH ROWID ON COMMIT AS

SELECT noequipement, name, id_societe, 0 AS typesess

FROM APP.site

WHERE id_typeequipement IN(8, 21)

/

exec DBMS_MVIEW.EXPLAIN_MVIEW (mv => 'SELECT noequipement, name, id_societe, 0 AS typesess

FROM APP.site

WHERE id_typeequipement IN(8, 21)');

7] Locks

7.1 The wonderful DDL_LOCK_TIMEOUT [11G] New Feature

I recently came accross a batch very slow on DELETING few rows on a 10 GB table with a Full Table Scan access : creating an index was vitale to speed up the program.

Infortunately, the multiple DELETEs prevent me to do the job :

ORA-00054  resource busy and acquire with NOWAIT specified or timeout expired

Now, with 11G, get rid of ORA-00054 :

ALTER SESSION SET ddl_lock_timeout=600;

create index IDX on USER.TAB ( COL ) -- resource busy -> index created

It will eventually run between the drops !

7.2 Kill user session that have lock causing "Enqueue TM"

$ cat kill_usr_sess_lock.sql

set serveroutput on

begin

for i in (

with A as ( select distinct owner , count(1) over ( partition by owner ) nb_obj from dba_objects where object_type != 'SYNONYM' )

select distinct username , object_type , owner , object_name  , sid , serial

from (

select b.username username, c.object_type object_type , c.owner owner , c.object_name object_name , b.sid sid, b.serial# serial

from v$locked_object a , v$session b , dba_objects c where a.object_id = c.object_id and b.sid = a.session_id and a.locked_mode = '3'

and username in ( select username from dba_users LEFT OUTER JOIN A on username = owner where nb_obj is null )

) )

 loop

  begin

    DBMS_OUTPUT.PUT_LINE('User: '||i.username||' , Object: '||i.object_type||' '||i.owner||'.'||i.object_name||' , ALTER SYSTEM KILL SESSION '''||i.sid||','||i.serial ||''' ') ;

    EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION '''||i.sid||','||i.serial ||''' ' ;

  end;

 end loop;

end;

/

exit

8] Index

Rebuild Invalid Index for a given Table ( &1 ) with a nice PL loop :

begin

 for i in (select owner,index_name name from dba_indexes where owner = 'SCH' and status != 'VALID' and table_name = '&1' )

  loop

   begin

     DBMS_OUTPUT.PUT_LINE('ALTER index ' || i.owner || '.' || i.name || ' rebuild');

     EXECUTE IMMEDIATE 'ALTER index ' || i.owner || '.' || i.name || ' rebuild';

   end;

  end loop;

end;

/

9] Database Links

How to Drop Database Links without owner’s password :

Shell gets user and link name as arguments.

$ cat schem_drop_dblk.ksh

#!/bin/ksh

username=$1

db_link=$2

sqlplus /nolog <<!

spool $1_$2_drop_dblink.log

conn / as sysdba

prompt " DB Link Before Drop"

set lines 100

col db_link for a30

select owner,db_link from dba_db_links where owner=upper('$1') and db_link=upper('$2') ;

CREATE or replace PROCEDURE $username.drop_db_link AS

BEGIN

EXECUTE IMMEDIATE 'drop database link $2';

END ;

/

execute $username.drop_db_link;

drop procedure $username.drop_db_link;

prompt " DB Link After Drop"

select owner,db_link from dba_db_links where owner=upper('$1') and db_link=upper('$2') ;

!

9] Passwords

Oracle - What is a Password last Change Date ?

Select name,password_date " Password Changed date" from sys.user$ A ,sys.user_history$ B where A.user# = B.user# and A.name='APP_DWH' order by password_date;

Note : USER_HISTORY$ table gets updated only if the user is assigned a profile with password reuse limit (i.e. PASSWORD_REUSE_TIME should not be UNLIMITED)

-- Also, We can query the PTIME column of SYS.USER$ to check when the password was last changed.

SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';

Session altered.

SQL> select PTIME " Password was last changed on" from sys.user$ where NAME='APP_DWH' ;

 Password was last

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

11/04/2017 11:44:11