CreateOUIProcess(): 13 : Permission denied

Hit the error when install 11.2.0.3 client on Redhat 5.4 x64

[oracle@test01 client]$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 664 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 4095 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2001-07-23_06-31-05AM. Please wait ...Error in CreateOUIProcess(): 13 : Permission denied


[oracle@test01 client]$ cd /tmp/OraInstall2001-07-23_06-31-05AM
[oracle@test01 OraInstall2001-07-23_06-31-05AM]$ ls -l
total 44
-rwxr-x--- 1 oracle dba 8218 Jul 23 06:31 command_output_8385
drwxr-x--- 3 oracle dba 4096 Jul 23 06:31 diagnostics
drwxrwx--- 3 oracle dba 4096 Jul 23 06:31 ext
drwxr-x--- 2 oracle dba 4096 Jul 23 06:31 images
-rwxr-x--- 1 oracle dba 4855 Jul 23 06:31 installActions2001-07-23_06-31-05AM.log
drwxr-x--- 6 oracle dba 4096 Jul 23 06:31 jdk
drwxr-x--- 8 oracle dba 4096 Jul 23 06:31 oui
drwxr-x--- 3 oracle dba 4096 Jul 23 06:31 srvm
[oracle@test01 OraInstall2001-07-23_06-31-05AM]$ find ./ -name java
./jdk/jre/bin/java
./jdk/bin/java
[oracle@test01 OraInstall2001-07-23_06-31-05AM]$ ls -l ./jdk/jre/bin/java
-rwxr-xr-x 1 oracle dba 66432 Jul 11  2011 ./jdk/jre/bin/java
[oracle@test01 OraInstall2001-07-23_06-31-05AM]$ ls -l ./jdk/bin/java
-rwxr-x--- 1 oracle dba 66432 Jul 11  2011 ./jdk/bin/java

[oracle@test01 OraInstall2001-07-23_06-31-05AM]$ ./jdk/jre/bin/java -version
-bash: ./jdk/jre/bin/java: Permission denied
[oracle@test01 OraInstall2001-07-23_06-31-05AM]$ ./jdk/bin/java  -version
-bash: ./jdk/bin/java: Permission denied
[oracle@test01 OraInstall2001-07-23_06-31-05AM]$ file ./jdk/bin/java
./jdk/bin/java: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.4.0, dynamically linked (uses shared libs), for GNU/Linux 2.4.0, stripped
[oracle@test01 OraInstall2001-07-23_06-31-05AM]$ file ./jdk/bin/java
./jdk/bin/java: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.4.0, dynamically linked (uses shared libs), for GNU/Linux 2.4.0, stripped
[oracle@test01 OraInstall2001-07-23_06-31-05AM]$ grep tmp /etc/fstab
/dev/vgvolume/lv_tmp    /tmp                    ext3    defaults,nodev,nosuid,noexec  1 2
/tmp                    /var/tmp                none    bind            0 0
tmpfs                   /dev/shm                tmpfs   defaults,nodev,nosuid,noexec  0 0

problem solved by following below URL

http://muthuappsdba.blogspot.sg/2013/02/error-in-createouiprocess-13-permission.html
http://pythianpang.wordpress.com/2010/06/03/oracle-runinstaller-createouiprocess-13-permission-denied/
http://kb.dbatoolz.com/tp/2817.error_in_createouiprocess_13.html

Solution :

 temporarily remove noexec option from /tmp

Or redirect TMP and TMPDIR environment variables to other directory

  

 

some links about ROWNUM

http://www.dbforums.com/oracle/988716-rownum-order.html

http://www.orafaq.com/wiki/ROWNUM

http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm

http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

one useful use I learned from Tom Kytes' article is pagination.  e.g the follow sql returns 2nd and 3rd rows.

 select *
  from ( select /*+ FIRST_ROWS(5) */
  a.*, ROWNUM rnum
      from ( select * from t order by b) a
      where ROWNUM <4 )
where rnum  >1 ;

