Control Files managing

Managing Control Files

Every Oracle Database has a control file, which is a small binary file that records the physical structure of the database. The control file includes:

·         The database name

·         Names and locations of associated datafiles and redo log files

·         The timestamp of the database creation

·         The current log sequence number

·         Checkpoint information

It is strongly recommended that you multiplex control files  i.e. Have at least two control files one in one hard disk and another one located in another disk, in a database.  In this way if control file becomes corrupt in one disk the another copy will be available and you don’t have to do recovery of control file.

You can  multiplex control file at the time of creating a database and later on also. If you have not multiplexed control file at the time of creating a database you can do it now by following given procedure.

Multiplexing Control File

Steps:

      1.      Shutdown the Database.

SQL>SHUTDOWN IMMEDIATE;

    2.      Copy the control file from old location to new location using operating system command. For example.

$cp /u01/oracle/ica/control.ora  /u02/oracle/ica/control.ora

    3.      Now open the parameter file and specify the new location like this

CONTROL_FILES=/u01/oracle/ica/control.ora

Change it to

CONTROL_FILES=/u01/oracle/ica/control.ora,/u02/oracle/ica/control.ora

    4.      Start the Database

Now Oracle will start updating both the control files and, if one control file is lost you can copy  it from another location.

Changing the Name of a Database

If you ever want to change the name of database or want to change the setting of MAXDATAFILES, MAXLOGFILES,MAXLOGMEMBERS then you have to create a new control file.

Creating A New Control File

Follow the given steps to create a new controlfile

Steps

1.      First generate the create controlfile statement

SQL>alter database backup controlfile to trace;

After giving this statement oracle will write the CREATE CONTROLFILE statement in a trace file. The trace file will be randomly named something like ORA23212.TRC and it is created in USER_DUMP_DEST directory.

2.      Go to the USER_DUMP_DEST directory and open the latest trace file in text editor. This file will contain the CREATE CONTROLFILEstatement. It will have two sets of statement one with RESETLOGS and another without RESETLOGS. Since we are changing the name of the Database we have to use RESETLOGS option of CREATE CONTROLFILE statement. Now copy and paste the statement in a file. Let it be c.sql

3.      Now open the c.sql file in text editor and set the database name from ica to prod shown in an example below

CREATE CONTROLFILE

   SET DATABASE prod   

   LOGFILE GROUP 1 ('/u01/oracle/ica/redo01_01.log',

                    '/u01/oracle/ica/redo01_02.log'),

           GROUP 2 ('/u01/oracle/ica/redo02_01.log',

                    '/u01/oracle/ica/redo02_02.log'),

           GROUP 3 ('/u01/oracle/ica/redo03_01.log',

                    '/u01/oracle/ica/redo03_02.log')

   RESETLOGS

   DATAFILE '/u01/oracle/ica/system01.dbf' SIZE 3M,

            '/u01/oracle/ica/rbs01.dbs' SIZE 5M,

            '/u01/oracle/ica/users01.dbs' SIZE 5M,

            '/u01/oracle/ica/temp01.dbs' SIZE 5M

   MAXLOGFILES 50

   MAXLOGMEMBERS 3

   MAXLOGHISTORY 400

   MAXDATAFILES 200

   MAXINSTANCES 6

   ARCHIVELOG;

4.      Start and do not mount the database.

SQL>STARTUP NOMOUNT;

5.      Now execute c.sql script

SQL> @/u01/oracle/c.sql

6.      Now open the database with RESETLOGS

SQL>ALTER DATABASE OPEN RESETLOGS;

 

 

                                                            Introduction:

 

Control files are created by Oracle. The path of a control file is specified in INIT.ORA. Every Oracle database should have at least two control files (recommended); each stored on different disks. If a control file is damaged due to disk failure the assocaited instance must be shutdown. Once the disk drive is reapired, the damaged control file can be restored using an intact copy of the control file and the instance can be restarted. Assume that one control file is located in the path /disk1/oradata/DEMO/control1.ctl, and the other in /disk2/oradata/DEMO/control2.ctl. If one control file 'control1.ctl' is in an unrecoverable state, then issue the following commands

$cp /disk1/oradata/DEMO/control1.ctl \ /disk2/oradata/DEMO/control2.ctl

SQL>startup

