How do you check the User failed login attempts without auditing the user?

A user created with default profile and the profile is modified to have the resource name FAILED_LOGIN_ATTEMPTS set to 10. Now the user account will lock when the attempts to connect to the user is more than 10.

How do we verify that there are so many number of failed login attempts earlier.
Is auditing the user the only solution ?
No

You can find the FAILED_LOGIN_ATTEMPTS value from the lcount column of user$ table.

Conclusions :
============
In order to check if the FAILED_LOGIN_ATTEMPTS is working or not , we need to try giving wrong passwords 10 times continuously without any successful attempts. Then 11th attempt will lock the user account. But if there is atleast one single successful attempt to connect to the user , the value resets to "0" again.

Purging LOBs:

If we purge a table which contains LOBs , we may not reclaim space. BLOBs are stored out of line once they exceed 4,000 bytes (you can also specify that they are always stored out of line as well).

If the BLOBs were all inline, each taking 4000 or less bytes, then you did free space in your table.  The table will not shrink in size (tables NEVER shrink) but it will have more blocks on its free list for subsequent inserts.

How do I determine what baseline I’m on ?
Following select will tell you the R12 version:
SQL>select 'RELEASE '||RELEASE_NAME from APPLSYS.FND_PRODUCT_GROUPS;

Following select will tell you the products and their corresponding baselines:
SQL>set pages 999
SQL>set long 9999
SQL>column BASELINE format a8
SQL>break on baseline
SQL>select BASELINE,upper(abbreviation),name from AD_TRACKABLE_ENTITIES order by baseline,abbreviation;

$ cd $AD_TOP/bin
$ perl admsi.pl -patch_top=<patch-top-directory> -appspass=<apps-password>
For details, run "perl admsi.pl --help"

adpatch defaultsfile=$APPL_TOP/admin/<SID>/onlinedef.txt \
patchtop=$PER_TOP/patch/115/driver  \
logfile=HRGLOBAL.log driver=hrglobal.drv \
workers=24  \
flags=hidepw

SELECT DECODE(hli.legislation_code
,null,'Global'
,hli.legislation_code)                legCode
, hli.application_short_name                  asn
, hli.status status, last_update_date
FROM hr_legislation_installations hli
where hli.status = 'I';

Explaining Patch c,d,g and Unified Driver:

The unzipped patch contains three  drivers "c,d,g" or "u". These are called copy,database,generate drivers or Unified drivers. The unified driver is the combination of all the three c,d,g drivers.

Now we will see these drivers one by one ....

Copy Driver : (cXXXXX.drv )
-- Copies all files to the appropriate directory ( ex:  .fmb , .pll files)
-- Relinks executable

Ex :
copy    ap     forms/US      APXIISIM.fmb     110.8
Driver will check the versions of APXIISIM.fmb on your system. If it is less than 110.8, then it will copy this file to the $AU_TOP/forms/US directory.

copy    ap     patch/110/sql apaiithb.pls     110.2
Driver will check the versions of apaiithb.pls on your system. If it is less than 110.2, then it will copy this file to the $AP_TOP/patch/110/sql directory.

copy    fnd    resource      JE.pll           110.8
Driver will check the versions of JE.pll on your system. If it is less than 110.8, then it will copy this file to the $AU_TOP/resource directory.

Database Driver : ( dXXXXXX.drv )
This is the driver that runs .sql, .pls, .odf and other files that update the database. As mentioned previously, some common ways the database is updated by the this driver are:
-- Create packages
-- Create new error messages
-- Add a new table or view to the database
-- Add a new column to a table
-- Add new seed data to a table

The database driver uses the same command structure:
<command>  <product>  <subdirectory>  <file>  <other arguments....>
However, as you will see, it uses the <other arguments> section much more. There are numerous different arguments that can be used, but following are some more common examples:
- sql Run the script directly from the worker
- sqlplus Spawn a new sqlplus session and run the script
- package Same as sqlplus but performs package version checking

Another argument you may see at the end of the command string is a 'phase='’ command. Before performing any actions, adpatch divides all actions contained in the patch driver file into phases based on information specified in the patch driver. Adpatch performs all actions grouped in one phase in parallel before proceeding to the next.
Many 'd' drivers in patches contain phase definitions that determine what order the files in the driver will
run. If the driver does not contain any phase definitions, then the files are executed in the order they appear in the driver.
There are currently around 19 different phases that can be defined, but some examples are:
Phase name      Action taken
seq             Create sequences
tab             Create tables and indexes
pls             Create package specifications (specs)
vw              Create Views
plb             Create package bodies

Following are some examples of common commands found in a dXXXXXX.drv driver, and a brief explanation.
sql  ar   patchsc/107/sql b512706a.sql  !AR_PERIOD_TYPES  &un_ar  &pw_ar sql  &phase=tab
Run the sql script $AR_TOP/patchsc/107/sql/b512706a.sql using the ar username and password. The !AR_PERIOD_TYPES is another parameter expected by the .sql script. As discussed previously the &phase argument specifies what type of action this script is doing, in this case creating a table, and when the script will be executed by the driver.

sql  ar   patchsc/107/sql    ARTEHPCS.pls    none none none package &phase=pls
Run the script $AR_TOP/patchsc/107/sql/ARTEHPCS.pls which creates a package (phase=pls).

sql ar patchsc/107/sql artcall5.sql none none none sqlplus &phase=tbm+5 &un_ar &pw_ar
Run the script $AR_TOP/patchsc/107/sql/artcall5.sql. The phase ‘tbm’ tells you that it is altering a table.
The +5 is a way of determining the order a script will run within a phase. For example, within a phase, commands would be run in the following order:
&phase=tbm
&phase=tbm+5
&phase=tbm+99

Generate Driver : (gXXXXXX.drv )
Following are some examples of commands in a 'g' driver. Once again, remember that
-- The .fmb files are stored in $AU_TOP, and when generated the executable (.fmx) is stored under the product.
-- The .pll is stored under $AU_TOP, and when generated the executable (.plx) is also stored under $AU_TOP.
genform    ap     forms/US     APXIISIM.fmb
Generate the form $AU_TOP/forms/US/APXIISIM.fmb and store the executable in $AP_TOP/forms/US/APXIISIM.fmx
genrep     ap     reports      APXIIADV.rdf
Generate the report $AP_TOP/reports/APXIIADV.rdf, and store the resulting file APXIIADV.rdf in the same directory.
genfpll    fnd    resource     JE.pll
Generate the PL/SQL library $AU_TOP/resource/JE.pll, and store the executable JE.plx in $AU_TOP/resource.