Public synonym for SEQUENCE ?

No, it is wrong to do so !

To refer to the current or next value of a sequence in the schema of another user, you must have been granted either SELECT object privilege on the sequence or SELECT ANY SEQUENCE system privilege, and you must qualify the sequence with the schema containing it:

schema.sequence.CURRVAL
schema.sequence.NEXTVAL

To refer to the value of a sequence on a remote database, you must qualify the sequence with a complete or partial name of a database link:

schema.sequence.CURRVAL@dblink
schema.sequence.NEXTVAL@dblink

Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns002.htm

 

Why Role is not enabled be default ?

Why Role is not enabled be default ?

SYS@NSMSP> select * from dba_role_privs where grantee='LIQY';

no rows selected

SYS@NSMSP> grant SMS_SEL_ROLE to liqy;

Grant succeeded.

SYS@NSMSP> grant SMS_FULL_ROLE to liqy;

Grant succeeded.

SYS@NSMSP> select * from dba_role_privs where grantee='LIQY';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
LIQY                           SMS_SEL_ROLE                   NO  YES
LIQY                           SMS_FULL_ROLE                  NO  YES

SYS@NSMSP> revoke SMS_SEL_ROLE from liqy;

Revoke succeeded.

SYS@NSMSP> revoke SMS_FULL_ROLE from liqy;

Revoke succeeded.

SYS@NSMSP> grant SMS_SEL_ROLE to liqy;

Grant succeeded.

SYS@NSMSP> alter user liqy default role SMS_SEL_ROLE;

User altered.

SYS@NSMSP> grant SMS_FULL_ROLE to liqy;

Grant succeeded.

SYS@NSMSP> select * from dba_role_privs where grantee='LIQY';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
LIQY                           SMS_SEL_ROLE                   NO  YES
LIQY                           SMS_FULL_ROLE                  NO  NO

# note that here SMS_FULL_ROLE   is not activated by default.


--rectify the issue 

dbsvr21:NSMSP:/software/oranSMS/admin/NSMSP/create/setup/role> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 3 15:53:29 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning option

SYS@NSMSP> spool alter_default_role_mnaccess.log
SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MNACCESS                       MNACCESS_ROLE                  NO  YES
MNACCESS                       SMS_FULL_ROLE                  NO  NO

SYS@NSMSP> prompt in DEF column SMS_FULL_ROLE is not activated by default
in DEF column SMS_FULL_ROLE is not activated by default
SYS@NSMSP> alter user MNACCESS default role none;

User altered.

SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MNACCESS                       MNACCESS_ROLE                  NO  NO
MNACCESS                       SMS_FULL_ROLE                  NO  NO

SYS@NSMSP> revoke MNACCESS_ROLE from  MNACCESS;

Revoke succeeded.

SYS@NSMSP> grant MNACCESS_ROLE to  MNACCESS;

Grant succeeded.

SYS@NSMSP> revoke SMS_FULL_ROLE from  MNACCESS;

Revoke succeeded.

SYS@NSMSP> grant SMS_FULL_ROLE to  MNACCESS;

Grant succeeded.

SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MNACCESS                       MNACCESS_ROLE                  NO  NO
MNACCESS                       SMS_FULL_ROLE                  NO  NO

SYS@NSMSP> alter user MNACCESS default role none;

User altered.

SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MNACCESS                       MNACCESS_ROLE                  NO  NO
MNACCESS                       SMS_FULL_ROLE                  NO  NO

SYS@NSMSP> revoke SMS_FULL_ROLE, MNACCESS_ROLE from MNACCESS;

Revoke succeeded.

SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

no rows selected

SYS@NSMSP> grant SMS_FULL_ROLE, MNACCESS_ROLE to  MNACCESS;

Grant succeeded.

SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MNACCESS                       MNACCESS_ROLE                  NO  NO
MNACCESS                       SMS_FULL_ROLE                  NO  NO


