Category Archives: Administration

11g, 12c, Administration, linux, Oracle, Oracle 10g, oracle 9i, script, unix

Executing SQL script from command line or executable shell script

Leave a comment

You can call into SQLPLUS to execute SQL script from any shell script. This procedure shows how to structure your statements to LINUX environment for call into SQLPLUS from shell script.

1. Logon to your LINUX server as a user with access to SQLPLUS.
2. Use VI to create a executable file. Replace the connection string with appropriate username/password@SID values. Additionally, change the script you which to execute in this example we are executing “utlrp.sql”.

sqlplus sys/password@orcl as sysdba < < EOF @?/rdbms/admin/utlrp.sql exit; EOF

Larry Catt
OCP

11g, 12c, Administration, hp-ux, linux, unix

Executing Oracle Script as a LINUX or UNIX background process

Leave a comment

There are multiple situations where you have to execute processes in your Oracle environment as a daemon or background process of your OS. This procedure describes the use of “NOHUP” to begin the execution of a background process. In this example we are executing the Oracle 12c upgrade script ‘preupgrd.sql’ on an OEL (Oracle Enterprise Linux) platform.

1. Logon to your OEL server as the oracle software owner.

root@mylinux#su – oracle
oracle@mylinux#

2. Change directory to the location of your script file.

oracle@mylinux#cd /opt/app/oracle/product/12.1.0.2/rdbms/admin/
oracle@mylinux#

3. At the OS prompt execute the NOHUP command passing in a sqlplus connection string and the script name to be executed following by a terminating ampersand sign ‘&’.

nohup sqlplus sys/password as sysdba @./preupgrd.sql &

oracle@mylinux#nohup sqlplus sys/password as sysdba @./preupgrd.sql &
[1] 7260
oracle@mylinux#nohup: ignoring input and appending output to `nohup.out’

oracle@mylinux#

4. The (nohup) command will produce a log file in the local directory named nohup.out which records all terminal output from the daemon.

oracle@mylinux#cat nohup.out
SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 26 17:28:19 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Loading Pre-Upgrade Package…

SQL>

5. You can also see the processing with the system process command (ps) as shown below.

oracle@mylinux#ps -ef|grep nohup
oracle 9707 9268 0 18:11 pts/0 00:00:00 grep nohup
[1]+ Exit 1 nohup sqlplus sys/password as sysdba @./preupgrd.sql
oracle@mylinux#

6. This completes executing an Oracle script as a LINUX or UNIX background process.

Larry J. Catt, OCP

11g, 12c, Administration, hp-ux, linux, Users

Change time zone of Oracle RDBMS on Linux OS

Leave a comment

This procedure demonstrates the method to change the time zone setting of an Oracle RDBMS installed on a Linux server.   NOTE:  Oracle RDBMS by default take the time of the OS which they are installed on.   Due to the number and variations LINUX distributions, this procedure may not work on all LINUX installs.   This procedure was performed on Oracle Enterprise Linux 7.2  (OEL 7.2).

 

  1. Logon to your Linux server as root.

 

[larry@linux2 ~]$ sudo su

[sudo] password for larry:

[root@linux2 larry]#

 

  1. Change user to Oracle software owner and logon to SQLPLUS.

 

[root@linux2 etc]# su – oracle

Last login: Wed Nov 30 16:36:03 UTC 2016 on pts/1

[oracle@linux2 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 30 16:38:13 2016

 

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

 

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

SQL>

 

  1. Show the current date and time zone for the Oracle database with command: select systimestamp from dual;

 

SQL> select systimestamp from dual;

 

SYSTIMESTAMP

—————————————————————————

30-NOV-16 04.40.33.575810 PM +00:00

 

SQL>

 

  1. Exit out of SQLPLUS and change user to root.

 

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@linux2 ~]$ su –

Password:

Last login: Wed Nov 30 16:38:00 UTC 2016 on pts/1

[root@linux2 ~]#

 

  1. Change to directory /etc

 

[root@linux2 ~]# cd /etc