Unified Driver : ( uXXXXXXX.drv )
The u driver is a merged driver that is a combined c, d, and/or g driver. Oracle is beginning to release a majority of its patches as unified driver patches. If a patch is a unified driver patch, then only the u driver is applied.

1) prepatch analysis
2) pre health checks
3) shutdown MT services
4) Enable Maintenance mode
5) Apply patch using adpatch
6) Disable Maintenance mode
7) Start all MT services
8 ) Perform any post patch steps
9) Post health checks

SQL> create table apps.invalids_askm_bak tablespace bkpd as (select owner,object_name,object_type from dba_objects where status='INVALID');
SQL> select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+) order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;
SQL> select nls_language, language_code, installed_flag from apps.fnd_languages where installed_flag in ('I','B');
SQL> select * from apps.ad_bugs where bug_number='&bug';

SQL> @$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE
SQL> select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual; --> to check

adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/default.txt logfile=patch.log \
patchtop=$APPL_TOP/patches/xxxxxxx options=noautoconfig,nocompilejsp,nocompiledb workers=8 \
driver=xxxxxxxx.drv

SQL> @$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE
SQL> select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual; --> to check

Do we have mod_plsql,jserv,reports server in R12 ?

mod_plsql :

Mod_plsql is an Apache web server extension that can be used to develop web application pages using Server PL/SQL.
Unlike Oracle E-Business Suite Release 11i, Release 12 does not include mod_plsql as part of its technology stack.
Modplsql component of Apache is removed in R12 .you have to see the alternate for custom developed programs which will be using this.
If you have developed mod_plsql extensions to Oracle E-Business Suite Release 11i, and are considering upgrading to Release 12, you will have to take some action to preserve that functionality.
mod_plsql is replaced by Oracle Application Framework in R12. The reasong for replacing the mod_plsql with Oracle Application Framework in R12 is that , mod_plsql does not provide solutions for a number of important problems that must be solved in a robust and secure web application.

The following components/modules were removed from Release 12
1. mod_plsql
2. Oracle Reports Server
3. Oracle Graphics Integration with Oracle Forms
4. Oracle Applications Framework pages in the AK Repository (AK mode)

JServ :

OC4J replaces the Jserv component which is there in the current release 11i of Oracle Applications. Also as a result the mod_jserv component would be replaced by the mod_oc4j component in release 12 of Oracle Applications. The mod_oc4j is used to communicate between different OC4J instances.

The default installation Release 12 of Oracle Applications creates 3 OC4J instances

-- Oacore: This runs the OA Framework -based applications.
-- Forms: This runs the Forms-based applications.
-- OAFM: This is responsible for running the web services.

The Jserv groups which are there current in Oracle Applications Release 11i are also planned be replaced by OC4J instances.

As mentioned earlier the OC4J properties are controlled using the XML files and OC4J.properties file. These files are managed by the standard Oracle Applications Autoconfig.

The Java code deployment in Oracle E-Business suite for OC4J is done at the time of install using rapid install and maintained by ad tools like adadmin and adpatch. New custom code deployment can be done by using the Application Server Control user interface.

The OC4J implementation In Oracle Applications Release 12 includes the following directory structure.

-- applications: Contains applications deployed
-- applications-deployment: Contains configuration settings for the applications deployed
-- config: Contains common configuration setting for the OC4J instance.

Remember there are no jserv.properties or jserv.conf or zone.properties in R12 (new techstack), Jserv is replaced by Oacore.

Finally ...
- Jserv component is removed and it is replaced in R12 by OC4J(oacore)
- mod_plsql is replaced by Oracle Application Framework
- The reports server has been removed in R12 and it runs as a spawned process called rwrun which is spawned by conc manager.

Ref Note : 726711.1

Killing runaway processes after terminating concurrent Request:

Every concurrent Request uses some resources for running. If you feel that the concurrent request is taking long time and decided to terminate the concurrent request , the resources may not be released soon. These processes are called runaway processes. So we need to manually kill the processes at database and os level to have the resources released to the system.

Complete details about the request can be found using the following query :

R12 - Various LogFiles Locations :

Startup/Shutdown Log files for Application Tier in R12

$LOG_HOME/appl/admin/log/

Service	Logfile Name
TNS Listener Start/Stop log	adalnctl.txt
Fulfillment Server Start/Stop log	jtffmctl.txt
Oracle HTTP Server start/stop log	adapcctl.txt
Concurrent Managers and ICM start/stop log	adcmctl.txt
Forms OC4J start/stop log	adformsctl.txt
OACore OC4J start/stop log	adoacorectl.txtq
OAFM OC4J start/stop log	adoafmctl.txt
OPMN start/stop log	adopmnctl.txt

Tech Stack  10.1.3 (Web/HTTP Server) Logs

Log File Name	Log File Location
AD script log files (e.g.from adapcctl.sh)	$INST_TOP/logs/appl/admin/log
CM Log Files ($APPLCSF/$APPLLOG)	$INST_TOP/logs/appl/conc/log
AD tools log files (e.g. ADPATCH)	$APPL_CONFIG_HOME/admin/$TWO_TASK/log
OPMN Log Files (text and ODL)	$ORA_CONFIG_HOME/10.1.3/opmn/logs (may move to  $INST_TOP/logs/10.1.3/opmn)
Apache Log Files (text and ODL)	$INST_TOP/logs/10.1.3/Apache/
OC4J Log Files (text)	$INST_TOP/logs/10.1.3/j2ee/oacore/
OC4J Log Files (ODL)	$INST_TOP/logs/10.1.3/j2ee/oacore/log/oacore_default_group_1/oc4j

Log files related to cloning in R12:

Patching related log files in R12

Autoconfig related log files in R12

Other log files in R12

Alert Log Files:
-- $ORACLE_HOME/admin/$CONTEXT_NAME/bdump/alert_$SID.log

Network Logs:
-- $ORACLE_HOME/network/admin/$SID.log

OUI Logs
OUI Inventory Logs :
-- $ORACLE_HOME/admin/oui/$CONTEXT_NAME/oraInventory/logs

Application Tier

R12 - Login Issue after any maintenance activity

Sometime if you try to access home page after any maintenance activity, it displays a blank page. This may occure due to jsp/class corruptions in $COMMON_TOP/_pages.

