How To Create Oracle 11g Database Manually on Windows

Below steps will help you to create 11.2.0.1 oracle database manually on windows platform. These steps would remain same on all the Windows version such as XP, Vista etc. Before starting the creation of the database, ensure you have Oracle Database binaries installed.

1. Set up environment variables

C:\>set ORACLE_HOME=c:\oracle\product\11.2.0\dbhome_1
C:\>set PATH=%ORACLE_HOME%\bin;%PATH%
C:\>set ORACLE_SID=ORADB

2. Create required directories

C:\>mkdir c:\oracle\db\oradb\admin\adump  
C:\>mkdir c:\oracle\db\oradb\diag
C:\>mkdir c:\oracle\db\oradb\flash_recovery_area
C:\>mkdir c:\oracle\db\oradb\oradata

3. Create the parameter file

Create the parameter file named initORADB.ora under ORACLE_HOME/database directorydb_name='ORADB'
db_block_size=8192
memory_target=500m
processes=100
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
compatible ='11.2.0'
audit_trail ='db'
db_name='ORADB'
db_recovery_file_dest_size=5g
db_recovery_file_dest='c:\oracle\db\oradb\flash_recovery_area'
audit_file_dest='c:\oracle\db\oradb\admin\adump'
diagnostic_dest='c:\oracle\db\oradb\diag'
control_files = ('c:\oracle\db\oradb\oradata\control1.ctl', 'c:\oracle\db\oradb\oradata\control2.ctl', 'c:\oracle\db\oradb\oradata\control3.ctl')

4. Create a Windows service 

C:\>oradim -NEW -SID ORADB -STARTMODE manual
Instance created.

Check the status of service

C:\>sc query oracleserviceORADB
SERVICE_NAME: oracleserviceORADB
        TYPE               : 10  WIN32_OWN_PROCESS
        STATE              : 4  RUNNING
       (STOPPABLE,PAUSABLE,ACCEPTS_SHUTDOWN)
        WIN32_EXIT_CODE    : 0  (0x0)
        SERVICE_EXIT_CODE  : 0  (0x0)
        CHECKPOINT         : 0x0
        WAIT_HINT          : 0x0

5.Connect to instance and Start the instance with NOMOUNT mode by newly created pfile

C:\>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Wed May 23 07:39:54 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup nomount pfile='c:\oracle\product\11.2.0\dbhome_1\dbs\initORADB.ora'
ORACLE instance started.
Total System Global Area  523108352 bytes
Fixed Size                  1375704 bytes
Variable Size             314573352 bytes
Database Buffers          201326592 bytes
Redo Buffers                5832704 bytes
Instance started.

6.Execute the CREATE DATABASE Command

CREATE DATABASE ORADB 
    USER sys IDENTIFIED BY sys
    USER system IDENTIFIED BY system
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 200
    MAXINSTANCES 1
    MAXLOGHISTORY 500
LOGFILE
GROUP 1 (
    'c:\oracle\db\oradb\oradata\redo01a.rdo',
    'c:\oracle\db\oradb\oradata\redo01b.rdo'
    ) SIZE 50M,
GROUP 2 (
    'c:\oracle\db\oradb\oradata\redo02a.rdo',
    'c:\oracle\db\oradb\oradata\redo02b.rdo'
    ) SIZE 50M,
GROUP 3 (
    'c:\oracle\db\oradb\oradata\redo03a.rdo',
    'c:\oracle\db\oradb\oradata\redo03b.rdo'
    ) SIZE 50M
DATAFILE 'c:\oracle\db\oradb\oradata\system01.dbf' SIZE 300M EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE 'c:\oracle\db\oradb\oradata\sysaux01.dbf' SIZE 200M
UNDO TABLESPACE UNDOTBS1 DATAFILE 'c:\oracle\db\oradb\oradata\undotbs01.dbf' SIZE 300M AUTOEXTEND OFF
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'c:\oracle\db\oradb\oradata\temp01.dbf' SIZE 200M REUSE AUTOEXTEND OFF
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET UTF8;

7. Create data dictionary objects 