MNACCESS@NSMSP> conn / as sysdba
Connected.
SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MNACCESS                       MNACCESS_ROLE                  NO  NO
MNACCESS                       SMS_FULL_ROLE                  NO  NO

SYS@NSMSP> conn mnaccess/mnaccess
Connected.
MNACCESS@NSMSP> insert into super_conf.cust_roam_cap(mobileno, accountid, starttime, indicator)values(97485258,51494367,20120403121541,'IN');
insert into super_conf.cust_roam_cap(mobileno, accountid, starttime, indicator)values(97485258,51494367,20120403121541,'IN')
                       *
ERROR at line 1:
ORA-01031: insufficient privileges


MNACCESS@NSMSP> set role all
  2  ;

Role set.

MNACCESS@NSMSP> insert into super_conf.cust_roam_cap(mobileno, accountid, starttime, indicator)values(97485258,51494367,20120403121541,'IN');

1 row created.

MNACCESS@NSMSP> rollback;

Rollback complete.

MNACCESS@NSMSP> conn / as sysdba
Connected.

SYS@NSMSP> ALTER USER mnaccess DEFAULT ROLE mnaccess_role, SMS_FULL_ROLE;

User altered.

SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MNACCESS                       MNACCESS_ROLE                  NO  YES
MNACCESS                       SMS_FULL_ROLE                  NO  YES

SYS@NSMSP> revoke create session from MNACCESS;

Revoke succeeded.

SYS@NSMSP> conn mnaccess/mnaccess
Connected.
MNACCESS@NSMSP> insert into super_conf.cust_roam_cap(mobileno, accountid, starttime, indicator)values(97485258,51494367,20120403121541,'IN');

1 row created.

MNACCESS@NSMSP> rollback;

Rollback complete.


  COUNT(*)
----------
         0

What does VALIDATE STRUCTURE do?

SYS@ADMP> analyze table dbapay.CASH_REGISTER_CONTROL validate structure;

Table analyzed.



VALIDATE STRUCTURE

Specify VALIDATE STRUCTURE to validate the structure of the analyzed object. The statistics collected by this clause are not used by the Oracle Database optimizer, as are statistics collected by the COMPUTE STATISTICS and ESTIMATE STATISTICS clauses.


For a table, Oracle Database verifies the integrity of each of the data blocks and rows. For an index-organized table, the database also generates compression statistics (optimal prefix compression count) for the primary key index on the table.
*

For a cluster, Oracle Database automatically validates the structure of the cluster tables.
*

For a partitioned table, Oracle Database also verifies that each row belongs to the correct partition. If a row does not collate correctly, then its rowid is inserted into the INVALID_ROWS table.
*

For a temporary table, Oracle Database validates the structure of the table and its indexes during the current session.
*

For an index, Oracle Database verifies the integrity of each data block in the index and checks for block corruption. This clause does not confirm that each row in the table has an index entry or that each index entry points to a row in the table. You can perform these operations by validating the structure of the table with the CASCADE clause.

Oracle Database also computes compression statistics (optimal prefix compression count) for all normal indexes.

Oracle Database stores statistics about the index in the data dictionary views INDEX_STATS and INDEX_HISTOGRAM.

HPUX SHLIB_PATH or LD_LIBRARY_PATH

today spent many time on perl script of Veritas database backup.

A gray area to all ... it looks somewhere is not refreshed immediately. (may be reside the EBS server , regardless of exporting LD_LIBRARY_PATH in .profile already at client side).

As workaround, have to explicitly export it inside the scripts. These Perl scripts are written by HP . Can't understand who chooze such difficult things.

also realize the difference between LD_LIBRARY_PATH and SHLIB_PATH from metalink doc 109621.1

A 64 bit install of Oracle includes both 32 bit executables (such as svrmgrl
and sqlplus) and 64 bit executables (such as oracle).

The installation contains two 'lib' areas:
  Prior to 9i:
     $ORACLE_HOME/lib     - 32 bit libs
     $ORACLE_HOME/lib64   - 64 bit libs
  9i onwards:
     $ORACLE_HOME/lib32   - 32 bit libs
     $ORACLE_HOME/lib     - 64 bit libs

