Very general informations

REM ################################################
REM # Creator: Vincent Fenoll
REM # Created: 2011/07/27
REM # Name: General_infos.sql
REM  ################################################
REM #
REM # Compatible: Oracle 7 8i 9i 10g 11g
REM#
REM ################################################
REM #
REM # Display very general informations about the database
REM #
REM ################################################

SET SERVEROUTPUT ON SIZE 1000000
ttitle off

col basename new_value basename
select instance_name "basename" from v$instance
/
spool general_&basename.log

set lines 130 head off

-- Get the Oracle Home
set autopri on
var oracle_home varchar2(255)
exec dbms_system.get_env('ORACLE_HOME',:ORACLE_HOME)

-- Get if the DB is 32 or 64 bits
select
   length(addr)*4 || '-bits' word_length
from
   v$process
where
   ROWNUM =1;

col name format a40
col value format a60
SELECT name, value FROM V$PARAMETER where ISDEFAULT='FALSE';

set head on
col comp_name format a50
col version format a20
col member format a50
select comp_name, version, status from dba_registry;


SELECT * FROM V$VERSION;

SELECT * FROM V$OPTION;

set lines 150
show parameter background_dump_dest
show parameter user_dump_dest
show parameter core_dump_dest

select name, status from v$tempfile;
select name, status from v$datafile;
select member, status from v$logfile;

PROMPT  Liste des objets invalides...

column object_name	format a35	heading "Nom objet"
column status		format a7	heading "Status"
column owner		format a12	heading "Proprietaire"
column object_type	format a12	heading "Type"
column created		format a15	heading "Date Cree"

SELECT decode(nombre, 0, 'Il n''y a pas d''objets invalides', 'il y a ' || nombre || ' objets invalides') as "Invalidités" 
from (select count(*) nombre FROM ALL_objects WHERE status != 'VALID');

SELECT	object_name, status, object_type, owner, created
FROM	ALL_objects
WHERE	status != 'VALID'
/

PROMPT liste des indexes inutilisables

SELECT DISTINCT index_name, status
           FROM user_indexes
          WHERE status = 'UNUSABLE'
/      

PROMPT Liste des objets invalides

set lines 120
col owner format a20
col OBJECT_NAME format a40
col object_type format a20
select owner, OBJECT_NAME, OBJECT_TYPE from dba_objects where status='INVALID';


PROMPT Upgrades, cpu history
col comments  format a40
set lines 110
select ACTION,VERSION,COMMENTS from registry$history

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.