Scripts

[Motta 645]

Gera script de criação de objetos sem sinonimos publicos :

 

select

('CREATE PUBLIC SYNONYM ' || OBJECT_NAME || ' FOR ' ||

OWNER || '.' || OBJECT_NAME || ';')

From all_objects o1

where

o1.owner = '...' AND

O1.OBJECT_TYPE IN ('TABLE','VIEW','SEQUENCE','PROCEDURE','FUNCTION') AND

SUBSTR(O1.OBJECT_NAME,1,2) NOT IN ('SM','EV') AND

NOT EXISTS

(SELECT 1 FROM all_objects o2

WHERE

O2.OBJECT_TYPE = UPPER('Synonym') AND

O2.OBJECT_NAME = O1.OBJECT_NAME) AND

TRUNC(CREATED) = TRUNC(SYSDATE);

 

Objetos 'mexidos'

 

select object_name,last_ddl_time

from user_objects

where object_type = 'PROCEDURE'

and last_ddl_time between '01-aug-2004' and '04-sep-2004'

and object_name like '%COB%'

ORDER BY 1

 

Objetos que precisam compilar (inválidos)

 

SELECT ('ALTER ' || RPAD(OBJECT_TYPE,9,' ') || ' ' ||

RPAD(OBJECT_NAME,30,' ') || ' COMPILE;') X

FROM ALL_OBJECTS

WHERE STATUS = 'INVALID'

AND OWNER = '...'

AND OBJECT_TYPE IN ('PROCEDURE','FUNCTION','VIEW','TRIGGER')

ORDER BY TIMESTAMP;

 

Recupera sql de sessão user

 

SELECT SQL_TEXT SQL, OPTIMIZER_MODE MODO, OPTIMIZER_COST CUSTO,

USERNAME USUARIO

FROM V$SQL, DBA_USERS

WHERE PARSING_SCHEMA_ID = USER_ID

and username = '...';

 

SELECT *

FROM V$SQL

WHERE TO_DATE(FIRST_LOAD_TIME,'YYYY-MM-DD/HH24:MI:SS') >= TRUNC(SYSDATE)

ORDER BY TO_DATE(FIRST_LOAD_TIME,'YYYY-MM-DD/HH24:MI:SS') DESC

 

Comentarios em colunas

 

SELECT *

FROM USER_COL_COMMENTS

WHERE COMMENTS IS NOT NULL

 

 

Ler uma SP no SqlPlus

 

SELECT TEXT

FROM USER_SOURCE

WHERE NAME = '...'

ORDER BY LINE

 

Verificar lock de Table

 

select substr(o.object_name,1,25) objeto,

l.session_id session_id,

l.oracle_username ora_user,

l.os_user_name os_user

from dba_objects o, v$locked_object l

where l.object_id = o.object_id

order by 1,3,4

 

Meio bagunçado (sem ordem de assunto) mas uteis creio.

[alphamek 2]

Legal Motta....Muito bom mesmo... valeu pela ajuda.Abraços,