Oracle DBA Scripts

When we last gathered stats in Oracle 11G

2010-08-27T07:22:00.000-07:00

select * from dba_optstat_operations;

To see the current retention value,

select dbms_stats.get_stats_history_retention from dual;

BIND VARIABLE PEEKING

2010-08-23T12:42:00.001-07:00

There are 3 things that might put you at risk of unstable plans due to bind variable peeking.
1. Histograms: The histogram tells Oracle about data distribution
2. Partitions,
3. Range-based predicates

select column_name, endpoint_number, endpoint_value
from dba_tab_histograms
where table_name='TEST_INVOICE';

Just get rid of any histogram that does nothing but messes up your execution plans.

exec dbms_stats.gather_table_stats('table_owner', 'test_invoice', method_opt => 'for columns invoice_id size 1', cascade => true);

shrink the undo tablespace to free up some disk space after some unusual database activity

2010-08-20T06:45:00.000-07:00

1. define a new temporary undo tablespace
CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE '/u01/oradata/xxxx/undotbs201.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10240K MAXSIZE 32767M ;
2. issue the alter system command to point to the temporary tablespace
alter system set undo_tablespace = UNDOTBS2 ;
3. drop the original undo tablespace including datafile
drop tablespace "UNDOTBS" INCLUDING CONTENTS AND DATAFILES ;
4. recreate the original one using the original name ( optional and size according to your needs )
CREATE UNDO TABLESPACE "UNDOTBS" DATAFILE '/u01/oradata/xxxx/undotbs01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10240K MAXSIZE 32767M ;
5. Repeat step 2 to switch back to the original named tablespace
6. drop the tablespace created in step 1 using the syntax in step 3

shrink table in Oracle 10g/11g

2010-07-16T18:07:00.000-07:00

In oracle 9i and below you need to export the table, drop the table and import the table to reclaim the unused space. In oracle 10g you need to perform following sql commands to reclaim the table unused space.

SQL>alter table sachi enable row movement;
SQL>alter table sachi shrink space;
SQL>alter table sachi shrink space cascade;(shrink tables and indexes on this tables)

That's it.

Finding Candidates for Shrinking:

you can use Oracle 10g/11g segment advisor to check if particular table or index requires shrinking.

Before performing an online shrink, you may want to find out the biggest bang-for-the-buck by identifying the segments that can be most
fully compressed. Simply use the built-in function verify_shrink_candidate in the package dbms_space.
Execute this PL/SQL code to test if the segment can be shrunk to 1,500,000 bytes:

begin
if (dbms_space.verify_shrink_candidate
('GUEST','SACHI','TABLE',1500000)
) then
:x := 'T';
else
:x := 'F';
end if;
end;
/

PL/SQL procedure successfully completed.

SQL> print x

X
--------------------------------
T

If you use a low number for the target shrinkage, say 10,0000:

begin
if (dbms_space.verify_shrink_candidate
('GUEST','SACHI','TABLE',100000)
) then
:x := 'T';
else
:x := 'F';
end if;
end;

the value of the variable x is set to 'F', meaning the table cannot be shrunk to 10,0000 bytes.

Summary:
1.Create table As Select (CTAS) - This copies the table rows into a clean area, lowering the high-water-mark, packing the rows densely (as dictated by PCTFREE) and releasing free space.
2.Online reorg - Using the dbms_redefinition package you can use parallel CTAS to reorganize tables, while the tables continue to accept updates.
3.Data pump (expdp, impdp) - Rarely used in high-speed production apps, except for backups.
4.Alter table move - The alter table xxx move command moves rows down into un-used space and adjusts the HWM but does not adjust the segments extents, and the table size remains the same. The alter table move syntax also preserves the index and constraint definitions.
5.Alter table shrink space - Using the "alter table xxx shrink space compact" command will re-pack the rows, move down the HWM, and releases unused extents. With standard Oracle tables, you can reclaim space with the "alter table shrink space" command

Problem with "library cache pin' when deplyoing the package

2010-07-15T05:55:00.000-07:00

Problem: Today i have been asked to deploy a package in the production. Same package has been deployed in staging and UAT successfully with in a minute. When I deployed the same package in production it did not go through. after 10 min I got following error.

SQL> @PKGALERTS.pkb;
CREATE OR REPLACE PACKAGE BODY Pkgalerts AS
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object

I tried it couple of time but got the same error again. Please help.

Solution:

select sid, event, p1raw, seconds_in_wait,wait_time
from v$session_wait
where event = 'library cache pin'
and state = 'WAITING';

SID,EVENT,P1RAW,SECONDS_IN_WAIT,WAIT_TIME
4962,library cache pin,070000031733BDF8,51,0

SELECT kglnaown "Owner", kglnaobj "Object"
FROM x$kglob WHERE kglhdadr='070000031733BDF8'

SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.status,
kglpnmod "Mode", kglpnreq "Req"
FROM x$kglpn p, v$session s WHERE p.kglpnuse=s.saddr AND
kglpnhdl='070000031733BDF8'

SELECT 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';'
FROM x$kglpn p, v$session s
WHERE p.kglpnuse=s.saddr
AND kglpnhdl='070000031733BDF8'

and then compile the object.
SQL> @PKGALERTS.pkb

Package body created.

Oracle Critical File Protection

2010-01-13T10:48:00.000-08:00

-- Step 1: Collect the file locations for control, redo log, and data files:
-- Step 2: Check file permission

Select 'Control Files: '||value from v$parameter where name= 'control_files';
Select 'Control Files: '||value from v$parameter where name= 'spfile';
select 'Logfile: '||member from v$logfile;
select 'Datafile: '||name from v$datafile;

Oracle Redo log files

2010-01-13T10:47:00.001-08:00

-- Step 1: Check how many redo log files exist.

select member from v$logfile;

-- Step 2: Check whether each group has more than 1 members.

select count(*) from V$LOG where members >1;

Auditing of Commands

2010-01-13T10:46:00.001-08:00

SELECT name
FROM stmt_audit_option_map
WHERE name not in (select audit_option from dba_stmt_audit_opts)
AND name not like 'SELECT%'
AND name not like 'INSERT%'
AND name not like 'UPDATE%'
AND name not like 'DELETE%'
AND name not like 'EXECUTE%'
AND name not like 'LOCK%'
AND name not in
('CREATE DIRECTORY','DROP DIRECTORY','GRANT LIBRARY',
'WRITEDOWN DBLOW','READUP DBHIGH','WRITEUP DBHIGH',
'WRITEDOWN','READUP','WRITEUP','EXISTS','SUMMARY',
'NETWORK','DEBUG CONNECT USER','DEBUG CONNECT ANY',
'CREATE CLUSTER','CREATE DATABASE LINK','CREATE DIMENSION',
'CREATE LIBRARY','CREATE PROCEDURE','CREATE ROLE',
'CREATE SEQUENCE','CREATE TABLE','CREATE TRIGGER',
'CREATE TYPE','CREATE VIEW')
AND decode(name, 'TABLESPACE', 3,
'PUBLIC DATABASE LINK', 2,
'PUBLIC SYNONYM', 2,
'ROLLBACK SEGMENT', 3,
'TABLESPACE', 3,
'USER', 3, 100) >
(SELECT COUNT(*)
FROM dba_stmt_audit_opts
WHERE audit_option LIKE '%'||name||'%')
UNION ALL
select name from system_privilege_map
where name not in
(select privilege from dba_priv_audit_opts)
and name not in (select name from stmt_audit_option_map)
and name not in ('EXTENDS TYPE','ALTER OPERATOR')
and name not like '%ROLE%'
and name not like '%SECURITY%'
and name not like '%EXECUTE%'
and name not like '%RULE%';

Audit Table Permissions

2010-01-13T10:45:00.002-08:00

select grantee from dba_tab_privs
where table_name='AUD$'
and grantee not in ('DELETE_CATALOG_ROLE')
and grantee not in
(select grantee from dba_role_privs where granted_role='DBA')
order by grantee;

Account Permissions

2010-01-13T10:45:00.001-08:00

select grantee||': '||privilege||': '||owner||'.'||table_name
from dba_tab_privs where grantee not in (select role from dba_roles)
and grantee not in ('ORDSYS','OUTLN','PUBLIC','SYS','SYSTEM',
'CTXSYS','MDSYS','ODM','OLAPSYS','WKSYS','WMSYS','XDB','LBACSYS',
'OSE$HTTP$ADMIN','AURORA$JIS$UTILITY$','REPADMIN','PERFSTAT','SYSMAN')
and table_name<>'DBMS_REPCAT_INTERNAL_PACKAGE'
and table_name not like '%RP'
and grantee not in
(select grantee from dba_tab_privs where table_name in ('DBMS_DEFER','DEFLOB'))
and grantee not in (select grantee from dba_role_privs where granted_role='DBA');

Resource Limits Not Enabled

2010-01-13T10:44:00.001-08:00

show parameter resource_limit;

-- OR

Select value from v$parameter where name='resource_limit';

REMOTE_LOGIN_PASSWORDFILE

2010-01-13T10:43:00.002-08:00

Show parameter remote_login_passwordfile;

-- OR

Select value from v$parameter where name='remote_login_passwordfile';

Failed Login Attempts

2010-01-13T10:43:00.001-08:00

select profile||': '||limit from dba_profiles,
(SELECT limit AS def_login_attmpts
FROM dba_profiles
WHERE profile = 'DEFAULT'
AND resource_name = 'FAILED_LOGIN_ATTEMPTS')
WHERE resource_name='FAILED_LOGIN_ATTEMPTS'
AND ((replace(limit,'DEFAULT',def_login_attmpts) IN ('UNLIMITED',NULL))
OR (lpad(replace(limit,'DEFAULT',def_login_attmpts),40,'0') >
lpad('3',40,'0')));

Idle Time Resource Usage Limit

2010-01-13T10:42:00.002-08:00

Select limit from DBA_PROFILES where profile='DEFAULT' and resource_name='IDLE_TIME';
select profile||': '||limit
FROM dba_profiles,
(SELECT limit AS def_idl_tm
FROM dba_profiles
WHERE profile = 'DEFAULT'
AND resource_name = 'IDLE_TIME')
WHERE resource_name='IDLE_TIME'
AND ((replace(limit,'DEFAULT',def_idl_tm) IN ('UNLIMITED', NULL))
OR (lpad(replace(limit,'DEFAULT',def_idl_tm),40,'0') >
lpad('15',40,'0')));

users or roles granted ALL PRIVILEGES or too many privileges

2010-01-13T10:42:00.001-08:00

-- Check for all privs:

(select count(*) from system_privilege_map) intersect
(select count(*) from dba_sys_privs group by grantee);

-- Check for number of privs in case too many:

select count(*) from system_privilege_map;

-- Check how many privileges each account has:

select count(*),grantee
from dba_sys_privs
group by grantee;

PUBLIC is granted with Object Permissions

2010-01-13T10:41:00.002-08:00

select owner||'.'||table_name||': '||privilege from dba_tab_privs
where grantee='PUBLIC'
and owner not in('SYS','CTXSYS','MDSYS','ODM','OLAPSYS','MTSSYS',
'ORDPLUGINS','ORDSYS','SYSTEM','WKSYS','WMSYS','XDB','LBACSYS',
'PERFSTAT','SYSMAN','DMSYS','EXFSYS');

Roles Granted With Admin

2010-01-13T10:41:00.001-08:00

select grantee||': '||granted_role from dba_role_privs
where grantee not in('DBA','SYS','SYSTEM','WKSYS','LBACSYS','WMSYS')
and admin_option='YES'
and grantee not in (select distinct owner from dba_objects)
and grantee not in (select grantee from dba_role_privs where granted_role='DBA');

PUBLIC is granted with System Privileges

2010-01-13T10:40:00.002-08:00

select privilege from dba_sys_privs where grantee='PUBLIC';

Check which user has been granted with the 'CONNECT' role

2010-01-13T10:40:00.001-08:00

select grantee
from dba_role_privs r, dba_users u
where granted_role ='CONNECT' and
r.grantee = u.username and
u.account_status not like '%LOCKED%' and
grantee not in ('SYS','SYSTEM','DBSNMP')
union
select grantee
from dba_role_privs r, dba_users u
where r.grantee = u.username and
u.account_status not like '%LOCKED%' and
grantee not in ('SYS','SYSTEM','DBSNMP') and
granted_role in
(select grantee
from dba_role_privs
where granted_role = 'CONNECT' and
grantee <> 'DBA');

Check which user has been granted with the 'RESOURCE' role

2010-01-13T10:39:00.002-08:00

select grantee
from dba_role_privs r, dba_users u
where granted_role ='RESOURCE' and
r.grantee = u.username and
u.account_status not like '%LOCKED%' and
grantee not in ('SYS','SYSTEM','DBSNMP')
union
select grantee
from dba_role_privs r, dba_users u
where r.grantee = u.username and
u.account_status not like '%LOCKED%' and
grantee not in ('SYS','SYSTEM','DBSNMP') and
granted_role in
( select grantee
from dba_role_privs
where granted_role = 'RESOURCE' and
grantee <> 'DBA');

Check which user has 'ALTER SESSION' and 'ALTER SYSTEM' privileges

2010-01-13T10:39:00.001-08:00

select grantee from dba_sys_privs where privilege = 'ALTER SESSION';

select grantee from dba_sys_privs where privilege = 'ALTER SYSTEM';

Prevent granting of CREATE LIBRARY

2010-01-13T10:38:00.000-08:00

select * from dba_sys_privs where privilege='CREATE LIBRARY';

Check for privileges with 'ANY' keywordgranted.

2010-01-13T10:37:00.002-08:00