Both of these directories contain libraries of the same name. To ensure
that 32 bit executables search the 32 bit dir, and 64 bit executables
search the 64 bit dir, set the following:

Prior to 9i:
$LD_LIBRARY_PATH - includes $ORACLE_HOME/lib64, but not $ORACLE_HOME/lib
$SHLIB_PATH      - includes $ORACLE_HOME/lib, but not $ORACLE_HOME/lib64

9i onwards:
$LD_LIBRARY_PATH - includes $ORACLE_HOME/lib, but not $ORACLE_HOME/lib32
$SHLIB_PATH      - includes $ORACLE_HOME/lib32, but not $ORACLE_HOME/lib

lock and create/drop index

Conclusion:

DDL (CREATE&DROP INDEX) conflict with DML. More explanation can be found below

Locks and ORA-00054 error

SQL> create table t1 (f1 number);

Table created.

SQL> select * from t1;

no rows selected

SQL> create index t1_idx on t1(f1);

Index created.

SQL> drop index t1_idx;
drop index t1_idx
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


SQL> commit;

Commit complete.

SQL> select * from t1;

no rows selected

SQL> select * from t1;

F1
----------
2

SQL> drop index t1_idx;

Index dropped.

SQL> create index t1_idx on t1(f1);
create index t1_idx on t1(f1)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

using sysdba for exp/imp

notice that there is one space after slash inside the single quote.
imp userid='/ as sysdba' full=y log=d:\sysfull.txt file=d:\back\sysfull.dmp

Typical feature not in standard edition 10g

FAST_START_MTTR_TARGET
PARALLEL SERVERS
PARTITIONING

v$option shows more info

Listener: Static and Dynamc Registration

Prior to Oracle 8i, a listener was statically configured (listener.ora) to
service a given set of SIDs. From 8i, PMON dynamically registers a database
service with the listener.

Further, if the listener is running on the default TCP port of 1521, then


there is no need to configure a listener.ora at all.from Oracle 8i onwards, database instances can register themselves with
the Listener when started. Prior to this release, information about the
instance was required to be manually configured in the "listener.ora" file
in the form of a STATIC SID_LIST.

Database instance registration is comprised of two elements:

 - Service registration provides the Listener with instance
        information, such as database service names and instance
        names.

 - MTS (Shared Server) dispatcher registration provides dispatcher
        information to the Listener.

By default, an instance background process registers instance information
to a Listener on the local machine. If a Listener is started after the
instance, there may be a delay before the instance and dispatchers are
registered (normally 30 to 35 seconds). As such, if a Listener receives an
incoming connection request before an instance is registered, the Listener
may reject the request with an ORA-12514 error. The instance will attempt to


register its services to the Listener periodically. 
To disable auto registration add the local_listener parameter to the init<>.ora file or in the spfile to specify a dummy address.

PMON will reject the invalid address and will not attempt to register with port
1521.


It will be necessary to statically configure the SID_LIST_
for your instance if you disable dynamic registration.

the after images

Today boss asked me log file is before image or after image.

I am confused, as the term "after image" ...

Google it and got the followings, ... , ring boss a call:

Oracle Log Buffer

Oracle creates redo logs for all update operations. In case of disk failure, the redo logs are used to roll forward, since they contain the after image of all row changes.

First, the after images are written to the log buffer area of RAM. The LGWR background process then transfers the images to Oracle online redo log files. In the last step, the ARCH background process writes the online redo log files to the archived redo log file system, where they are available to recover the database in the event of disk failure.

However, some shops avoid this overhead by using triple-mirrored disks and running their databases in NOARCHIVELOG mode. These shops believe the high redundancy is sufficient protection from a disk crash, and they deliberately reject the ability to roll the database forward, in return for faster performance.

About Histogram

Generally help with skewed data
column level statistics (include index statistics and histrogram)

