Coletando informações de uma base Oracle

Rápido e rasteiro:

Vira e mexe você precisa dar uma geral num banco de dados que você nunca viu antes. Bom, então segue abaixo um pequeno script para lhe ajudar na tarefa. Eu, sei, podia melhorar muito, aceito sugestões, claro. Em todo caso, já dá para começar:

SET serveroutput ON SIZE 1000000 FORMAT WRAPPED
SET autotrace    OFF
SET feedback     OFF
SET wrap         OFF
SET trimspool    ON
SET pagesize     100
SET linesize     200

ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YY';

DECLARE
  v_media_archive   number;
  v_inst_num        number;
  v_spfile          varchar(10);
  v_version         v$version.banner%TYPE;
  v_host            v$instance.host_name%TYPE;
  v_instance        v$instance.instance_name%TYPE;
  v_thread          v$instance.thread#%TYPE;
  v_startup_time    v$instance.startup_time%TYPE;
  v_dbid            v$database.dbid%TYPE;
  v_name            v$database.db_unique_name%TYPE;
  v_created         v$database.created%TYPE;
  v_resetlogs_time  v$database.resetlogs_time%TYPE;
  v_log_mode        v$database.log_mode%TYPE;
  v_open_mode       v$database.open_mode%TYPE;
  v_platform        V$database.platform_name%TYPE;

BEGIN
  SELECT DECODE(COUNT(*),0,'PFILE','SPFILE') INTO v_spfile FROM v$spparameter
    WHERE isspecified != 'FALSE';
  SELECT banner INTO v_version FROM v$version  WHERE ROWNUM < 2;
  SELECT UPPER(host_name), UPPER(instance_name), thread#, startup_time
    INTO v_host, v_instance, v_thread, v_startup_time
    FROM gv$instance
  ;
  SELECT count(1) INTO v_inst_num FROM gv$instance;
  SELECT dbid, db_unique_name, created, resetlogs_time, log_mode, open_mode, platform_name
    INTO v_dbid, v_name, v_created, v_resetlogs_time, v_log_mode, v_open_mode, v_platform
    FROM v$database
  ;

  dbms_output.put_line('');
  dbms_output.put_line('Informações de ' || v_name);
  dbms_output.put_line('========================');
  dbms_output.put_line('');
  dbms_output.put_line('Informações Gerais');
  dbms_output.put_line('------------------');
  dbms_output.put_line('DBID..................: ' || v_dbid);
  dbms_output.put_line('Servidor..............: ' || v_host || ' / ' || v_platform);
  dbms_output.put_line('Versão................: ' || v_version);
  dbms_output.put_line('Número de intâncias...: ' || v_inst_num);
  dbms_output.put_line('Instância / Thread....: ' || v_instance || ' / ' || v_thread);
  dbms_output.put_line('Criado em.............: ' || to_char(v_created,'DD/MM/YYYY'));
  dbms_output.put_line('Último RESETLOG.......: ' || to_char(v_resetlogs_time,'DD/MM/YYYY'));
  dbms_output.put_line('Última inicialização..: ' || to_char(v_startup_time,'DD/MM/YYYY'));
  dbms_output.put_line('Inicializado com......: ' || v_spfile);
  dbms_output.put_line('Modo ARCHIVE..........: ' || v_log_mode);
  dbms_output.put_line('Status................: ' || v_open_mode);
  dbms_output.put_line('');

  dbms_output.put_line('Opções ativas');
  dbms_output.put_line('=============');

FOR options IN (SELECT parameter FROM v$option WHERE value = 'TRUE') LOOP
    dbms_output.put_line(options.parameter);
  END LOOP;
  dbms_output.put_line('');

  dbms_output.put_line('Limites utilizados');
  dbms_output.put_line('==================');
  dbms_output.put_line('Inst. | Sessões  | Usuários | CPUs | Cores');
  dbms_output.put_line('------|----------|----------|------|------');

FOR license IN (
  SELECT
    RPAD(inst_id,5) inst_id,
    RPAD(sessions_max,8) AS sessions,
    RPAD(users_max,8) AS users,
    RPAD(cpu_core_count_highwater,4) AS cpu,
    cpu_socket_count_highwater AS socket
      FROM  gv$license
      ORDER BY inst_id
  ) LOOP
    dbms_output.put_line(license.inst_id || ' | ' || license.sessions ||
      ' | ' || license.users || ' | ' || license.cpu || ' | ' || license.socket);
  END LOOP;
  dbms_output.put_line('');