how do we come to know that there is jsp/class corruption ?
Enable and Collect Debug for HTTP, OC4J and OPMN in R12 ,
we may see in:
$LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1/application.log
(for various class files:

Missing class: _OA
Missing class: _AppsLocalLogin
Missing class: _OAInfo
Missing class: _jsp._fnd._aoljtest
)

Solution
========
To implement the solution, please execute the following steps:

Locking Improvements for Index Rebuild :

Red for session 1

Green for session 2

Blue for session 3

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

*** 10g

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

***

********************
*** 11g
********************

SQL> sho user
USER is "SH"
SQL> desc spm_test_table_1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
PROD_ID                                   NOT NULL NUMBER
CUST_ID                                   NOT NULL NUMBER
TIME_ID                                   NOT NULL DATE
CHANNEL_ID                                NOT NULL NUMBER
PROMO_ID                                  NOT NULL NUMBER
QUANTITY_SOLD                             NOT NULL NUMBER(10,2)
AMOUNT_SOLD                               NOT NULL NUMBER(10,2)

SQL> desc spm_test_table_2
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
CHANNEL_ID                                NOT NULL NUMBER
CUST_ID                                   NOT NULL NUMBER
PROD_ID                                   NOT NULL NUMBER
PROMO_ID                                  NOT NULL NUMBER
TIME_ID                                   NOT NULL DATE
UNIT_COST                                 NOT NULL NUMBER(10,2)
UNIT_PRICE                                NOT NULL NUMBER(10,2)
AMOUNT_SOLD                               NOT NULL NUMBER(10,2)
QUANTITY_SOLD                             NOT NULL NUMBER(10,2)
TOTAL_COST                                         NUMBER

SQL> select count(1) from spm_test_table_1;

COUNT(1)
----------
1

SQL> select count(1) from spm_test_table_2;

COUNT(1)
----------
1

 we are using two tables for this demo

SQL> create index spm_index_table1 on spm_test_table_1(cust_id);

Index created.

SQL> exec dbms_stats.gather_table_stats('SH', 'SPM_TEST_TABLE_1', NULL, dbms_stats.auto_sample_size);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SH', 'SPM_TEST_TABLE_2', NULL, dbms_stats.auto_sample_size);

PL/SQL procedure successfully completed.

SQL> explain plan for select count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1633133659

----------------------------------------------------------------------------------------
| Id  | Operation           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                  |     1 |     8 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |                  |     1 |     8 |            |          |
|   2 |   NESTED LOOPS |                  |     1 |     8 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| SPM_TEST_TABLE_2 |     1 |     4 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN | SPM_INDEX_TABLE1 |     1 |     4 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("T1"."CUST_ID"="T2"."CUST_ID")

16 rows selected.

SQL> sho parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     TRUE

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%SPM%spm_test_table%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';

no rows selected

 -- currently there is no baseline plan for this statement.

SQL> select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

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

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%SPM%spm_test_table%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';

no rows selected

-- Still no baseline for this

SQL> select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

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

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%SPM%spm_test_table%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT
------------------------------ ------------------------------ --- --- --- ---
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO  YES

 -- We now have the base line plan which was captured automatically.

We will now populate the two test tables with some huge data.

SQL> select count(1) from spm_test_table_1;

COUNT(1)
----------
918844

SQL> select count(1) from spm_test_table_2;

COUNT(1)
----------
916040

SQL> exec dbms_stats.gather_table_stats('SH', 'SPM_TEST_TABLE_1', NULL, dbms_stats.auto_sample_size);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SH', 'SPM_TEST_TABLE_2', NULL, dbms_stats.auto_sample_size);

PL/SQL procedure successfully completed.

SQL> explain plan for select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1633133659

----------------------------------------------------------------------------------------
| Id  | Operation           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                  |     1 |    10 |  1834K  (1)| 06:06:59 |
|   1 |  SORT AGGREGATE     |                  |     1 |    10 |            |          |
|   2 |   NESTED LOOPS |                  |   119M|  1137M|  1834K  (1)| 06:06:59 |
|   3 |    TABLE ACCESS FULL| SPM_TEST_TABLE_2 |   916K|  4472K|  1644   (1)| 00:00:20 |
|*  4 |    INDEX RANGE SCAN | SPM_INDEX_TABLE1 |   130 |   650 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("T1"."CUST_ID"="T2"."CUST_ID")

Note
-----
- SQL plan baseline "SQL_PLAN_53fxyhv7tzwqxbbf661e7" used for this statement

20 rows selected.

 -- We are still using the same baseline plan.

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%SPM%spm_test_table%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT
------------------------------ ------------------------------ --- --- --- ---
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO  YES

SQL> select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

COUNT(*)
----------
172868759

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%SPM%spm_test_table%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT
------------------------------ ------------------------------ --- --- --- ---
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqx91ccd494 YES NO NO  YES
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO  YES

 --- The new plan is captured but not inserted into the plan baseline.

We will now simulate the automatic SQL tuning by executing the task manually.

SQL> DECLARE
my_task_name varchar2(30);
sql_txt clob;
BEGIN
sql_txt := 'select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => sql_txt,
user_name => 'SH',
scope => 'COMPREHENSIVE',
time_limit => 300,
task_name => 'Task_askm',
description => 'Tune SPM Query Task');
END;
/

PL/SQL procedure successfully completed.

SQL> exec dbms_sqltune.execute_tuning_task('Task_askm');

PL/SQL procedure successfully completed.

SQL> select dbms_sqltune.report_tuning_task('Task_askm') FROM dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : Task_askm
Tuning Task Owner  : SH
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 300
Completion Status  : COMPLETED
Started at         : 10/02/2009 09:07:14
Completed at       : 10/02/2009 09:08:06

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SH
SQL ID     : cvfq6n3ngyzzy
SQL Text   : select /* SPM_AUTO */ count(*) from spm_test_table_1
t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
--------------------------------------------------------
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 99.77%)
------------------------------------------
- Consider accepting the recommended SQL profile. The SQL plan baseline
corresponding to the plan with the SQL profile will also be updated to an
accepted plan.
execute dbms_sqltune.accept_sql_profile(task_name => 'Task_askm',
task_owner => 'SH', replace => TRUE);

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 1633133659