select grantee
from dba_sys_privs s, dba_users u
where s.grantee = u.username and
u.account_status not like '%LOCKED%' and
privilege like '%ANY%' and
grantee <> 'DBA' and
grantee not in ('SYS','SYSTEM','MDSYS','CTXSYS',
'ORDSYS','ORDPLUGINS','AURORA$JIS$UTILITY$',
'HR','ODM','ODM_MTR','OE','OLAPDBA','OLAPSYS',
'OSE$HTTP$ADMIN','OUTLN','TRACESVR','REPADMIN',
'PM','QS','QS_ADM','QS_CB','QS_CBADM', 'DBSNMP',
'QS_CS','QS_ES','QS_OS','QS_WS','RMAN','SH','WKSYS',
'WMSYS','XDB','WKPROXY','LBACSYS','OLAPSVR', 'PERFSTAT')
union
select grantee
from dba_role_privs r, dba_users u
where r.grantee = u.username and
u.account_status not like '%LOCKED%' and
granted_role in
( select grantee
from dba_sys_privs
where privilege like '%ANY%' and
grantee <> 'DBA'
) and
grantee not in ('SYS','SYSTEM','MDSYS','CTXSYS',
'ORDSYS','ORDPLUGINS','AURORA$JIS$UTILITY$',
'HR','ODM','ODM_MTR','OE','OLAPDBA','OLAPSYS',
'OSE$HTTP$ADMIN','OUTLN','TRACESVR','REPADMIN',
'PM','QS','QS_ADM','QS_CB','QS_CBADM', 'DBSNMP',
'QS_CS','QS_ES','QS_OS','QS_WS','RMAN','SH','WKSYS',
'WMSYS','XDB','WKPROXY','LBACSYS','OLAPSVR', 'PERFSTAT')
union
select grantee
from dba_role_privs r, dba_users u
where r.grantee = u.username and
u.account_status not like '%LOCKED%' and
granted_role <> 'DBA' and
granted_role in
( select grantee
from dba_role_privs
where granted_role in
( select grantee
from dba_sys_privs
where privilege like '%ANY%' and
grantee <> 'DBA'
)
) and
grantee not in ('SYS','SYSTEM','MDSYS','CTXSYS',
'ORDSYS','ORDPLUGINS','AURORA$JIS$UTILITY$',
'HR','ODM','ODM_MTR','OE','OLAPDBA','OLAPSYS',
'OSE$HTTP$ADMIN','OUTLN','TRACESVR','REPADMIN',
'PM','QS','QS_ADM','QS_CB','QS_CBADM', 'DBSNMP',
'QS_CS','QS_ES','QS_OS','QS_WS','RMAN','SH','WKSYS',
'WMSYS','XDB','WKPROXY','LBACSYS','OLAPSVR', 'PERFSTAT');

Check for Restricted PL/SQL Packages

2010-01-13T10:37:00.001-08:00

select table_name from dba_tab_privs
where grantee='PUBLIC'
and privilege ='EXECUTE'
and table_name in
('UTL_SMTP', 'UTL_TCP', 'UTL_HTTP', 'UTL_FILE',
'DBMS_RANDOM','DBMS_LOB', 'DBMS_SQL','DBMS_JOB',
'DBMS_BACKUP_RESTORE','DBMS_OBFUSCATION_TOOLKIT');

Check for Role Permissions

2010-01-13T10:36:00.002-08:00

select grantee,owner,table_name from dba_tab_privs
where privilege in ('ALTER','REFERENCES','INDEX')
and grantee not in ('DBA','SYSTEM','LBACSYS','XDBADMIN','XDB_ADMIN')
and table_name not in ('SDO_IDX_TAB_SEQUENCE','XDB$ACL')
and grantee not in (select grantee from dba_role_privs where granted_role='DBA')
and grantee not in (select owner from dba_objects);

Prevent granting of privileges that have WITH GRANT option

2010-01-13T10:36:00.001-08:00

select grantee||': '||owner||'.'||table_name from dba_tab_privs
where grantable='YES'
and owner not in ('SYS','MDSYS','ORDPLUGINS',
'ORDSYS','SYSTEM','WMSYS','XDB','LBACSYS')
and grantee not in (select grantee from dba_role_privs where granted_role='DBA')
order by grantee;

Check Privileges Granted With Admin

2010-01-13T10:34:00.002-08:00

select grantee,privilege from dba_sys_privs
where grantee not in
('SYS','SYSTEM','AQ_ADMINISTRATOR_ROLE','DBA','MDSYS','LBACSYS','SCHEDULER_ADMIN','WMSYS')
and admin_option='YES'
and grantee not in (select grantee from dba_role_privs where granted_role='DBA');

SYSDBA Privilege Assignments

2010-01-13T10:34:00.001-08:00

SELECT USERNAME FROM V$PWFILE_USERS
WHERE USERNAME NOT IN
(SELECT GRANTEE FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE='DBA')
and USERNAME !='INTERNAL' and sysdba='TRUE';

Check for System Privilege Assignments

2010-01-13T10:32:00.000-08:00

select grantee||': '||PRIVILEGE from dba_sys_privs
where privilege<>'CREATE SESSION' and
grantee not in ('PUBLIC','AQ_ADMINISTRATOR_ROLE',
'AQ_USER_ROLE','CTXSYS','DBA','DELETE_CATALOG_ROLE',
'EXECUTE_CATALOG_ROLE', 'EXP_FULL_DATABASE',
'GATHER_SYSTEM_STATISTICS','HS_ADMIN_ROLE', 'IMP_FULL_DATABASE',
'LOGSTDBY_ADMINISTRATOR', 'MDSYS','ODM',
'OEM_MONITOR','OLAPSYS','ORDSYS','OUTLN','MTSSYS',
'RECOVERY_CATALOG_OWNER','SELECT_CATALOG_ROLE','SNMPAGENT',
'SYSTEM','WKSYS','WKUSER','WMSYS','WM_ADMIN_ROLE','XDB',
'ANONYMOUS','CONNECT','DBSNMP','JAVADEBUGPRIV','ODM_MTR',
'OLAP_DBA','ORDPLUGINS','RESOURCE','RMAN','SYS','WKPROXY',
'AURORA$JIS$UTILITY$','AURORA$ORB$UNAUTHENTICATED',
'OSE$HTTP$ADMIN','TIMESERIES_DBA','TIMESERIES_DEVELOPER',
'OLAP_USER')
and grantee not in (select grantee from dba_role_privs where granted_role='DBA')
and grantee not in (select username from dba_users where account_status = 'LOCKED');

Check for Roles Assigned to PUBLIC

2010-01-13T10:29:00.001-08:00

select granted_role from dba_role_privs where grantee='PUBLIC';

Check for Access to System Tables/DBA Views

2010-01-13T10:28:00.000-08:00

select grantee,privilege,owner,table_name from dba_tab_privs
where (owner='SYS' or table_name like 'DBA_%')
and privilege <> 'EXECUTE'
and grantee not in ('PUBLIC','AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE',
'AURORA$JIS$UTILITY$','OSE$HTTP$ADMIN','TRACESVR',
'CTXSYS','DBA','DELETE_CATALOG_ROLE','EXECUTE_CATALOG_ROLE',
'EXP_FULL_DATABASE','GATHER_SYSTEM_STATISTICS','HS_ADMIN_ROLE',
'IMP_FULL_DATABASE','LOGSTDBY_ADMINISTRATOR','MDSYS','ODM',
'OEM_MONITOR','OLAPSYS','ORDSYS','OUTLN','RECOVERY_CATALOG_OWNER',
'SELECT_CATALOG_ROLE','SNMPAGENT','SYSTEM','WKSYS','WKUSER',
'WMSYS','WM_ADMIN_ROLE','XDB','LBACSYS','PERFSTAT','XDBADMIN')
and grantee not in (select grantee from dba_role_privs where granted_role='DBA')
order by grantee;

Check for Tablespace Quotas

2010-01-13T10:27:00.002-08:00

Select username,tablespace_name
from dba_ts_quotas
where username not in (select distinct owner from dba_objects)
and username not in (select grantee from dba_role_privs where granted_role='DBA');

Check for Default Tablespace

2010-01-13T10:27:00.001-08:00

Select username from dba_users
where (default_tablespace= 'SYSTEM' or temporary_tablespace= 'SYSTEM')
and username not in
('AURORA$JIS$UTILITY$', 'AURORA$ORB$UNAUTHENTICATED', 'DBSNMP',
'MDSYS', 'ORDPLUGINS', 'ORDSYS', 'OSE$HTTP$ADMIN', 'OUTLN', 'REPADMIN',
'SYS', 'SYSTEM','TRACESVR','MTSSYS','DIP');

Replication Account Use

2010-01-13T10:26:00.001-08:00

select 'The number of replication objects defined is: '||
count(*) from system.REPCAT$_REPOBJECT;

Oracle Predefined Roles

2010-01-13T10:25:00.001-08:00

select grantee||': '||granted_role from dba_role_privs
where grantee not in
('SYS','SYSTEM','MDSYS','CTXSYS',
'ORDSYS','ORDPLUGINS','AURORA$JIS$UTILITY$',
'HR','ODM','ODM_MTR','OE','OLAPDBA','OLAPSYS',
'OSE$HTTP$ADMIN','OUTLN','TRACESVR','REPADMIN',
'PM','PUBLIC','QS','QS_ADM','QS_CB','QS_CBADM', 'DBSNMP',
'QS_CS','QS_ES','QS_OS','QS_WS','RMAN','SH','WKSYS',
'WMSYS','XDB','WKPROXY','LBACSYS','OLAPSVR', 'PERFSTAT')
and grantee not in (select role from dba_roles)
and grantee not in
(select grantee from dba_role_privs where granted_role='DBA')
and grantee not in (select distinct owner from dba_objects)
and granted_role in
('AQ_ADMINISTRATOR_ROLE',
'AQ_USER_ROLE','AUTHENTICATEDUSER','CONNECT',
'CTXAPP','DELETE_CATALOG_ROLE',
'EJBCLIENT','EXECUTE_CATALOG_ROLE',
'EXP_FULL_DATABASE','GATHER_SYSTEM_STATISTICS',
'GLOBAL_AQ_USER_ROLE','HS_ADMIN_ROLE',
'IMP_FULL_DATABASE','JAVADEBUGPRIV', 'JAVAIDPRIV','JAVASYSPRIV',
'JAVAUSERPRIV','JAVA_ADMIN','JAVA_DEPLOY',
'LOGSTDBY_ADMINISTRATOR','OEM_MONITOR','OLAP_DBA',
'RECOVERY_CATALOG_OWNER','RESOURCE','SALES_HISTORY_ROLE',
'SELECT_CATALOG_ROLE','WKUSER','WM_ADMIN_ROLE','XDBADMIN')
order by grantee;

Check for Active Schema Owner Account

2010-01-13T10:24:00.001-08:00

select distinct owner from dba_objects, dba_users
where owner not in
('SYS','SYSTEM','MDSYS','CTXSYS',
'ORDSYS','ORDPLUGINS','AURORA$JIS$UTILITY$',
'ODM','ODM_MTR','OLAPDBA','OLAPSYS','MTSSYS',
'OSE$HTTP$ADMIN','OUTLN','LBACSYS',
'PUBLIC','DBSNMP','RMAN','WKSYS',
'WMSYS','XDB')
and owner = username
and account_status not like '%LOCKED';

Check Developer Privileges on Shared Systems

2010-01-13T10:22:00.000-08:00

SELECT GRANTEE||': '||PRIVILEGE FROM DBA_SYS_PRIVS WHERE
(PRIVILEGE LIKE 'CREATE%' OR PRIVILEGE LIKE 'ALTER%'OR PRIVILEGE LIKE 'DROP%')
AND PRIVILEGE<>'CREATE SESSION'
AND GRANTEE NOT IN
('SYS','SYSTEM','ANONYMOUS','AQ_ADMINISTRATOR_ROLE','MTSSYS',
'CONNECT','CTXSYS','DBA','IMP_FULL_DATABASE','MDSYS','ODM',
'ODM_MTR','OEM_MONITOR','OLAPSYS','OLAP_DBA','ORDPLUGINS','ORDSYS',
'RECOVERY_CATALOG_OWNER','RESOURCE','SYS','WKPROXY','WKSYS',
'WKUSER','WMSYS','XDB','TIMESERIES_DEVELOPER','TIMESERIES_DBA',
'LBACSYS','DBSNMP','OLAP_USER') order by grantee;

Check for Unauthorized Object Owner

2010-01-13T10:21:00.000-08:00

select distinct owner from dba_objects
where owner not in
('SYS','SYSTEM','MDSYS','CTXSYS',
'ORDSYS','ORDPLUGINS','AURORA$JIS$UTILITY$',
'HR','ODM','ODM_MTR','OE','OLAPDBA','OLAPSYS',
'OSE$HTTP$ADMIN','OUTLN','LBACSYS','MTSYS',
'PM','PUBLIC','QS','QS_ADM','QS_CB','QS_CBADM', 'DBSNMP',
'QS_CS','QS_ES','QS_OS','QS_WS','RMAN','SH','WKSYS',
'WMSYS','XDB')
and owner not in (select grantee from dba_role_privs where granted_role='DBA');

Check if DBA Includes Non-default Account

2010-01-13T10:20:00.000-08:00

select grantee from dba_role_privs
where granted_role='DBA'
and grantee not in ('SYS','SYSTEM','CTXSYS','WKSYS','SYSMAN');

GLOBAL_NAMES Parameter

2010-01-13T10:19:00.001-08:00

Select value from v$parameter where name= 'global_names';

Oracle database log mode

2010-01-13T10:18:00.000-08:00

Select log_mode from v$database;