--SELECT * FROM v$configured_interconnects;

  dbms_output.put_line('Localização');
  dbms_output.put_line('===========');
  dbms_output.put_line('Parâmetro            | Valor');
  dbms_output.put_line('---------------------|------');
  FOR nls IN (
    SELECT RPAD(description,20) parameter, property_value
      FROM database_properties
      WHERE property_name IN ('NLS_CHARACTERSET', 'NLS_DATE_FORMAT','NLS_LANGUAGE',
        'NLS_NUMERIC_CHARACTERS', 'NLS_TERRITORY', 'DBTIMEZONE')
  ) LOOP
    dbms_output.put_line(nls.parameter || ' | ' || nls.property_value);
  END LOOP;
  dbms_output.put_line('');

  dbms_output.put_line('Memória');
  dbms_output.put_line('=======');
  dbms_output.put_line('Parâmetro                 | Valor(MB)');
  dbms_output.put_line('--------------------------|----------');
  FOR mem IN (
    SELECT RPAD(name,25) parameter, ROUND(value/1024/1024) valor_mb
      FROM v$parameter
      WHERE name IN ('db_cache_size','large_pool_size','java_pool_size',
        'sga_max_size','shared_pool_size','pga_aggregate_target')
      ORDER BY name
  ) LOOP
    dbms_output.put_line(mem.parameter || ' | ' || mem.valor_mb);
  END LOOP;
  dbms_output.put_line('');

  dbms_output.put_line('REDO');
  dbms_output.put_line('====');
  dbms_output.put_line('Grupo | Tamanho | Arquivo');
  dbms_output.put_line('------|---------|--------');
  FOR log IN (
    SELECT f.GROUP# AS grupo, ROUND(l.bytes/1024/1024) AS tamanho, f.member AS arquivo
      FROM v$logfile f, v$log l
      WHERE f.GROUP# = l.group#) LOOP
    dbms_output.put_line(lpad( log.grupo,5) || ' | ' || lpad(log.tamanho,7) ||
      ' | ' || log.arquivo);
  END LOOP;
  dbms_output.put_line('');

  dbms_output.put_line('Control File');
  dbms_output.put_line('============');
  FOR control IN ( SELECT name FROM v$controlfile) LOOP
    dbms_output.put_line(control.name);
  END LOOP;
  dbms_output.put_line('');

  IF v_log_mode = 'ARCHIVELOG' THEN
    SELECT ROUND(SUM(blocks * block_size) / to_number( MAX(first_time) -
      MIN(first_time)) /1024/1024) media
      INTO v_media_archive
      FROM V$ARCHIVED_LOG;

    dbms_output.put_line('ARCHIVE');
    dbms_output.put_line('=======');
    dbms_output.put_line('Quantidade média de archive gerado: ' || v_media_archive ||
      'MB / dia');
    dbms_output.put_line('');
    dbms_output.put_line('ID | Status     | Tipo       | Destino    | Arquivo');
    dbms_output.put_line('---|------------|------------|------------|--------');

    FOR arch IN (
      SELECT RPAD(dest_id, 2) id, RPAD(STATUS,10) STATUS, RPAD(binding,10) tipo,
        RPAD(target,10) destino, destination arquivo
        FROM v$archive_dest WHERE destination IS NOT NULL) LOOP
      dbms_output.put_line(arch.id || ' | ' || arch.STATUS || ' | ' || arch.tipo ||
        ' | ' || arch.destino || ' | ' || arch.arquivo);
    END LOOP;
    dbms_output.put_line('');
  END IF;

  dbms_output.put_line('Logs do grupo ADMIN');
  dbms_output.put_line('===================');
  dbms_output.put_line('Nome            | Diretório');
  dbms_output.put_line('----------------|----------');
  FOR admin IN (
    SELECT RPAD(name,15) log, value
      FROM v$parameter
      WHERE name IN ('audit_file_dest', 'background_dump_dest', 'core_dump_dest',
        'user_dump_dest')
      ORDER BY NAME) LOOP
    dbms_output.put_line(admin.log || ' | ' || admin.value);
  END LOOP;
  dbms_output.put_line('');

  dbms_output.put_line('Configurações de auditoria e segurança');
  dbms_output.put_line('======================================');
  dbms_output.put_line('Parâmetro       | Valor');
  dbms_output.put_line('----------------|------');
  FOR security IN (
    SELECT RPAD(name,15) log, value
      FROM v$parameter
      WHERE name IN ('audit_trail', 'os_authent_prefix', 'remote_os_authent',
        'remote_login_passwordfile', 'utl_file_dir')
      ORDER BY NAME) LOOP
    dbms_output.put_line(security.log || ' | ' || security.value);
  END LOOP;
  dbms_output.put_line('');

END;
/

SET serveroutput OFF
SET serveroutput ON SIZE 1000000  FORMAT WRAPPED

BEGIN
  dbms_output.put_line('JOBs');
  dbms_output.put_line('====');
  dbms_output.put_line('    Nº | Esquema         | Dur.(min) | BK| Intervalo    ' ||
    '                       | SQL');
  dbms_output.put_line('-------|-----------------|-----------|---|--------------' ||
    '-----------------------|----');
  FOR job IN (
    SELECT LPAD(job,6) id, RPAD(schema_user,15) esquema, RPAD(TRUNC(total_time/60),9) dur_mi,
      broken, RPAD(interval,35) interval, what
      FROM dba_jobs
      WHERE interval !='null'
  ) LOOP
    dbms_output.put_line(job.id || ' | ' || job.esquema || ' | ' || job.dur_mi || ' | '
      || job.broken || ' | ' || job.interval || ' | ' || job.what);
  END LOOP;
  dbms_output.put_line('');
END;
/

SET serveroutput OFF
SET serveroutput ON SIZE 1000000  FORMAT WRAPPED

BEGIN
  dbms_output.put_line('Segmentos por esquema, tablespace e tipo');
  dbms_output.put_line('========================================');
  dbms_output.put_line('Esquema         | Tablespace      | Tipo de Objeto  | QT   | Tam(MB)');
  dbms_output.put_line('----------------|-----------------|-----------------|------|--------');
  FOR schema IN (
    SELECT
      RPAD(owner,15) schema,
      RPAD(tablespace_name, 15) tablespace,
      RPAD(segment_type,15) type,
      LPAD(count(*),4) qt, LPAD(ROUND(SUM(bytes)/1024/1024),6) mb
        FROM dba_segments
        WHERE OWNER NOT IN ('SYS','OUTLN','SYSTEM','WMSYS','XDB','SISMON')
        GROUP BY owner, tablespace_name, segment_type
        ORDER BY owner, tablespace_name, segment_type) LOOP
    dbms_output.put_line(schema.schema || ' | ' || schema.tablespace || ' | '
      || schema.type || ' | ' || schema.qt || ' | ' || schema.mb);
  END LOOP;
  dbms_output.put_line('');
END;
/

SET serveroutput OFF
SET serveroutput ON SIZE 1000000  FORMAT WRAPPED

BEGIN
  dbms_output.put_line('Objetos inválidos por esquema e tipo');
  dbms_output.put_line('====================================');
  dbms_output.put_line('Esquema         | Tipo de Objeto  |  QT');
  dbms_output.put_line('----------------|-----------------|----');
  FOR invalid IN (
    SELECT RPAD(owner,15) AS esquema, RPAD(object_type,15) tipo, LPAD(COUNT(*),4) qt
      FROM dba_objects
      WHERE STATUS != 'VALID'
      GROUP BY owner, object_type) LOOP
    dbms_output.put_line(invalid.esquema || ' | ' || invalid.tipo || ' | ' || invalid.qt);
  END LOOP;
  dbms_output.put_line('');
END;
/

SET serveroutput OFF
SET serveroutput ON SIZE 1000000 FORMAT WRAPPED FORMAT WRAPPED

BEGIN
  dbms_output.put_line('Diretorios');
  dbms_output.put_line('==========');
  dbms_output.put_line('Esquema         | Nome                           | Diretório');
  dbms_output.put_line('----------------|--------------------------------|----------');
  FOR directory IN (
    SELECT
      RPAD(owner,15) AS esquema,
      RPAD(directory_name,30) nome,
      directory_path AS path
        FROM dba_directories ORDER BY owner, path) LOOP
    dbms_output.put_line(directory.esquema || ' | ' || directory.nome || ' | ' ||
      directory.path);
  END LOOP;
  dbms_output.put_line('');

  dbms_output.put_line('Database Links');
  dbms_output.put_line('==============');
  dbms_output.put_line('Esquema         | Nome            | Criacao    |' ||
    ' Esquema remoto  | Host remoto');
  dbms_output.put_line('----------------|-----------------|------------|' ||
    '-----------------|------------');
  FOR dblink IN (
    SELECT
      RPAD(owner,15) AS esquema,
      RPAD(db_link,15) nome,
      RPAD(username,15) esquema_destino,
      host host_destino,
      to_char(created,'DD-MM-YYYY') criacao
        FROM dba_db_links ORDER BY host, owner) LOOP
    dbms_output.put_line(dblink.esquema || ' | ' || dblink.nome || ' | ' ||
      dblink.criacao || ' | ' || dblink.esquema_destino || ' | ' || dblink.host_destino);
  END LOOP;
  dbms_output.put_line('');

  dbms_output.put_line('Visões Materializadas');
  dbms_output.put_line('=====================');
  dbms_output.put_line('Esquema         | Nome            | Q Len | Atualiz. | DBLink');
  dbms_output.put_line('----------------|-----------------|-------|----------|-------');
  FOR mview IN (
    SELECT RPAD(owner,15) esquema, RPAD(mview_name,15) nome, RPAD(master_link,15) link,
      LPAD(query_len,5) len, last_refresh_date FROM dba_mviews
  ) LOOP
    dbms_output.put_line(mview.esquema || ' | ' || mview.nome || ' | ' || mview.len ||
      ' | ' || mview.last_refresh_date || ' | ' || mview.link);
  END LOOP;
  dbms_output.put_line('');

END;
/
SET feedback ON
SET LINESIZE  120