Now media recovery is required. By using mirrored control files you avoid unnecessary problems if a disk failure occurs on the database servers.

Managing the size of the control file:

Typical control files are small. The major portion of a control file size depends on the values set for the parameters: MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXINSTANCES of the CREATE DATABASE statement that created the associated database. The maximum control file size is operating system specific:

To check the number of files specified in control files:

SQL>alter database backup controlfile to trace;

$cd /disk1/oradata/DEMO/udump

$cp ora_2065.trc bkup.sql

$cat bkup.sql

If the MAXDATAFILES parameter is set to 5 and if you try to add sixth datafile issuing the command:

SQL>alter tablespace user_demo add datafile '/disk1/oradata/DEMO/user03.dbf' size 10m;

ORA-1503 create control file failed

ORA-1166 file number 3 larger than MAXDATAFILES (5)

To increase the number of maximum data files supported by your DB edit your trace file.

$vi bkup.sql

     MAX_DATA_FILES 10

:wq

SQL>conn / as sysdba

SQL>@bkup.sql

SQL>alter tablespace user_demo add datafile '/disk1/oradata/DEMO/user03.dbf' size 10m;

Follow the same steps to increase the parameter values for LOGFILES and LOGMEMBERS.

To create an additional copy of controlfile, issue the following statements. Include the complete path of the new file in 'control_files' parameter in INIT.ORA.

$cd /disk2/oradata/DEMO

$cp control1.ctl control2.ctl

SQL>startup

To drop excessive control files:

-Shutdown the database

-Edit the parameter control_files in INIT.ORA and remove one of the control file entries, leaving atleast one control file to start the database

-Restart the database

-The above steps do not delete the file physically from the disk.

SQL>SHUTDOWN IMMEDIATE

$cat initDEMO.ora             # Here we are only observing 1 line which reads controlfiles

CONTROL_FILES=(/disk2/oradata/DEMO/control2.ctl)

SQL>STARTUP

To Trace the control file to udump destination and generate the create controlfile syntax.

SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

$cd /disk2/oradata/DEMO/udump

$vi ora_2065.trc

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "DEMO" RESETLOGS NOARCHIVELOG

LOGFILE GROUP 1 ('/disk1/oradata/DEMO/redolog1.log',

                                        '/disk2/oradata/DEMO/redolog2.log') SIZE 4M

                    GROUP 2 ('/disk1/oradata/DEMO/redolog1.log',

                                        '/disk2/oradata/DEMO/redolog2.log') SIZE 4M

                    DATAFILE '/disk1/oradata/DEMO/system01.dbf'

:wq!

SQL>ALTER DATABASE OPEN;

$cp ora_2065.trc orabkup.sql

SQL>SHUTDOWN ABORT

SQL>@orabkup.sql

To rename (change the name of) a database:

-To rename a database follow the following steps:

-Trace the controlfile.

SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

-Edit the trace file as follows, replacing the keyword "REUSE" to "SET"

SQL> CREATE CONTROLFILE SET DATABASE "ACCT" RESETLOGS NOARCHIVELOG

LOGFILE GROUP 1 ('/disk1/oradata/DEMO/redolog1.log',

                                        '/disk2/oradata/DEMO/redolog2.log') SIZE 4M

                    GROUP 2 ('/disk1/oradata/DEMO/redolog1.log',

                                        '/disk2/oradata/DEMO/redolog2.log') SIZE 4M

                    DATAFILE '/disk1/oradata/DEMO/system01.dbf'

SQL>ALTER DATABASE OPEN RESETLOGS;

-Set the parameter db_name to the new name in the init.ora file.

-Remove the existing control files from their destination.

-Finally execute the traced controlfile to create new controlfiles.

Data Dictionary Views you can Query:

-V$CONTROLFILE

-V$CONTROLFILE_RECORD_SECTION

 

                                               Control File

Control File

Smallest file in database.

Crucial information about the database:

Database Name

Creation time of Database

Location of Redo & DBA files

SCN No(System Chane Number)(Logical time stamp for transaction)

Log Sequence Number

To create a database there should be one minimum control file.

Min: 1 Control File

Max: 8 Control Files

Multiplex of Control Files-Availability

Oracle recommends 3 control files.

Demo on control file & redo log file

SQL>startup

SQL>sho parameter control_

SQL>desc v$controlfile

SQL>select * from v$controlfile;

SQL>shutdown immediate

SQL>exit

$cd ORACLE_HOME/dbs

$vi init$ORACLE_SID.ora

control_files=_____,/disk2/oradata/xyz/control2.ctl

$cp control.ctl /disk2/oradata/xyz/control2.ctl

SQL>startup

SQL>sho parameter control_

SQL>sho parameter spfile

SQL>shutdown immediate

SQL>alter system set control_files='/disk3/oradata/earnest3/control.ctl','/disk2/oradata/xyz/control2.ctl' scope=spfile;

SQL>startup force

SQL>sho parameter control_

SQL>select * from v$controlfile;

SQL>shutdown immediate

SQL>exit

$cd $ORACLE_HOME/dbs

$vi init$ORACLE_SID.ora

SQL>startup

SQL>sho parameter spfile

SQL>sho parameter control_

SQL>shutdown immediate

SQL>startup mount

SQL>alter database backup controlfile to trace;

SQL>exit

$cd udump

$cp ___.trc ___/control.trc

$vi control.trc

SQL>@control.trc

SQL>select status from v$instance;

SQL>alter database open;

SQL>sho parameter control_

How to rename the database:

SQL>startup mount

SQL>alter database backup controlfile to trace;

Edit the trace file and select the second set2, remove {reuse} & place set & change the database name.

$vi control.trc

SQL>@control.trc

SQL>select name from v$database;

SQL>alter database open;

SQL>alter database open resetlogs;

SQL>archive log list

How to multiple redo log:

SQL>select * from v$log;

SQL>desc v$log

SQL>desc v$logfile

SQL>select * from v$log;

How to add a group:

SQL>alter database add logfile group 3 '/disk3/oradata/earnest3/redo3a.log' size 5m;

SQL>select * from v$log;

SQL>alter database add logfile member '/disk2/oradata/xyz/redo3b.log' to group 3;

 

************************************************************

 

RMAN Enhancements

RMAN Enhancements

Data Recovery Advisor:

Consider the error shown below:


SQL>conn scott/tiger
Connected.



SQL> create table t (col1 number);
create table t (col1 number)
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/home/oracle/oradata/PROD3/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3


This error occurs because the datafile in question is not available--it could be corrupt or perhaps someone removed the file while the database was running. In any case, you need to take some proactive action before the problem has a more widespread impact.


In Oracle Database 11g, the new Data Recovery Advisor makes this operation much easier. The advisor comes in two flavors: command line mode and as a screen in Oracle Enterprise Manager Database Control. Each flavor has its advantages for a given specific situation. For instance, the former option comes in handy when you want to automate the identification of such files via shell scripting and schedule recovery through a utility such as cron or at. The latter route is helpful for novice DBAs who might want the assurance of a GUI that guides them through the process.

Command Line Options:
The command line option is executed through RMAN. First, start the RMAN process and connect to the target.

$ rman target=/

Recovery Manager: Release 11.1.0.5.0 - Beta on Sun Jul 15 19:43:45 2007

Connected to target database: PROD3 (DBID=3132722606)

Assuming that some error has occured, you want to find out what happened. The list failure command tells you that in a jiffy.



RMAN> list failure;

If there is no error, this command will come back with the message:


no failures found that match specification

If there is an error, a more explanatory message will follow:

using target database control file instead of recovery catalog

List of Database failures


Failure ID Priority Status Time Detected Summary
142 HIGH OPEN 15-JUL-07 One or more non-system datafiles are missing




This message shows that some datafiles are missing. As the datafiles belong to a tablespace other than SYSTEM, the database stays up with that tablespace being offline. This error is fairly critical, so the priority is set to HIGH. Each failure gets a Failure ID, which makes it easier to identify and address individual failures. For instance you can issue the following command to get the details of failures 142.





RMAN> list failure detail;


This command will show you the exact cause of the error.


Use Data Recovery Advisor for assistance:

RMAN>advice failure;


It responds with a detailed explanation of the error and how to correct it:



The output has several important parts. First, the advisor analyzes the error. In this case, it's pretty obvious: the datafile is missing. Next, it suggests a strategy. In this case, this is fairly simply as well: restore and recover the file.(The dynamic performance view V$IR_MANUAL_CHECKLIST also shows this information.)



However, the most useful task Data Recovery Advisor does is shown in the very last line. It generates a script that can be used to repair the datafile or resolve the issue. The script does all the work; you don't have to write a single line of code.




Sometimes the advisor doesn't have all the information it needs. For instance, in this case, it does not know if someone moved the file to a different location or renamed it. In that case, it advices to move the file back to the original location and name (under Optional Manual Actions).


Issue the following command to "preview" the actions the repair task will execute:


RMAN> repair failure preview;


RMAN>repair failure;

Proactive Health Checks:
Bad blocks show themselves only when they are accessed so you want to identify them early and hopefully repair using simple commands before the users get an error. The tool dbverify can do the job but it might be a little inconvenient to use because it requires writing a script file containing all datafiles and a lot of parameters. The output also needs scanning and interpretation. In Oracle Database 11g, a new command in RMAN, VALIDATE DATABASE, makes this operation trival by checking database blocks  for physical corruption. If corruption is detected, it logs into the Automatic Diagnostic Repository. RMAN then produces an output.

RMAN>validate database;

We can also validate a specific tablespace:

RMAN>validate tablespace users;
We can also validate a specific datafile:

RMAN>validate datafile 1;

We can also validate even a block in a datafile:
RMAN>validate datafile 4 block 56;


The VALIDATE command extends much beyond datafiles however. We can validate spfile, controlfile copy, recovery files, Flash Recovery Area, and so on.

Parallel Backup of the Same Datafile:
We probably already know that you can parallelize the backup by declaring more than one channel so that each channel becomes a RMAN session. However, very few realize that each channel can back up only one datafile at a time. So even though there are several channels, each datafile is backed by only one channel, somewhat contrary to the perception that the backup is truly parallel. In Oracle Database 11g RMAN, the channels can break the datafiles into chunks known as "sections". We can specify the size of each section. Here's an example:

RMAN>run
{
allocate channel c1 type disk format '/backup1/%U';
allocate channel c2 type disk format '/backup2/%U';
backup
section size 500m
datafile 6;
}


This RMAN command allocates two channels and backs up the user's tablespace in parallel on two channels. Each channel takes a 500MB section of the datafile and backs it up in parallel. This makes backup of large files faster. When backed up this way, the backups show up as sections as well.

RMAN> list backup of datafile 6;


Now how the pieces of the backup show up as sections of the file. As each section goes to a different channel, we can define them as different mount points (such as /backup1 and /backup2), we can back them to tape in parallel as well.
However, if the large file #6 resides on only one disk, there is no advantage to using parallel backups. If we section this file, the disk head has to move constantly to address different sections of the file, outweighing the benefits of sectioning.


Virtual Private Catalog:
We are most likely using a catalog database for the RMAN repository. There are several advantages, such as reporting, simpler recovery in case the controlfile is damaged, and so on.
Generally, it makes sense to have only one catalog database as the repository for all databases. However, that might not be a good approach for security. A catalog owner will be able to see all the repositories of all databases. Since each database to be backed up may have a separate DBA, making the catalog visible may not be acceptable. The alternative is of course, we could create a separate catalog database for each target database, which is probably impractical due to cost considerations. The other option is to create only one database for catalog yet create a virtual catalog for each target database. Virtual catalogs are new in Oracle Database 11g. Let's see how to create them. First, we need to create a base catalog that contains all the target databases. The owner is, say, "RMAN". From the target database, connect to the catalog database as the base user and create the catalog.

$ rman target=/ rcvcat rman/rman@catdb

RMAN> create catalog;

RMAN> register database;

This is called the base catalog, owned by the user named "RMAN". Now, let's create two additional users who will own the respective virtual catalogs. For simplicity, let's gives these users the same name as the target database. While still connected as the base catalog owner (RMAN), issue the statement:

RMAN> grant catalog for database ode111 to ode111;


Now connect using the virtual catalog owner (ode111), and issue the statement create virtual catalog:

$ rman target=/ rcvcat ode111/ode111@catdb

RMAN> create virtual catalog;


Now, register a different database (PRONE3) to the same RMAN repository and create a virtual catalog owner "prone3" for its namesake database.

RMAN> grant catalog for database prone3 to prone3;

$ rman target=/ rcvcat prone3/prone3@catdb

RMAN> create virtual catalog;

Now, connecting as the base catalog owner (RMAN), if we want to see the database registered, we will see:

$ rman target=/ rcvcat=rman/rman@catdb

RMAN> list db_unique_name all;

As expected, it shows both the registered databases. Now, connect as ODE111 and issue the same command:

$ rman target=/ rcvcat ode111/ode111@catdb

RMAN> list db_unique_name all;

Note: Only one database was listed, not both. This user(ode111) is allowed to see only one database (ODE111), and that's what it sees. We can confirm this by connecting to the catalog as the other owner, PRONE3:

$rman target=/ rcvcat prone3/prone3@catdb


RMAN> list db_unique_name all;

Virtual catalogs allow you to maintain only one database for the RMAN repository catalog yet establish secure boundaries for individual database owners to manage their own virtual repositories. A common catalog database makes administration simpler, reduces costs, and enables the database to be highly available, again, at less cost.

Merging Catalog:
let's consider another issue. Now that we've learned how to create virtual catalogs on the same base catalogs, we may see the need to consolidate all these independent repositories into a single one. One option is to deregister the target databases from their respective catalogs and re-register them to this new central catalog. However, doing so also means losing all those valuable information stored in those repositories. We can, of course, sync the controlfiles and then sync back to the catalog, but that will initiate the controlfile and be impractical. Oracle Database 11g offers a new feature: merging the catalogs. Actually, it's importing a catalog from one database to another, or in other words, "moving" catalogs. Let's see how it is done. Suppose we want to move the catalog from database CATDB1 to another database called CATDB2.
First, connect to the catalog database CATDB2 (the target):
$ rman target=/ rcvcat rman/rman@catdb2

If this database already has a catalog owned by the user, "RMAN", then go on to the next step of importing: otherwise, we will need to create the catalog:

RMAN>create catalog;

Now, import from the remote catalog (catdb1):

RMAN> import catalog rman/rman@catdb1;

There are several important information in the output. Note how the target database got de-registered from its original catalog database. Now if we check the database names in this new catalog:

RMAN> list db_unique_name all;

Note that the DB key has changed.

The above operations will import the catalogs of all target databases registered to the catalog database. Sometimes we may not want to import only one or two databases. Here is a command to do that:

RMAN> import catalog rman/rman@catdb3 db_name = ode111

If we want to keep the database registered in both catalog databases. We will need to use the "no unregister" clause:

RMAN> import catalog rman/rman@catdb1 db_name = ode111 no unregister;

This will make sure the databases ODE111 is not unregistered from catalog database catdb1 but rather registered in the new catalog.

Configuring the Backup Compression Algorithm:
ZLIB compression, which is very fast but has a compression ratio that is not as good as the ratio of other algorithms. BZIP2 has a very good compression ratio, but is slower than ZLIB. The default compression algorithm is BZIP2.

RMAN> CONFIGURE COMPRESSION ALGORITHM 'BZIP2';

We can configure the compression algorithm with the following syntax

RMAN> CONFIGURE COMPRESSION ALGORITHM 'ZLIB';



Note that the COMPATIBLE initialization parameter must be set to 11.0.0 or higher for ZLIB compression.

Read-Only Transported Tablespaces Backup:


Alter a tablespace readonly, in the below list we can find logmnrts1.dbf of tablespace logmnrts1 as readonly

SQL> select name||' '||plugged_in||' '||enabled from v$datafile;


In this output we can find the datafile logmnrts1.dbf readonly. In Oracle 11g we can take the backup of the readonly tablespace normally.

RMAN> backup tablespace logmnrts1;
Duplicate Command:
A clone database on a remote site can now be easily created directly over the network with the enhanced DUPLICATE command without existing backups. ASM-to-ASM DUPLICATE over the network is also supported. This feature eliminates the need to copy or move backups to the remote site before executing the DUPLICATE command. This reduces DBA time and effort, and eliminates storage for the additional copy at the remote site.


RMAN> DUPLICATE TARGET DATABASE TO dupdb FROM ACTIVE DATABASE SPFILE NOFILENAMECHECK;

To duplicate a database to a remote host with the same directory structure:

- Preparing the Auxiliary Instance. (the initialization parameter file contains only DB_NAME set to an arbitrary value.)
- Configuring RMAN Before Duplication the database is open, RMAN has automatic channels already configured. We connect to the database instances as follows

CONNECT TARGET SYS/password@prod

CONNECT AUXILIARY SYS/password@dupdb

- Execute the DUPLICATE command.

- Use DUPLICATE for active duplication, this requires the NOFILENAMECHECK option because the source database files have the same names as the duplicate database files.

- Duplicating to a Host with the Same Directory Structure

DUPLICATE TARGET DATABASE
TO dupdb
FROM ACTIVE DATABASE
SPFILE
NOFILENAMECHECK;


RMAN automatically copies the server parameter file to the destination host, starts the auxiliary instance with the server parameter file, copies all necessary database files and archives redo logs over the network to the destination host, and recovers the database. Finally, RMAN opens the database with the RESETLOGS option to create the online redo logs.

*************************************************************************

System Privilages

Oracle System Privileges

General Information

 Note: System privileges are privileges that do not relate to a specific schema or object. 

 Data Dictionary Objects Related To System Privileges

all_sys_privs	session_privs	user_sys_privs
dba_sys_privs	system_privilege_map

Administer

• Administer Any SQL Tuning Set
• Administer Database Trigger (database level trigger)
• Administer Resource Manager
• Administer SQL Management Object
• Administer SQL Tuning Set
• Flashback Archive Administrator
• Grant Any Object Privilege
• Grant Any Privilege
• Grant Any Role
• Manage Scheduler
• Manage Tablespace

Advanced Queuing

• Dequeue Any Queue
• Enqueue Any Queue
• Manage Any Queue

Advisor Framework

• Advisor
• Administer SQL Tuning Set
• Administer Any SQL Tuning Set
• Administer SQL Management Object
• Alter Any SQL Profile
• Create Any SQL Profile
• Drop Any SQL Profile

Alter Any Privileges

• Alter Any Cluster
• Alter Any Cube
• Alter Any Cube Dimension
• Alter Any Dimension
• Alter Any Evaluation Context
• Alter Any Index
• Alter Any Indextype
• Alter Any Library
• Alter Any Materialized View
• Alter Any Mining Model
• Alter Any Operator
• Alter Any Outline
• Alter Any Procedure
• Alter Any Role
• Alter Any Rule
• Alter Any Rule Set
• Alter Any Sequence
• Alter Any SQL Profile
• Alter Any Table
• Alter Any Trigger
• Alter Any Type

Alter Privileges

• Alter Database
• Alter Profile
• Alter Resource Cost
• Alter Rollback Segment
• Alter Session
• Alter System
• Alter Tablespace
• Alter User

Analyze Privileges

• Analyze Any
• Analyze Any Dictionary

Audit Privileges

• Audit Any
• Audit System

Backup Privileges

• Backup Any Table

Change Privilege

• Change Notification

Clusters

• Alter Any Cluster
• Create Cluster
• Create Any Cluster
• Drop Any Cluster

Comment Privileges

• Comment Any Mining Model
• Comment Any Table

Contexts

• Create Any Context
• Drop Any Context

Create Any Privileges

• Create Any Cluster
• Create Any Context
• Create Any Cube
• Create Any Cube Build Process
• Create Any Cube Dimension
• Create Any Dimension
• Create Any Directory
• Create Any Evaluation Context
• Create Any Index
• Create Any Indextype
• Create Any Job
• Create Any Library
• Create Any Materialized View
• Create Any Measure Folder
• Create Any Mining Model
• Create Any Operator
• Create Any Outline
• Create Any Procedure
• Create Any Rule
• Create Any Rule Set
• Create Any Sequence
• Create Any SQL Profile
• Create Any Synonym
• Create Any Table
• Create Any Trigger
• Create Any Type
• Create Any View

Create Privileges

• Create Cluster
• Create Cube
• Create Cube Build Process
• Create Cube Dimension
• Create Database Link
• Create Dimension
• Create Evaluation Context
• Create External Job
• Create Indextype
• Create Job
• Create Library
• Create Materialized View
• Create Measure Folder
• Create Mining Model
• Create Operator
• Create Procedure
• Create Profile
• Create Public Database Link
• Create Public Synonym
• Create Role
• Create Rollback Segment
• Create Rule
• Create Rule Set
• Create Sequence
• Create Session
• Create Synonym
• Create Table
• Create Tablespace
• Create Trigger
• Create Type
• Create User
• Create View

Database

• Alter Database
• Alter System
• Audit System

Database Links

• Create Database Link
• Create Public Database Link
• Drop Public Database Link

Debug

• Debug Any Procedure
• Debug Connect Session

Delete

• Delete Any Cube Dimension
• Delete Any Measure Folder
• Delete Any Table

Dimensions

• Alter Any Dimension
• Create Any Dimension
• Create Dimension
• Drop Any Dimension

Directories

• Create Any Directory
• Drop Any Directory

Drop Any Privileges

• Drop Any Cluster
• Drop Any Context
• Drop Any Cube
• Drop Any Cube Build Process
• Drop Any Cube Dimension
• Drop Any Dimension
• Drop Any Directory
• Drop Any Evaluation Context
• Drop Any Index
• Drop Any Indextype
• Drop Any Library
• Drop Any Materialized View
• Drop Any Measure Folder
• Drop Any Mining Model
• Drop Any Operator
• Drop Any Outline
• Drop Any Procedure
• Drop Any Role
• Drop Any Rule
• Drop Any Rule Set
• Drop Any Sequence
• Drop Any SQL Profile
• Drop Any Synonym
• Drop Any Table
• Drop Any Trigger
• Drop Any Type
• Drop Any View

Drop Privileges

• Drop Profile
• Drop Public Database Link
• Drop Public Synonym
• Drop Rollback Segment
• Drop Tablespace
• Drop User

Evaluation Context

• Alter Any Evaluation Context
• Create Any Evaluation Context
• Create Evaluation Context
• Drop Any Evaluation Context
• Execute Any Evaluation Context

Execute Any Privileges

• Execute Any Class
• Execute Any Evaluation Context
• Execute Any Indextype
• Execute Any Library
• Execute Any Operator
• Execute Any Procedure
• Execute Any Program
• Execute Any Rule
• Execute Any Rule Set
• Execute Any Type

Export & Import

• Export Full Database
• Import Full Database

Fine Grained Access Control

• Exempt Access Policy

File Group

• Manage Any File Group
• Manage File Group
• Read Any File Group

Flashback

• Flashback Any Table
• Flashback Archive Administrator

Force

• Force Any Transaction
• Force Transaction

Indexes

• Alter Any Index
• Create Any Index
• Drop Any Index

Indextype

• Alter Any Indextype
• Create Any Indextype
• Create Indextype
• Drop Any Indextype
• Execute Any Indextype

Insert

• Insert Any Cube Dimension
• Insert Any Measure Folder
• Insert Any Table

Job Scheduler

• Create Any Job
• Create External Job
• Create Job
• Execute Any Class
• Execute Any Program
• Manage Scheduler

Libraries

• Alter Any Library
• Create Any Library
• Create Library
• Drop Any Library
• Execute Any Library

Locks

• Lock Any Table

Materialized Views

• Alter Any Materialized View
• Create Any Materialized View
• Create Materialized View
• Drop Any Materialized View
• Flashback Any Table
• Global Query Rewrite
• On Commit Refresh
• Query Rewrite

Mining Models

• Alter Any Mining Model
• Comment Any Mining Model
• Create Any Mining Model
• Create Mining Model
• Drop Any Mining Model
• Select Any Mining Model

OLAP Cubes

• Alter Any Cube
• Create Any Cube
• Create Cube
• Drop Any Cube
• Select Any Cube
• Update Any Cube

OLAP Cube Build

• Create Any Cube Build Process
• Create Cube Build Process
• Drop Any Cube Build Process
• Update Any Cube Build Process

OLAP Cube Dimensions

• Alter Any Cube Dimension
• Create Any Cube Dimension
• Create Cube Dimension
• Delete Any Cube Dimension
• Drop Any Cube Dimension
• Insert Any Cube Dimension
• Select Any Cube Dimension
• Update Any Cube Dimension

OLAP Cube Measure Folders

• Create Any Measure Folder
• Create Measure Folder
• Delete Any Measure Folder
• Drop Any Measure Folder
• Insert Any Measure Folder

Operator

• Alter Any Operator
• Create Any Operator
• Create Operator
• Drop Any Operator
• Execute Any Operator