'user_dump_dest' and 'background_dump_dest'

2010-01-13T10:16:00.001-08:00

Select value from v$parameter where name='user_dump_dest';

Select value from v$parameter where name='background_dump_dest';

'user_dump_dest' and 'background_dump_dest'

2010-01-13T10:16:00.000-08:00

Select value from v$parameter where name='user_dump_dest';

Select value from v$parameter where name='background_dump_dest';

Default Oracle System Identifier

2010-01-13T10:15:00.001-08:00

Select value from v$parameter where name= 'instance_name';

Password Verify Function

2010-01-13T10:14:00.001-08:00

SELECT profile, limit
FROM dba_profiles,
(SELECT limit AS def_pwd_verify_func
FROM dba_profiles
WHERE resource_name = 'PASSWORD_VERIFY_FUNCTION'
AND profile = 'DEFAULT')
WHERE resource_name='PASSWORD_VERIFY_FUNCTION'
AND REPLACE(limit,'DEFAULT',def_pwd_verify_func) in
('UNLIMITED','NULL');

Account Lock Time

2010-01-13T10:13:00.000-08:00

SELECT profile, limit
FROM dba_profiles,
(SELECT limit AS def_pwd_lock_tm
FROM dba_profiles
WHERE profile = 'DEFAULT'
AND resource_name = ' PASSWORD_LOCK_TIME ')
WHERE resource_name = ' PASSWORD_LOCK_TIME '
AND ((replace(limit,'DEFAULT',def_pwd_lock_tm) IN ('UNLIMITED',NULL))
OR (lpad(replace(limit,'DEFAULT',def_pwd_lock_tm),40,'0') <
lpad('.0104',40,'0')));

Password Reuse

2010-01-13T10:12:00.001-08:00

-- Check for both reuse max and reuse time not set

select profile from DBA_PROFILES
where (resource_name='PASSWORD_REUSE_MAX'
and limit in ('UNLIMITED','NULL'))
and profile in
(select profile from DBA_PROFILES where
resource_name='PASSWORD_REUSE_TIME')
and limit in ('UNLIMITED','NULL');

-- Check for reuse max with value that is less than allowed minimum

select profile from DBA_PROFILES
where resource_name='PASSWORD_REUSE_MAX'
and limit not in ('UNLIMITED','NULL')
and limit < '10';

-- Check for reuse time that is less than allowed minimum

select profile from DBA_PROFILES
where resource_name='PASSWORD_REUSE_TIME'
and limit not in ('UNLIMITED','NULL')
and limit< '365';

Password Life Time

2010-01-13T10:11:00.001-08:00

SELECT profile, limit
FROM dba_profiles,
(SELECT limit AS def_pwd_life_tm
FROM dba_profiles
WHERE profile = 'DEFAULT'
AND resource_name = 'PASSWORD_LIFE_TIME')
WHERE resource_name = 'PASSWORD_LIFE_TIME'
AND ((replace(limit,'DEFAULT',def_pwd_life_tm) IN ('UNLIMITED',NULL))
OR (lpad(replace(limit,'DEFAULT',def_pwd_life_tm),40,'0') >
lpad('90',40,'0')));

Database Links - SQL

2010-01-13T10:06:00.000-08:00

Show all links

Select db_link||': '||host from dba_db_links;

-- Check links
select owner, db_link, username from dba_db_links;

select owner||': '||db_link from dba_db_links
where (username is not NULL and username <> 'CURRENT_USER');

select 'Number of replication objects defined = '||count(*)
from dba_tables where table_name='REPCAT$_REPOBJECT';

--Database Link Permissions

select grantee||': '||privilege from dba_tab_privs
where grantee <> 'DELETE_CATALOG_ROLE'
and table_name='LINK$'
and grantee not in (select grantee from dba_role_privs where granted_role='DBA');

-- Check which links are defined in LINK$

select substr(name,1,20) Name, substr(host,1,20) Host, substr(password,1,20),
substr(authusr,1,20), substr(authpwd,1,20), substr(passwordx,1,5)
from sys.link$;

determine if pl/sql code is wrapped or not

2010-01-13T10:03:00.000-08:00

select owner||'.'||name from dba_source
where
line=1
and
owner not in ('SYS', 'CTXSYS', 'MDSYS', 'ODM', 'OE', 'OLAPSYS',
'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS_ADM',
'RMAN', 'SYSTEM', 'WKSYS','WMSYS','XDB')
and
owner not like 'OEM%'
and
text not like '%wrapped%'
and
type in ('PACKAGE BODY','FUNCTION','PROCEDURE');

Determine Version and options of database examined

2010-01-13T10:01:00.002-08:00

select comp_name, version, status from dba_registry;

Check the patch level

2010-01-13T10:01:00.001-08:00

select * from registry$history;

Get the name of the DB

2010-01-13T09:58:00.000-08:00

select name from v$database;

Oracle Critical Patch Update Advisory - October 2009

2010-01-13T09:54:00.000-08:00

A Critical Patch Update is a collection of patches for multiple security vulnerabilities. It also includes non-security fixes that are required (because of interdependencies) by those security patches. Critical Patch Updates are cumulative, except as noted below, but each advisory describes only the security fixes added since the previous Critical Patch Update. Thus, prior Critical Patch Update Advisories should be reviewed for information regarding earlier accumulated security fixes. Please refer to:
Critical Patch Updates and Security Alerts for information about Oracle Security Advisories.

OPatch lsinventory

2010-01-13T09:52:00.000-08:00

Ensure that you are able to invoke the opatch lsinventory -detail command and are able to see the node information being printed out. A sample listing of the output of the command is as follows:

Oracle interim Patch Installer version 10.2.0.4.6
Copyright (c) 2009, Oracle Corporation. All rights reserved..
Oracle Home : /home/racqa/102_twork/toii/toiir/toiir571/HOMEtoiir571
Central Inventory : /home/racqa/102_twork/toii/toiir/toiir571/inventory
from : /home/racqa/102_
twork/toii/toiir/toiir571/HOMEtoiir571/oraInst.loc
OPatch version : 10.2.0.4.6
OUI version : 10.2.0.4.6
OUI location : /home/racqa/102_twork/toii/toiir/toiir571/HOMEtoiir571/oui
Log file location : /home/racqa/102_
twork/toii/toiir/toiir571/HOMEtoiir571/cfgtoollogs/opatch/opatch-2008_May_30_
01-04-52-PDT_Mon.log
Lsinventory Output file location : /home1/racqa/102_
twork/toii/toiir/toiir571/HOMEtoiir571/cfgtoollogs/opatch/lsinv/lsinventory-2008_
May_30_01-04-52-PDT_Mon.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 10g 10.2.0.4.6
There are 1 products installed in this Oracle Home.
Installed Products (151):
Advanced Queueing (AQ) API 10.2.0.4.6
Advanced Replication 10.2.0.4.6
Agent Required Support Files 10.2.0.4.6
Assistant Common Files 10.2.0.4.6
Authentication and Encryption 10.2.0.4.6
Bali Share 1.1.18.0.0
Character Set Migration Utility 10.2.0.4.6
CSS Single-instance Common Files 10.2.0.4.6
Data Management Services Common Files 10.2.0.4.6
Database Configuration Assistant 10.2.0.4.6
Database SQL Scripts 10.2.0.4.6
Database Upgrade Assistant 10.2.0.4.6
Database Verify Utility 10.2.0.4.6
Database Workspace Manager 10.2.0.4.6
DBJAVA Required Support Files 10.2.0.4.6
Documentation Required Support Files 10.2.0.4.6
Enterprise Edition Options 10.2.0.4.6
Enterprise Manager Agent 10.2.0.4.6
Enterprise Manager Common Files 10.2.0.4.6
Enterprise Manager Minimal Integration 10.2.0.4.6
Enterprise Manager plugin Common Files 10.2.0.4.6
Enterprise Manager Repository 10.2.0.4.6
Export/Import 10.2.0.4.6
Extended Windowing Toolkit 3.3.18.0.0
Generic Connectivity Common Files 10.2.0.4.6
Generic Connectivity Using ODBC 10.2.0.4.6
Installation Common Files 10.2.0.4.6
Installer SDK Component 10.2.0.1.0
iSQL*Plus 10.2.0.4.6
Java Naming and Directory Interface Libraries 10.2.0.4.6
Java Runtime Environment 1.4.2.0.0
JDBC Common Files 10.2.0.4.6
JDBC/OCI Common Files 10.2.0.4.6
JDBC/OCI Common Files for Instant Client 10.2.0.4.6
LDAP Required Support Files 10.2.0.4.6
New Database ID 10.2.0.4.6
Object Type Translator 10.2.0.4.6
Oracle Advanced Security 10.2.0.4.6
Oracle C++ Call Interface 10.2.0.4.6
Oracle C++ Call Interface for Instant Client 10.2.0.4.6
Oracle Call Interface (OCI) 10.2.0.4.6
Oracle Client Required Support Files 10.2.0.4.6
Oracle Code Editor 1.2.1.0.0I
Oracle Containers for Java 10.2.0.4.6
Oracle Core Required Support Files 10.2.0.4.6
Oracle Data Mining 10.2.0.4.6
Oracle Database 10g 10.2.0.4.6
Oracle Database 10g 10.2.0.4.6
Oracle Database User Interface 2.2.13.0.0
Oracle Database Utilities 10.2.0.4.6
Oracle Development Kit 10.2.0.4.6
Oracle Display Fonts 9.0.2.0.0
Oracle Enterprise Manager 10g Database Control 10.2.0.4.6
Oracle Enterprise Manager Console DB 10.2.0.4.6
Oracle Extended Windowing Toolkit 3.4.28.0.0
Oracle Globalization Support 10.2.0.4.6
Oracle Help For Java 4.2.5.0.0a
Oracle Help for the Web 1.1.7.0.0a
Oracle Ice Browser 5.2.3.3.0
Oracle interMedia 10.2.0.4.6
Oracle interMedia Annotator 10.2.0.4.6
Oracle interMedia Audio 10.2.0.4.6
Oracle interMedia Client Compatibility Files 10.2.0.4.6
Oracle interMedia Client Demos 10.2.0.4.6
Oracle interMedia Client Option 10.2.0.4.6
Oracle interMedia Common Files 10.2.0.4.6
Oracle interMedia Image 10.2.0.4.6
Oracle interMedia Java Advanced Imaging 10.2.0.4.6
Oracle interMedia Java Client 10.2.0.4.6
Oracle interMedia Locator 10.2.0.4.6
Oracle interMedia Video 10.2.0.4.6
Oracle interMedia Web Client 10.2.0.4.6
Oracle Internet Directory Client 10.2.0.4.6
Oracle Internet Directory Client Common Files 10.2.0.4.6
Oracle Internet Directory Tools 10.2.0.4.6
Oracle Java Tools 10.2.0.4.6
Oracle JDBC Thin Driver for JDK 1.2 10.2.0.4.6
Oracle JDBC Thin Driver for JDK 1.4 10.2.0.4.6
Oracle JDBC Thin Driver for JDK 1.4 for Instant Client 10.2.0.4.6
Oracle JDBC/OCI Driver for JDK 1.4 10.2.0.4.6
Oracle JFC Extended Windowing Toolkit 4.2.18.0.0
Oracle JVM 10.2.0.4.6
Oracle Locale Builder 10.2.0.4.6
Oracle Message Gateway Common Files 10.2.0.4.6
Oracle Net 10.2.0.4.6
Oracle Net Configuration Assistant 10.2.0.4.6
Oracle Net Listener 10.2.0.4.6
Oracle Net Manager 10.2.0.4.6
Oracle Net Required Support Files 10.2.0.4.6
Oracle Net Services 10.2.0.4.6
Oracle Notification Service 9.0.4.0.0
Oracle OLAP 10.2.0.4.6
Oracle OLAP API 10.2.0.4.6
Oracle OLAP Catalog 10.2.0.4.6
Oracle One-Off Patch Installer 10.2.0.1.0
Oracle Partitioning 10.2.0.4.6
Oracle Programmer 10.2.0.4.6
Oracle RAC Required Support Files 10.2.0.4.6
Oracle RAC Required Support Files-HAS 10.2.0.4.6
Oracle Real Application Clusters 10.2.0.4.6
Oracle Spatial 10.2.0.4.6
Oracle Starter Database 10.2.0.4.6
Oracle Text 10.2.0.4.6
Oracle UIX 2.1.21.0.0a
Oracle Ultra Search Common Files 10.2.0.4.6
Oracle Ultra Search Middle-Tier 10.2.0.4.6
Oracle Ultra Search Server 10.2.0.4.6
Oracle Universal Installer 10.2.0.1.0
Oracle Wallet Manager 10.2.0.4.6
Oracle XML Developer's Kit 10.2.0.4.6
Oracle XML Runtime Components 10.2.0.4.6
Oracle XML SQL Utility 10.2.0.4.6
Oracle10g Real Application Clusters Common Files 10.2.0.4.6
Parser Generator Required Support Files 10.2.0.4.6
Perl Interpreter 5.6.1.0.2d
PL/SQL 10.2.0.4.6
PL/SQL Embedded Gateway 10.2.0.4.6
PL/SQL Required Support Files 10.2.0.4.6
Platform Required Support Files 10.2.0.4.6
Precompiler Common Files 10.2.0.4.6
Precompiler Required Support Files 10.2.0.4.6
Pro*C/C++ 10.2.0.4.6
RDBMS Required Support Files 10.2.0.4.6
Recovery Manager 10.2.0.4.6
regexp 2.1.9.0.0
Required Support Files 10.2.0.4.6
Sample Schema 10.2.0.4.6
Secure Socket Layer 10.2.0.4.6
Secure Socket Layer 10.2.0.4.6
SQL*Loader 10.2.0.4.6
SQL*Plus 10.2.0.4.6
SQL*Plus Required Support Files 10.2.0.4.6
SQLJ Runtime 10.2.0.4.6
SSL Required Support Files 10.2.0.4.6
SSL Required Support Files for InstantClient 10.2.0.4.6
Sun JDK 1.4.2.0.0
Sun JDK extensions 9.0.4.0.0
Utilities Common Files 10.2.0.4.6
Visigenics ORB 3.4.0.0.0
XDK Required Support Files 10.2.0.4.6
XML 10.2.0.4.6
XML Class Generator for C++ 10.2.0.4.6
XML Class Generator for Java 10.2.0.4.6
XML Parser for C 10.2.0.4.6
XML Parser for C++ 10.2.0.4.6
XML Parser for Java 10.2.0.4.6
XML Parser for Oracle JVM 10.2.0.4.6
XML Parser for PL/SQL 10.2.0.4.6
XML Transviewer Beans 10.2.0.4.6
XML Transx 10.2.0.4.6
XSQL Servlet 10.2.0.4.6
There are 151 products installed in this Oracle Home.
Intermin patches (1) :
Patch 3811942 : applied on Mon May 30 00:59:33 PDT 2008
Created on 31 Aug 2007, 12:06:28 hrs US/Pacific
Bugs fixed:
3811942
Files Touched:
/hosp.o --> ORACLE_HOME/lib/libagent10.a
/pesblt.o --> ORACLE_HOME/lib/libpls10.a
/kgl.o --> ORACLE_HOME/lib/libgeneric10.a
/qcpi6.o --> ORACLE_HOME/lib/libgeneric10.a
ins_rdbms.mk --> ORACLE_HOME/rdbms/lib/iextjob
ins_rdbms.mk --> ORACLE_HOME/rdbms/lib/iextjobo
ins_rdbms.mk --> ORACLE_HOME/rdbms/lib/client_sharedlib
/hosp.o --> ORACLE_HOME/lib/libserver10.a
/prse.o --> ORACLE_HOME/lib/libserver10.a
/prsa.o --> ORACLE_HOME/lib/libserver10.a
/prsf.o --> ORACLE_HOME/lib/libserver10.a
/prssz.o --> ORACLE_HOME/lib/libserver10.a
/kprc.o --> ORACLE_HOME/lib/libserver10.a
/qmhdr.o --> ORACLE_HOME/lib/libserver10.a
/pesblt.o --> ORACLE_HOME/lib/libpls10.a
/qcpi6.o --> ORACLE_HOME/lib/libgeneric10.a
prvtpexp.plb --> ORACLE_HOME/rdbms/admin/prvtpexp.plb
sjsex.o --> ORACLE_HOME/rdbms/lib/sjsex.o
ins_rdbms.mk --> ORACLE_HOME/rdbms/lib/iextjob
ins_rdbms.mk --> ORACLE_HOME/rdbms/lib/iextjobo
ins_rdbms.mk --> ORACLE_HOME/rdbms/lib/ioracle
ins_rdbms.mk --> ORACLE_HOME/rdbms/lib/client_sharedlib
driload.pkh --> ORACLE_HOME/ctx/admin/driload.pkh
/oracle/sysman/assistants/util/em/EMConfigStep.class --> ORACLE_
HOME/assistants/jlib/assistantsCommon.jar
/oracle/sysman/assistants/util/sqlEngine/SQLEngine.class --> ORACLE_
HOME/assistants/jlib/assistantsCommon.jar
/oracle/sysman/assistants/dbca/backend/DBEntryStep.class --> ORACLE_
HOME/assistants/dbca/jlib/dbca.jar
/oracle/sysman/assistants/dbca/backend/EMConfigStep.class --> ORACLE_
HOME/assistants/dbca/jlib/dbca.jar
/oracle/sysman/assistants/dbca/backend/PostDBCreationStep.class --> ORACLE_
HOME/assistants/dbca/jlib/dbca.jar
/oracle/sysman/emcp/EMConfig.class --> ORACLE_HOME/jlib/emca.jar
/oracle/sysman/emcp/IEMCAConstants.class --> ORACLE_HOME/jlib/emca.jar
/oracle/sysman/emcp/EMConfig.class --> ORACLE_HOME/sysman/jlib/emCORE.jar
/oracle/sysman/emcp/IEMCAConstants.class --> ORACLE_
HOME/sysman/jlib/emCORE.jar
/oracle/sysman/emSDK/conf/ConfigManager.class --> ORACLE_
HOME/sysman/jlib/emCORE.jar
/oracle/sysman/emSDK/eml/EmlConstants.class --> ORACLE_
HOME/sysman/jlib/emCORE.jar
/oracle/sysman/util/pref/PrefUtil.class --> ORACLE_
HOME/sysman/jlib/emCORE.jar
emctl.pl --> ORACLE_HOME/emdw/bin/emctl.pl
/ncrfipm.o --> ORACLE_HOME/lib/libnro10.a
libsqlplus.a --> ORACLE_HOME/lib/libsqlplus.a
libsqlplus.so --> ORACLE_HOME/lib/libsqlplus.so
libisqlplus.a --> ORACLE_HOME/lib/libisqlplus.a
libisqlplus.so --> ORACLE_HOME/lib/libisqlplus.so
mdprivs.sql --> ORACLE_HOME/md/admin/mdprivs.sql
prvtgmd.plb --> ORACLE_HOME/md/admin/prvtgmd.plb
sdolrsmd.sql --> ORACLE_HOME/md/admin/sdolrsmd.sql
prvtccbk.plb --> ORACLE_HOME/md/admin/prvtccbk.plb
wk0acl.pkh --> ORACLE_HOME/ultrasearch/admin/wk0acl.pkh
wk0acl.plb --> ORACLE_HOME/ultrasearch/admin/wk0acl.plb
wk0adm.pkh --> ORACLE_HOME/ultrasearch/admin/wk0adm.pkh
wk0adm.plb --> ORACLE_HOME/ultrasearch/admin/wk0adm.plb
wk0snapshot.plb --> ORACLE_HOME/ultrasearch/admin/wk0snapshot.plb
wk0util.pkh --> ORACLE_HOME/ultrasearch/admin/wk0util.pkh
wk0util.plb --> ORACLE_HOME/ultrasearch/admin/wk0util.plb
Patch Location in Inventory:
/home1/racqa/102_
twork/toii/toiir/toiir571/HOMEtoiir571/inventory/oneoffs/3811942
Patch Location in Storage area:
/home1/racqa/102_twork/toii/toiir/toiir571/HOMEtoiir571/.patch_
storage/3811942_Aug_31_2007_12_06_28
Rac system comprising of multiple nodes
Local node = stadu56
Remote node = stacg16
--------------------------------------------------------------------------------
OPatch succeeded.