--------------------------------------------------------------------------------
--------
| Id  | Operation           | Name             | Rows  | Bytes | Cost (%CPU)| Ti

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
me     |
--------------------------------------------------------------------------------
--------
|   0 | SELECT STATEMENT    |                  |     1 |    10 |  1834K  (1)| 06
:06:59 |
|   1 |  SORT AGGREGATE     |                  |     1 |    10 |            |
|
|   2 |   NESTED LOOPS |                  |   119M|  1137M|  1834K  (1)| 06
:06:59 |
|   3 |    TABLE ACCESS FULL| SPM_TEST_TABLE_2 |   916K|  4472K|  1644   (1)| 00
:00:20 |

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
|*  4 |    INDEX RANGE SCAN | SPM_INDEX_TABLE1 |   130 |   650 |     2   (0)| 00
:00:01 |
--------------------------------------------------------------------------------
--------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("T1"."CUST_ID"="T2"."CUST_ID")

2- Using SQL Profile

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
--------------------
Plan hash value: 3821076920

--------------------------------------------------------------------------------
-------------------
| Id  | Operation              | Name             | Rows  | Bytes |TempSpc| Cost
(%CPU)| Time     |
--------------------------------------------------------------------------------
-------------------
|   0 | SELECT STATEMENT       |                  |     1 |    10 |       |  421
9  (10)| 00:00:51 |

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE        |                  |     1 |    10 |       |
|          |
|*  2 |   HASH JOIN |                  |   119M|  1137M|    14M|  421
9  (10)| 00:00:51 |
|   3 |    TABLE ACCESS FULL   | SPM_TEST_TABLE_2 |   916K|  4472K|       |  164
4   (1)| 00:00:20 |
|   4 |    INDEX FAST FULL SCAN| SPM_INDEX_TABLE1 |   918K|  4486K|       |   72
3   (1)| 00:00:09 |
--------------------------------------------------------------------------------
-------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ASKM')
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T1"."CUST_ID"="T2"."CUST_ID")

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

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%SPM%spm_test_table%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT
------------------------------ ------------------------------ --- --- --- ---
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqx91ccd494 YES NO NO  YES
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO  YES

 -- We now need to accept the recommendation before the new plan is integrated into the plan baseline. This would be done automatically by automatic SQL Tuning.

SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'Task_askm',task_owner => 'SH', replace => TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%SPM%spm_test_table%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT
------------------------------ ------------------------------ --- --- --- ---
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqx91ccd494 YES YES NO  YES
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO  YES

SQL> explain plan for select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'basic +note'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3821076920

---------------------------------------------------
| Id  | Operation              | Name             |
---------------------------------------------------
|   0 | SELECT STATEMENT       |                  |
|   1 |  SORT AGGREGATE        |                  |
|   2 |   HASH JOIN |                  |
|   3 |    TABLE ACCESS FULL   | SPM_TEST_TABLE_2 |
|   4 |    INDEX FAST FULL SCAN| SPM_INDEX_TABLE1 |
---------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
- SQL profile "SYS_SQLPROF_0124159dddfa0000" used for this statement
- SQL plan baseline "SQL_PLAN_53fxyhv7tzwqx91ccd494" used for this statement

16 rows selected.

SQL>  var my_var number
SQL> exec :my_var := dbms_spm.alter_sql_plan_baseline -
(sql_handle => 'SYS_SQL_51bbbe86cf9ff2dd', -
plan_name => ' SQL_PLAN_53fxyhv7tzwqx91ccd494', -
attribute_name => 'ACCEPTED', attribute_value => 'NO');

PL/SQL procedure successfully completed.

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%SPM%spm_test_table%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT
------------------------------ ------------------------------ --- --- --- ---
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqx91ccd494 YES NO NO  YES
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO  YES

SQL> explain plan for select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1633133659

----------------------------------------------------------------------------------------
| Id  | Operation           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                  |     1 |    10 |  1834K  (1)| 06:06:59 |
|   1 |  SORT AGGREGATE     |                  |     1 |    10 |            |          |
|   2 |   NESTED LOOPS |                  |   119M|  1137M|  1834K  (1)| 06:06:59 |
|   3 |    TABLE ACCESS FULL| SPM_TEST_TABLE_2 |   916K|  4472K|  1644   (1)| 00:00:20 |
|*  4 |    INDEX RANGE SCAN | SPM_INDEX_TABLE1 |   130 |   650 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("T1"."CUST_ID"="T2"."CUST_ID")

Note
-----
- SQL plan baseline "SQL_PLAN_53fxyhv7tzwqxbbf661e7" used for this statement

20 rows selected.

SQL>  var my_var number
SQL> exec :my_var := dbms_spm.alter_sql_plan_baseline -
(sql_handle => 'SYS_SQL_51bbbe86cf9ff2dd', -
plan_name => ' SQL_PLAN_53fxyhv7tzwqx91ccd494', -
attribute_name => 'ACCEPTED', attribute_value => 'YES');

PL/SQL procedure successfully completed.

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%SPM%spm_test_table%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT
------------------------------ ------------------------------ --- --- --- ---
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqx91ccd494 YES YES NO  YES
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO  YES

SQL> explain plan for select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'basic +note'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3821076920

---------------------------------------------------
| Id  | Operation              | Name             |
---------------------------------------------------
|   0 | SELECT STATEMENT       |                  |
|   1 |  SORT AGGREGATE        |                  |
|   2 |   HASH JOIN |                  |
|   3 |    TABLE ACCESS FULL   | SPM_TEST_TABLE_2 |
|   4 |    INDEX FAST FULL SCAN| SPM_INDEX_TABLE1 |
---------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
- SQL profile "SYS_SQLPROF_0124159dddfa0000" used for this statement
- SQL plan baseline "SQL_PLAN_53fxyhv7tzwqx91ccd494" used for this statement

16 rows selected.

SQL>

SQL> sho user
USER is "SYS"
SQL> select name,user from v$database;

NAME      USER
--------- ------------------------------
ASKM      SYS

SQL> CREATE TABLESPACE fda_ts DATAFILE '/u01/app/oracle/oradata/askm/fda_ts01.dbf' SIZE 24M;

Tablespace created.

SQL> GRANT FLASHBACK ARCHIVE ADMINISTER TO sh;

Grant succeeded.

SQL> GRANT FLASHBACK ANY TABLE TO sh;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_FLASHBACK TO sh;

Grant succeeded.

SQL> conn sh/sh
Connected.
SQL> CREATE FLASHBACK ARCHIVE fda TABLESPACE fda_ts     QUOTA 1M RETENTION 7 DAY;

Flashback archive created.

SQL> create table rate_conv (currency varchar2(6),Rate number(15,4));

Table created.

SQL> ALTER TABLE sh.rate_conv FLASHBACK ARCHIVE  fda;

Table altered.

SQL> insert into rate_conv values ('IND-Rs',48.1012);

1 row created.

SQL> commit;

