Tuesday, May 28, 2013

Auditing FGA:


create user adlfga identified by adlfga;
grant resource,connect to adlfga;

BEGIN DBMS_FGA.ADD_POLICY(      object_schema => 'AE001T3',
object_name => 'ID_TICKET_DETAILS',
policy_name => 'TICKET_LPO_POLICY',
audit_condition => NULL,
audit_column => 'TICKET_LPO',
enable => true,
statement_types => ‘SELECT,INSERT,UPDATE,DELETE’
);
end;

conn adl_fga_handler/adl_fga_handler;
create table audit_event 2 (audit_event_no number);

create or replace procedure sp_audit(object_schema in varchar2,object_name in varchar2,policy_name in varchar2)ascount number;

begin
select nvl(max(audit_event_no),0) into count from audit_event;
insert into audit_event values (count+1); commit;

end;

select DB_USER,OS_USER,POLICY_NAME,SQL_TEXT, TIMESTAMP from dba_fga_audit_trail where POLICY_NAME='TICKET_LPO_POLICY';

How to Check the version of and Update opatch utility



Checking the opatch utility version:
$ cd $ORACLE_HOME/OPatch
$ opatch lsinventory

Invoking OPatch 10.2.0.4.3
Oracle Interim Patch Installer version 10.2.0.4.3
Copyright (c) 2007, Oracle Corporation.  All rights reserved.
Oracle Home       : /u01/app/oracle/product/10.2.0
Central Inventory : /u00/app/oracle/oraInventory
   from           : /var/opt/oracle/oraInst.loc
OPatch version    : 10.2.0.4.3
OUI version       : 10.2.0.4.0
OUI location      : /u01/app/oracle/product/10.2.0/oui
Log file location : /u01/app/oracle/product/10.2.0/cfgtoollogs/opatch/opatch2013-05-27_16-24-15PM.log
Lsinventory Output file location : /u01/app/oracle/product/10.2.0/cfgtoollogs/opatch/lsinv/lsinventory2013-05-27_16-24-15PM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):
Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 3                            10.2.0.4.0
There are 2 products installed in this Oracle Home.
Interim patches (1) :
Patch  8350262      : applied on Thu Nov 22 14:55:53 GMT-04:00 2012
   Created on 14 Sep 2010, 04:59:44 hrs PST8PDT
   Bugs fixed:
     8350262
--------------------------------------------------------------------------------
OPatch succeeded.



How to update the opatch utility?
Go to :
https://updates.oracle.com/Orion/PatchDetails/handle_plat_lang_change?release=80102000&plat_lang=46P&patch_file=&file_id=&password_required=&password_required_readme=&merged_trans=&aru=13116067&patch_num=6880880&patch_num_id=979663&default_release=80102000&default_plat_lang=23P&default_compatible_with=&patch_password=&orderby=&direction=&no_header=0&sortcolpressed=&tab_number=

Description OPatch 10.2
Product Universal Installer
Select a Release : ORACLE 10.2.0.0.0
Platform or Language   : ORACLE SOLARIS  ON SPARC(64-BIT)
Last Updated 07-NOV-2010
Size 27M (28674793 bytes)
Entitlement Class   Software
Classification   General

Download the patch
Transfer the patch p6880880_102000_SOLARIS64.zip to remote server and unzip it

Backup the existing OPatch directory
$ mv OPatch/ opatch_backup

$ mv p6880880_102000_SOLARIS64.zip $ORACLE_HOME
$ unzip_sparc p6880880_102000_SOLARIS64.zip
bash-3.00$ cd OPatch/
bash-3.00$ opatch lsinventory
Invoking OPatch 10.2.0.5.1

Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/10.2.0
Central Inventory : /u00/app/oracle/oraInventory
   from           : /var/opt/oracle/oraInst.loc
OPatch version    : 10.2.0.5.1
OUI version       : 10.2.0.4.0
OUI location      : /u01/app/oracle/product/10.2.0/oui
Log file location : /u01/app/oracle/product/10.2.0/cfgtoollogs/opatch/opatch2013-05-27_16-33-16PM.log
Patch history file: /u01/app/oracle/product/10.2.0/cfgtoollogs/opatch/opatch_history.txt
Lsinventory Output file location : /u01/app/oracle/product/10.2.0/cfgtoollogs/opatch/lsinv/lsinventory2013-05-27_16-33-16PM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):
Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 3                            10.2.0.4.0
There are 2 products installed in this Oracle Home.
Interim patches (1) :
Patch  8350262      : applied on Thu Nov 22 14:55:53 GMT-04:00 2012
Unique Patch ID:  13011839
   Created on 14 Sep 2010, 04:59:44 hrs PST8PDT
   Bugs fixed:
     8350262