If you do not find the node information correctly printed out, you need to update the node list. For more information on updating the node list, see the Oracle Universal Installer User's Guide.

How to use wrap utility to encode Oracle database source code Object

2010-01-13T08:22:00.000-08:00

The wrap program provided by Oracle encodes the PL/SQL source code but does not encrypt it.

select owner||'.'||name from dba_source
where
line=1
and
owner not in ('SYS', 'CTXSYS', 'MDSYS', 'ODM', 'OE', 'OLAPSYS',
'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'QS_ADM',
'RMAN', 'SYSTEM', 'WKSYS','WMSYS','XDB')
and
owner not like 'OEM%'
and
text not like '%wrapped%'
and
type in ('PACKAGE BODY','FUNCTION','PROCEDURE');

How to find locked oracle database user account details

2009-09-18T18:06:00.000-07:00

USER_AUDIT_SESSION contains audit trail records concerning connections and disconnections of the current user.
======================================================================================
Select os_username, username,userhost,terminal,timestamp,action_name,logoff_time,RETURNCODE
from DBA_AUDIT_SESSION where RETURNCODE in (1017,28000) order by timestamp.
look for the row just before reurncode 28000 and you will get when,which os user from which terminal the account got unlocked.

DBA_AUDIT_SESSION table
======================================================================================
Column Datatype NULL Description
OS_USERNAME VARCHAR2(255) Operating system login username of the user whose actions were audited
USERNAME VARCHAR2(30) Name (not ID number) of the user whose actions were audited
USERHOST VARCHAR2(128) Client host machine name
TERMINAL VARCHAR2(255) Identifier of the user's terminal
TIMESTAMP DATE Date and time of the creation of the audit trail entry (date and time of user login for entries created by AUDIT SESSION) in the local database session time zone
ACTION_NAME VARCHAR2(28) Name of the action type corresponding to the numeric code in the ACTION column in DBA_AUDIT_TRAIL
LOGOFF_TIME DATE Date and time of user log off
LOGOFF_LREAD NUMBER Logical reads for the session
LOGOFF_PREAD NUMBER Physical reads for the session
LOGOFF_LWRITE NUMBER Logical writes for the session
LOGOFF_DLOCK VARCHAR2(40) Deadlocks detected during the session
SESSIONID NUMBER NOT NULL Numeric ID for each Oracle session
RETURNCODE NUMBER NOT NULL Oracle error code generated by the action.
Some useful values:
0 - Action succeeded
2004 - Security violation
28000 - user locked
1017 -wrong combination user/pasword
CLIENT_ID VARCHAR2(64) Client identifier in each Oracle session
SESSION_CPU NUMBER Amount of CPU time used by each Oracle session
EXTENDED_TIMESTAMP TIMESTAMP(6) WITH TIME ZONE Timestamp of the creation of the audit trail entry (timestamp of user login for entries created by AUDIT SESSION) in UTC (Coordinated Universal Time) time zone
PROXY_SESSIONID NUMBER Proxy session serial number, if an enterprise user has logged in through the proxy mechanism
GLOBAL_UID VARCHAR2(32) Global user identifier for the user, if the user has logged in as an enterprise user
INSTANCE_NUMBER NUMBER Instance number as specified by the INSTANCE_NUMBER initialization parameter
OS_PROCESS VARCHAR2(16) Operating System process identifier of the Oracle process

Get table and index DDL using DBMS_METADATA.GET_DDL

2009-08-19T04:54:00.000-07:00

Prior to Oracle9i, getting table and index DDL was a time-consuming and tricky process. You could run the export utility with ROWS=NO, but the output was hard to re-use because of quoted strings. The only other option was to write complex dictionary scripts that might not work on complex objects such as IOT and nested tables.

Punching DDL from the dictionary is very useful when you are migrating a system to a new platform and you want to pre-create the objects in a new tablespace so that you can import with IGNORE=Y.

In Oracle9i we have the exciting new dbms_metadata utility to display DDL directly from the data dictionary. Using this powerful utility, we can punch individual objects or an entire schema.

Best of all, it is easy. You simply execute dbms_metadata. get_ddl.

capture index ddl before you drop.

create table indxddl
(
tbl_name varchar(32),
indx_name varchar(32),
indxddl varchar(4000)
);

create table test31(id numeric(16), name varchar2(128));

create index test31indx1 on test31(id);

insert into indxddl(tbl_name,indx_name,indxddl) values ('test31','test31indx1','create index test31indx1 on test31(id)');
INSERT INTO INDXDDL(TBL_NAME,INDX_NAME,INDXDDL) VALUES ('TEST31','TEST31INDX1','CREATE INDEX TEST31INDX1 ON TEST31(ID)');

set long 1000
select * from indxddl;

delete from indxddl;

create index test31indx2 on test31(name);

select upper(index_name) from user_indexes where upper(table_name)='TEST31'
minus
select upper(indx_name) from indxddl where upper(tbl_name)='TEST31';

SELECT DBMS_METADATA.GET_DDL('INDEX','TEST31INDX2') FROM USER_INDEXES u;

CREATE OR REPLACE procedure indexddl_proc( p_table_name varchar2)
as
cnt number;
indexddl varchar(4000);
begin
select count(*)
into cnt
from
(select upper(index_name) from user_indexes where table_name ='TEST31'
minus
select upper(indx_name) from INDXDDL where tbl_name = 'TEST31');
if cnt>0 then
for i in
(select index_name as a from user_indexes where table_name ='TEST31'
minus
select upper(indx_name) as a from indxddl where upper(tbl_name)= 'TEST31')
loop
indexddl:=DBMS_METADATA.GET_DDL('INDEX',i.a);
--SELECT DBMS_METADATA.GET_DDL('INDEX',i.a) into indexddl FROM USER_INDEXES u;
insert into indxddl(tbl_name,indx_name,indxddl) values (upper(p_table_name),upper(i.a),upper(indexddl));
end loop;
end if;