Commit complete.

SQL> update rate_conv set rate=49.0120;

1 row updated.

SQL> commit;

Commit complete.

SQL> update rate_conv set rate=48.2012;

1 row updated.

SQL> commit;

Commit complete.

SQL> delete rate_conv;

1 row deleted.

SQL> commit;

Commit complete.

SQL> insert into rate_conv values ('IND-Rs',50.0021);

1 row created.

SQL> commit;

Commit complete.

SQL> update rate_conv set rate=49.0120;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from rate_conv;

CURREN       RATE
------ ----------
IND-Rs     49.012

SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,rate from rate_conv versions between timestamp minvalue and maxvalue order by versions_starttime;

VERSIONS_STARTTIME        VERSIONS_ENDTIME          VERSIONS_XID     V       RATE
------------------------- ------------------------- ---------------- - ----------
01-OCT-09 08.56.25 AM     01-OCT-09 08.56.28 AM     0A0020009B020000 I    48.1012
01-OCT-09 08.56.28 AM     01-OCT-09 08.56.31 AM     060011002A030000 U     49.012
01-OCT-09 08.56.31 AM     01-OCT-09 08.56.40 AM     03000F003A030000 U    48.2012
01-OCT-09 08.56.40 AM                               050005006D030000 D    48.2012
01-OCT-09 08.56.44 AM     01-OCT-09 08.56.48 AM     0900090027030000 I    50.0021
01-OCT-09 08.56.48 AM                               0200010018030000 U     49.012

6 rows selected.

SQL> desc dba_FLASHBACK_ARCHIVE_TABLES;
Name                                                                                Null?    Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
TABLE_NAME                                                                          NOT NULL VARCHAR2(30)
OWNER_NAME                                                                          NOT NULL VARCHAR2(30)
FLASHBACK_ARCHIVE_NAME                                                              NOT NULL VARCHAR2(255)
ARCHIVE_TABLE_NAME                                                                           VARCHAR2(53)
STATUS                                                                                       VARCHAR2(8)

SQL> select TABLE_NAME, OWNER_NAME, FLASHBACK_ARCHIVE_NAME, ARCHIVE_TABLE_NAME from dba_FLASHBACK_ARCHIVE_TABLES;

TABLE_NAME                     OWNER_NAME
------------------------------ ------------------------------
FLASHBACK_ARCHIVE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
ARCHIVE_TABLE_NAME
-----------------------------------------------------
RATE_CONV                      SH
FDA
SYS_FBA_HIST_74714

SQL> select TABLE_NAME, OWNER_NAME, FLASHBACK_ARCHIVE_NAME, ARCHIVE_TABLE_NAME from dba_FLASHBACK_ARCHIVE_TABLES;

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME    ARCHIVE_TABLE_NAME
---------- ---------- ------------------------- --------------------
RATE_CONV  SH         FDA                       SYS_FBA_HIST_74714

SQL> desc SYS_FBA_HIST_74714
Name                                                  Null?    Type
----------------------------------------------------- -------- ------------------------------------
RID                                                            VARCHAR2(4000)
STARTSCN                                                       NUMBER
ENDSCN                                                         NUMBER
XID                                                            RAW(8)
OPERATION                                                      VARCHAR2(1)
CURRENCY                                                       VARCHAR2(6)
RATE                                                           NUMBER(15,4)

SQL> select count(1) from SYS_FBA_HIST_74714;

COUNT(1)
----------
4

SQL> desc rate_conv
Name                                                  Null?    Type
----------------------------------------------------- -------- ------------------------------------
CURRENCY                                                       VARCHAR2(6)
RATE                                                           NUMBER(15,4)

SQL> select * from SYS_FBA_HIST_74714;

RID                   STARTSCN    ENDSCN XID                       O CURREN       RATE
-------------------- --------- --------- ------------------------- - ------ ----------
AAASPaAAEAABhUvAAA     1234564   1234570 0A0020009B020000          I IND-Rs    48.1012
AAASPaAAEAABhUvAAA     1234570   1234573 060011002A030000          U IND-Rs     49.012
AAASPaAAEAABhUvAAA     1234573   1234577 03000F003A030000          U IND-Rs    48.2012
AAASPaAAEAABhUvAAB     1234588   1234591 0900090027030000          I IND-Rs    50.0021

SQL> select * from rate_conv;

CURREN       RATE
------ ----------
IND-Rs     49.012

SQL> conn /as sysdba
Connected.
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/u01/app/oracle/oradata/askm/undotbs2_01.dbf' SIZE 2M;

Tablespace created.

SQL> ALTER SYSTEM CHECKPOINT;

System altered.

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = 'UNDOTBS2' SCOPE=BOTH;

System altered.

SQL> conn sh/sh
Connected.
SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,rate from rate_conv versions between timestamp minvalue and maxvalue order by versions_starttime;

VERSIONS_STARTTIME                  VERSIONS_ENDTIME                    VERSIONS_XID     V       RATE
----------------------------------- ----------------------------------- ---------------- - ----------
01-OCT-09 08.56.25.000000000 AM     01-OCT-09 08.56.28.000000000 AM     0A0020009B020000 I    48.1012
01-OCT-09 08.56.28.000000000 AM     01-OCT-09 08.56.31.000000000 AM     060011002A030000 U     49.012
01-OCT-09 08.56.31.000000000 AM     01-OCT-09 08.56.40.000000000 AM     03000F003A030000 U    48.2012
01-OCT-09 08.56.44.000000000 AM     01-OCT-09 08.56.48.000000000 AM     0900090027030000 I    50.0021
01-OCT-09 08.56.48.000000000 AM                                         0200010018030000 U     49.012

SQL> select rate from rate_conv as of timestamp to_timestamp('2009-10-01 08:56:29','yyyy-mm-dd hh24:mi:ss');

RATE
----------
48.1012