[root@linux2 etc]#

 

  1. Display the currently set system time with command date.

 

[root@linux2 etc]# date

Wed Nov 30 12:39:08 UTC 2016

[root@linux2 etc]#

 

  1. List the current setting to link /etc/localtime and remove link /etc/localtime, using the “rm –f” command.

 

[root@linux2 etc]# ls -l localtime

lrwxrwxrwx. 1 root root 23 Nov 30 14:22 localtime -> /usr/share/zoneinfo/UTC

[root@linux2 etc]# rm -rf localtime

[root@linux2 etc]#

 

  1. Create new link to directory /usr/share/zoneinfo/<your time zone> with you correct timezone.  In this example we are changing to EST.

 

ln -s /usr/share/zoneinfo/EST localtime

 

 

[root@linux2 etc]# ls -l localtime

lrwxrwxrwx. 1 root root 23 Nov 30 09:23 localtime -> /usr/share/zoneinfo/EST

[root@linux2 etc]#

 

  1. Execute linux date command to see new timezone.

 

[root@linux2 etc]# date

Wed Nov 30 11:43:59 EST 2016

[root@linux2 etc]#

 

  1. Logon as the oracle software owner and logon to SQLPLUS.

[root@linux2 etc]# su – oracle

Last login: Wed Nov 30 11:41:44 EST 2016 on pts/1