Run the catalog.sql and catproc.sql scripts to create the dictionary objects.
SQL> @%ORACLE_HOME%\rdbms\admin\catalog.sql
SQL> @%ORACLE_HOME%\rdbms\admin\catproc.sql
SQL> connect system/system
SQL> @%ORACLE_HOME%\sqlplus\admin\pupbld.sql
SQL> conn / as sysdba

8. Create spfile from pfile, So that db will starts with spfile by default

SQL> Create spfile from pfile;
File created

9. Enable Archiving

To enable archiving shutdown the database and startup it in mount status. Enable the archiving and then open the database.
SQL> connect /as sysdba
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area  523108352 bytes
Fixed Size                  1375704 bytes
Variable Size             314573352 bytes
Database Buffers          201326592 bytes
Redo Buffers                5832704 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     12
Next log sequence to archive   13
Current log sequence           13

10. Check for any invalid component or objects

SQL> select comp_id,version,status from dba_registry;

COMP_ID                        VERSION                        STATUS
------------------------------ ------------------------------ -------
CATALOG                        11.2.0.1.0                     VALID
CATPROC                        11.2.0.1.0                     VALID
2 rows selected.

SQL> select owner,object_name,object_type from all_objects where status='INVALID';
no rows selected

Note:If any inavlid objects are found then run ultrp.sql to validate objects

SQL> @%ORACLE_HOME%/rdbms/admin/utlrp.sql

Oracle Database Role Change

                                        Objective

If the production database becomes unavailable because of a planned or an unplanned outage, Data Guard Role Management Services quickly switch a chosen standby database to the production role, minimizing the downtime and preventing data loss.

Approach

Switchover [Role change]

Allows the primary database to switch roles with its standby database, there is no data loss during a switchover. After a switchover, each database continues to participate in the Data Guard configuration with its new role. It is planned activity.

A switchover takes place in two phases. In the first phase, the existing primary database undergoes a transition to a standby role. In the second phase, a standby database undergoes a transition to the primary role.

Steps to Switchover to physical standby database

Step 1à  on the primary db

Runà cmd

SQL> sqlplus / as sysdba

SQL> Select database_role from v$database;

SQL> Select switchover_status from v$database;

SQL> Alter database commit to switchover to physical standby;

 OR   [if output of the above query is SESSIONS ACTIVE]

SQL> Alter database commit to switchover to standby with session shutdown;

SQL> Shutdown immediate;

SQL> exit;

SQL> sqlplus / as sysdba

SQL> Startup nomount

SQL> Alter database mount standby database;

Step 2à On the Standby db

Runà cmd

SQL> sqlplus / as sysdba

SQL> Select switchover_status from v$database;

SQL> Alter database commit to switchover to primary;

OR [if output of the above query is SESSIONS ACTIVE]

SQL> Alter database commit to switchover to primary with session shutdown;

SQL> Shut immediate;

SQL> exit;

SQL> sqlplus / as sysdba

SQL> startup

Step 3à On the New Standby db [Previously Primary]

Start the recovery process

Runà cmd

SQL> sqlplus / as sysdba

SQL> Alter database recover managed standby database disconnect from session;

SQL> exit;

Failover

During a failover, standby database takes the primary database role and the old primary database is rendered unable to participate in the configuration. Depending on the protection mode under which the old primary database was operating before the failover, there might be little or no data loss during a failover. A failover is typically used only when a primary database becomes unavailable and there is no possibility of restoring it to service within a reasonable amount of time.

Steps to failover to physical standby database

Runà cmd

SQL> sqlplus / as sysdba

SQL> Select database_role from v$database;

SQL> alter database recover managed standby database finish force;

SQL> alter database commit to switchover to primary with session shutdown;

SQL> alter database open;

SQL> select name, open_mode, database_role from v$database;

          Note: Shutdown and Backup the new primary database.

Primary database can be restored from physical standby database (Currently primary) by following steps….

1. Take offline Backup of the new primary [Previously Physical Standby]

2. Restore offline backup on the original primary server

3. Configure new physical standby on the primary server

4. Perform switchover

Note: Backup the Original Primary Database

Oracle Database Links