--------------------------------------------------------------------------------
OPatch succeeded.
bash-3.00$

Thursday, May 2, 2013

Oracle terms and Ideas you need to know before beginning


Oracle Parser
It performs syntax analysis as well as semantic analysis of SQL statements for execution, expands views referenced in the query into separate query blocks, optimizing it and building (or locating) an executable form of that statement.

Hard Parse
A hard parse occurs when a SQL statement is executed, and the SQL statement is either not in theshared pool, or it is in the shared pool but it cannot be shared. A SQL statement is not shared if the metadata for the two SQL statements is different i.e. a SQL statement textually identical to a preexisting SQL statement, but the tables referenced in the two statements are different, or if the optimizer environment is different.

Soft Parse
A soft parse occurs when a session attempts to execute a SQL statement, and the statement is already in the shared pool, and it can be used (that is, shared). For a statement to be shared, all data, (including metadata, such as the optimizer execution plan) of the existing SQL statement must be equal to the current statement being issued.

Cost Based Optimizer
It generates a set of potential execution plans for SQL statements, estimates the cost of each plan, calls the plan generator to generate the plan, compares the costs, and then chooses the plan with the lowest cost. This approach is used when the data dictionary has statistics for at least one of the tables accessed by the SQL statements. The CBO is made up of the query transformer, the estimator and the plan generator.

EXPLAIN PLAN
A SQL statement that enables examination of the execution plan chosen by the optimizer for DML statements. EXPLAIN PLAN makes the optimizer to choose an execution plan and then to put data describing the plan into a database table. The combination of the steps Oracle uses to execute a DML statement is called an execution plan. An execution plan includes an access path for each table that the statement accesses and an ordering of the tables i.e. the join order with the appropriate join method.

Oracle Trace
Oracle utility used by Oracle Server to collect performance and resource utilization data, such as SQL parse, execute, fetch statistics, and wait statistics. Oracle Trace provides several SQL scripts that can be used to access server event tables, collects server event data and stores it in memory, and allows data to be formatted while a collection is occurring.

SQL Trace
It is a basic performance diagnostic tool to monitor and tune applications running against the Oracle server. SQL Trace helps to understand the efficiency of the SQL statements an application runs and generates statistics for each statement. The trace files produced by this tool are used as input for TKPROF.

TKPROF
It is also a diagnostic tool to monitor and tune applications running against the Oracle Server. TKPROF primarily processes SQL trace output files and translates them into readable output files, providing a summary of user-level statements and recursive SQL calls for the trace files. It also shows the efficiency of SQL statements, generate execution plans, and create SQL scripts to store statistics in the database.

Wednesday, May 1, 2013

Object Statistics Are Locked



ORA-38029 "Object Statistics Are Locked" - Possible Causes [ID 433240.1]

Cause: Using import (imp) or data pump import (impdp) to import a table without data results in the table's statistics being locked in 10gR2.

SQL> exec dbms_stats.gather_table_stats('AE1T3OBY','id_emp_leave_details',cascade => true);
BEGIN dbms_stats.gather_table_stats('AE1T3OBY','id_emp_leave_details',cascade => true); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 13437
ORA-06512: at "SYS.DBMS_STATS", line 13457
ORA-06512: at line 1

This query shows that the table statistics are locked.

SQL> select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;

Unlock it

Sql> exec dbms_stats.unlock_schema_stats('AE1T3OBY');

SQL> select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;

Now gather the table statistics


SQL> exec dbms_stats.gather_table_stats('AE1T3OBY','id_emp_leave_details',cascade => TRUE);

PL/SQL procedure successfully completed.


Gathering statistics sample:
begin
dbms_stats.gather_table_stats( ownname=> 'AE1T3', tabname=> 'ID_NUMBER_GEN_SOURCE' , estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE, cascade=> DBMS_STATS.AUTO_CASCADE, degree=> null, no_invalidate=> DBMS_STATS.AUTO_INVALIDATE, granularity=> 'AUTO', method_opt=> 'FOR ALL COLUMNS SIZE AUTO');
end;