How to enable and disable the automatic SQL tuning advisor in Oracle 11g

To enable automatic SQL tuning, use the ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package:

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'sql tuning advisor',
    operation => NULL,
    window_name => NULL);
END;
/

To disable automatic SQL tuning, use the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'sql tuning advisor', 
    operation => NULL, 
    window_name => NULL);
END;
/

How to create an ACL

-- step 1: Create the Access Control List and its Privilege Definitions
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
  acl          => 'my_acl.xml',
  description  => 'Limit the use of package utl_http til webservice',
  principal    => 'SCOTT',
  is_grant     => TRUE,
  privilege    => 'connect');
END;
/

-- Step 2: Assign the Access Control List to One or More Network Hosts
-- After you create the access control list, then you are ready to assign it to one or more network host computers.
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
  acl         => 'my_acl.xml',
  host        => '*');
END;
/

In the code above, there is no restrictions on which ports to use. If desirable, use the lower_port and upper_port directives in the ASSIGN_ACL procedure. You can also assign the ACL to apply to one specific host, or group of hosts.

For example

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
  acl         => 'my_acl.xml',
  host        => 'appserver1.mycompany.com',
  lower_port => 80,
  upper_port => 3999);
END;
/

You can find information about the currently set up ACLs in the following views:

DBA_NETWORK_ACLS

DBA_NETWORK_ACL_PRIVILEGES

DBA_WALLET_ACLS

USER_NETWORK_ACL_PRIVILEGES

Sources: Oracle Documentation

http://docs.oracle.com/cd/E11882_01/network.112/e36292/authorization.htm#DBSEG121 - creating an ACL
http://docs.oracle.com/cd/E11882_01/network.112/e36292/authorization.htm#DBSEG99984 - finding information about ACL
http://docs.oracle.com/cd/E11882_01/network.112/e36292/authorization.htm#DBSEG106 - Specifying a Group of Network Host

How to fix error >>> DBMS_AW_EXP: BIN$DdyrY during export

During export using data pump, you see the following:

Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 7.968 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
>>> DBMS_AW_EXP: BIN$DdyrY/cyAMLgU5/YLVaAbg==$0 not AW$

Solution: empty the recyclebin:

purge dba_recyclebin;

and restart the export.

How to use SUBSTR and INSTR to find the path used for the files in a tablespace

SELECT SUBSTR(FILE_NAME, 1, 
               INSTR(FILE_NAME, '/', -1, 1)  -- Search for position of the first occurrence of the char '/', start at end of string
              -1) "PATH" -- starting from the rightmost side of the string, search from position found in INSTR above, and work your way to position 1
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'USERS'

If you want to limit the output the first file found for a tablespace, add

AND ROWNUM < 2;

See also this post for how to use this piece of code in a script using input variables.

Source: "SQL Certified Expert Exam Guide" by Steve O'Hearn, page 220

How to unzip to a specific folder in Unix-based operating systems

Use the -d switch and redirect the content of the zip file to the desired directory

unzip myzipfile.zip -d myfolder

How to create a stored procedure for partition maintenance, executed by the Oracle Scheduler.

I needed to setup a small job that would remove partitions from a table with records older than 35 days.
The procedure should be flexible enough to accept different tables in different schemas, and there should be some basic logging, too.

Below is how I solved it.


First, grant permission, setup the logging table and add a sequence:

connect / as sysdba
grant select on dba_tab_partitions to scott;

connect scott/tiger

whenever sqlerror continue
drop sequence part_maint_seq;

create sequence part_maint_seq
increment by 1
start with 1
nomaxvalue
nominvalue
nocycle;

drop table partition_maintenance;

create table partition_maintenance(
    opid           number not null,
    table_owner    varchar2(8),
    table_name     varchar2(30),
    partition_name varchar2(30),
    operation      varchar2(10),
    success        char(1),
    exe_date       date,
    exec_by        varchar2(30) default 'automatic',
    errormsg       varchar2(512)
);

alter table partition_maintenance
add constraint part_maint_pk primary key (opid);