Oracle Database Link is used to establish connections between two database.
The purpose of database link is used to exchange the data between 2 databases.

Types Of Database Links: 

Public Database Link:  All users in a database can use it.

Private Database Link: Belongs to a specific database schema. Only the owner of a private database link can use it.

Global Database Link:  Defined in an OID or Oracle Names Server. Anyone on the network can use it.

Prerequisites  To Create Database Link:

• Need to copy target database tns entry in source database tns file.
• Need to grant CREATE DATABASE LINK to user.

Create/Drop Private DBLink:

CREATE DATABASE LINK ABC<LINK_NAME> CONNECT TO SCOTT IDENTIFIED BY TIGER USING  'ORADB<TARGET TNS>';

DROP DATABASE LINK ABC;

Create/Drop Public DBLink:

CREATE PUBLIC  DATABASE LINK ABC<LINK_NAME> CONNECT TO SCOTT IDENTIFIED BY TIGER USING  'ORADB<TARGET TNS>';

DROP PUBLIC DATABASE LINK ABC;


Close Database Link:

ALTER SESSION CLOSE DATABASE LINK ABC<LINK_NAME>;

Drop database link when you are not the owner of  the database link:

When you are the owner of the link you can drop it without any problem but if you are a DBA and want to purge the database . Lets say after restoring the TEST database from live database you can use the proxy user feature so you can connect to the database link owner  without knowing or changing it's password (Here Michel is the DBA).

SQL>CONNECTED test/test;
Connected.

TEST>CREATE DATABASE LINK mika@loop CONNECT TO TEST IDENTIFIED BY test USING 'MIKA';
Database Link Created.

TEST>CONNECT Michel/Michel;
Connected.
MICHEL>ALTER USER TEST GRANT CONNECT THROUGH MICHEL;
MICHEL>CONNECT michel[test]/michel;
Connected.
TEST>SHOW USER;
USER is "TEST".
TEST>DROP DATABASE LINK mika@loop;
Database Link Dropped.
TEST>CONNECT michel/michel;
Connected.
MICHEL>ALTER USER TEST REVOKE CONNECT THROUGH MICHEL;

User Altered.

Creating Database Link With Encrypted Password:

CREATE DATABASE LINK ABC<LINK_NAME> CONNECT TO SCOTT IDENTIFIED BY VALUES 'DEA2G0D1A57B0071057A11DA7A' USING  'ORADB<TARGET TNS>';

Views Related To Database Links:

• DBA_DB_LINKS
• ALL_DB_LINKS
• USER_DB_LINKS

Oracle File_Name_Covert

Title:

Move data file from one location to another location, and change the parameter DB_FILE_NAME_CONVERT

Problem Decription:

We are adding datafiles for SYSAUX tablespace in production database in following mountpoint: "/hilo_oradata01/HILO/sysaux02.dbf"

           

But in standby database it will be added in following mountpoint:"/hilo_oradata02/HILO/sysaux02.dbf"

Cause:

In parameter file DB_FILE_NAME_CONVERT="/hilo_oradata01/HILO,"/hilo_oradata02/HILO'(Remove or Comment this parameter) for that reason it through the error like that.

Action:

We need to comment that parameter in parameter file after that move the datafiles from "/hilo_oradata02/HILO" to "/hilo_oradata01/HILO".

Solution:

1.Create pfile

 Sql> create pfile from spfile;

2.Down the standby database.

Sql>Alter database Recover managed standby databae cancel;

Sql>Shutdown database;

3.Open pfile and comment or remove  the parameter

           

            # DB_FILE_NAME_CONVERT==/hilo_oradata01/HILO,/hilo_oradata02/HILO

4.Copy all datafile from "/hilo_oradata02/HILO" to "/hilo_oradata01/HILO" manually.

cp /hilo_oradata01/HILO/users01.dbf    /hilo_oradata02/HILO/users01.dbf

cp /hilo_oradata01/HILO/undotbs01.dbf  /hilo_oradata02/HILO/undotbs01.dbf

cp /hilo_oradata01/HILO/sysaux01.dbf   /hilo_oradata02/HILO/sysaux01.dbf

