ORA STATO E CONFIG
main monitor ddl objects dml stato e configurazione rman & dpump pl/sql cluster ASM
main config start-shut process memory monitor diag
user rman & dpump pl/sql cluster ASM
DB in NOARCHIVE mode
shutdown immediate
startup mount
alter database noarchivelog;
alter database open;
archive log list
Character Set
/* Impostazione per tutte le query */
col value form a32
col parameter form a32
set line 200 pages 4000
SELECT value$ value 
    FROM sys.props$ 
    WHERE name = 'NLS_CHARACTERSET' ;
SELECT * FROM NLS_DATABASE_PARAMETERS;
select DECODE(parameter, 'NLS_CHARACTERSET', 'CHARACTER SET',
        'NLS_LANGUAGE', 'LANGUAGE',
        'NLS_TERRITORY', 'TERRITORY') name,
                value 
    from v$nls_parameters
    WHERE parameter IN ( 'NLS_CHARACTERSET', 'NLS_LANGUAGE', 'NLS_TERRITORY');
Parametri
set line 200 pages 5000 long 999999
col name form a43
col type form a8
col display_value form a64 head VALUE
select name, decode(type, 1, 'bool', 2, 'str', 3, 'int', 4, 'pfile', 5, 'rsrvd', 6, 'bigint') TYPE, display_value 
    from v$parameter 
    order by name;
Componenti installati
COLUMN comp_id    FORMAT a9    HEADING 'Component|ID'
COLUMN comp_name  FORMAT a35   HEADING 'Component|Name'
COLUMN version    FORMAT a13   HEADING 'Version'
COLUMN status     FORMAT a11   HEADING 'Status'
COLUMN modified                HEADING 'Modified'
COLUMN Schema     FORMAT a16    HEADING 'Schema'
COLUMN procedure  FORMAT a41   HEADING 'Procedure'
 
SELECT comp_id, comp_name, version, status, modified, schema, procedure
     FROM dba_registry;
Opzioni installate
/* Lista completa */
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY   OFF
 
COLUMN parameter  FORMAT a45   HEADING 'Option Name'
COLUMN value      FORMAT a10   HEADING 'Installed?'
 
SELECT parameter, value
    FROM v$option
    WHERE upper(parameter) like upper('%&PARAM%')
    ORDER BY parameter;
/* Input quale opzione */
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY   OFF
 
COLUMN parameter  FORMAT a45   HEADING 'Option Name'
COLUMN value      FORMAT a10   HEADING 'Installed?'
 
SELECT parameter, value
    FROM v$option
    WHERE upper(parameter) like upper('%&PARAM%')
    ORDER BY parameter;

Shared Memory Management

SGA Infos
select name, bytes/1024/1024 MB, resizeable from v$sgainfo;
Calcolo Memoria Ottimale
select * from v$sga_target_advice order by sga_size;
Stato dell'istanza
Stato dell'istanza
select status, database_status, instance_mode 
     from v$instance;
List: Parametri e opzioni
col c1 heading 'feature' format a45
col c2 heading 'times|used' format 999,999
col c3 heading 'first|used'
col c4 heading 'used|now'
col c5 heading 'parameter' format a64
col c6 heading 'value' format a16
select name c1, detected_usages c2, first_usage_date c3, currently_used c4
     from dba_feature_usage_statistics
     where first_usage_date is not null;
select comp_name, version 
     from dba_registry
     where status = 'VALID';
select parameter c5, value c6
     from v$option
     order by parameter
/
List: Archive e Flashback
SELECT name, flashback_on, log_mode FROM v$database;
Set: Parametri e log files
alter system set sga_max_size=4G scope=spfile;
alter system set sga_target=4G scope=spfile;
alter system set pga_aggregate_target=1G scope=spfile;
alter system set processes=300 scope=spfile;
alter system set LOG_ARCHIVE_DEST_1= 'LOCATION=/arch_reprman2/REPRMAN2/archive' scope=both;

TABLESPACES

+++++Report Dimensioni e utilizzo TBS

set line 200 pages 500
col tablespace_name for a30
col TOTAL_MB for 999,999,999
col USED_MB like TOTAL_MB
col FREE_MB like TOTAL_MB
break on report
compute sum of TOTAL_MB USED_MB FREE_MB on report 
select     IV1.tablespace_name, 
        a TOTAL_MB, 
        a-c USED_MB, 
        c FREE_MB,
        round(c/a*100,0) FREE_PCENT 
    from     (select tablespace_name, round(sum(bytes)/1048576) a 
                from dba_data_files 
                group by tablespace_name) IV1,
            (select tablespace_name, round(sum(bytes)/1048576) c 
                from dba_free_space 
                group by tablespace_name) IV2 
    where IV1.tablespace_name=IV2.tablespace_name (+) 
    order by 2 desc
/
Datafiles di una tablespace
set line 200 pages 5000 long 9999
col FILE_NAME for a80
select FILE_ID, RFILE#, vd.creation_time, autoextensible, dd.bytes/1024/1024 "size_MB", file_name
    from dba_data_files dd
    INNER JOIN v$datafile vd ON file_id=file#
    where tablespace_name ='AUDTBS';
Resize datafile
 ALTER DATABASE DATAFILE
  '/ora01_kim/KIM_PRO/dwh_staging_own_data01.dbf' RESIZE 10737418240;
Add datafile
alter tablespace USR add datafile '/ora04_nis/NIS_PRO/data/usr_42.dbf' size 8G;
Add datafile on ASM configuration
alter tablespace AUDTBS add datafile '+DATA_PRE' size 30G;
Rename/move REDO
shutdown
connect / as sysdba
startup mount
 
set line 200 pages 5000
col member for a48
select group#, status, type, is_recovery_dest_file, member from v$logfile;
 
alter database rename file '/ora01_dbm/DBM_DEV/redo_a/redo01.log', '/ora02_dbm/DBM_DEV/redo_b/redo02.log'
    to '/ora01_dbm/DBM_DEV/redo_a/redo01a.log', '/ora02_dbm/DBM_DEV/redo_b/redo01b.log';
 
alter database open;

TEMP TABLESPACE

check TEMP files
set line 200
col fid for 999
col fname for a64
col tbs for a24
col fsize_MB for 999,999,999
col aut for a4
col fstatus for a12
select file_id fid, file_name fname, tablespace_name tbs, bytes/1024/1024 fszie_MB, AUTOEXTENSIBLE aut, status fstatus 
from dba_temp_files 
order by tablespace_name;
alter SIZE
alter database tempfile &fileid resize &newsize;
 
alter database tempfile '&filename' resize &newsize;
alter AUTOEXTEND
ALTER DATABASE TEMPFILE &fileid autoextend &newauto;
 
ALTER DATABASE TEMPFILE '&filename' autoextend &newauto;
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License