Historique : Oracle

Oracle : Requêtes utiles

Quelques requêtes utiles dans ma découverte de ce SGBD.

Liste des objets invalides d'une base

[Source >> ]

SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

Compiler tous les objets d'un schéma

[Source >> ]

EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');

Géneration automatique des tables d'un schéma (reverse engineering)

[Source >> ]

set echo off 
set heading off
set pagesize 1000 
set long 10000
spool C:\isbench_alltables.sql
  
select 'create table '||table_name|| ' ( '||
column_name || ' '||data_type 
|| decode(nullable,'N',' NOT NULL') 
|| ' );'
from user_tab_columns
where column_id=1
union
select 'alter table '||table_name
|| ' add ('||column_name||' ' ||data_type||'('||data_length||')'||
' '||decode(nullable,'N',' NOT NULL')|| ');'
from user_tab_columns
where column_id != 1
group by table_name, column_name, data_type, 
data_length,decode(nullable,'N',' NOT NULL')
order by 1 desc ;
spool off
set echo on
set heading on
exit