cp /hilo_oradata01/HILO/system01.dbf   /hilo_oradata02/HILO/system01.dbf

cp /hilo_oradata01/HILO/example01.dbf  /hilo_oradata02/HILO/example01.dbf

cp /hilo_oradata01/HILO/hrms_dat01.dbf /hilo_oradata02/HILO/hrms_dat01.dbf

cp /hilo_oradata01/HILO/hrms_dat02.dbf /hilo_oradata02/HILO/hrms_dat02.dbf

cp /hilo_oradata01/HILO/tds_dat01.dbf  /hilo_oradata02/HILO/tds_dat01.dbf

cp /hilo_oradata01/HILO/sysaux02.dbf   /hilo_oradata01/HILO/sysaux02.dbf

5.Startup mount pfile="/hilo_engn01/app/product/11.1.0/db_1/dbs/initHILO.ora".

6. Rename the datafiles.

Sql>alter database rename file '/hilo_oradata01/HILO/users01.dbf' 
to '/hilo_oradata02/HILO/users01.dbf';

Sql>alter database rename file '/hilo_oradata01/HILO/undotbs01.dbf' to  '/hilo_oradata02/HILO/undotbs01.dbf';

Sql>alter database rename file '/hilo_oradata01/HILO/sysaux01.dbf' to  '/hilo_oradata02/HILO/sysaux01.dbf';

Sql>alter database rename file '/hilo_oradata01/HILO/system01.dbf' to '/hilo_oradata02/HILO/system01.dbf';

Sql>alter database rename file '/hilo_oradata01/HILO/example01.dbf' to '/hilo_oradata02/HILO/example01.dbf';

Sql>alter database rename file '/hilo_oradata01/HILO/hrms_dat01.dbf' to '/hilo_oradata02/HILO/hrms_dat01.dbf';

Sql>alter database rename file '/hilo_oradata01/HILO/hrms_dat02.dbf' to '/hilo_oradata02/HILO/hrms_dat02.dbf';

Sql>alter database rename file '/hilo_oradata01/HILO/tds_dat01.dbf' to '/hilo_oradata02/HILO/tds_dat01.dbf';

Sql>alter database rename file '/hilo_oradata01/HILO/sysaux02.dbf' to '/hilo_oradata01/HILO/sysaux02.dbf';

7.Shutdown immediate;

8.Startup mount pfile="/hilo_engn01/app/product/11.1.0/db_1/dbs/initHILO.ora".

9.create spfle from pfile;

10.shutdown immediate;

11.startup nomount;

12. alter database mount standby database;

13.alter database recover managed standby database disconnect from session;

14. Finally remove the datafiles from "/hilo_oradata02/HILO" .

15 Task Completed Successfully 

Oracle Database Auditing

The auditing mechanism for Oracle is extremely flexible. Oracle stores information that is relevant to auditing in its data dictionary.

Every time a user attempts anything in the database where audit is enabled the Oracle kernel checks to see if an audit record should be created or updated (in the case or a session record) and generates the record in a table owned by the SYS user called AUD$. This table is, by default, located in the SYSTEM tablespace. This itself can cause problems with potential denial of service attacks. If the SYSTEM tablespace fills up, the database will hang.

init parameters

Until Oracle 10g, auditing is disabled by default, but can be enabled by setting the AUDIT_TRAIL static parameter in the init.ora file.

From Oracle 11g, auditing is enabled for some system level privileges.

SQL> show parameter audit

NAME                    TYPE            VALUE

----------------------  ------------    ----------

audit_file_dest string  ?/rdbms/audit

audit_sys_operations    boolean         FALSE

audit_syslog_level      string          NONE

audit_trail             string          DB

transaction_auditing    boolean         TRUE

AUDIT_TRAIL can have the following values.

AUDIT_TRAIL={NONE or FALSE| OS| DB or TRUE| DB_EXTENDED| XML |XML_EXTENDED}

Download Pdf Multiples Base Table Cause Problems Change Number Parameter

The following list provides a description of each value:

NONE or FALSE -> Auditing is disabled. Default until Oracle 10g.

