Tips: Oracle Database Adminstration

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

'SELECT ANY DICTIONARY'

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"
from
(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,
ref.table_name,
s.segment_type,
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'
UNION
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))
ORDER BY 1, 2, 3 DESC

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
V$NLS_PARAMETERS - All NLS parameters like NLS_LANGUAGE; NLS_CHARACTERSET etc.

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'
Resources:

9. Setting ORACLE_HOME in Solaris

ORACLE_HOME=
export ORACLE_HOME
echo $ORACLE_HOME

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:

ALTER DATABASE
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;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - 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]
iv. 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