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