DB or TRUE -> Auditing is enabled, with all audit records stored in the database audit trial (AUD$). Default from Oracle 11g.

DB_EXTENDED –> Same as DB, but the SQL_BIND and SQL_TEXT columns are also populated.

XML-> Auditing is enabled, with all audit records stored as XML format OS files.

XML_EXTENDED –> Same as XML, but the SQL_BIND and SQL_TEXT columns are also populated.

OS -> Auditing is enabled, with all audit records directed to the operating system's file specified by AUDIT_FILE_DEST.

Note: In Oracle 10g Release 1, DB_EXTENDED was used in place of "DB,EXTENDED". The XML options were brought in Oracle 10g Release 2.

The AUDIT_FILE_DEST parameter specifies the OS directory used for the audit trail when the OS, XML and XML_EXTENDED options are used. It is also the location for all mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.

The AUDIT_SYS_OPERATIONS static parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail.

Run the $ORACLE_HOME/rdbms/admin/cataudit.sql script while connected as SYS (no need to run this, if you ran catalog.sql at the time of database creation).

Start Auditing

Syntax of audit command:

audit {statement_option|privilege_option} [by user] [by {session|access}] [whenever {successful|not successful}]

Sql Statements Table All System The Users Download Pdf Multiples Base Table

Only the statement_option or privilege_option part is mandatory. The other clauses are optional and enabling them allows audit be more specific.

There are three levels that can be audited:

Statement level

Auditing will be done at statement level.

Statements that can be audited are found in STMT_AUDIT_OPTION_MAP.

SQL> audit table by scott;

Audit records can be found in DBA_STMT_AUDIT_OPTS.

SQL> select * from DBA_STMT_AUDIT_OPTS;

Object level

Auditing will be done at object level.

These objects can be audited: tables, views, sequences, packages, stored procedures and stored functions.

SQL> audit insert, update, delete on scott.emp by hr;

Audit records can be found in DBA_OBJ_AUDIT_OPTS.

SQL> select * from DBA_OBJ_AUDIT_OPTS;

Privilege level

Auditing will be done at privilege level.

All system privileges that are found in SYSTEM_PRIVILEGE_MAP can be audited.

SQL> audit create tablespace, alter tablespace by all;

Specify ALL PRIVILEGES to audit all system privileges.

Audit records can be found in DBA_PRIV_AUDIT_OPTS.

SQL> select * from DBA_PRIV_AUDIT_OPTS;

Audit options

BY SESSION

Specify BY SESSION if you want Oracle to write a single record for all SQL statements of the same type issued and operations of the same type executed on the same schema objects in the same session.

Oracle database can write to an operating system audit file but cannot read it to detect whether an entry has already been written for a particular operation. Therefore, if you are using an operating system file for the audit trail (that is, the AUDIT_TRAIL initialization parameter is set to OS), then the database may write multiple records to the audit trail file even if you specify BY SESSION.

SQL> audit create, alter, drop on currency by xe by session;

SQL> audit alter materialized view by session;

BY ACCESS

Specify BY ACCESS if you want Oracle database to write one record for each audited statement and operation.

If you specify statement options or system privileges that audit data definition language (DDL) statements, then the database automatically audits by access regardless of whether you specify the BY SESSION clause or BY ACCESS clause.

For statement options and system privileges that audit SQL statements other than DDL, you can specify either BY SESSION or BY ACCESS. BY SESSION is the default.

SQL> audit update on health by access;

SQL> audit alter sequence by tester by access;

WHENEVER [NOT] SUCCESSFUL

Specify WHENEVER SUCCESSFUL to audit only SQL statements and operations that succeed.

Specify WHENEVER NOT SUCCESSFUL to audit only SQL statements and operations that fail or result in errors.

If you omit this clause, then Oracle Database performs the audit regardless of success or failure.

SQL> audit insert, update, delete on hr.emp by hr by session whenever not successful;

SQL> audit materialized view by pingme by access whenever successful;

Examples

Auditing for every SQL statement related to roles (create, alter, drop or set a role).

SQL> AUDIT ROLE;

Auditing for every statement that reads files from database directory

