Voici une astuce pour connaitre la liste des index par table

select * from user_indexes order by table_owner, table_name, index_name;

voici le script qui permet de le faire :

rem ----------------------
rem Liste des TablesSpaces
rem ----------------------

select file_name,tablespace_name,bytes,status from sys.dba_data_files order by tablespace_name;

 col username format a10
 col default_tablespace format a17
 col temporary_tablespace format a17
 col created format a12
 col temporary_tablespace heading Temporary_table
 col default_tablespace heading Default_table

select username, default_tablespace, temporary_tablespace, created from sys.dba_users;

 col segment_name format a12
 col tablespace_name format a15
 col status format a11
 col extents format 999999  
 col max_extents format 999999  
 col bytes format 999,999999
 col max_extents heading MAX_EXT;

select dba_segments.segment_name, dba_segments.tablespace_name, dba_segments.bytes, dba_segments.extents,
 dba_segments.max_extents, dba_rollback_segs.next_extent, dba_rollback_segs.status
 from sys.dba_segments, sys.dba_rollback_segs where dba_segments.segment_type = 'ROLLBACK'
 and dba_rollback_segs.segment_name=dba_segments.segment_name order by dba_segments.tablespace_name;


rem * -----------------------------------------
rem * Place disponible par TableSpace (cumulée)
rem * -----------------------------------------

select tablespace_name,file_id,sum(bytes),sum(blocks) from sys.dba_free_space
 group by tablespace_name,file_id;

 col tablespace_name format a30
 col bytes format 9999,999999
 col sum(bytes) format 9999,999999
 col count(dba_free_space.bytes) heading FREE_EXTENTS    
 col sum(blocks) heading Blocks=2048Bytes

select count(dba_free_space.bytes), dba_free_space.bytes, dba_free_space.tablespace_name,                 
 dba_free_space.file_id from sys.dba_free_space group by dba_free_space.tablespace_name,         
 dba_free_space.file_id,dba_free_space.bytes;                

rem ---------------------- rem LOCKS rem ---------------------- col type format a4 col lmode format 99999 col object_name format a40 select distinct type, lmode, object_name, count(*) from v$lock, sys.dba_objects where id1 = object_id group by type, lmode, object_name;

select table_name,sum(data_length) from user_tab_columns
where table_name in (select table_name from user_tables)
group by table_name order by sum(data_length) desc;

create view size_tab_jf as
select table_name,round(sum(data_length)*1000/1048576,2) as VOL_ENR1_Mo,
round(sum(data_length)*5000/1048576,2) as VOL_ENR5_Mo,
round(sum(data_length)*10000/1048576,2) as VOL_ENR10_Mo
from user_tab_columns where table_name in (select table_name from user_tables)
group by table_name order by sum(data_length) desc;

select sum(vol_enr1_Mo),sum(vol_enr5_Mo),sum(vol_enr10_Mo) from size_tab_jf;

rem analyse comptabilité
rem --------------------

set echo on
spool c:anal_trig.log
set arraysize 30
set pagesize 300
set linesize 132
set numwidth 12
set long 9000
set escape on


select trigger_name,description,trigger_body from user_triggers;

spool off;

rem analyse comptabilité
rem --------------------

set echo on
spool c:anal_vue.log
set arraysize 30
set pagesize 300
set linesize 132
set numwidth 12
set long 9000
set escape on


select view_name,text from user_views;

spool off;

Cela permet de voir si une table a été recréée à tort...

select object_name,object_type,created, last_ddl_time,status from user_objects order by created desc;

set echo off
set heading off
set pagesize 1000
spool c:vol_bas.sql
select 'select count(*) as '||table_name||' from '||' '||table_name||';' from user_tables ;
spool off
set heading on
spool c:vol1.txt
select 'REM utilisateur courant='||username from user_users;
@d:vol_bas
spool off