Oracle 101
Oracle 101
another good oracle reference:
Version information
SELECT * FROM product_component_version ;
--------------------------------------------------------------------------------
List free and used space in database
SELECT sum(bytes)/1024 "free space in KB"
FROM dba_free_space;
SELECT sum(bytes)/1024 "used space in KB"
FROM dba_segments;
--------------------------------------------------------------------------------
List session information
SELECT * FROM V$SESSION ;
--------------------------------------------------------------------------------
List names and default storage parameters for all tablespaces
SELECT TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENTS,
PCT_INCREASE, MIN_EXTLEN
FROM DBA_TABLESPACES;
--------------------------------------------------------------------------------
Tablespace types, and availability of data files
SELECT TABLESPACE_NAME, CONTENTS, STATUS
FROM DBA_TABLESPACES;
--------------------------------------------------------------------------------
List information about tablespace to which datafiles belong
SELECT FILE_NAME,TABLESPACE_NAME,BYTES,AUTOEXTENSIBLE,
MAXBYTES,INCREMENT_BY
FROM DBA_DATA_FILES;
--------------------------------------------------------------------------------
List data file information
SELECT FILE#,T1.NAME,STATUS,ENABLED,BYTES,CREATE_BYTES,T2.NAME
FROM V$DATAFILE T1, V$TABLESPACE T2
WHERE T1.TS# = T2.TS# ;
--------------------------------------------------------------------------------
List tablespace fragmentation information
SELECT tablespace_name,COUNT(*) AS fragments,
SUM(bytes) AS total,
MAX(bytes) AS largest
FROM dba_free_space
GROUP BY tablespace_name;
--------------------------------------------------------------------------------
Check the current number of extents and blocks allocated to a segment
SELECT SEGMENT_NAME,TABLESPACE_NAME,EXTENTS,BLOCKS
FROM DBA_SEGMENTS;
--------------------------------------------------------------------------------
Check the extents for a given segment
SELECT TABLESPACE_NAME, COUNT(*), MAX(BLOCKS), SUM(BLOCKS)
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NUMBER ;
--------------------------------------------------------------------------------
Extent information
SELECT segment_name, extent_id, blocks, bytes
FROM dba_extents
WHERE segment_name = TNAME ;
--------------------------------------------------------------------------------
Extent information for a table
SELECT segment_name, extent_id, blocks, bytes
FROM dba_extents
WHERE segment_name = TNAME ;
--------------------------------------------------------------------------------
List segments with fewer than 5 extents remaining
SELECT segment_name,segment_type,
max_extents, extents
FROM dba_segments
WHERE extents+5 > max_extents
AND segment_type<>'CACHE';
--------------------------------------------------------------------------------
List segments reaching extent limits
SELECT s.segment_name,s.segment_type,s.tablespace_name,s.next_extent
FROM dba_segments s
WHERE NOT EXISTS (SELECT 1
FROM dba_free_space f
WHERE s.tablespace_name=f.tablespace_name
HAVING max(f.bytes) > s.next_extent);
--------------------------------------------------------------------------------
List table blocks, empty blocks, extent count, and chain block count
SELECT blocks as BLOCKS_USED, empty_blocks
FROM dba_tables
WHERE table_name=TNAME;
SELECT chain_cnt AS CHAINED_BLOCKS
FROM dba_tables
WHERE table_name=TNAME;
SELECT COUNT(*) AS EXTENT_COUNT
FROM dba_extents
WHERE segment_name=TNAME;
--------------------------------------------------------------------------------
Information about all rollback segments in the database
SELECT SEGMENT_NAME,TABLESPACE_NAME,OWNER,STATUS
FROM DBA_ROLLBACK_SEGS;
/* General Rollback Segment Information */
SELECT t1.name, t2.extents, t2.rssize, t2.optsize, t2.hwmsize, t2.xacts, t2.status
FROM v$rollname t1, v$rollstat t2
WHERE t2.usn = t1.usn ;
/* Rollback Segment Information - Active Sessions */
select t2.username, t1.xidusn, t1.ubafil, t1.ubablk, t2.used_ublk
from v$session t2, v$transaction t1
where t2.saddr = t1.ses_addr
--------------------------------------------------------------------------------
Statistics of the rollback segments currently used by instance
SELECT T1.NAME, T2.EXTENTS, T2.RSSIZE, T2.OPTSIZE, T2.HWMSIZE,
T2.XACTS, T2.STATUS
FROM V$ROLLNAME T1, V$ROLLSTAT T2
WHERE T1.USN = T2.USN AND
T1.NAME LIKE '%RBS%';
--------------------------------------------------------------------------------
List sessions with active transactions
SELECT s.sid, s.serial#
FROM v$session s
WHERE s.saddr in
(SELECT t.ses_addr
FROM V$transaction t, dba_rollback_segs r
WHERE t.xidusn=r.segment_id
AND r.tablespace_name='RBS');
--------------------------------------------------------------------------------
Active sorts in instance
SELECT T1.USERNAME, T2.TABLESPACE, T2.CONTENTS, T2.EXTENTS, T2.BLOCKS
FROM V$SESSION T1, V$SORT_USAGE T2
WHERE T1.SADDR = T2.SESSION_ADDR ;
--------------------------------------------------------------------------------
Index & constraint information
SELECT index_name,table_name,uniqueness
FROM dba_indexes
WHERE index_name in
(SELECT constraint_name
FROM dba_constraints
WHERE table_name = TNAME
AND constraint_type in ('P','U')) ;
--------------------------------------------------------------------------------
Updating statistics for a table or schema
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA1','COMPANY');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA1');
ANALYZE TABLE COMPANY COMPUTE STATISTICS ;
--------------------------------------------------------------------------------
List tables and synonyms
set pagesize 0;
select 'TABLE:',table_name,'current' from user_tables
union
select 'SYNONYM:',synonym_name,table_owner from user_synonyms
order by 1,2 ;
--------------------------------------------------------------------------------
Constraint columns
SELECT constraint_name,table_name, column_name
FROM dba_cons_columns
WHERE table_name = TNAME
ORDER BY table_name, constraint_name, position
END IF;
--------------------------------------------------------------------------------
Constraint listing
SELECT constraint_name, table_name,
constraint_type, validated, status
FROM dba_constraints;
--------------------------------------------------------------------------------
Indexed column listing
select
b.uniqueness, a.index_name, a.table_name, a.column_name
from user_ind_columns a, user_indexes b
where a.index_name=b.index_name
order by a.table_name, a.index_name, a.column_position;
--------------------------------------------------------------------------------
Trigger listing
SELECT trigger_name, status
FROM dba_triggers ;
--------------------------------------------------------------------------------
Tuning: library cache
Glossary:
pins = # of time an item in the library cache was executed
reloads = # of library cache misses on execution
Goal:
get hitratio to be less than 1
Tuning parm:
adjust SHARED_POOL_SIZE in the initxx.ora file, increasing by small increments
SELECT SUM(PINS) EXECS,
SUM(RELOADS)MISSES,
SUM(RELOADS)/SUM(PINS) HITRATIO
FROM V$LIBRARYCACHE ;
--------------------------------------------------------------------------------
Tuning: data dictionary cache
Glossary:
gets = # of requests for the item
getmisses = # of requests for items in cache which missed
Goal:
get rcratio to be less than 1
Tuning parm:
adjust SHARED_POOL_SIZE in the initxx.ora file, increasing by small increments
SELECT SUM(GETS) HITS,
SUM(GETMISSES) LIBMISS,
SUM(GETMISSES)/SUM(GETS) RCRATIO
FROM V$ROWCACHE ;
--------------------------------------------------------------------------------
Tuning: buffer cache
Calculation:
buffer cache hit ratio = 1 - (phy reads/(db_block_gets + consistent_gets))
Goal:
get hit ratio in the range 85 - 90%
Tuning parm:
adjust DB_BLOCK_BUFFERS in the initxx.ora file, increasing by small increments
SELECT NAME, VALUE
FROM V$SYSSTAT WHERE NAME IN
('DB BLOCK GETS','CONSISTENT GETS','PHYSICAL READS');
--------------------------------------------------------------------------------
Tuning: sorts
Goal:
Increase number of memory sorts vs disk sorts
Tuning parm:
adjust SORT_AREA_SIZE in the initxx.ora file, increasing by small increments
SELECT NAME, VALUE
FROM V$SYSTAT
WHERE NAME LIKE '%SORT%';
--------------------------------------------------------------------------------
Tuning: dynamic extension
An informational query.
SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME='RECURSIVE CALLS' ;
--------------------------------------------------------------------------------
Tuning: rollback segments
Goal:
Try to avoid increasing 'undo header' counts
Tuning method:
Create more rollback segments, try to reduce counts
SELECT CLASS,COUNT
FROM V$WAITSTAT
WHERE CLASS LIKE '%UNDO%' ;
--------------------------------------------------------------------------------
Tuning: physical file placement
Informational in checking relative usages of the physical data files.
SELECT NAME, PHYRDS,PHYWRTS
FROM V$DATAFILE DF, V$FILESTAT FS
WHERE DF.FILE#=FS.FILE# ;
--------------------------------------------------------------------------------
Killing Sessions
Runaway processes can be killed on the UNIX side, or within server manager.
/* Kill a session, specified by the returned sess-id / serial number */
SELECT sid, serial#, username from v$session
ALTER SYSTEM KILL SESSION 'sessid,ser#'
--------------------------------------------------------------------------------
Archive Log Mode Status
/* Status of Archive Log Subsystem */
ARCHIVE LOG LIST
/* log mode of databases */
SELECT name, log_mode FROM v$database;
/* log mode of instance */
SELECT archiver FROM v$instance;
--------------------------------------------------------------------------------
Recovering an Instance
An incomplete recovery is the only option if backups are run periodically on a cold instance. Complete recovery is possible if archive logging is enabled, and backups are run while the database is active.
/* diagnose data file problem */
select * from v$recover_file ;
/* diagnose data file problem, by displaying tablespace info */
select file_id, file_name, tablespace_name, status
from dba_data_files ;
/* find archive log files */
select * from v$recovery_log ;
/* incomplete recovery #1 */
svrmgrl> shutdown abort
[[ In Unix copy data files from backup area to data directory(s). ]]
svrmgrl> connect;
svrmgrl> startup;
/* incomplete recovery #2 */
svrmgrl> shutdown abort;
svrmgrl> connect;
svrmgrl> startup mount;
svrmgrl> alter database rename file '/data2/ts05.dbf' to '/backups/ts05.dbf'
svrmgrl> alter database open;
/* incomplete recovery #3, for user error (i.e. drop table ) */
Note: archive logs must exist in LOG_ARCHIVE_DEST
svrmgrl> shutdown abort
[[ backup all files ]]
[[ restore required data file(s), using OS commands ]]
svrmgrl> connect;
svrmgrl> startup mount;
svrmgrl> recover database until time '2002-03-04:15:00:00' ;
svrmgrl> alter database open resetlogs;
/* complete recovery #1, for major recovery operations, closed instance */
Note: archive logs must exist in LOG_ARCHIVE_DEST
svrmgrl> shutdown abort
[[ backup all files ]]
svrmgrl> connect;
svrmgrl> startup mount;
svrmgrl> recover database ;
< or >
svrmgrl> recover datafile '/data4/ts03.dbf'
svrmgrl> startup open;
/* complete recovery #2, for major/minor recovery operations, open instance */
Note: archive logs must exist in LOG_ARCHIVE_DEST
svrmgrl> shutdown abort
[[ backup all files ]]
[[ restore corrupted data files, using OS commands ]]
svrmgrl> connect;
svrmgrl> startup mount;
svrmgrl> set autorecovery on ;
svrmgrl> recover tablespace ts03 ;
< or >
svrmgrl> recover datafile 4 ;
svrmgrl> startup open;
--------------------------------------------------------------------------------
List log file information
These queries list the status / locations of the redo log files.
select group#, member, status from v$logfile ;
select group#,thread#,archived,status from v$log ;
--------------------------------------------------------------------------------
A Simple Monitoring Tool
This tool loops a specified number of times, displaying memory usage along with user process counts for a specific username.
--=================================================
--
-- proc_ora_monitor
--
-- parm1: username to count
-- parm2: number of loops, 5 sec duration
--
--
--=================================================
set serveroutput on ;
create or replace procedure
proc_ora_monitor ( user1 in varchar, reps1 in integer )
is
i number ;
usercount1 number ;
memory1 number ;
date1 varchar(20) ;
msg varchar(99) ;
begin
i := 0 ;
while ( i < reps1 )
loop
msg := '=> ' || to_char(SYSDATE, 'HH:MM:SS PM');
select count(1)
into usercount1
from sys.v_$session
where username = user1 ;
msg := msg || ', ' || user1 || ': ' || usercount1 ;
select round(sum(bytes)/1024/1024 ,2)
into memory1
from sys.v_$sgastat
where pool = 'shared pool' and
name = 'free memory' ;
msg := msg || ', free mb = ' || memory1 ;
select round(sum(bytes)/1024/1024 ,2)
into memory1
from sys.v_$sgastat
where pool = 'shared pool' and
name = 'processes' ;
msg := msg || ', processes mb = ' || memory1 ;
dbms_output.put_line(msg) ;
dbms_lock.sleep(5) ;
i := i + 1 ;
end loop ;
end;
/
show errors ;
execute proc_ora_monitor('SILVERUSER',2) ;
exit
--------------------------------------------------------------------------------
Connection Errors
-------------------------------------------------------
ORA-01034: ORACLE not available
-------------------------------------------------------
TNS-12564: TNS:connection refused
-------------------------------------------------------
TNS-12530: Unable to start a dedicated server process
-------------------------------------------------------
Connection errors can crop up out of nowhere ; the error message tend to be vague, and not useful at all. Here's a plan of attack which will solve many connection issues. Try each step, and proceed if the problem persists.
1) Check your environment ; verify the variables depicted below are set.
( NT: check the registry )
The example below details a Solaris/CSH environment.
Note the TWO_TASK setting ...
setenv ORACLE_BASE /apps/oracle
setenv ORACLE_HOME ${ORACLE_BASE}
setenv ORACLE_SID db22
setenv TWO_TASK $ORACLE_SID
setenv LD_LIBRARY_PATH $ORACLE_HOME/lib:/usr/lib/X11
setenv ORACLE_PATH $ORACLE_HOME/bin:/usr/bin:/usr/local/bin
setenv ORA_CLIENT_LIB shared
set path = ($ORACLE_HOME/bin /bin /usr/bin /usr/local/bin /sbin /usr/sbin /usr/bin/X11 .)
2) Try to ping the instance:
tnsping db22
If there's an error, check $ORACLE_HOME/network/admin/tnsnames.ora
3) Restart the TNS service.
Solaris:
1) kill the process, running the tnslsnr binary
2) nohup $ORACLE_HOME/bin/tnslsnr start &
NT:
1) restart the service, in the control panel
4) SQL-Plus / ServerMgr
Try using this syntax: sqlplus user/password@instance
5) Solaris, shell change
Try switching the oracle user to the Bourne or Csh shell ; make a script for SQL-Plus
as follow:
#!/usr/bin/csh
setenv ORACLE_BASE /apps/oracle
setenv ORACLE_HOME ${ORACLE_BASE}
setenv ORACLE_SID db22
setenv LD_LIBRARY_PATH $ORACLE_HOME/lib:/usr/lib/X11
setenv ORACLE_PATH $ORACLE_HOME/bin:/usr/bin:/usr/local/bin
setenv ORA_CLIENT_LIB shared
setenv TWO_TASK $ORACLE_SID
set path = ($ORACLE_HOME/bin /bin /usr/bin /usr/local/bin /sbin /usr/sbin /usr/bin/X11 .)
sqlplus $1 $2 $3
# OR
#sqlplus $1@$ORACLE_SID
Also verify the oracle user owns the oracle directory tree.
6) Check the pfile, verify the settings detailed below. For this example,
the machine should have at least 512mb of memory, to handle the OS and
other processes.
# 100 MB shared pool memory
shared_pool_size = 104857600
# 65 processes need 130 MB of additional memory
processes = 65
sessions = 65
Solaris: check the "shared memory" and "semaphores" settings
also, in the /etc/system file.
7) Look at sqlnet.log ; also check the alert log in
$ORACLE_HOME/admin/$ORACLE_SID/bdump
8) Verify the Oracle version, SQLNet version, and patched OS are all compatible.
9) If the problem is still a mystery, the server may need to be restarted.
--------------------------------------------------------------------------------
Explain Plan: syntax
Below is sample syntax for explain plan ( getting output from the optimizer )
delete from plan_table
where statement_id = '9999';
commit;
COL operation FORMAT A30
COL options FORMAT A15
COL object_name FORMAT A20
/* ------ Your SQL here ------*/
EXPLAIN PLAN set statement_id = '9999' for
select count(1) from asia_monthly_pricing_data where order_id > 5000
/
/*----------------------------*/
select operation, options, object_name
from plan_table
where statement_id = '9999'
start with id = 0
connect by prior id=parent_id and prior statement_id = statement_id;
exit
/
posted by John Brahy @ 8:49 AM
0 comments