for x in (select table_name,index_name
from indxddl
where table_name = ltrim(rtrim(p_table_name,''''), '''' ) ) loop
execute immediate 'drop index '||x.index_name;
end loop;
execute immediate 'truncate table '||ltrim(rtrim(p_table_name,''''), '''' );
exception
when others then
raise;
end indexddl_proc;
/

execute indexddl_proc('TEST31');

To punch off all table and indexes for the EMP table, we execute dbms_metadata. get_ddl, select from DUAL, and providing all required parameters.

set heading off;
set echo off;
Set pages 999;
set long 90000;

spool ddl_list.sql

select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;

select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;

spool off;

Here is the output. The only thing missing is the ending semicolons after each statement. Just for illustration, we show how a primary key can be punched as part of the table DDL or separately using the INDEX argument.
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 12288 NEXT 12288 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 12288 NEXT 12288 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM"

CREATE UNIQUE INDEX "SCOTT"."DEPT_IDX" ON "SCOTT"."DEPT" ("DNAME")
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM"

Now we can modify the syntax to punch a whole schema. It us easily done by selecting dbms_metadata. get_ddl and specifying USER_TABLES and USER_INDEXES. :

set pagesize 0
set long 90000
set feedback off
set echo off

spool scott_schema.sql

connect scott/tiger;

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u;

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
FROM USER_INDEXES u;

spool off;

Find the tables/indexes that have stale statistics in a database

2009-05-20T14:18:00.000-07:00

Using the code below one can find the tables/indexes that have stale statistics in a database, when options=>’GATHER AUTO’
is used oracle gathers statistics analyzes the tables/indexes that have stale statistics. Table monitoring is
enabled by default in 10g to find table which is used to find table statistics, when STATISTICS_LEVEL is set
to BASIC table monitoring is disabled. In 9i one can enable/disable table monitoring by
calling DBMS_STATS.ALTER_SCHEMA_TABLE_MONITORING

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList, options=>’LIST STALE’);
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || ‘.’ || ObjList(i).ObjName || ‘ ‘ || ObjList(i).ObjType || ‘ ‘ || ObjList(i).partname);
END LOOP;
END;
/

SYS.COL_USAGE$ TABLE
SYS.DEPENDENCY$ TABLE
SYS.HISTGRM$ TABLE
SYS.HIST_HEAD$ TABLE

Note: To find schema level stats that are stale one can call
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>’SCOTT’, objlist=>ObjList, options=>’LIST STALE’);

Detailed report of all invalid objects in the database.

2009-05-20T10:02:00.000-07:00

SET LINESIZE 145
SET PAGESIZE 9999

clear columns
clear breaks
clear computes

column owner format a25 heading 'Owner'
column object_name format a30 heading 'Object Name'
column object_type format a20 heading 'Object Type'
column status format a10 heading 'Status'

break on owner skip 2 on report
compute count label "" of object_name on owner
compute count label "Grand Total: " of object_name on report

SELECT
owner
, object_name
, object_type
, status
FROM dba_objects
WHERE status <> 'VALID'
ORDER BY owner, object_name
/

Truncating Listener Log File

2009-05-20T09:57:00.000-07:00

$cp $ORACLE_HOME/network/log/listener.log $ORACLE_HOME/network/log/listener.log_`date +"%Y%m%d"`

$cat /dev/null > $ORACLE_HOME/network/log/listener.log

$nohup compress $ORACLE_HOME/network/log/listener.log_`date +"%Y%m%d"` &

Refreshing stale statistics using the DBMS_STATS package and the GATHER AUTO option.

2009-05-20T09:50:00.000-07:00

BEGIN
DBMS_STATS.gather_schema_stats (
ownname => USER,
cascade => TRUE,
options => 'GATHER AUTO');
END;
/

The results of the statistics refresh are evident in the %_TABLES views, as shown below.

SELECT table_name,
num_rows,
avg_row_len
FROM user_tables;

TABLE_NAME NUM_ROWS AVG_ROW_LEN
------------------------------ ---------- -----------
DEPARTMENTS 1000 21

To automate the gathering of statistics, simply schedule a job to the call the DBMS_STATS package. The example below refreshes stale statistics for for schema "TIM" at midnight every day.

SET SERVEROUTPUT ON
DECLARE
l_job BINARY_INTEGER;
BEGIN
DBMS_JOB.submit (
job => l_job,
what => 'BEGIN DBMS_STATS.gather_schema_stats (' ||
'ownname => ''TIM'', cascade => TRUE, options => ''GATHER AUTO''); END;',
next_date => SYSDATE,
interval => '/* 1 Day Interval */ TRUNC(SYSDATE) + 1');
COMMIT;
DBMS_OUTPUT.put_line('Job: ' || TO_CHAR(l_job));
END;
/

Alternatively we could set monitoring on for all database tables, with the exception of those in the SYS schema, and use the following procedure:

DBMS_STATS.gather_database_stats(cascade => TRUE, options => 'GATHER AUTO');

Since only those tables with stale or empty stats are processed the overhead on the system is greatly reduced.

Note. In Oracle 10g, gathering stale statistics for the database once a day happens by default.

Statistics on Tables, Indexes and Columns

2009-05-20T09:46:00.000-07:00

Statistics on tables, indexes, and columns are stored in the data dictionary. To view statistics in the data dictionary, query the appropriate data dictionary view (USER, ALL, or DBA). These DBA_* views include the following:

* DBA_TABLES
* DBA_OBJECT_TABLES
* DBA_TAB_STATISTICS
* DBA_TAB_COL_STATISTICS
* DBA_TAB_HISTOGRAMS
* DBA_INDEXES
* DBA_IND_STATISTICS
* DBA_CLUSTERS
* DBA_TAB_PARTITIONS
* DBA_TAB_SUBPARTITIONS
* DBA_IND_PARTITIONS
* DBA_IND_SUBPARTITIONS
* DBA_PART_COL_STATISTICS
* DBA_PART_HISTOGRAMS
* DBA_SUBPART_COL_STATISTICS
* DBA_SUBPART_HISTOGRAMS

Oracle 10g Data Dictionary Table Statistics

2009-05-20T09:33:00.001-07:00

To collect statistics on dictionary objects, execute the following statements. The collection process can be done in different formats.

Format (1)
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');

Format (2)
SQL> exec DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);

Format (3)
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

As seen in the above examples, the gather_schema_stats procedure accepts the sys argument to perform statistics collections. In case of the gather_database_stats procedure, there is a new argument gather_sys whose default value is FALSE. When needed, you can set it to TRUE and execute the procedure.

There is another procedure, delete_dictionary_stats, which allows you to remove data dictionary stats.

When you execute the gather_dictionary_stats procedure, it gathers statistics from the SYS and SYSTEM schemas, as well as any other schemas that are related, such as OUTLN or DBSNMP schemas.

Difference between the PFILE, SPFILE and INIT.ORA

2007-08-16T06:37:00.000-07:00

Difference between the PFILE, SPFILE and INIT.ORA, and what is the function of each?

In the "old days," Oracle kept its parameters in a text file. By default, this parameter file's name was of the form initsid.ora where "sid" is replaced by the ORACLE_SID value. So if ORACLE_SID=ORCL, the parameter file was named "initorcl.ora." When two DBAs have a conversation about the parameter file, their SIDs may be different, so they refer to this parameter file as the "INIT.ORA" file. They just remove the SID from the file name, but it is implied.

Back in the old days, many of the initialization parameters were not dynamically modifiable. If you wanted to use a different set of parameters for weekend batch processing compared to online use during the week, the DBA would have two parameter files -- two INIT.ORAs. Now the DBA could not name them the same if they were in the same directory. So the DBA might have one parameter file called "init_weekday.ora" and another called "init_weekend.ora." To change the parameter values, the DBA would shut down the database Friday evening at 6 p.m. and start it up with the weekend's parameter values. Monday morning at 5 a.m., the database was restarted with the weekday's parameter values. The PFILE parameter of the STARTUP command could instruct Oracle to start with a specific Parameter FILE (PFILE) like:

STARTUP PFILE=/home/oracle/7.3/dbs/init_weekend.ora

Oracle DBAs would refer to the initialization parameter file as either the INIT.ORA or the PFILE. They are the same thing. The PFILE (INIT.ORA) is a text file which can be edited with any text editor.

In Oracle 9i, the binary parameter file was introduced. This is called the SPFILE (Server Parameter File). The SPFILE replaces the PFILE. For the most part, you can use the PFILE or the SPFILE, but not both. The SPFILE is a binary file and is not edited with any text editor. Instead, you modify the parameter's values with the ALTER SYSTEM SET command. For information on these parameter files, please refer to the following Oracle documentation:

Initialization Parameter Files and Server Parameter Files

Managing Initialization Parameters Using a Server Parameter File

There are benefits to the SPFILE over the PFILE. Since the SPFILE was introduced in Oracle 9i, I do not use the old PFILE any more. Some Oracle functionality does not work as well if you are not using the SPFILE. The self-tuning SGA works best when using a SPFILE as Oracle can store some information abou the SGA components in the SPFILE. THe SPFILE can hold parameters for multiple Oracle instances, something that is very important in a Real Application Clusters (RAC) environment. Oracle's Recovery Manager (RMAN) can automatically back up the SPFILE's contents while it cannot back up the PFILE.

Complete guide to becoming an Oracle database administrator

2007-07-31T11:16:00.000-07:00

Complete guide to becoming an Oracle database administrator.

Oracle Database 11g

2007-07-12T08:14:00.000-07:00

Oracle 11g new Features

NEW YORK CITY 11-JUL-2007 06:30 AM Oracle today introduced Oracle(r) Database 11g, the latest release of the world's most popular database. With more than 400 features, 15 million test hours, and 36,000 person-months of development, Oracle Database 11g is the most innovative and highest quality software product Oracle have ever announced.

"Oracle Database 11g, built on 30 years of design experience, delivers the next generation of enterprise information management," said Andy Mendelsohn, senior vice president of Database Server Technologies, Oracle. "More than ever, our customers are facing the challenges of, rapid data growth, increased data integration, and data connectivity IT cost pressures. Oracle Database 10g pioneered grid computing, and more than half of Oracle customers have moved to that release. Oracle Database 11g delivers the key features our customers have asked for to accelerate broad adoption and growth of Oracle grids; representing real innovation, that addresses real challenges, as told to us by real customers."

Oracle Database 11g can help organizations take control of their enterprise information, gain better business insight, and quickly and confidently adapt to an increasingly changing competitive environment. To do this, the new release extends Oracle's unique database clustering, data center automation, and workload management capabilities. With secure, highly available and scalable grids of low-cost servers and storage, Oracle customers can tackle the most demanding transaction processing, data warehousing, and content management applications.

Real Application Testing Helps Reduce Time, Risk and Cost of Change
Oracle Database 11g features advanced self-management and automation features to help organizations meet service level agreements. For example, with organizations facing regular database and operating system software upgrades, and hardware and system changes, Oracle Database 11g introduces Oracle Real Application Testing, making it the first database to help customers test and manage changes to their IT environment quickly, in a controlled, cost effective manner.

Increase Return On Investment for Disaster Recovery Solutions
In Oracle Database 11g, Oracle Data Guard enables customers to use their standby database to improve performance in their production environments as well as provide protection from system failures and site-wide disasters. Oracle Data Guard uniquely enables simultaneous read and recovery of a single standby database making it available for reporting, backup, testing and 'rolling' upgrades to production databases. By offloading workloads from production to a standby system, Oracle Data Guard helps enhance the performance of production systems and provides a more cost-effective disaster recovery solution.

Enhanced Information Lifecycle Management and Storage Management
Oracle Database 11g has significant new data partitioning and compression capabilities, for more cost-effective Information Lifecycle Management and storage management. Oracle Database 11g automates many manual data partitioning operations and extends existing range, hash and list partitioning to include interval, reference and virtual column partitioning. In addition, Oracle Database 11g provides a complete set of composite partitioning options, allowing storage management that is driven by business rules.

Building on its long-standing data compression capabilities, Oracle Database 11g offers advanced data compression for both structured and unstructured (LOB) data managed in transaction processing, data warehousing, and content management environments. Compression ratios of 2x to 3x or more for all data can be achieved with the new advanced compression capabilities in Oracle Database 11g.

Total Recall of Data Changes
The new release also features "Oracle Total Recall," enabling administrators to query data in designated tables "as of" earlier times in the past. This offers an easy, practical way to add a time dimension to data for change tracking, auditing, and compliance.

Maximum Availability of Information
Oracle has consistently led the industry in protecting database applications from planned and unplanned downtime. Oracle Database 11g continues this lead by making it easier for administrators to meet their users' availability expectations. New availability features include Oracle Flashback Transaction which makes it easy to back out a transaction made in error, as well as any dependent transactions; Parallel Backup and Restore which helps improve the backup and restore performance of very large databases; and 'hot patching,' which improves system availability by allowing database patches to be applied without the need to shut databases down. In addition, a new advisor - Data Recovery Advisor - helps administrators significantly reduce recovery downtime by automating problem investigation, intelligently determining recovery plan and handling multiple failure situations.

Oracle Fast Files
The next-generation capability for storing large objects (LOBs) such as images, large text objects, or advanced data types ? including XML, medical imaging, and three-dimensional objects - within the database. Oracle Fast Files offers database applications performance fully comparable to file systems. By storing a wider range of enterprise information and retrieving it quickly and easily, enterprises can know more about their business and adapt more rapidly.

Faster XML
Oracle Database 11g includes significant performance enhancements to XML DB, a feature of Oracle database that enables customers to natively store, and manipulate XML data. Support for binary XML has been added offering customers a choice of XML storage options to match their specific application and performance requirements. XML DB also enables manipulation of XML data using industry standard interfaces with support for XQuery, Java Specification Requests (JSR)-170 and SQL/XML standards.

Transparent Encryption
Oracle Database 11g continues to build on its unmatched security capabilities through the addition of significant enhancements. The new release features improved Oracle Transparent Data Encryption capabilities beyond column level encryption. Oracle Database 11g offers tablespace encryption that can be utilized to encrypt entire tables, indexes, and other data storage. Encryption is also provided for LOBs stored in the database.

Embedded OLAP Cubes
Oracle Database 11g also provides data warehousing innovations. OLAP cubes are enhanced to behave as materialized views in the database. This allows developers to use industry standard SQL for data query, but still benefit from the high performance delivered by an OLAP cube. New Continuous Query Notification features allow applications to be immediately notified when important changes are made to database data without burdening the database with constant polling.

Connection Pooling and Query Result Caches
The performance and scalability features in Oracle Database 11g are designed to help organizations maintain a highly performant, scalable infrastructure to provide users' with the best quality of service. Oracle Database 11g further enhances Oracle's position as the industry's performance and scalability leader with new features such as Query Result Caches which improves application performance and scalability by caching and reusing the results of often called database queries and functions in database and application tiers, and Database Resident Connection Pooling which improves the scalability of web-based systems by providing connection pooling for non-multi-threaded applications.

Enhanced Application Development
Oracle Database 11g offers developers a choice of development tools, and a streamlined application development process that takes full advantage of key Oracle Database 11g features. These include new features such as Client Side Caching, Binary XML for faster application performance, XML processing, and the storing and retrieving of files. In addition, Oracle Database 11g also includes a new Java just-in-time Compiler to execute database Java procedures faster without the need for a third party compiler; native integration with Visual Studio 2005 for developing .NET applications on Oracle; Access migration tools with Oracle Application Express; and SQL Developer easy query building feature for fast coding of SQL and PL/SQL routines.

Enhanced Self-Management and Automation
The manageability features in Oracle Database 11g are designed to help organizations easily manage enterprise grids and deliver on their users' service level expectations. Oracle Database 11g introduces more self-management and automation that will help customers reduce their system management costs, while increasing performance, scalability, availability and security of their database applications. New manageability capabilities in Oracle Database 11g include Automatic SQL and memory tuning, a new Partitioning Advisor which automatically advises administrators on how to partition tables and indexes in order to improve performance, and enhanced performance diagnostics for database clusters. In addition, Oracle Database 11g includes a new Support Workbench which provides an easy-to-use interface that presents database health-related incidents to administrators along with information on how to quickly manage the resolution of incidents.

Oracle is the #1 Database: Gartner 2006 Worldwide RDBMS Market Share Reports 47.1 Percent Share for Oracle
Gartner recently published their market share numbers by operating system for 2006 based on total software revenues. According to Gartner, Oracle:

* Has 47.1 percent share (up from 46.8 percent in 2005);

* Has revenue growth of 14.9 percent, faster than the market average of 14.2 percent with US$7.2 Billions in revenues; and,

* Continues to hold more market share than its two closest competitors combined.

About Oracle Database 11g
Oracle Database is the only database designed for grid computing. With the release of Oracle Database 11g, Oracle is making the management of enterprise information easier than ever; enabling customers to know more about their business and innovate more quickly. Oracle Database 11g delivers superior performance, scalability, availability, security and ease of management on a low-cost grid of industry standard storage and servers. Oracle Database 11g is designed to be effectively deployed on everything from small blade servers to the biggest SMP servers and clusters of all sizes. It features automated management capabilities for easy, cost-effective operation. Oracle Database 11g's unique ability to manage all data from traditional business information to XML and 3D spatial information makes it the ideal choice to power transaction processing, data warehousing, and content management applications.

Database 11g, the long-awaited overhaul of Oracle's flagship database management system (DBMS), made its official debut today.
Database 11g, the first major overhaul of Oracle's flagship database management system in about four years, is set to make its official debut tomorrow, leaving some IT industry experts and Oracle users wondering if Oracle security and patching improvements will come along with it.

Oracle has caught a great deal of flack for its patching policies over the last couple of years, mainly for the time it takes to issue fixes.

"I know that Oracle has to spend an inordinate amount of time testing each patch or each bug fix before they release it in a patch," said Brian Peasland, an independent Oracle consultant. "But it seems that some of these bugs have been known for two years or even longer before they get patched, and that's just quite disturbing, actually."

Database 11g, which is entering the market after a nine-month beta testing period, offers a host of fully automated features, new testing-related capabilities, better overall performance, and many other new capabilities that are in line with the release's key themes of "innovation" and "change management," according to Oracle.

"Oracle has invested heavily into self-tuning capabilities, including automated storage and memory management and intelligent tuning advisors," said Donald K. Burleson, a well-known independent Oracle consultant. "Now, in 11g, Oracle closes the loop and offers intelligent automation tools to create a self-healing database. The most important 11g new automation features include fully automated memory tuning and fully automated SQL tuning, a major advance in database technology."

Oracle executives led by Charles Phillips, company president, were set to officially launch Database 11g at a kick-off event in New York City today. Database 11g is the company's first major DBMS revamp since it released Database 10g about four years ago. Oracle said the software will be commercially available on Linux sometime in August. Release dates for Database 11g on Microsoft Windows and other platforms have not been announced.

Database 11g features and functionality

Database 11g offers nearly 500 new features covering a wide range of areas, including manageability, high availability, scalability, infrastructure, content management, and business intelligence.

"Probably the most important [new feature] is our Real Application Testing capability," said Robert G. Shimp, a vice president with Oracle's global technology business unit. "This allows us to dramatically shrink the time it takes for a customer to do a database upgrade."

Oracle says the system offers enhanced self-management and automated features designed to help organizations manage enterprise grids and deliver on service-level agreements. Key among them are automatic SQL and memory tuning.

More Database 11g info:

Database 11g debut raises Oracle security questions

Oracle expert looks ahead to Database 11g

Oracle Database 11g to feature XML enhancements

Oracle Database 11g preview

Jeff S. Buelt, the director of information technology at Pro Staff, a Minneapolis-based temporary staffing firm that runs several versions of Oracle, says features like automatic SQL tuning will be a welcome change when the time comes to upgrade.

"We have lots of users running queries and reports," Buelt said. "It would be very nice to be able to tune the system easier and faster."

Andy Mendelsohn, Oracle's senior vice president of database server technologies, said other new features in Database 11g, such as enhanced partitioning and storage-related capabilities, were created to help organizations deal with the dramatic growth in data resulting from relatively new data-retention regulations like Sarbanes-Oxley.

Along those lines, Database 11g offers enhanced data compression capabilities. Experts point out that compressing data could have an overhead in terms of performance. But they add that the performance cost is probably negligible compared to the storage-related savings.

"The sizes of databases are growing exponentially [and] the whole cost of storage is getting out of control," Mendelsohn said. "By using a combination of our partitioning technologies -- what we call Information Lifecycle Management -- and our compression technologies, you can go from in the order of a million-dollar storage system down to $50,000, or something of that sort."

Database 11g also offers Database Replay and SQL Replay capabilities, which give users a better understanding of how database changes affect SQL performance.

"The Database Replay basically provides the ability to replay the workload from production onto test environments," said Noel Yuhanna, a database analyst with Cambridge, Mass.-based Forrester Research. "It really minimizes some of these challenges and issues which customers are having around application deployments. We believe that around 20% of applications typically fail because [proper] testing is not there, and the Database Replay mitigates this risk."

Database 11g's newly included Oracle Data Guard technology lets companies use their standby systems to improve performance in production environments while providing protection from system failures and disasters, according to Oracle. The technology enables users to take snapshots of database systems for testing, reporting, backups and rolling upgrades.

Oracle Database 11g will also offer significant new XML-related features and enhancements. Oracle says the main XML features focus on XML DB, the company's XML storage and retrieval technology, and they include a new binary XML data type, a new XML index, and enhanced support for XQuery and other emerging standards. Oracle expert Donald Burleson wrote that Database 11g provides support for schema-based Document Type Definitions, which let users describe the structure of XML documents.

Some other new Database 11g features include Quick Fault Resolution, the ability to automatically retain all the diagnostics related to a fault; online table and index redefinition; Database Repair Advisor, a wizard that helps DBAs deal with the fault diagnosis and resolution process; a new high-performance Large Objects infrastructure; native Java and PL/SQL compilers; and a re-engineered driver for PHP.

An Oracle and Microsoft role reversal?

Oracle is big with large companies, but hopes the fully automated features of Database 11g will help it achieve greater mindshare among smaller organizations, a market dominated by Microsoft SQL Server, according to industry experts. Meanwhile, they say, Microsoft is hoping to push SQL Server further into the high end of the market.

"SQL Server is driving up, Oracle is driving down," Burleson said. "And part of that drive-down that Oracle is doing in 11g is putting artificial intelligence in the database. That's where I think that Oracle is going to beat the daylights out of SQL Server."

Here is a comprehensive list of Oracle 11g new Features from dba-oracle.com.

table_dep.sql

2007-07-12T06:56:00.001-07:00

-- -----------------------------------------------------------------------------------
-- File Name : table_dep.sql
-- Author : Sachchida Ojha
-- Description : Displays a list dependencies for the specified table.
-- Requirements : Access to the ALL views.
-- Call Syntax : @table_dep (table-name) (schema-name)
-- Last Modified: 15/07/2000
-- -----------------------------------------------------------------------------------
PROMPT
SET VERIFY OFF
SET FEEDBACK OFF
SET LINESIZE 255
SET PAGESIZE 1000

SELECT ad.referenced_name "Object",
ad.name "Ref Object",
ad.type "Type",
Substr(ad.referenced_owner,1,10) "Ref Owner",
Substr(ad.referenced_link_name,1,20) "Ref Link Name"
FROM all_dependencies ad
WHERE ad.referenced_name = Upper('&&1')
AND ad.owner = Upper('&&2')
ORDER BY 1,2,3;

SET VERIFY ON
SET FEEDBACK ON
SET PAGESIZE 14
PROMPT

user_hit_ratio.sql

2007-07-12T06:55:00.001-07:00

-- -----------------------------------------------------------------------------------
-- File Name : user_hit_ratio.sql
-- Author : Sachchida Ojha
-- Description : Displays the Cache Hit Ratio per user.
-- Requirements : Access to the V$ views.
-- Call Syntax : @user_hit_ratio
-- Last Modified: 15/07/2000
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
COLUMN "Hit Ratio %" FORMAT 999.99

SELECT a.username "Username",
b.consistent_gets "Consistent Gets",
b.block_gets "DB Block Gets",
b.physical_reads "Physical Reads",
Round(100* (b.consistent_gets + b.block_gets - b.physical_reads) /
(b.consistent_gets + b.block_gets),2) "Hit Ratio %"
FROM v$session a,
v$sess_io b
WHERE a.sid = b.sid
AND (b.consistent_gets + b.block_gets) > 0
AND a.username IS NOT NULL;

unusable_indexes.sql

2007-07-12T06:54:00.000-07:00

-- -----------------------------------------------------------------------------------
-- File Name : unusable_indexes.sql
-- Author : Sachchida Ojha
-- Description : Displays unusable indexes for the specified schema or all schemas.
-- Requirements : Access to the DBA views.
-- Call Syntax : @unusable_indexes (schema-name or all)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET VERIFY OFF

SELECT owner,
index_name
FROM dba_indexes
WHERE owner = DECODE(UPPER('&1'), 'ALL', owner, UPPER('&1'))
AND status NOT IN ('VALID', 'N/A')
ORDER BY owner, index_name;

top_sql.sql

2007-07-11T10:38:00.002-07:00

-- -----------------------------------------------------------------------------------
-- File Name : top_sql.sql
-- Author : Sachchida Ojha
-- Description : Displays a list of SQL statements that are using the most resources.
-- Comments : The address column can be use as a parameter with SQL_Text.sql to
-- display the full statement.
-- Requirements : Access to the V$ views.
-- Call Syntax : @top_sql (number)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

SELECT *
FROM (SELECT Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a
ORDER BY 2 DESC)
WHERE rownum <= &&1;

SET PAGESIZE 14

top_sessions.sql

2007-07-11T10:38:00.001-07:00

-- -----------------------------------------------------------------------------------
-- File Name : top_sessions.sql
-- Author : Sachchida Ojha
-- Description : Displays information on all database sessions ordered by executions.
-- Requirements : Access to the V$ views.
-- Call Syntax : @top_sessions.sql (reads, execs or cpu)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20

SELECT NVL(a.username, '(oracle)') AS username,
a.osuser,
a.sid,
a.serial#,
c.value AS &1,
a.lockwait,
a.status,
a.module,
a.machine,
a.program,
TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session a,
v$sesstat c,
v$statname d
WHERE a.sid = c.sid
AND c.statistic# = d.statistic#
AND d.name = DECODE(UPPER('&1'), 'READS', 'session logical reads',
'EXECS', 'execute count',
'CPU', 'CPU used by this session',
'CPU used by this session')
ORDER BY c.value DESC;

SET PAGESIZE 14

session_io.sql

2007-07-11T10:37:00.001-07:00

-- -----------------------------------------------------------------------------------
-- File Name : session_io.sql
-- Author : Sachchida Ojha
-- Description : Displays I/O information on all database sessions.
-- Requirements : Access to the V$ views.
-- Call Syntax : @session_io
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000

COLUMN username FORMAT A15

SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
si.block_gets,
si.consistent_gets,
si.physical_reads,
si.block_changes,
si.consistent_changes
FROM v$session s,
v$sess_io si
WHERE s.sid = si.sid
ORDER BY s.username, s.osuser;

SET PAGESIZE 14

open_cursors.sql

2007-07-11T10:36:00.001-07:00

-- -----------------------------------------------------------------------------------
-- File Name : open_cursors.sql
-- Author : Sachchida Ojha
-- Description : Displays a list of all cursors currently open.
-- Requirements : Access to the V$ views.
-- Call Syntax : @open_cursors
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SELECT a.user_name,
a.sid,
a.sql_text
FROM v$open_cursor a
ORDER BY 1,2
/

locked_objects.sql

2007-07-11T10:35:00.001-07:00

-- -----------------------------------------------------------------------------------
-- File Name : locked_objects.sql
-- Author : Sachchida Ojha
-- Description : Lists all locked objects.
-- Requirements : Access to the V$ views.
-- Call Syntax : @locked_objects
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

SELECT a.object_name,
a.owner object_owner,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.session_id sid,
b.oracle_username,
b.os_user_name
FROM all_objects a,
v$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1;

SET PAGESIZE 14
SET VERIFY ON

high_water_mark.sql

2007-07-11T10:33:00.001-07:00

-- -----------------------------------------------------------------------------------
-- File Name : high_water_mark.sql
-- Author : Sachchida Ojha
-- Description : Displays the High Water Mark for the specified table, or all tables.
-- Requirements : Access to the Dbms_Space.
-- Call Syntax : @high_water_mark (table_name or all) (schema-name)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET SERVEROUTPUT ON
SET VERIFY OFF

DECLARE
CURSOR cu_tables IS
SELECT a.owner,
a.table_name
FROM all_tables a
WHERE a.table_name = Decode(Upper('&&1'),'ALL',a.table_name,Upper('&&1'))
AND a.owner = Upper('&&2');

op1 NUMBER;
op2 NUMBER;
op3 NUMBER;
op4 NUMBER;
op5 NUMBER;
op6 NUMBER;
op7 NUMBER;
BEGIN

Dbms_Output.Disable;
Dbms_Output.Enable(1000000);
Dbms_Output.Put_Line('TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK');
Dbms_Output.Put_Line('------------------------------ --------------- --------------- ---------------');
FOR cur_rec IN cu_tables LOOP
Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7);
Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') ||
LPad(op3,15,' ') ||
LPad(op1,15,' ') ||
LPad(Trunc(op1-op3-1),15,' '));
END LOOP;