block's life of Full Table Scan

When a block needs to be read into the buffer cache, Oracle must first find a free buffer in
which to store the block. The process searches the LRU list, beginning at the least recently used
end. It will search until it finds a free buffer. If it cannot find any, it will signal the DBW0 process
to flush any dirty blocks back to disk in order to make room. If no dirty blocks exist, the least
recently used block will be aged out to make room. The block will then be written to the buffer,
and the buffer moves to the most recently used end of the LRU list.

The exception to this rule is when a full table scan operation is performed. Blocks retrieved
from a full table scan are added to the least recently used end of the LRU list, so they will be aged
out quickly. This is because full table scan blocks are generally scanned quickly and are no longer
needed. This functionality can cause problems for small tables that are accessed frequently, such
as lookup tables. Small tables are meant to be accessed via full table scans. This is because a full
table scan will outperform an index lookup on a very small table.
Because Oracle 10g will put these blocks at the least recently used end of the LRU list, they
will age out quickly. As a result, the next time that the table is accessed, Oracle 10g may likely
have to perform PIO to retrieve the blocks again. In this situation, you can add the CACHE clause
to the table (either via ALTER TABLE or in the CREATE TABLE statements) to circumvent this
behavior. You can also choose to pin the table in memory.

remove external procedures

By default, Oracle creates a service for external procedures in the listener. For the majority
of users, this service is never used. Therefore, the service should be removed from the listener.

Database checkpoint

Database checkpoints are closely tied to redo log file switches. A checkpoint is an event that
flushes the modified data from the buffer cache to the disk and updates the control file and datafiles.
The CKPT process updates the headers of datafiles and control files; the actual blocks are
written to the file by the DBWn process. A checkpoint is initiated
When the redo log file is filled and a log switch occurs.
When the instance is shut down with NORMAL, TRANSACTIONAL, or IMMEDIATE.
When a tablespace status is changed to read-only or put into BACKUP mode.
When other values specified by certain parameters (discussed later in this section) are
reached.
You can force a checkpoint if needed, as shown here:
ALTER SYSTEM CHECKPOINT;
Forcing a checkpoint ensures that all changes to the database buffers are written to the datafiles
on disk.
Another way to force a checkpoint is by forcing a log file switch:
ALTER SYSTEM SWITCH LOGFILE;

Automatic Checkpoint Tuning
===========================
Oracle Database 10g supports automatic checkpoint tuning. It is enabled if
fast_start_mttr_target is explicitly set to an non-zero value, or
if fast_start_mttr_target is not set at all.
It is an advancement over the MTTR related parameter introduced in
earlier versions. The idea is to use the periods of low I/O usage
to advance checkpoints and therefore improve availability.

How it works
============
Enabling fast-start checkpointing increases the average number of writes
per transaction that DBWn issues for a given workload. However, if the
system is not already near or at its maximum I/O capacity, then
fast-start checkpointing has a negligible impact on performance

How to monitor
==============
View V$MTTR_TARGET_ADVICE will give information on the additional I/O's
on various values of FAST_START_MTTR_TARGET.

PROs and CONs:
=============
- FAST_START_MTTR_TARGET is set to a low value :
Fast-start checkpointing is more aggressive.
The average number of writes per transaction that DBWn issues
is higher in order to keep the thread checkpoint sufficiently
advanced to meet the requested MTTR.
- FAST_START_MTTR_TARGET is set to a high value:
Fast-start checkpointing in less aggressive, and the average
number of writes per transaction that DBWn issues is lower.

rows returned order

In heap-organized tables and traditional hash clusters, the order in which
rows are returned is not under user control and depends on internal algorithms and the relative
physical location of data blocks on disk.
For each hash cluster key, Oracle maintains a list of
rows sorted by one or more sort columns.

object's precedence

There is an order of precedence with regards to the use of synonyms and local objects. This is:

1. Local objects will always be accessed first.

2. If a local object does not exist, the object with a private synonym will be accessed.

