Featured Post

The great debacle of healthcare.gov

This is the first time in history when the president of the United States of America, or probably for any head of state around the world,...

Tuesday, November 3, 2009

Tips: Oracle Database Adminstration

Note: you should have the below session privilege to be able to execute the statements mentioned in this blog.


1. Monitor the running sql statements

SELECT b.SID "Oracle Sid",
a.spid "Unix PID",
b.serial# "Oracle Serial",
c.sql_text "SQL Text",
b.username "Oracle User",
b.osuser "OS User",
b.machine "Computer",
b.program "Program",
TO_CHAR (logon_time, 'HH24:MI:SS,yyyy/mm/dd') "Login Time",
b.action "Session Action",
c.action "SQL Action",
lockwait "LockWait",
status "Status",
optimizer_cost "Optimizer_cost"
FROM v$process a, v$session b, v$sql c
WHERE a.addr = b.paddr
AND b.sql_address = c.address
AND b.sql_hash_value = c.hash_value;

2. Find the size of tablespace

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name

3. Size of tables

SELECT s.owner,
CAST(SUM(s.bytes)/1048576 AS NUMBER(17,5)) MB
FROM dba_segments@database s,
(SELECT t.table_name,
t.table_name seg_name,
'TABLE' seg_type
FROM dba_tables@database t
WHERE t.owner = 'schema_name'
SELECT i.table_name,
i.index_name seg_name,
'INDEX' seg_type
FROM dba_indexes@database i
WHERE i.table_owner = 'schema_name'
) ref
WHERE s.segment_name = ref.seg_name
AND s.segment_type = ref.seg_type
GROUP BY GROUPING SETS((s.owner, ref.table_name, s.segment_type),(ref.table_name))

Size of individual database objects (e.g. Indexes,Tables etc.)
SELECT sum(bytes)/1048576 Megs, segment_name
FROM user_extents
WHERE segment_name = ''
GROUP BY segment_name

4. Associated tablespace name of tables

select table_name, tablespace_name
from user_tables
where table_name = 'table_name'

5. Default tablespace name of the schema

select username, default_tablespace
from dba_users
where username = 'schema_name'

6. All locked objects:
SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status,
b.osuser, b.machine
FROM v$locked_object a, v$session b, dba_objects c
WHERE b.SID = a.session_id AND a.object_id = c.object_id;

7. Frequently used DBA views:

DBA_TABLESPACES - To view the configuration of the tablespces in the database. e.g. name, size, free and used space, etc.
DBA_DATA_FILES - Data files used by the tablespaces and current statistics
DBA_SEGEMENTS - Holds the Segments information. e.g. Segment name, tablespace name, used and free bytes, etc.
DBA_INDEXES - Information of all indexes (except partitioned indexes) e.g. Index name, tablespace name, etc.
DBA_PART_INDEXES - Holds the information of the partitioned indexes. e.g. Index name, partition type, index partition type etc.
DBA_IND_PARTITIONS - Holds the partitioned index detail (including the tablespace name)
DBA_EXTENTS - Contain information on database extents for each database objects i.e. tables, indexes etc.
DBA_CONSTRAINTS - Holds the definition and detail of constraints created in the system. uses example: find the table to which foreign keys referencing to
V$PARAMETER - All database parameters e.g. recyclebin; sga_max_size

8. Kill an Oracle session
search for the session you want to kill: select s.sid, s.serial#, s.username from v$session
kill the session: alter system kill session 'sid, serial#' -- in 11g, use 'sid,serial#,@inst_id'

9. Setting ORACLE_HOME in Solaris


10. to run Operating System/Shell command from sqlplus console use host keyword
e.g. host set ORACLE_HOME to check oracle home directory on MS Windows

11. Add space to tabalespace

To existing datafile:

DATAFILE ‘[../data. dbf]’
RESIZE [size in M];
Add new datafile:
ALTER TABLESPACE [tablespace_name]
ADD DATAFILE ‘[../data. dbf]’
SIZE [Size in M];

12. How to get the version of Oracle database installed -

i. SQL> select * from v$version;

Oracle Database 11g Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
CORE      Production
TNS for Linux: Version - Production
NLSRTL Version - Production

ii. check SQLNET.LOG file

# Frequently used User management commands:
i. alter user [user_name] account lock/unlock
ii. select password from dba_users where user_name = [user_name]
iii. alter user [user_name] identified by [newpass]
select_any_dictionary privilege is required to be able to view dba views

http://www.experts-exchange.com/Database/Oracle/Q_23065829.html - Killing a running statement
http://www.benh.org/techblog/2006/07/finding-locked-objects-in-oracle/ - locked session
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/start001.htm - starting/stopping Oracle database 11g
http://mhabib.wordpress.com/2007/03/31/increase-the-size-of-tablespace/ - increase tablespace size

No comments: