Oracle system privileges

Note: Oracle 11gR1

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
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 Privileges
Grant A Privilege	GRANT TO ;
	GRANT create table TO usera;
Revoking System Privileges
Revoke A Single Privilege	REVOKE FROM ;
	REVOKE create table FROM usera;

How Much Space an Index is Using

In order to gather the necessary index statistics, you need to 'validate
the structure' of the index. This is achieved using the following command:

analyze index validate structure;

Analyze index validate structure checks the structure of the index and
populates a table called index_stats. The command (in this form) does not
gather statistics for use by the Cost Based Optimizer. The index_stats table
can only hold 1 row at a time. So, if you are planning to store index data for
multiple indexes or for historical comparison purposes, you will need to
insert the data into a more permanent table. Statistics are also lost at the
end of each session.

This analyze command only checks the structure of the index and populates the
index_stats table. It does not gather statistics for use by the Cost Based
Optimizer.

Once you have gathered the statistics, they can be retrieved as follows:

column name format a15
column blocks heading "ALLOCATED|BLOCKS"
column lf_blks heading "LEAF|BLOCKS"
column br_blks heading "BRANCH|BLOCKS"
column Empty heading "UNUSED|BLOCKS"
select name,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;

Example output
==============

SQL> analyze index draw1 validate structure;

Index analyzed.

SQL> select name, blocks, lf_blks, br_blks, blocks-(lf_blks+br_blks) empty
2 from index_stats;

ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
DRAW1 433 426 6 1

It is also possible to gather statistics on the use of space within the
btree itself:

select name,
btree_space,
used_space,
pct_used
from index_stats;

Example output
==============

SQL> select name, btree_space, used_space, pct_used
2 from index_stats;

NAME BTREE_SPACE USED_SPACE PCT_USED
--------------- ----------- ---------- ----------
DRAW1 810624 236284 30

Using DBA_FREE_SPACE

Oracle Metalink 121259.1

View DBA_FREE_SPACE can be used to determine the space available in the database.

Sometimes the DBA does not know why Oracle is unable to allocate a new extent and for a quick solution he proceeds to add a new datafile.  Not always is this solution is the best.  Oracle has some views that help to determine which solution is better.  These views are:

DBA_FREE_SPACE:  lists the free extents in all tablespaces

Column	Datatype	NULL	Description
TABLESPACE_NAME	VARCHAR2(30)	NOT NULL	Name of the tablespace containing the extent
FILE_ID	NUMBER	NOT NULL	ID number of the file containing the extent
BLOCK_ID	NUMBER	NOT NULL	Starting block number of the extent
BYTES	NUMBER		Size of the extent in bytes
BLOCKS	NUMBER	NOT NULL	Size of the extent in Oracle blocks
RELATIVE_FNO	NUMBER	NOT NULL	Relative file number of the first extent block

DBA_FREE_SPACE_COALESCED: This view help to determine how coalesce is a tablespace.

Column	Datatype	NULL	Description
TABLESPACE_NAME	VARCHAR2(30)	NOT NULL	Name of tablespace
TOTAL_EXTENTS	NUMBER		Total number of free extents in tablespace
EXTENTS_COALESCED	NUMBER		Total number of coalesced free extents in tablespace
PERCENT_EXTENTS_COALESCED	NUMBER		Percentage of coalesced free extents in tablespace
TOTAL_BYTES	NUMBER		Total number of free bytes in tablespace
BYTES_COALESCED	NUMBER		Total number of coalesced free bytes in tablespace
TOTAL_BLOCKS	NUMBER		Total number of free Oracle blocks in tablespace
BLOCKS_COALESCED	NUMBER		Total number of coalesced free Oracle blocks in tablespace
PERCENT_BLOCKS_COALESCED	NUMBER		Percentage of coalesced free Oracle blocks in tablespace

Errors such as:

ORA-1653 unable to extend table by in tablespace

ORA-1654 unable to extend index by for tablespace

ORA-1655 unable to extend cluster e by for tablespace

ORA-1658 unable to create INITIAL extent for segment in tablespace %s

ORA-1659 unable to allocate MINEXTENTS beyond in tablespace

indicate to DBA there is not free extent in the tablespace reported to support the new extent.  Consulting the view DBA_FREE_SPACE the DBA can know if really the tablespace does not have space available or the tablespace is fragmented and reorganization should be made.  Remember that two contiguous extents are considered two free spaces and both spaces can not be neither summarized nor counted as one for free space contiguous.

A simple query to get information about the free space in a tablespace is:

SELECT TABLESPACE_NAME, BLOCK_ID, BLOCKS, BYTES FROM DBA_FREE_SPACE;

Depending on the problem, the solution to apply can vary from adding new datafile to recreating the tablespace in order to defragment it.  To know which is the problem, the DBA should look up on DBA_FREE_SPACE and check how freed is the tablespace.

If the tablespace have no space avaliable, the solution can be:

1.  Add a new datafile using ALTER TABLESPACE command.

2.  Resize the current datafile using ALTER DATABASE command.

If the tablespace is fragmented the only solution is to reorganize the tablespace.

Some useful scripts:

--SPACE AVAILABLE IN TABLESPACES          select a.tablespace_name,        round(sum(a.tots)/1024/1024) Tot_Size_MB,          round(sum(a.sumb)/1024/1024) Tot_Free_MB,         round(sum(a.sumb)*100/sum(a.tots)) Pct_Free,          round(sum(a.largest)/1024/1024) Max_Free_MB,        sum(a.chunks) Chunks_Free   from   (   select tablespace_name, 0 tots, sum(bytes) sumb,           max(bytes) largest, count(*) chunks   from   DBA_FREE_SPACE a   group by tablespace_name   union   select tablespace_name, sum(bytes) tots, 0, 0, 0  from   DBA_DATA_FILES    group by tablespace_name) a   group by a.tablespace_name;

--SEGMENTS WITH MORE THAN 20 EXTENTS          select owner, segment_name, extents,        round(bytes/1024/1024) size_MB,          max_extents, next_extent   from   DBA_SEGMENTS    where  segment_type in ('TABLE','INDEX') and extents > 20   order by owner, segment_name;

Last updated: 2009-11-30 Monday