Then, create the stored procedure that will do the job. It requires two parameters to be passed to it: table owner and table name. Note that the procedure will need to be called with invoker's right, since it is accessing DBA_TAB_PARTITION:

connect scott/tiger

set serveroutput on

CREATE OR REPLACE PROCEDURE DROP_PARTITIONS ( towner IN VARCHAR2, tname IN VARCHAR2) AUTHID CURRENT_USER AS

  PNAME DBA_TAB_PARTITIONS.PARTITION_NAME%TYPE;
  L_DATA LONG;
  V_HVD DATE;
  V_PURGE_DATE DATE;
  SQLTORUN VARCHAR2(200);

  CURSOR PART_NAME
  IS
  SELECT PARTITION_NAME
    FROM DBA_TAB_PARTITIONS
    WHERE TABLE_NAME =  UPPER(tname)
    AND   TABLE_OWNER = UPPER(towner)
    AND   INTERVAL='YES'
    ORDER BY partition_position;

BEGIN

    OPEN PART_NAME;
    FETCH PART_NAME INTO PNAME;
    IF PART_NAME%ROWCOUNT = 0 THEN
        dbms_output.put_line('no such table as ' || UPPER(towner) ||'.'||UPPER(tname) );
            INSERT INTO PARTITION_MAINTENANCE (OPID, TABLE_OWNER,TABLE_NAME,PARTITION_NAME, OPERATION, SUCCESS, EXE_DATE, ERRORMSG)
                VALUES (PART_MAINT_SEQ.NEXTVAL,UPPER(towner),UPPER(tname),PNAME ,'DROP','N', SYSDATE, 'No table such as '|| towner || '.' || tname );
                COMMIT;
    END IF;
    WHILE PART_NAME%FOUND
        LOOP
            --DBMS_OUTPUT.PUT_LINE( pname );
            SELECT HIGH_VALUE
            INTO L_DATA
            FROM DBA_TAB_PARTITIONS
            WHERE TABLE_OWNER= UPPER(towner)
            AND   TABLE_NAME = UPPER(tname)
            AND PARTITION_NAME = PNAME;

            --dbms_output.put_line('partition name: ' || pname || ' ' ||  trim (substr ( l_data, 12, 10 )  ) );
             V_HVD := TO_DATE(TRIM (SUBSTR ( L_DATA, 12, 10 ))  ,'YYYY-MM-DD');
            -- dbms_output.put_line('high value date: ' || v_hvd);
           V_PURGE_DATE := TRUNC(SYSDATE)-35;
            --dbms_output.put_line('purge date: ' || v_purge_date);
            IF V_HVD < V_PURGE_DATE THEN
               DBMS_OUTPUT.PUT_LINE('partition ' || PNAME ||' too old, high value date for partition is: ' || V_HVD);
               -- drop statement goes here
               SQLTORUN := 'alter table ' || towner || '.' || tname || ' drop partition ' || PNAME || ' UPDATE INDEXES';
               DBMS_OUTPUT.PUT_LINE(SQLTORUN);
               EXECUTE IMMEDIATE SQLTORUN;
               -- logging to a separate table goes here
               INSERT INTO PARTITION_MAINTENANCE (OPID, TABLE_OWNER,TABLE_NAME,PARTITION_NAME, OPERATION, SUCCESS, EXE_DATE, ERRORMSG)
               VALUES (PART_MAINT_SEQ.NEXTVAL,UPPER(towner),UPPER(tname),PNAME ,'DROP','Y', SYSDATE, 'Partition ' || pname || ' of table ' || towner || '.' || tname || ' was dropped.');
               COMMIT;
            END IF;
            FETCH PART_NAME INTO PNAME;
        END LOOP;

    CLOSE PART_NAME;

END;
/

Finally, create a program of type "STORED_PROCEDURE", define its arguments, and create a job that executes the program on a regular basis:

whenever sqlerror continue
set serveroutput on