END;
/

SET VERIFY ON

grant_update.sql

2007-07-11T10:32:00.003-07:00

-- -----------------------------------------------------------------------------------
-- File Name : grant_update.sql
-- Author : Sachchida Ojha
-- Description : Grants update on current schemas tables to the specified user/role.
-- Call Syntax : @grant_update (schema-name)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'GRANT UPDATE ON "' || u.table_name || '" TO &1;'
FROM user_tables u
WHERE NOT EXISTS (SELECT '1'
FROM all_tab_privs a
WHERE a.grantee = UPPER('&1')
AND a.privilege = 'UPDATE'
AND a.table_name = u.table_name);

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

grant_select.sql

2007-07-11T10:32:00.001-07:00

-- -----------------------------------------------------------------------------------
-- File Name : grant_select.sql
-- Author : Sachchida Ojha
-- Description : Grants select on current schemas tables, views & sequences
-- to the specified user/role.
-- Call Syntax : @grant_select (schema-name)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'GRANT SELECT ON "' || u.object_name || '" TO &1;'
FROM user_objects u
WHERE u.object_type IN ('TABLE','VIEW','SEQUENCE')
AND NOT EXISTS (SELECT '1'
FROM all_tab_privs a
WHERE a.grantee = UPPER('&1')
AND a.privilege = 'SELECT'
AND a.table_name = u.object_name);

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