Outlines

• Alter Any Outline
• Create Any Outline
• Drop Any Outline

Procedures

• Alter Any Procedure
• Create Any Procedure
• Create Procedure
• Drop Any Procedure
• Execute Any Procedure

Profiles

• Alter Profile
• Create Profile
• Drop Profile

Query Rewrite

• Global Query Rewrite
• Query Rewrite

Refresh

• On Commit Refresh

Resumable

• Resumable

Roles

• Alter Any Role
• Create Role
• Drop Any Role
• Grant Any Role

Rollback Segment

• Alter Rollback Segment
• Create Rollback Segment
• Drop Rollback Segment

Scheduler

• Manage Scheduler

Select

• Select Any Cube
• Select Any Cube Dimension
• Select Any Dictionary
• Select Any Mining Model
• Select Any Sequence
• Select Any Table
• Select Any Transaction

Sequence

• Alter Any Sequence
• Create Any Sequence
• Create Sequence
• Drop Any Sequence
• Select Any Sequence

Session

• Alter Resource Cost
• Alter Session
• Create Session
• Restricted Session

Synonym

• Create Any Synonym
• Create Public Synonym
• Create Synonym
• Drop Any Synonym
• Drop Public Synonym

Sys Privileges

• SYSDBA
• SYSOPER

Tablespace

• Alter Tablespace
• Create Tablespace
• Drop Tablespace
• Manage Tablespace
• Unlimited Tablespace

Table

• Alter Any Table
• Backup Any Table
• Comment Any Table
• Create Any Table
• Create Table
• Delete Any Table
• Drop Any Table
• Flashback Any Table
• Insert Any Table
• Lock Any Table
• Select Any Table
• Update Any Table

Transaction

• Force Any Transaction
• Force Transaction

Trigger

• Administer Database Trigger
• Alter Any Trigger
• Create Any Trigger
• Create Trigger
• Drop Any Trigger

Types

• Alter Any Type
• Create Any Type
• Create Type
• Drop Any Type
• Execute Any Type
• Under Any Type

Update

• Update Any Cube
• Update Any Cube Build Process
• Update Any Cube Dimension
• Update Any Table

Under

• Under Any Table
• Under Any Type
• Under Any View

User

• Alter User
• Become User
• Create User
• Drop User

View

• Create Any View
• Create View
• Drop Any View
• Flashback Any Table
• Merge Any View
• Under Any View

 Granting System PrivilegesGrant A PrivilegeGRANT <privilege_name> TO <schema_name>;GRANT create table TO uwclass; Revoking System PrivilegesRevoke A Single PrivilegeREVOKE <privilege_name> FROM <schema_name>;REVOKE create table FROM uwclass; Determine User Privs
This query will list the system privileges assigned to a userSELECT LPAD(' ', 2*level) || granted_role "USER PRIVS"
FROM (
  SELECT NULL grantee,  username granted_role
  FROM dba_users
  WHERE username LIKE UPPER('%&uname%')
  UNION
  SELECT grantee, granted_role
  FROM dba_role_privs
  UNION
  SELECT grantee, privilege
  FROM dba_sys_privs)
START WITH grantee IS NULL
CONNECT BY grantee = prior granted_role;

or

SELECT path
FROM (
  SELECT grantee,
    sys_connect_by_path(privilege, ':')||':'||grantee path
  FROM (
    SELECT grantee, privilege, 0 role
    FROM dba_sys_privs
    UNION ALL
    SELECT grantee, granted_role, 1 role
    FROM dba_role_privs)
  CONNECT BY privilege=prior grantee
  START WITH role = 0)
WHERE grantee IN (
  SELECT username
  FROM dba_users
  WHERE lock_date IS NULL
  AND password != 'EXTERNAL'
  AND username != 'SYS')
OR grantee='PUBLIC'
/ Dangerous Demo
Execute Any ProcedureSELECT *
FROM dba_sys_privs
WHERE privilege LIKE '%CREATE ANY PROC%';

conn owb/owb

CREATE OR REPLACE PROCEDURE <any owner>.do_sql(sqlin VARCHAR2) IS
BEGIN
  EXECUTE IMMEDIATE sqlin;
END;
/

BEGIN
  <any user>.do_sql('drop table emp cascade constraints');
END;
/

**********************************************************