BEGIN
DBMS_SCHEDULER.DROP_JOB(
    job_name        => 'DROP_PARTITION_5WEEK'
  );
  DBMS_SCHEDULER.DROP_PROGRAM(
     program_name       => 'DROP_PARTITION_PROG'
  );
  DBMS_SCHEDULER.create_program (
    program_name        => 'DROP_PARTITION_PROG',
    program_type        => 'STORED_PROCEDURE',
    program_action      => 'DROP_PARTITIONS',
    number_of_arguments => 2,
    enabled             => FALSE,
    comments            => 'Program to drop partitions with contents older than 35 days..');

  DBMS_SCHEDULER.define_program_argument (
    program_name      => 'DROP_PARTITION_PROG',
    argument_name     => 'towner',
    argument_position => 1,
    argument_type     => 'VARCHAR2',
    default_value     => 'SCOTT');

  DBMS_SCHEDULER.define_program_argument (
    program_name      => 'DROP_PARTITION_PROG',
    argument_name     => 'tname',
    argument_position => 2,
    argument_type     => 'VARCHAR2',
    default_value     => 'SALES');

  DBMS_SCHEDULER.enable( name => 'DROP_PARTITION_PROG');

  DBMS_SCHEDULER.CREATE_JOB
    (
    job_name        => 'DROP_PARTITION_5WEEK',
    program_name    => 'DROP_PARTITION_PROG',
    start_date      => TO_TIMESTAMP_TZ('2015/05/26 21:45:00.000000 +02:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),
    repeat_interval => 'FREQ=WEEKLY;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job will execute the program drop_partitions.');
END;
/

Some common errors seen when configuring TSM

ANU2534E Option file error.

The above error can occur when you run the command

tdpoconf showenv

as a non-root user (typically the user owning the oracle database software):

tdpoconf showenv

IBM Tivoli Storage Manager for Databases:
Data Protection for Oracle

Version 6, Release 3, Level 0.0
(C) Copyright IBM Corporation 1997, 2011. All rights reserved.

ANU2534E Option file error.

Solution: Change permissions on two of the configuration files:

chmod 644 /usr/tivoli/tsm/client/oracle/bin64/tdpo.opt
chmod 644 /usr/tivoli/tsm/client/api/bin64/dsm.sys

ANS1217E (RC409) Server name not found in System Options File.

The following error typically occurs during a redirected restore, which is often needed during duplication of Oracle databases:

ANS1217E (RC409)  Server name not found in System Options File

The above indicates that the attribute "TCPServeraddress" found in the file /usr/tivoli/tsm/client/api/bin64/dsm.sys must be changed so that it identical to the source server.

ANS1087E (RC106) Access to the specified file or directory is denied.

This error could occur when you run the command tdpoconf shownev as a non-root user:

ANS1087E (RC106)  Access to the specified file or directory is denied

The above error is resolved by setting correct permissions on the /var/adm/log folder:

su -
cd /var/adm/
chmod 755 log
cd log
chmod 777 tdpoerror.log dsmerror.log

ANS1035S (RC406) Options file '*' could not be found, or it cannot be read.

This error is also triggered when you access tdpo as a non-root user, for example, by executing

tdpoconf showenv

as a non-root user:

ANS1035S (RC406)  Options file '*' could not be found, or it cannot be read.

To solve the problem, grant the right permissions on the files:

su -
chmod 644 /usr/tivoli/tsm/client/api/bin64/dsm.opt
chmod 644 /usr/tivoli/tsm/client/api/bin64/dsm.sys

ANS1025E (RC137) Session rejected Authentication failure.

The above error may occur when you are attempting to set a new password as a part of reconfiguring the server for a redirected restore:

ANS1025E (RC137) Session rejected: Authentication failure

When you run

tdpoconf passwd

you are prompted for password three times:

current password
new password
confirm new password

At this point during the configuration, the tsm server pointed to in the attribute "TCPServeraddress" in the configuration file /usr/Tivoli/tsm/client/api/bin64/dsm.sys, believes that it is communicating to the original backupclient. Therefore, it is important that you supply the original backupclient's password at all three prompts.

ANS0282E (RC168) Password file is not available.

When the above error message occurs, you simply need to execute

tdpoconf passwd

to reset the password. Supply the original backup client's password at all three prompts.