SQL> AUDIT READ ON DIRECTORY ext_dir;

Auditing for every statement that performs any operation on the sequence

SQL> AUDIT ALL ON hr.emp_seq;

View Audit Trail

The audit trail is stored in the base table SYS.AUD$.

It's contents can be viewed in the following views:

· DBA_AUDIT_TRAIL

· DBA_OBJ_AUDIT_OPTS

· DBA_PRIV_AUDIT_OPTS

· DBA_STMT_AUDIT_OPTS

· DBA_AUDIT_EXISTS

· DBA_AUDIT_OBJECT

· DBA_AUDIT_SESSION

· DBA_AUDIT_STATEMENT

· AUDIT_ACTIONS

· DBA_AUDIT_POLICIES

· DBA_AUDIT_POLICY_COLUMNS

· DBA_COMMON_AUDIT_TRAIL

· DBA_FGA_AUDIT_TRAIL (FGA_LOG$)

· DBA_REPAUDIT_ATTRIBUTE

· DBA_REPAUDIT_COLUMN

The audit trail contains lots of data, but the following are most likely to be of interest:

 Username - Oracle Username.

 Terminal - Machine that the user performed the action from.

 Timestamp - When the action occurred.

 Object Owner - The owner of the object that was interacted with.

 Object Name - name of the object that was interacted with.

 Action Name - The action that occurred against the object (INSERT, UPDATE, DELETE, SELECT, EXECUTE)

Fine Grained Auditing (FGA), introduced in Oracle9i, allowed recording of row-level changes along with SCN numbers to reconstruct the old data, but they work for select statements only, not for DML such as update, insert, and delete.

From Oracle 10g, FGA supports DML statements in addition to selects.

Several fields have been added to both the standard and fine-grained audit trails:

EXTENDED_TIMESTAMP - A more precise value than the existing TIMESTAMP column.

PROXY_SESSIONID - Proxy session serial number when an enterprise user is logging in via the proxy method.

GLOBAL_UID - Global Universal Identifier for an enterprise user.

INSTANCE_NUMBER - The INSTANCE_NUMBER value from the actioning instance.

OS_PROCESS - Operating system process id for the oracle process.

TRANSACTIONID - Transaction identifier for the audited transaction. This column can be used to join to the XID column on the FLASHBACK_TRANSACTION_QUERY view.

SCN - System change number of the query. This column can be used in flashback queries.

SQL_BIND - The values of any bind variables if any.

SQL_TEXT - The SQL statement that initiated the audit action.

The SQL_BIND and SQL_TEXT columns are only populated when the AUDIT_TRAIL=DB_EXTENDED or AUDIT_TRAIL=XML_EXTENDED initialization parameter is set.

Maintenance

The audit trail must be deleted/archived on a regular basis to prevent the SYS.AUD$ table growing to an unacceptable size.

Only users who have been granted specific access to SYS.AUD$ can access the table to select, alter or delete from it. This is usually just the user SYS or any user who has permissions. There are two specific roles that allow access to SYS.AUD$ for select and delete, these are DELETE_CATALOG_ROLE and SELECT_CATALOG_ROLE. These roles should not be granted to general users.

Auditing modifications of the data in the audit trail itself can be achieved as follows

SQL> AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;

To delete all audit records from the audit trail:

SQL> DELETE FROM sys.aud$;

From Oracle 11g R2, we can change audit table's (SYS.AUD$ and SYS.FGA_LOG$) tablespace and we can periodically delete the audit trail records using DBMS_AUDIT_MGMT package.

Disabling Auditing

The NOAUDIT statement turns off the various audit options of Oracle. Use it to reset statement, privilege and object audit options. A NOAUDIT statement that sets statement and privilege audit options can include the BY USER option to specify a list of users to limit the scope of the statement and privilege audit options.

SQL> NOAUDIT;

SQL> NOAUDIT session;

SQL> NOAUDIT session BY scott, hr;

SQL> NOAUDIT DELETE ON emp;

SQL> NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;

SQL> NOAUDIT ALL;

SQL> NOAUDIT ALL PRIVILEGES;

SQL> NOAUDIT ALL ON DEFAULT;