SQL> explain plan for select rate from rate_conv as of timestamp to_timestamp('2009-10-01 08:56:29','yyyy-mm-dd hh24:mi:ss');

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3276248349

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                    |     5 |    65 |     9  (12)| 00:00:01 |       |       |
|   1 |  VIEW                     |                    |     5 |    65 |     9  (12)| 00:00:01 |       |       |
|   2 |   UNION-ALL               |                    |       |       |            |          |       |       |
|*  3 |    FILTER                 |                    |       |       |            |          |       |       |
|   4 |     PARTITION RANGE SINGLE|                    |     1 |    39 |     3   (0)| 00:00:01 |   KEY |     1 |
|*  5 |      TABLE ACCESS FULL    | SYS_FBA_HIST_74714 | 1 |    39 |     3   (0)| 00:00:01 |   KEY |     1 |
|*  6 |    FILTER                 |                    |       |       |            |          |       |       |
|*  7 |     HASH JOIN OUTER       |                    |     4 |  8212 |     6  (17)| 00:00:01 |       |       |
|*  8 |      TABLE ACCESS FULL    | RATE_CONV          |     4 |   100 |     2   (0)| 00:00:01 |       |       |
|*  9 |      TABLE ACCESS FULL    | SYS_FBA_TCRV_74714 |     2 |  4056 |     3   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("TIMESTAMP_TO_SCN"(TIMESTAMP' 2009-10-01 08:56:29.000000000')<1242042)
5 - filter("ENDSCN"<=1242042 AND "ENDSCN">"TIMESTAMP_TO_SCN"(TIMESTAMP' 2009-10-01
08:56:29.000000000') AND ("STARTSCN" IS NULL OR "STARTSCN"<="TIMESTAMP_TO_SCN"(TIMESTAMP' 2009-10-01
08:56:29.000000000')))
6 - filter("STARTSCN"<="TIMESTAMP_TO_SCN"(TIMESTAMP' 2009-10-01 08:56:29.000000000') OR "STARTSCN"
IS NULL)
7 - access("T".ROWID=CHARTOROWID("RID"(+)))
8 - filter("T"."VERSIONS_STARTSCN" IS NULL)
9 - filter(("ENDSCN"(+) IS NULL OR "ENDSCN"(+)>1242042) AND ("STARTSCN"(+) IS NULL OR
"STARTSCN"(+)<1242042))

Note
-----
- dynamic sampling used for this statement (level=2)

34 rows selected.

SQL>

SQL> alter table rate_conv NO FLASHBACK ARCHIVE;

Table altered.

SQL> conn /as sysdba
Connected.
SQL> sho user
USER is "SYS"
SQL> SELECT table_name FROM dict WHERE  table_name LIKE '%FLASHBACK_ARCHIVE%';

TABLE_NAME
------------------------------
DBA_FLASHBACK_ARCHIVE
DBA_FLASHBACK_ARCHIVE_TABLES
DBA_FLASHBACK_ARCHIVE_TS
USER_FLASHBACK_ARCHIVE
USER_FLASHBACK_ARCHIVE_TABLES

SQL> SELECT flashback_archive_name, create_time, status FROM   dba_flashback_archive;

FLASHBACK_ CREATE_TIME                                   STATUS
---------- --------------------------------------------- ----------
FDA        01-OCT-09 08.55.34.000000000 AM

SQL> SELECT * FROM   dba_flashback_archive_ts;

FLASHBACK_ FLASHBACK_ARCHIVE# TABLESPACE_NAME
---------- ------------------ ------------------------------
QUOTA_IN_MB
----------------------------------------
FDA                         1 FDA_TS
1

SQL> SELECT * FROM   dba_flashback_archive_tables;

TABLE_NAME                     OWNER_NAME                     FLASHBACK_
------------------------------ ------------------------------ ----------
ARCHIVE_TABLE_NAME                                    STATUS
----------------------------------------------------- ----------
RATE_CONV                      SH                             FDA
SYS_FBA_HIST_74714                                    ENABLED

SQL> ALTER FLASHBACK ARCHIVE fda MODIFY TABLESPACE fda_ts QUOTA 2M;

Flashback archive altered.

SQL> ALTER FLASHBACK ARCHIVE fda MODIFY RETENTION 90 DAY;

Flashback archive altered.

SQL> CREATE TABLESPACE fda_ts2 DATAFILE '/u01/app/oracle/oradata/askm/fda_ts02.dbf' SIZE 16M;

Tablespace created.

SQL> ALTER FLASHBACK ARCHIVE fda ADD TABLESPACE fda_ts2;

Flashback archive altered.

SQL> ALTER FLASHBACK ARCHIVE fda PURGE BEFORE TIMESTAMP(SYSTIMESTAMP - INTERVAL '1' DAY);

Flashback archive altered.

SQL> DROP FLASHBACK ARCHIVE fda;

Flashback archive dropped.

SQL> conn sh/sh
Connected

SQL> sho user
USER is "SH"
SQL> execute dbms_advisor.create_task ( 'SQL Access Advisor','SQL_ACC_TASK1','New SQLAccess  Task');

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.reset_task('SQL_ACC_TASK1');

PL/SQL procedure successfully completed.

SQL> create table temp_table AS SELECT * FROM SYS.WRI$_ADV_SQLW_STMTS WHERE NULL IS NOT NULL;

Table created.

SQL> create table sql_access_temp_table (c number, d varchar2(1000));

Table created.

SQL> begin
for i in 1..20000 loop
insert into sql_access_temp_table values(-i,'aoiejnflamnskdfjnsijndfklsjpoakneflkajsdfkjankdsnflkasjdnfkjasndjkfnjdklfsbkbsdnbaiuebdfiausdybfaouisdbflkjabdoiufbaklsdjfaksdiufakjsdofiuasdjfalkjsdfdjkhfakjshsjdfkjasksdfkjalkaksdjhfkjaakjdsfsdjfklasjdlkadfoiuaksmdnflkanmdlfknalkksdnflkansdflklksdnflkadsnflaknsdfoiweuroiamsdflkamsalksdmffalkklvmklakmdvlkamsdlkmvlksdmvlksdmvkllsdkfmasdfmalksdmfaksmdnflkanmdlfknalkksdnflkansdflklksdnflkadsnflaknsdfoiweuroiamsdflkamsalksdmffalkklvmklakmdvlkamsdlkmvlksdmvlksdmvkllsdkfmasdfmalksdmfaksmdnflkanmdlfknalkksdnflkansdflklk');
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.

SQL> create table customers_askm as select * from customers;

Table created.

SQL> @askm_sts.sql

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.add_sqlwkld_ref('SQL_ACC_TASK1','SQLSET_MY_SQLACCESS_WORKLOAD',1);

PL/SQL procedure successfully completed.

SQL> @askm_param.sql

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.execute_task('SQL_ACC_TASK1');

PL/SQL procedure successfully completed.

$ cat askm_sts.sql
DECLARE
sql_stmt   varchar2(2000);
sqlsetname  VARCHAR2(30);
sqlsetcur   dbms_sqltune.sqlset_cursor;
refid    NUMBER;
k NUMBER := 0;
num_queries NUMBER := 500;
BEGIN

sql_stmt := 'SELECT /* QueryASKM 2 */ ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND c.cust_state_province = ''CA'' AND ch.channel_desc in (''Internet'',''Catalog'') AND t.calendar_quarter_desc IN (''1999-01'',''1999-02'') GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc';

insert into temp_table values(1,1,NULL,0,'SH','Access Advisor','Workload',0,0,0,0,1,100,2,to_date('02-FEB-2007'),3,0,sql_stmt,1);

sql_stmt := 'SELECT /* QueryASKM 3 */ ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND c.cust_state_province = ''CA'' AND ch.channel_desc in (''Internet'',''Catalog'') AND t.calendar_quarter_desc IN (''1999-03'',''1999-04'') GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc';

insert into temp_table values(1,1,NULL,0,'SH','Access Advisor','Workload',0,0,0,0,1,100,2,to_date('02-FEB-2007'),3,0,sql_stmt,1);

sql_stmt := 'SELECT /* QueryASKM 4 */ c.country_id, c.cust_city, c.cust_last_name FROM sh.customers c WHERE c.country_id in (52790, 52798) ORDER BY c.country_id, c.cust_city, c.cust_last_name';

insert into temp_table values(1,1,NULL,0,'SH','Access Advisor','Workload',0,0,0,0,1,100,2,to_date('02-FEB-2007'),3,0,sql_stmt,1);

sql_stmt := 'select /* func_indx */ count(*) from sql_access_temp_table where abs(c)=5';

insert into temp_table values(1,1,NULL,0,'SH','Access Advisor','Workload',0,0,0,0,1,100,2,to_date('02-FEB-2007'),3,0,sql_stmt,1);

sql_stmt := 'SELECT /* QueryASKM 5 */ * FROM sh.customersjfv WHERE cust_state_province = ''CA''';

insert into temp_table values(1,1,NULL,0,'SH','Access Advisor','Workload',0,0,0,0,1,100,2,to_date('02-FEB-2007'),3,0,sql_stmt,1);

sqlsetname := 'SQLSET_MY_SQLACCESS_WORKLOAD';

dbms_sqltune.create_sqlset(sqlsetname, 'Generated STS');

OPEN sqlsetcur FOR
SELECT
SQLSET_ROW(null,null, sql_text, null, null, username, module,
action, elapsed_time, cpu_time, buffer_gets, disk_reads,
0,rows_processed, 0, executions, 0, optimizer_cost, null,
priority, command_type,
to_char(last_execution_date,'yyyy-mm-dd/hh24:mi:ss'),
0,0,NULL,0,NULL,NULL
)
FROM temp_table;

dbms_sqltune.load_sqlset(sqlsetname, sqlsetcur);
END;
/

$

$ cat askm_param.sql
/* Set STS Workload Parameters */
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','VALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','VALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','SQL_LIMIT','25');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','VALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','VALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','INVALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','INVALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','INVALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','INVALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','VALID_SQLSTRING_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','INVALID_SQLSTRING_LIST','"@!"');

/* Set Task Parameters */
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','ANALYSIS_SCOPE','ALL');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','RANKING_MEASURE','PRIORITY,OPTIMIZER_COST');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DEF_PARTITION_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','TIME_LIMIT',10000);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','MODE','LIMITED');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','STORAGE_CHANGE',DBMS_ADVISOR.ADVISOR_UNLIMITED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DML_VOLATILITY','TRUE');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','WORKLOAD_SCOPE','PARTIAL');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DEF_INDEX_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DEF_INDEX_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DEF_MVIEW_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DEF_MVIEW_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DEF_MVLOG_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','CREATION_COST','TRUE');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','JOURNALING','4');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DAYS_TO_EXPIRE','30');