grant_insert.sql

2007-07-11T10:31:00.001-07:00

-- -----------------------------------------------------------------------------------
-- File Name : grant_insert.sql
-- Author : Sachchida Ojha
-- Description : Grants insert on current schemas tables to
-- the specified user/role.
-- Call Syntax : @grant_insert (schema-name)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'GRANT INSERT ON "' || u.table_name || '" TO &1;'
FROM user_tables u
WHERE NOT EXISTS (SELECT '1'
FROM all_tab_privs a
WHERE a.grantee = UPPER('&1')
AND a.privilege = 'INSERT'
AND a.table_name = u.table_name);

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

grant_execute.sql

2007-07-11T10:30:00.002-07:00

-- -----------------------------------------------------------------------------------
-- File Name : grant_execute.sql
-- Author : Sachchida Ojha
-- Description : Grants execute on current schemas code objects
-- to the specified user/role.
-- Call Syntax : @grant_execute (schema-name)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'GRANT EXECUTE ON "' || u.object_name || '" TO &1;'
FROM user_objects u
WHERE u.object_type IN ('PACKAGE','PROCEDURE','FUNCTION')
AND NOT EXISTS (SELECT '1'
FROM all_tab_privs a
WHERE a.grantee = UPPER('&1')
AND a.privilege = 'EXECUTE'
AND a.table_name = u.object_name);

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

grant_delete.sql

2007-07-11T10:30:00.001-07:00

-- -----------------------------------------------------------------------------------
-- File Name : grant_delete.sql
-- Author : Sachchida Ojha
-- Description : Grants delete on current schemas tables to the
-- specified user/role.
-- Call Syntax : @grant_delete (schema-name)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'GRANT DELETE ON "' || u.table_name || '" TO &1;'
FROM user_tables u
WHERE NOT EXISTS (SELECT '1'
FROM all_tab_privs a
WHERE a.grantee = UPPER('&1')
AND a.privilege = 'DELETE'
AND a.table_name = u.table_name);

SPOOL OFF

@temp.sql

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

enable_fkeys.sql

2007-07-11T10:28:00.002-07:00

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER TABLE "' || a.table_name || '" ENABLE CONSTRAINT "' || a.constraint_name || '";'
FROM all_constraints a
WHERE a.constraint_type = 'R'
AND a.owner = Upper('emsowner');

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

disable_fkeys.sql

2007-07-11T10:28:00.001-07:00

SET PAGESIZE 0
SET FEEDBACK OFF
SET LINESIZE 150
SET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER TABLE "' || a.table_name || '" DISABLE CONSTRAINT "' || a.constraint_name || '";'
FROM all_constraints a
WHERE a.constraint_type = 'R'
AND a.owner = Upper('emsowner');

SPOOL OFF

@temp.sql

db_info.sql

2007-07-11T10:27:00.001-07:00

-- -----------------------------------------------------------------------------------
-- File Name : db_info.sql
-- Author : Sachchida Ojha
-- Description : Displays general information about the database.
-- Requirements : Access to the v$ views.
-- Call Syntax : @db_info
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 1000
SET LINESIZE 100
SET FEEDBACK OFF

SELECT *
FROM v$database;

SELECT *
FROM v$instance;

SELECT *
FROM v$version;

SELECT a.name,
a.value
FROM v$sga a;

SELECT Substr(c.name,1,60) "Controlfile",
NVL(c.status,'UNKNOWN') "Status"
FROM v$controlfile c
ORDER BY 1;

SELECT Substr(d.name,1,60) "Datafile",
NVL(d.status,'UNKNOWN') "Status",
d.enabled "Enabled",
LPad(To_Char(Round(d.bytes/1024000,2),'9999990.00'),10,' ') "Size (M)"
FROM v$datafile d
ORDER BY 1;

SELECT l.group# "Group",
Substr(l.member,1,60) "Logfile",
NVL(l.status,'UNKNOWN') "Status"
FROM v$logfile l
ORDER BY 1,2;

PROMPT
SET PAGESIZE 14
SET FEEDBACK ON

create_data.sql

2007-07-11T10:26:00.001-07:00

-- -----------------------------------------------------------------------------------
-- File Name : create_data.sql
-- Author : Sachchida Ojha
-- Description : Creates the DDL to repopulate the specified table.
-- Call Syntax : @create_data (table-name) (schema)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET LINESIZE 1000
SET SERVEROUTPUT ON
SET FEEDBACK OFF
SET PAGESIZE 0
SET VERIFY OFF
SET TRIMSPOOL ON
SET TRIMOUT ON

ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

SPOOL temp.sql

DECLARE

CURSOR c_columns (p_table_name IN VARCHAR2,
p_owner IN VARCHAR2) IS
SELECT Lower(a.column_name) column_name,
a.data_type
FROM all_tab_columns a
WHERE a.table_name = p_table_name
AND a.owner = p_owner
AND a.data_type IN ('CHAR','VARCHAR2','DATE','NUMBER','INTEGER');

v_table_name VARCHAR2(30) := Upper('&&1');
v_owner VARCHAR2(30) := Upper('&&2');