3. If a private synonym does not exist or the object does not exist, then the public synonym will be used.

Note that a synonym can be created for an object that does not exist, and an object with an associated synonym can be dropped without removing the synonym. This can cause all sorts of interesting problems for DBA’s, so be careful.

Ojbect Namespace

the namespace shared by tables and views, and the database has separate
namespaces for each of the following:
Indexes
Constraints
Clusters
Database triggers
Private database links
Dimensions
Roles
Public synonyms
Public database links
Tablespaces
Profiles
Parameter files (PFILEs)

v$sqlarea,v$sql,v$sqltext这三个视图提供的sql语句有什么区别？

V$SQLAREA

V$SQLAREA lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.

V$SQL

V$SQL lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered. Statistics displayed in V$SQL are normally updated at the end of query execution. However, for long running queries, they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are still in progress.

V$SQLTEXT

This view contains the text of SQL statements belonging to shared SQL cursors in the SGA.

v$sqltext
存储的是完整的SQL,SQL被分割

SQL> desc v$sqltext
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDRESS RAW(4) ---------
HASH_VALUE NUMBER --------- 和 address 一起唯一标志一条sql
COMMAND_TYPE NUMBER
PIECE NUMBER ---------- 分片之后的顺序编号
SQL_TEXT VARCHAR2(64) -------------- 注意长度



v$sqlarea --------- 存储的SQL 和一些相关的信息，比如累计的执行次数，逻辑读，物理读等统计信息
SQL> desc v$sqlarea
Name Null? Type
----------------------------------------- -------- ----------------------------
SQL_TEXT VARCHAR2(1000)
SHARABLE_MEM NUMBER
PERSISTENT_MEM NUMBER
RUNTIME_MEM NUMBER
SORTS NUMBER
VERSION_COUNT NUMBER
LOADED_VERSIONS NUMBER
OPEN_VERSIONS NUMBER
USERS_OPENING NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
USERS_EXECUTING NUMBER
LOADS NUMBER
FIRST_LOAD_TIME VARCHAR2(38)
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
BUFFER_GETS NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
OPTIMIZER_MODE VARCHAR2(25)
PARSING_USER_ID NUMBER
PARSING_SCHEMA_ID NUMBER
KEPT_VERSIONS NUMBER
ADDRESS RAW(4)
HASH_VALUE NUMBER
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
SERIALIZABLE_ABORTS NUMBER
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
IS_OBSOLETE VARCHAR2(1)
CHILD_LATCH NUMBER




v$sql ---------- 存储的是具体的SQL 和执行计划相关信息，实际上，v$sqlarea 可以看做 v$sql 根据 sqltext 等 做了 group by 之后的信息


SQL> desc v$sql
Name Null? Type
----------------------------------------- -------- ----------------------------
SQL_TEXT VARCHAR2(1000)
SHARABLE_MEM NUMBER
PERSISTENT_MEM NUMBER
RUNTIME_MEM NUMBER
SORTS NUMBER
LOADED_VERSIONS NUMBER
OPEN_VERSIONS NUMBER
USERS_OPENING NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
USERS_EXECUTING NUMBER
LOADS NUMBER
FIRST_LOAD_TIME VARCHAR2(38)
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
BUFFER_GETS NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
OPTIMIZER_MODE VARCHAR2(10)
OPTIMIZER_COST NUMBER
PARSING_USER_ID NUMBER
PARSING_SCHEMA_ID NUMBER
KEPT_VERSIONS NUMBER
ADDRESS RAW(4)
TYPE_CHK_HEAP RAW(4)
HASH_VALUE NUMBER
PLAN_HASH_VALUE NUMBER
CHILD_NUMBER NUMBER ---------- 注意这个
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
SERIALIZABLE_ABORTS NUMBER
OUTLINE_CATEGORY VARCHAR2(64)
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
OUTLINE_SID NUMBER -------------- 注意这里跟 outline 有关
CHILD_ADDRESS RAW(4)
SQLTYPE NUMBER
REMOTE VARCHAR2(1)
OBJECT_STATUS VARCHAR2(19)
LITERAL_HASH_VALUE NUMBER
LAST_LOAD_TIME VARCHAR2(38)
IS_OBSOLETE VARCHAR2(1)
CHILD_LATCH NUMBER


