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 |
Table of Contents
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
col value form a32 col parameter form a32 set line 200 pages 4000 SELECT name, value$ value FROM sys.props$ WHERE name like 'NLS%' ;
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;
page revision: 20, last edited: 12 Oct 2017 14:37