[oracle@linux2 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 30 11:45:13 2016

 

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

 

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

SQL>

 

 

  1. To verify the new timezone update has been completed, execute the SQL statement: select systimestamp from dual;

 

SQL> select systimestamp from dual;

 

SYSTIMESTAMP

—————————————————————————

30-NOV-16 11.46.25.722487 AM -05:00

 

SQL>

 

 

  1. This completes update of timezone for Oracle RDBMS on a Linux platform.

 

 

Larry Catt

OCP

 

Administration, error, hp-ux, linux, unix

Allowing SCP on Linux 7

Leave a comment

Attempting to transfer files from one server Linux1 to Linux2 results in error: Linux2 sshd not started.

[oracle@linux1 STAGE]$ scp * oracle@10.2.0.155:/u01/STAGE/*
ssh: connect to host 10.2.0.155 port 22: Connection refused
lost connection
[oracle@linux1 STAGE]$
1. Logon as the root user to linux2 server.

[larry@linux2 ~]$ su –
Password:
Last login: Wed Sep 14 10:26:34 EDT 2016 on pts/1
[root@linux2 ~]#

2. Attempt to start the SSH service with command: service sshd start

[root@linux2 ~]# service sshd start
Redirecting to /bin/systemctl start sshd.service
[root@linux2 ~]#

3. Check the status of sshd service with command: service sshd status

[root@linux2 ~]# service sshd status
Redirecting to /bin/systemctl status sshd.service
? sshd.service – OpenSSH server daemon
Loaded: loaded (/usr/lib/systemd/system/sshd.service; enabled; vendor preset: enabled)
Active: active (running) since Wed 2016-09-14 10:23:36 EDT; 16min ago
Docs: man:sshd(8)
man:sshd_config(5)
Main PID: 1283 (sshd)
CGroup: /system.slice/sshd.service
??1283 /usr/sbin/sshd -D

Sep 14 10:23:36 linux2.localdomain systemd[1]: Started OpenSSH server daemon.
Sep 14 10:23:36 linux2.localdomain systemd[1]: Starting OpenSSH server daemon…
Sep 14 10:23:36 linux2.localdomain sshd[1283]: Server listening on 0.0.0.0 p….
Sep 14 10:23:36 linux2.localdomain sshd[1283]: Server listening on :: port 22.
Sep 14 10:25:45 linux2.localdomain sshd[3639]: pam_unix(sshd:auth): authenti…y
Sep 14 10:25:47 linux2.localdomain sshd[3639]: Failed password for larry fro…2
Sep 14 10:25:50 linux2.localdomain sshd[3639]: Accepted password for larry f…2
Sep 14 10:39:50 linux2.localdomain systemd[1]: Started OpenSSH server daemon.
Hint: Some lines were ellipsized, use -l to show in full.
[root@linux2 ~]#

4. Re-attempt the transfer from linux1 server.

[oracle@linux1 STAGE]$ scp * oracle@10.2.0.155:/u01/STAGE/.
oracle@10.2.0.155’s password:
fmw_12.2.1.1.0_infrastructure_Disk1_1of1.zip 100% 1490MB 99.3MB/s 00:15
fmw_12.2.1.1.0_infrastructure.jar 100% 1490MB 99.4MB/s 00:15
fmw_12211_readme.htm 100% 19KB 19.1KB/s 00:00
fmw.rsp 100% 1996 2.0KB/s 00:00
jdk1.7.0_79: not a regular file
jdk-7u79-linux-x64.tar.gz 100% 146MB 146.4MB/s 00:01
[oracle@linux1 STAGE]$
This completes failure of SCP transfer from stopped SSHD processes.
Larry Catt
OCP

Administration, hp-ux, linux, unix

Change time zone of Linux Server

Leave a comment

This procedure demonstrates the method to change the Time Zone of a typical Linux server.   NOTE:  Due to the number and variations LINUX distributions, this procedure may not work on all LINUX installs.   This procedure was performed on Oracle Enterprise Linux 7.2  (OEL 7.2).

 

  1. Logon to your Linux server as root.

 

[larry@linux2 ~]$ sudo su –

[sudo] password for larry:

Last login: Thu Nov  3 11:02:19 EDT 2016 on pts/1

[root@linux2 ~]#

 

 

  1. Change to directory /etc

 

[root@linux2 ~]# cd /etc

[root@linux2 etc]#

 

  1. Display the currently set system time with command date.

 

[root@linux2 etc]# date

Wed Nov 30 12:39:08 UTC 2016

[root@linux2 etc]#

 

  1. List the current setting to link /etc/localtime and remove link /etc/localtime, using the “rm –f” command.

 

[root@linux2 etc]# ls -l localtime

lrwxrwxrwx. 1 root root 23 Nov 30 14:22 localtime -> /usr/share/zoneinfo/UTC

[root@linux2 etc]# rm -rf localtime

[root@linux2 etc]#

 

  1. Create new link to directory /usr/share/zoneinfo/<your time zone> with you correct timezone.  In this example we are changing to EST.

 

ln -s /usr/share/zoneinfo/EST localtime

 

 

[root@linux2 etc]# ls -l localtime

lrwxrwxrwx. 1 root root 23 Nov 30 09:23 localtime -> /usr/share/zoneinfo/EST

[root@linux2 etc]#

 

  1. Execute linux date command to see new timezone.

 

[root@linux2 etc]# date

Wed Nov 30 09:25:40 EST 2016

[root@linux2 etc]#

 

  1. This completes changing the time zone on Linux server.

 

Larry Catt

OCP

 

12c, Administration, privileges, Users

Oracle 12c – User privilege analysis

Leave a comment

Oracle 12c gives the ability to perform a user privilege analysis to bounce against granted privileges.   This allows you to reduce granted privilege down to what the individual user actually needs and no more.   This article gives a procedure to perform User privilege analysis.

 

 

  1. Logon oracle server as the oracle software owner than logon to sqlplus with sysdba privilegs. If this is done by none privileged user account, the user must have the CAPTURE_ADMIN role granted.

 

[root@linux2 ~]# su – oracle

Last login: Thu Dec 15 07:50:00 EST 2016 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

Option >

1

[oracle@linux2 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 15 13:05:59 2016

 

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

 

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

 

SQL>

 

 

 

  1. Define the privilege analysis policy with the CREATE_CAPTURE procedure of DBMS_PRIVILEGE_CAPTURE package. This example below will capture all privileges used.

 

BEGIN

DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(

        name         => ‘user_priv_capture’,

        description  => ‘Capture the privileges by user test’,

        type         => DBMS_PRIVILEGE_CAPTURE.G_DATABASE);

END;

/

  1. Enable the privilege analysis policy.

 

SQL> EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (‘user_priv_capture’);

 

PL/SQL procedure successfully completed.

 

SQL>

 

  1. Logon as the user test and perform DML processing.

 

SQL> connect test/test

Connected.

 

SQL>  insert into test.test_times(pers_id, first_name) values(99,’test’);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

[oracle@linux2 ~]$

 

 

  1. Logon as sysdba and disable the privilege analysis policy’s recording of privilege use.

 

[oracle@linux2 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 15 13:25:29 2016

 

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

 

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

 

SQL> EXEC DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (‘user_priv_capture’);

 

PL/SQL procedure successfully completed.

 

SQL>

  1. Generate privilege analysis results.

 

SQL> EXEC DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (‘user_priv_capture’);

 

PL/SQL procedure successfully completed.

 

SQL>

 

  1. Query the table dba_used_privs to see privileges used during the capture period.

 

SQL> column username format a12

column sys_priv format a15

column object_owner format a10

column object_name format a25

set pagesize 500

SELECT username, sys_priv, object_owner, object_name FROM dba_used_privs WHERE capture = ‘user_priv_capture’;

SQL> SQL> SQL> SQL> SQL>

USERNAME     SYS_PRIV        OBJECT_OWN OBJECT_NAME

———— ————— ———- ————————-

TEST         CREATE SESSION

TEST                               SYS        USER_TABLES

APEX_040200                  SYS        DBMS_OUTPUT

TEST         CREATE SESSION

TEST         CREATE SESSION

TEST         CREATE SESSION

TEST         CREATE SESSION

TEST                               SYS        DUAL

APEX_040200                  SYS        DBMS_OUTPUT

TEST         CREATE SESSION

TEST         CREATE SESSION

TEST         CREATE SESSION

TEST                               SYS        DUAL

APEX_040200                  SYS        DEFAULT_JOB_CLASS

TEST         CREATE SESSION

TEST                               SYSTEM     PRODUCT_PRIVS

TEST                               SYS        DBMS_APPLICATION_INFO

 

17 rows selected.

 

SQL>

  1. Optionally, drop the privilege analysis policy.

 

EXEC DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (‘user_priv_capture’);

 

Larry Catt

OCP

12c, Administration, Oracle

Oracle 12c – Valid Time Temporal Rules

Leave a comment

In Oracle 12c Valid Time Temporal functionality to define a start and stop time where the data is valid.   This article covers the basic of Valid Time Temporal rules for implementation

Temporal Validity

  1. Valid time dimension is placed on a table for each row.
  2. Two hidden columns are added to table definition.
  3. When querying the table, rows will be displayed regardless of temporal validity, unless you filter based on these two columns.
  4. Temporal Validity has no effect on storage or performance.
  5. It provides a new filter capability only.
  6. Provides a range of time for each row when the data is valid.
  7. Date range of validity can be set by user or application.
  8. Concepts of Temporal Validity”
    1. VALID TIME – This is a user-defined representation of time. Examples of a valid time include project start and finish dates, and employee hire and termination dates.
    2. Tables with valid-time semantics — These tables have one or more dimensions of user-defined time, each of which has a start and an end.
    3. Valid-time flashback queries — This is the ability to do as-of and versions queries using a valid-time dimension.
  9. Valid-time periods require pairs of date-time: start and end.

 

 

Larry Catt

OCP

12c, Administration, Performance

Oracle 12c – Use Adaptive SQL Plan Management

Leave a comment

SQL Plan Management (SPM) Evolve Advisor in Oracle 12c analyzes SQL plans which were not used during executions to determine if they would run better than accepted plans.  If the plan is determined to run better than the accepted plan it will be accepted, more than one plan can be accepted for give SQL statement.   This evaluation is executed during maintenance window when automatic SQL tuning is set to COMPREHENSIVE mode.  The evaluation can be run manually with DBMS_SPM package, SPM Evolve Advisor or the Automatic SQL Tuning advisor.  This can be set in EM or through DBMS_AUTO_TASK_ADMIN package.   This procedure show the setup of SPM.

 

 

  1. Logon to you database server as the oracle software owner and connect to SQLPLUS as sysdba.

 

[root@linux2 ~]# su – oracle

Last login: Tue Dec 27 07:53:43 EST 2016 on pts/2

Enter database to use:

1 – ORCL

2 – CDB1

Option >

1

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 28 11:06:31 2016

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

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

 

SQL>

 

  1. Execute the following block:

BEGIN

DBMS_AUTO_TASK_ADMIN.ENABLE ( client_name => ‘sql tuning advisor’,

operation => NULL,

window_name => NULL );

END;

/

 

 

SQL> BEGIN

DBMS_AUTO_TASK_ADMIN.ENABLE ( client_name => ‘sql tuning advisor’,

operation => NULL,

window_name => NULL );

END;

/

  2    3    4    5    6

PL/SQL procedure successfully completed.

 

SQL>

 

  1. Query dictionary to confirm:

 

SELECT client_name, status

FROM dba_autotask_client

WHERE client_name = ‘sql tuning advisor’;

CLIENT_NAME STATUS

——————– ——–

sql tuning advisor ENABLED

 

  1. Disable with DBMS_AUTO_TASK_ADMIN.DISABLE procedure.

 

 

  1. This completes overview of Adaptive SQL PLAN Management.

 

Larry Catt

OCP

12c, Administration, Performance

Oracle 12c – SQL Tuning

Leave a comment

Adaptive query optimization in 12c allows for changes to execution plan at run time and gathering of new information to generate better statistics.  The two aspects of Adaptive Query Optimization:

  1. Adaptive Plan – Improving the initial execution of query at run time.
  2. Adaptive Statistics – Gathering new statistics to improve future executions.

 

Adaptive Plans – allows optimizer to make decision on SQL at runtime and in mid execution.  It will actually make sub-plans and collect statistics to make final decision on execution.

  1. This may cause a difference in the EXPAIN PLAN command and the DBMS_XPLAN.DISPLAY_CURSOR where the EXPLAIN PLAN will show the initial plan execution and DISPLAY_CURSOR will show the final plan executed.
  2. In oracle 12c only Join and Parallel queries are supported of adaptive plans.
  3. You can actually see all plans tried with the DBMS_XPLAN function by using the ‘+adaptive’ parameter with the notation (-) against the id column of plan table.
  4. V$SQL view has new column IS_RESOLVED_ADAPTIVE_PLAN which show if SQL has an adaptive plan. If it ‘Y’ the statement has adaptive plan which has been used, if ‘N’ it has an adaptive plan that is not completed yet and if NULL it has no adaptive plan.
  5. INIT parameter OPTIMIZER_ADAPTIVE_REPORTING_ONLY is set to TRUE (default is FALSE) information on adaptive plan will be collected but not used. You can then use DBMS_XPLAN plan to view data with ‘+report”.

 

Adaptive Statistics:

Optimizer uses statistics in order to create good execution plans.  Re-optimization uses the execution of previous statements results to determine if the executed plan was optimal and adjusts plans for future execution in an iterative process.

Three types of re-optimization:

  1. Statistics Feedback – also known as cardinality feedback, it compares cardinality of estimated and actual executions. Storing the correct cardinality statistics and creates SQL plane directive for future use.
  2. Performance Feedback – Used to improve degree of parallelism for future SQL statements based on previous executions when Automatic Degree of Parallelism (AutoDOP) is enabled in adaptive mode.
  3. SQL plan directives – Is additional information used by optimizer to determine correct execution plans and are created on query expressions not SQL statements thus can be applied to multiple SQL statements. Multiple directives can be used for single SQL statement.  SQL directives are purged if not used in 53 days.  You can see directives in views:   DBA_SQL_PLAN_DIRECTIVES and DBA_SQL_PLAN_DIR_OBJECTS.

 

 

 

Partition table statistics – Two sets of statistics are gathered at the partition level and table level, in 12c the table level statistics are aggregated by partition level avoiding full table scan.

 

INCREMENTAL_STALENESS – preference setting in DBMS_STATS.SET_TABLE_PREFS allows you to determine when statistics on tables are stale by percentage of change with USE_STALE_PERCENT or locking statistics forcing their use regardless of how much change has occurred with USE_LOCKED_STATS.

 

Incremental statistics improved by EXCHANGE_PARTITION command allows you to statistics from non-partitioned table with those in a partition.  Prior to 12c you had to gather data on all partitions before using incremental global statistics.   Use the DBMS_STATS package preference of INCREMENTAL_LEVEL set to TABLE (the default is PARTITION) to use this EXCHANGE_PARTITION feature.

 

Concurrent Statistics – when global statistics gathering preference CONCURRENT is set, the Job Scheduler and Advance queuing gather states on table/partitions concurrently.  12c has optimized automatic statistics gathering by batching smaller jobs to run together.  When preference CONCURRENT is set to ALL or AUTOMATIC stats a gathered nightly.

 

 

Automatic column group detection – Gathering of statistics on columns used in filter predicates by estimating cardinality.   Determining columns to gather increased stats is determined by workload history.  Three step process determines Auto Column Group detection:

  1. Seed column usage – workload history via the DBMS_STATS.SEED_COL_USAGE procedure.
  2. Create the column groups – Use the DBMS_STATS.CREATE_EXTENDED_STATS function to id columns groups based on usage information gathered. Once identified extended statistics will be automatically generated.
  3. Regather statistics – Regather statistics for all tables identified for column stats extended.

 

 

Larry Catt

OCP

12c, Administration, privileges, Users

Oracle 12c – User privilege analysis overview

Leave a comment

Oracle 12c contains the feature to analyze the privileges actually used by an individual user account in order to implement least privilege policies within the RDBMS.  This article gives an overview of User privilege analysis in Oracle 12c.

 

 

  1. Privilege analysis is provided by Oracle Data Vault and it allows for creation of profile which captures system and object privileges used by a user.
  2. This profile can be used to bounce used privileges against granted privileges.
  3. You can reduce privileges that are not in use through this analysis.
  4. It is possible to perform privilege analysis with or without have Database Vault configured and enabled.
  5. It is possible to administer privilege analysis by EM Cloud Control or the package DBMS_PRIVILEGE_CAPTURE
  6. The role CAPTURE_ADMIN grants execute privilege on DBMS_PRIVILEGE_CAPTURE package and select on views to results.
  7. The DBMS_PRIVILEGE_CAPTURE package allows you to create/enable/disable/drop privilege analysis policies.
  8. Only one privilege analysis policy can be enabled in the database at a time, but the DBMS_PRIVILEGE_CPATURE.G_DATABASE privilege analysis can be done with one other user defined policy at the same time.
  9. Analysis policies running will still be running after DB restart.
  10. Privilege analysis policies must be disabled, before reports can be generated.
  11. Policies must be disabled before they are dropped.
  12. Dropping a privilege policy, also drops all collected data by that policy.
  13. List of some view available with Privilege Analysis:
    1. DBA_PRIV_CAPTURES — Lists information about existing privilege analysis policies
    2. DBA_USED_PRIVS — Lists the privileges that have been used for reported privilege analysis policies
    3. DBA_UNUSED_PRIVS — Lists the privileges that have not been used for reported privilege analysis policies
    4. DBA_USED_OBJPRIVS — Lists the object privileges that have been used for reported privilege analysis policies. It does not include the object grant paths.
    5. DBA_UNUSED_OBJPRIVS — Lists the object privileges that have not been used for reported privilege analysis policies. It does not include the object privilege grant paths.
    6. DBA_USED_SYSPRIVS — Lists the system privileges that have been used for reported privilege analysis policies. It does not include the system privilege grant paths.
    7. DBA_UNUSED_SYSPRIVS — Lists the system privileges that have not been used for reported privilege analysis policies. It does

Larry Catt

OCP