$

SQL> SET LONG 100000
SQL> SET PAGESIZE 50000
SQL> SELECT DBMS_ADVISOR.get_task_script('SQL_ACC_TASK1') AS script FROM   dual;

SCRIPT
--------------------------------------------------------------------------------
Rem  SQL Access Advisor: Version 11.2.0.1.0 - Production
Rem
Rem  Username:        SH
Rem  Task:            SQL_ACC_TASK1
Rem  Execution date:
Rem

Rem
Rem  Repartitioning table "SH"."CUSTOMERS"
Rem

SET SERVEROUTPUT ON
SET ECHO ON

Rem
Rem Creating new partitioned table
Rem
CREATE TABLE "SH"."CUSTOMERS1"
(    "CUST_ID" NUMBER,
"CUST_FIRST_NAME" VARCHAR2(20),
"CUST_LAST_NAME" VARCHAR2(40),
"CUST_GENDER" CHAR(1),
"CUST_YEAR_OF_BIRTH" NUMBER(4,0),
"CUST_MARITAL_STATUS" VARCHAR2(20),
"CUST_STREET_ADDRESS" VARCHAR2(40),
"CUST_POSTAL_CODE" VARCHAR2(10),
"CUST_CITY" VARCHAR2(30),
"CUST_CITY_ID" NUMBER,
"CUST_STATE_PROVINCE" VARCHAR2(40),
"CUST_STATE_PROVINCE_ID" NUMBER,
"COUNTRY_ID" NUMBER,
"CUST_MAIN_PHONE_NUMBER" VARCHAR2(25),
"CUST_INCOME_LEVEL" VARCHAR2(30),
"CUST_CREDIT_LIMIT" NUMBER,
"CUST_EMAIL" VARCHAR2(30),
"CUST_TOTAL" VARCHAR2(14),
"CUST_TOTAL_ID" NUMBER,
"CUST_SRC_ID" NUMBER,
"CUST_EFF_FROM" DATE,
"CUST_EFF_TO" DATE,
"CUST_VALID" VARCHAR2(1)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
TABLESPACE "EXAMPLE"
PARTITION BY RANGE ("CUST_ID") INTERVAL( 3000) ( PARTITION VALUES LESS THAN (3000));

Rem
Rem Copying comments to new partitioned table
Rem
COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_ID" IS 'primary key';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_FIRST_NAME" IS 'first name of the customer';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_LAST_NAME" IS 'last name of the customer';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_GENDER" IS 'gender; low cardinality attribute';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_YEAR_OF_BIRTH" IS 'customer year of birth';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_MARITAL_STATUS" IS 'customer maritalstatus; low cardinality attribute';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_STREET_ADDRESS" IS 'customer street address';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_POSTAL_CODE" IS 'postal code of the customer';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_CITY" IS 'city where the customer lives';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_STATE_PROVINCE" IS 'customer geography: state or province';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."COUNTRY_ID" IS 'foreign key to the countries table (snowflake)';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_MAIN_PHONE_NUMBER" IS 'customer mainphone number';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_INCOME_LEVEL" IS 'customer income level';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_CREDIT_LIMIT" IS 'customer credit limit';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_EMAIL" IS 'customer email id';

COMMENT ON TABLE "SH"."CUSTOMERS1"  IS 'dimension table';

Rem
Rem Copying constraints to new partitioned table
Rem
ALTER TABLE "SH"."CUSTOMERS1" ADD CONSTRAINT "CUSTOMERS_PK1" PRIMARY KEY ("CUST_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS  TABLESPACE "EXAMPLE"  ENABLE NOVALIDATE;

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_ID" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_FIRST_NAME" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_LAST_NAME" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_GENDER" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_YEAR_OF_BIRTH" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_STREET_ADDRESS" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_POSTAL_CODE" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_CITY" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_CITY_ID" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_STATE_PROVINCE" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_STATE_PROVINCE_ID" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("COUNTRY_ID" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_MAIN_PHONE_NUMBER" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_TOTAL" NOT NULL ENABLE);

ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_TOTAL_ID" NOT NULL ENABLE);

Rem
Rem Copying referential constraints to new partitioned table
Rem
ALTER TABLE "SH"."CUSTOMERS1" ADD CONSTRAINT "CUSTOMERS_COUNTRY_FK1" FOREIGN KEY ("COUNTRY_ID")
REFERENCES "SH"."COUNTRIES" ("COUNTRY_ID") ENABLE NOVALIDATE;

Rem
Rem Copying indexes to new partitioned table
Rem
CREATE UNIQUE INDEX "SH"."CUSTOMERS_PK1" ON "SH"."CUSTOMERS1" ("CUST_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" ;

CREATE BITMAP INDEX "SH"."CUSTOMERS_GENDER_BIX1" ON "SH"."CUSTOMERS1" ("CUST_GENDER")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" LOCAL;

CREATE BITMAP INDEX "SH"."CUSTOMERS_MARITAL_BIX1" ON "SH"."CUSTOMERS1" ("CUST_MARITAL_STATUS")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" LOCAL;

CREATE BITMAP INDEX "SH"."CUSTOMERS_YOB_BIX1" ON "SH"."CUSTOMERS1" ("CUST_YEAR_OF_BIRTH")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE "EXAMPLE" LOCAL;

Rem
Rem Copying object grants to new partitioned table
Rem
GRANT SELECT ON "SH"."CUSTOMERS1" TO "BI";

Rem
Rem Populating new partitioned table with data from original table
Rem
INSERT /*+ APPEND */ INTO "SH"."CUSTOMERS1"
SELECT * FROM "SH"."CUSTOMERS";
COMMIT;

begin
dbms_stats.gather_table_stats('"SH"', '"CUSTOMERS1"', NULL, dbms_stats.auto_sample_size);
end;
/

Rem
Rem Renaming tables to give new partitioned table the original table name
Rem
ALTER TABLE "SH"."CUSTOMERS" RENAME TO "CUSTOMERS11";
ALTER TABLE "SH"."CUSTOMERS1" RENAME TO "CUSTOMERS";

Rem
Rem Revalidating dimensions for use with new partitioned table
Rem
ALTER DIMENSION "SH"."CUSTOMERS_DIM" COMPILE;

CREATE MATERIALIZED VIEW LOG ON
"SH"."CUSTOMERS"
WITH ROWID, SEQUENCE("CUST_ID","CUST_CITY","CUST_STATE_PROVINCE")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
"SH"."CHANNELS"
WITH ROWID, SEQUENCE("CHANNEL_ID","CHANNEL_DESC","CHANNEL_CLASS")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
"SH"."TIMES"
WITH ROWID, SEQUENCE("TIME_ID","CALENDAR_QUARTER_DESC")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
"SH"."SALES"
WITH ROWID, SEQUENCE("CUST_ID","TIME_ID","CHANNEL_ID","AMOUNT_SOLD")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW "SH"."MV$$_004D0000"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH.CUSTOMERS.CUST_STATE_PROVINCE C1, SH.CUSTOMERS.CUST_CITY C2, SH
.CHANNELS.CHANNEL_CLASS
C3, SH.CHANNELS.CHANNEL_DESC C4, SH.TIMES.CALENDAR_QUARTER_DESC C5, SUM("
SH"."SALES"."AMOUNT_SOLD")
M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.CUSTOMERS,
SH.CHANNELS, SH.TIMES, SH.SALES WHERE SH.SALES.CHANNEL_ID = SH.CHANNELS.C
HANNEL_ID
AND SH.SALES.TIME_ID = SH.TIMES.TIME_ID AND SH.SALES.CUST_ID = SH.CUSTOME
RS.CUST_ID
AND (SH.TIMES.CALENDAR_QUARTER_DESC IN ('1999-04', '1999-03', '1999-02'
, '1999-01')) AND (SH.CHANNELS.CHANNEL_DESC IN ('Internet', 'Catalog'
)) AND (SH.CUSTOMERS.CUST_STATE_PROVINCE = 'CA') GROUP BY SH.CUSTOMERS.CU
ST_STATE_PROVINCE,
SH.CUSTOMERS.CUST_CITY, SH.CHANNELS.CHANNEL_CLASS, SH.CHANNELS.CHANNEL_DESC,
SH.TIMES.CALENDAR_QUARTER_DESC;

begin
dbms_stats.gather_table_stats('"SH"','"MV$$_004D0000"',NULL,dbms_stats.auto_sa
mple_size);
end;
/

CREATE MATERIALIZED VIEW "SH"."MV$$_004D0001"
REFRESH FORCE WITH ROWID
ENABLE QUERY REWRITE
AS SELECT "SH"."CUSTOMERS"."COUNTRY_ID" M1, "SH"."CUSTOMERS"."CUST_CITY" M2,
"SH"."CUSTOMERS"."CUST_LAST_NAME"
M3 FROM SH.CUSTOMERS WHERE (SH.CUSTOMERS.COUNTRY_ID IN (52798, 52790));

begin
dbms_stats.gather_table_stats('"SH"','"MV$$_004D0001"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE INDEX "SH"."CUSTOMERS_IDX$$_004D0000" ON "SH"."CUSTOMERS" ("COUNTRY_ID","CUST_CITY","CUST_LAST_NAME") COMPUTE STATISTICS;

SQL>