FUNCTION Format_Col(p_column IN VARCHAR2,
p_datatype IN VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
IF p_datatype IN ('CHAR','VARCHAR2','DATE') THEN
RETURN ''' || Decode(' || p_column || ',NULL,''NULL'','''''''' || ' || p_column || ' || '''''''') || ''';
ELSE
RETURN ''' || Decode(' || p_column || ',NULL,''NULL'',' || p_column || ') || ''';
END IF;
END;

BEGIN

Dbms_Output.Disable;
Dbms_Output.Enable(1000000);

Dbms_Output.Put_Line('SELECT ''INSERT INTO ' || Lower(v_owner) || '.' || Lower(v_table_name));
Dbms_Output.Put_Line('(');
<< Columns_Loop >>
FOR cur_rec IN c_columns (v_table_name, v_owner) LOOP
IF c_columns%ROWCOUNT != 1 THEN
Dbms_Output.Put_Line(',');
END IF;
Dbms_Output.Put(cur_rec.column_name);
END LOOP Columns_Loop;
Dbms_Output.New_Line;
Dbms_Output.Put_Line(')');
Dbms_Output.Put_Line('VALUES');
Dbms_Output.Put_Line('(');

<< Data_Loop >>
FOR cur_rec IN c_columns (v_table_name, v_owner) LOOP
IF c_columns%ROWCOUNT != 1 THEN
Dbms_Output.Put_Line(',');
END IF;
Dbms_Output.Put(Format_Col(cur_rec.column_name, cur_rec.data_type));
END LOOP Data_Loop;
Dbms_Output.New_Line;
Dbms_Output.Put_Line(');''');
Dbms_Output.Put_Line('FROM ' || Lower(v_owner) || '.' || Lower(v_table_name) );
Dbms_Output.Put_Line('/');

END;
/

SPOOL OFF

SET LINESIZE 1000
SPOOL table_data.sql

@temp.sql

SPOOL OFF

SET PAGESIZE 14
SET FEEDBACK ON

compile_invalid_objects.sql

2007-07-11T10:25:00.001-07:00

-- -----------------------------------------------------------------------------------
-- File Name : compile_invalid_objects.sql
-- Author : Sachchida Ojha
-- Description : Compiles all invalid objects for user schema
-- Call Syntax : @compile_invalid_objects.sql
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------

spool cinv.sql
select 'alter '||decode(object_type,'PACKAGE BODY','PACKAGE',object_type)
|| ' '||'.'|| object_name ||' compile' ||
decode(object_type, 'PACKAGE BODY', ' body;', ';')
from user_objects
where status = 'INVALID'
/
spool off
set termout on feedback on echo on
spool cinvobj.lis
@cinv.sql

column object_name format A30
column object_type format A20
select object_name, object_type, status
from user_objects
where status = 'INVALID'
/
spool off
set lines 80
prompt Spool file saved at cinvobj.lis
prompt Script file saved at cinv.sql
prompt

compile_all.sql

2007-07-11T10:24:00.001-07:00

-- -----------------------------------------------------------------------------------
-- File Name : compile_all.sql
-- Author : Sachchida Ojha
-- Description : Compiles all invalid objects for specified schema, or all schema.
-- Requirements : Requires all other "Compile_All" scripts.
-- Call Syntax : @compile_all (schema-name or all)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
@Compile_All_Specs &&1
@Compile_All_Bodies &&1
@Compile_All_Procs &&1
@Compile_All_Funcs &&1
@Compile_All_Views &&1

compile_all_views.sql

2007-07-11T10:23:00.002-07:00

-- -----------------------------------------------------------------------------------
-- File Name : compile_all_views.sql
-- Author : Sachchida Ojha
-- Description : Compiles all invalid views for specified schema, or all schema.
-- Call Syntax : @compile_all_views (schema-name or all)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER VIEW ' || a.owner || '.' || a.object_name || ' COMPILE;'
FROM all_objects a
WHERE a.object_type = 'VIEW'
AND a.status = 'INVALID'
AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

compile_all_trigs.sql

2007-07-11T10:23:00.001-07:00

-- -----------------------------------------------------------------------------------
-- File Name : compile_all_trigs.sql
-- Author : Sachchida Ojha
-- Description : Compiles all invalid triggers for specified schema, or all schema.
-- Call Syntax : @compile_all_trigs (schema-name or all)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER TRIGGER ' || a.owner || '.' || a.object_name || ' COMPILE;'
FROM all_objects a
WHERE a.object_type = 'TRIGGER'
AND a.status = 'INVALID'
AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

compile_all_specs.sql

2007-07-11T10:22:00.001-07:00

-- -----------------------------------------------------------------------------------
-- File Name : compile_all_specs.sql
-- Author : Sachchida Ojha
-- Description : Compiles all invalid package specifications for specified schema,
-- or all schema.
-- Call Syntax : @compile_all_specs (schema-name or all)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER PACKAGE ' || a.owner || '.' || a.object_name || ' COMPILE;'
FROM all_objects a
WHERE a.object_type = 'PACKAGE'
AND a.status = 'INVALID'
AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

compile_all_procs.sql

2007-07-11T10:21:00.002-07:00

-- -----------------------------------------------------------------------------------
-- File Name : compile_all_procs.sql
-- Author : Sachchida Ojha
-- Description : Compiles all invalid procedures for specified schema, or all schema.
-- Call Syntax : @compile_all_procs (schema-name or all)
-- Last Modified: 02/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER PROCEDURE ' || a.owner || '.' || a.object_name || ' COMPILE;'
FROM all_objects a
WHERE a.object_type = 'PROCEDURE'
AND a.status = 'INVALID'
AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

compile_all_funcs.sql

2007-07-11T10:21:00.001-07:00

-- -----------------------------------------------------------------------------------
-- File Name : compile_all_funcs.sql
-- Author : Sachchida Ojha
-- Description : Compiles all invalid functions for specified schema, or all schema.
-- Call Syntax : @compile_all_funcs (schema-name or all)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER FUNCTION ' || a.owner || '.' || a.object_name || ' COMPILE;'
FROM all_objects a
WHERE a.object_type = 'FUNCTION'
AND a.status = 'INVALID'
AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

compile_all_bodies.sql

2007-07-11T10:12:00.001-07:00

-- -----------------------------------------------------------------------------------
-- File Name : compile_all_bodies.sql
-- Author : Sachchida Ojha
-- Description : Compiles all invalid package bodies for specified schema, or all schema.
-- Call Syntax : @compile_all_bodies (schema-name or all)
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'ALTER PACKAGE ' || a.owner || '.' || a.object_name || ' COMPILE BODY;'
FROM all_objects a
WHERE a.object_type = 'PACKAGE BODY'
AND a.status = 'INVALID'
AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

access.sql

2007-07-11T10:11:00.001-07:00

-- -----------------------------------------------------------------------------------
-- File Name : access.sql
-- Author : Sachchida Ojha
-- Description : Lists all objects being accessed in the schema.
-- Call Syntax : @access (schema-name)
-- Requirements : Access to the v$views.
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 255
SET VERIFY OFF

SELECT Substr(a.object,1,30) object,
a.type,
a.sid,
b.username,
b.osuser,
b.program
FROM v$access a,
v$session b
WHERE a.sid = b.sid
AND a.owner = Upper('&1');

PROMPT
SET PAGESIZE 18

active_sessions.sql

2007-07-11T10:10:00.000-07:00

-- -----------------------------------------------------------------------------------
-- File Name : active_sessions.sql
-- Author : Sachchida Ojha
-- Description : Displays information on all active database sessions.
-- Requirements : Access to the V$ views.
-- Call Syntax : @active_sessions
-- Last Modified: 04/28/2005
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000

COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20

SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
p.spid,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
AND s.status = 'ACTIVE'
ORDER BY s.username, s.osuser;

SET PAGESIZE 14

Hot Backup Script

2007-07-11T09:04:00.001-07:00

#!/bin/ksh
############################################################################
####
#
# Hot Backup Script
# Sachchida Ojha
############################################################################
###
#
#
#DBA=sojha@dbceo.com; export DBA
ORACLE_HOME=/export/home/oracle/OraHome1; export ORACLE_HOME

#ARCH_DEST=/u05/arch/hsphtpr; export ARCH_DEST
ORACLE_SID=arsystem; export ORACLE_SID

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib; export LD_LIBRARY_PATH
LOG_DIR=$HOME/scripts/log;export LOG_DIR

LOG_FILE=$LOG_DIR/do_hot_back_$ORACLE_SID.log ; export LOG_FILE
ALERT_LOG=$ORACLE_HOME/admin/$ORACLE_SID/bdump/alert_${ORACLE_SID}.log;
export ALERT_LOG
UDUMP=$ORACLE_HOME/admin/$ORACLE_SID/udump ;export UDUMP

BACKUP_BASE=/u01/oracle_backup/hotbackup/$ORACLE_SID; export
BACKUP_BASE

#
# Check if DB shutdown normally don't check the rest
#
ENUM=`tail -35 $ALERT_LOG | egrep -c 'CLOSE'`
if [ $ENUM -ne 0 ]; then
echo "`uname -n`:$ORACLE_SID instance was down." > $LOG_FILE
exit
fi

#
# Creating backup directory and purge directory
#
A=`date | awk '{print $2}'`; export A
B=`date | awk '{print $3}'`; export B
C=`date | awk '{print $6}'`; export C

BACKUP_DIR=$BACKUP_BASE/$A'_'$B'_'$C; export BACKUP_DIR

echo "Starting hot backup of $ORACLE_SID at" $(date) > $LOG_FILE
echo " " >> $LOGFILE
echo "Creating backup directory $BACKUP_DIR...\c" >> $LOG_FILE
echo " " >> $LOGFILE
mkdir $BACKUP_DIR || exit 1
echo "Done" >> $LOG_FILE
df -k $BACKUP_BASE >> $LOG_FILE

# Creating list of tablespaces
TSP=$HOME/scripts/tmp/hot_tsp.lis
export TSP

$ORACLE_HOME/bin/sqlplus -s internal < $TSP
set head off pages 0
set lines 150
set echo off verify off feedback off
select name from sys.ts$ where online$ !=3 and name != 'TEMP'
order
by ts# desc;
exit;
EOF

# Declaring variables for log file and shell script names to be used in
the
following loop

cat $TSP | while read LINE
do
TSPNAME=$LINE; export TSPNAME
COPY_FILE_NAME=$HOME/scripts/tmp/$TSPNAME.ksh; export
COPY_FILE_NAME
BEGIN_TBS_LOG=$HOME/scripts/tmp/$TSPNAME.begin; export
BEGIN_TBS_LOG
END_TBS_LOG=$HOME/scripts/tmp/$TSPNAME.end; export END_TBS_LOG

# Altering the tablespace to Begin backup mode

$ORACLE_HOME/bin/sqlplus -s internal < $BEGIN_TBS_LOG
set head off pages 0
set lines 150
alter tablespace $TSPNAME begin backup;
exit;
EOF

# Check for error in the begin backup execution and email error

NUM=`egrep -ci 'ORA-|error' $BEGIN_TBS_LOG`
if [ $NUM -gt 0 ]
then
echo "Failed $ORACLE_SID begin backup at "`date ' %m/%d/%y,%T'`
>>
$LOG_FILE
echo "" >> $LOG_FILE
SUBJ="ERROR: `uname -n`:$ORACLE_SID $TSPNAME failed begin
backup."
echo $SUBJ | mailx -s "$SUBJ" $DBA >> $LOG_FILE
exit 1
fi
rm -f $BEGIN_TBS_LOG

#Generate shell scripts to compress and copy different tablespaces and
run
them

$ORACLE_HOME/bin/sqlplus -s internal < $COPY_FILE_NAME
set head off pages 0
set lines 150
set echo off verify off feedback off

select '#!/bin/ksh' from dual;
select 'cp ' || file_name || ' $BACKUP_DIR' ||
substr(file_name,
instr(file_name,'/',-1)) from dba_data_files
where tablespace_name='$TSPNAME';
select 'wait;' from dual;
exit;
EOF
# Run the compress and copy and email if any errors

chmod 744 $COPY_FILE_NAME
if $COPY_FILE_NAME
then
echo "Finished copy and compress Datafiles of" $TSPNAME " at"
$(date) >> $LOG_FILE
else
echo "Copy and Compress Failed for tablespace " $TSPNAME " at"
$(date) >> $LOG_FILE
SUBJ="ERROR: `uname -n`:$ORACLE_SID $COPY_FILE_NAME failed to
copy."
echo $SUBJ | mailx -s "$SUBJ" $DBA >> $LOG_FILE
fi
rm $COPY_FILE_NAME

# Put the tablespace back in end backup mode

$ORACLE_HOME/bin/sqlplus -s internal < $END_TBS_LOG
alter tablespace $TSPNAME end backup;
exit;
EOF

# Check the end backup log for any errors and email if any

NUM=`egrep -ci 'ORA-|error' $END_TBS_LOG`
if [ $NUM -gt 0 ]
then
echo "Failed $ORACLE_SID end backup at "`date ' %m/%d/%y,%T'` >>
$LOG_FILE
echo "" >> $LOG_FILE
SUBJ="ERROR: `uname -n`:$ORACLE_SID $TSPNAME failed to end
backup"
echo $SUBJ | mailx -s "$SUBJ" $DBA >> $LOG_FILE
exit 1
fi
rm $END_TBS_LOG
done

echo "Backing up the latest 7 archive files generated today" >>
$LOG_FILE
#
#
$ORACLE_HOME/bin/sqlplus -s internal <alter system archive log current;
exit;
EOF

#cd $ARCH_DEST || exit 1
#ls -ltr *.arc | tail -7 | awk '{print $9}' >
$HOME/scripts/tmp/ARCHLIST
#cat $HOME/scripts/tmp/ARCHLIST | while read EACHLINE
#do
#/usr/local/bin/gzip -cv < $ARCH_DEST/$EACHLINE >
$BACKUP_DIR/$EACHLINE.gz &
#done

echo "Taking a binary backup of the latest controlfile " >> $LOG_FILE
$ORACLE_HOME/bin/sqlplus -s internal < alter database backup controlfile to
'$BACKUP_DIR/ctrl_file_binary_copy';
alter database backup controlfile to trace;
exit;
EOF

cd $ORACLE_HOME/admin/$ORACLE_SID/udump
echo " " >> $LOGFILE
echo " Taking an ascii copy of control file " >> $LOGFILE
cp `ls -ltr $ORACLE_HOME/admin/$ORACLE_SID/udump/*.trc|tail -1| awk
'{print
$9}'` $BACKUP_DIR

#
#
echo " Backup Done" >> $LOG_FILE
df -k $BACKUP_BASE >> $LOG_FILE

if [ -e $BACKUP_DIR/ctrl_file_binary_copy ]
then
cd $BACKUP_BASE
echo " " >> $LOGFILE
echo "Seems like today's backup completed Okay" >> $LOGFILE
echo " " >> $LOGFILE
echo "Following older backup directories will be purged" >>
$LOG_FILE
echo " " >> $LOGFILE
/usr/bin/find . -mtime 1 -type d >> $LOG_FILE
/usr/bin/find . -mtime 1 -type d -exec /usr/bin/rm -rf {} \;

sqlplus internal << EOF > /tmp/verbackup.lis
set pages 1000 lines 400 feedback off echo off verify off trimspool
off;
column recover heading "Requires|Recovery?" format a10;
column time heading "Date Of|Last Backup" format a15;
column Name format a40;
column Mode format a20;

select substr(tablespace_name,1,20) "Tablespace
Name",substr(name,1,45)
"Name",
a.status "Status", decode(fuzzy,'YES','BACKUP','NORMAL') "Mode"
,
recover, time
from v\$datafile_header a, v\$backup b
where a.file#=b.file# order by tablespace_name,name;
exit
EOF
cat /tmp/verbackup.lis >> $LOG_FILE
else
echo "$BACKUP_DIR does not exist" >> $LOGFILE
fi
NUM=`egrep -ci 'ORA-|error' $LOG_FILE`
if [ $NUM -gt 0 ]
then
echo "Failed $ORACLE_SID online Hot backup at "`date
' %m/%d/%y,%T'`
>> $LOG_FILE
echo "" >> $LOG_FILE
SUBJ="ERROR: `uname -n`:$ORACLE_SID `basename $0` failed"
echo $SUBJ | mailx -s "$SUBJ" $DBA >> $LOG_FILE

else

echo "Completed $ORACLE_SID Hot backup at "`date
' %m/%d/%y,%T'` >>
$LOG_FILE
SUBJ="SUCCESS: `uname -n`:$ORACLE_SID `basename $0` completed."
cat $LOG_FILE | mailx -s "$SUBJ" $DBA >> $LOG_FILE
fi
exit

Oracle's history

2007-07-11T09:01:00.000-07:00

Oracle

Oracle Corporation was founded in 1977 in Redwood, California. They introduced the first Relational Database Management System based on the IBM System/R model and the first database management system utilizing IBM's Structured Query Language (SQL) technology.

What is Oracle's history?

1977
Relational Software Inc. (RSI - currently Oracle Corporation) established
1978
Oracle V1 ran on PDP-11 under RSX, 128 KB max memory. Written in assembly language. Implementation separated Oracle code and user code. Oracle V1 was never officially released.
1980
Oracle V2 released - the first commercially available relational database to use SQL. Oracle runs on on DEC PDP-11 machines. Coide is still written in PDP-11 assembly language, but now ran under Vax/VMS.
1982
Oracle V3 released, Oracle became the first DBMS to run on mainframes, minicomputers, and PC's (portable codebase). First release to employ transactional processing. Oracle V3's server code was written in C.
1983
Relational Software Inc. changed its name to Oracle Corporation.
1984
Oracle V4 released, introduced read consistency, was ported to multiple platforms, first interoperability between PC and server.
1986
Oracle V5 released. Featured true client/server, VAX-cluster support, and distributed queries. (first DBMS with distributed capabilities).
1987
CASE and 4GL toolset
1988
Oracle V6 released - PL/SQL introduced.
Oracle Financial Applications built on relational database.
1989
Released Oracle 6.2 with Symmetric cluster access using the Oracle Parallel Server
1991
Reached power of 1,000 TPS on a parallel computing machine.
First database to run on a massively parallel computer (Oracle Parallel Server).
1992
Released Oracle7 for Unix
1993
Rollout of Oracle's Cooperative Development Environment (CDE).
Introduction of Oracle Industries and the Oracle Media Server.
1994
Oracle's headquarters moved to present location.
Released Oracle 7.1 and Oracle7 for the PC.
1995
Reported gross revenues of almost $3 billion.
1995
OraFAQ.com website launched.
1997
Oracle8 released (supports more users, more data, higher availability, and object-relational features)
1998
Oracle announces support for the Intel Linux operating system
1999
Oracle8i (the "i" is for internet) or Oracle 8.1.5 with Java integration (JVM in the database)
2000
Oracle8i Release 2 released
Oracle now not only the number one in Databases but also in ERP Applications
Oracle9i Application Server generally available: Oracle tools integrated in middle tier
2001
Oracle9i Release 1 (with RAC and Advanced Analytic Service)
2002
Oracle9i Release 2
2004
Oracle10g Release 1 (10.1.0) available ("g" is for grid, the latest buzzword)
2005
The Oracle FAQ (this site) is 10 years old!
2005
Oracle10g Release 2 (10.2.0) available

Welcome to Oracle DBA Scripts Blog

2007-07-11T08:25:00.000-07:00

Welcome to Oracle DBA Scripts Blog. Here you will find useful oracle database scripts to do your routine jobs. If you have any useful scripts then you can add it into this blog.