另外注意这个
QL> desc v$sql_plan
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDRESS RAW(4)
HASH_VALUE NUMBER
CHILD_NUMBER NUMBER ------------ 注意这个和 v$sql 里面的相同字段
OPERATION VARCHAR2(60)
OPTIONS VARCHAR2(60)
OBJECT_NODE VARCHAR2(20)
OBJECT# NUMBER
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(64)
OPTIMIZER VARCHAR2(40)
ID NUMBER
PARENT_ID NUMBER
DEPTH NUMBER
POSITION NUMBER
SEARCH_COLUMNS NUMBER
COST NUMBER
CARDINALITY NUMBER
BYTES NUMBER
OTHER_TAG VARCHAR2(70)
PARTITION_START VARCHAR2(10)
PARTITION_STOP VARCHAR2(10)
PARTITION_ID NUMBER
OTHER VARCHAR2(4000)
DISTRIBUTION VARCHAR2(40)
CPU_COST NUMBER
IO_COST NUMBER
TEMP_SPACE NUMBER
ACCESS_PREDICATES VARCHAR2(4000)
FILTER_PREDICATES VARCHAR2(4000)


实际上，看起来同样的一句SQL ，往往具有不同的执行计划
如果是不同的数据库用户，那么相应的涉及的 对象 可能都不一样，注意v$sql 中
OBJECT# NUMBER
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(64)
OPTIMIZER VARCHAR2(40)

即使是相同的数据库用户，若 session 的优化模式、session 级的参数 等不一样，执行计划也能不同。所以即使相同的sql，也可能具有不同的执行计划！

v$sql join to v$sql_plan 就代表了具体的sql的执行计划,通过下面3个字段做连接

ADDRESS RAW(4)
HASH_VALUE NUMBER
CHILD_NUMBER NUMBER


而v$SQLAREA 忽略了 执行计划 等差异，只是在形式上sql文本看起来一样！相当于做了个聚合，是多个不同执行计划的sql的聚合和累计信息

补充：

1、查一下这些视图的定义你就能理解，它们的源都是一个。
SELECT view_definition FROM v$fixed_view_definition WHERE view_name='GV$SQL';
SELECT view_definition FROM v$fixed_view_definition WHERE view_name='GV$SQL_AREA';

2、实际上最模糊的是v$sql与v$sqlarea,区别与联系除biti说的还有：

a、v$sql_area相当于是按INST_ID, KGLNAOBJ, KGLHDPAR, KGLNAHSH, KGLNATIM, GLOBTS0,GLOBT19, KGLOBTS1, KGLOBT20,DECODE(KGLOBT33, 1, 'Y', 'N'),KGLHDCLT这些列的自v$sql的group by，也就是说v$sql的每一行表示的是每一个sql语句的一个versiion，而v$sqlarea存放的是相同语句不同version一个汇总。

b、 v$sql与v$sqlarea的源都是一个：X$KGLCURSOR

c、实际调优中建议使用v$sql,相对来说比v$sqlarea快，而且还不会产生share pool latch的争用。

3、因v$sql及v$sqlarea存放着统计信息在调优时使用居多，但其sql是不全的，如果想获得完整的sql就要用v$sqltext了。

Purging the Audit Trail

Database audit records for statement, privilege, and object auditing are stored in the table
SYS.AUD$. Depending on how extensive your auditing and retention policies are, you will need
to periodically delete old audit records from this table. The database does not provide an inter-
face to assist in deleting rows from the audit table, so you will need to do so yourself. To purge
audit records older than 90 days, execute the following as user SYS:
DELETE FROM sys.aud$ WHERE timestamp# < SYSDATE -90;