Specifying comments in SQL to support auditing in Oracle

Adding comments to an SQL statement in Oracle is a good way of providing context to auditing and sql forensic activities directed against an Oracle database audit log. The Oracle Audit trail, and related software like Oracle Audit Vault will capture any comments embedded within an sql statement as part of its normal activities. This, then, provides a way for applications built on Oracle to provide metadata that can assist in analytics performed on the audit record.

As an example, an online shopping cart may audit searches by keyword and may audit individual product pages. At the database level the sql run to generate such pages can be marked with a comment and a textual description of the functionality of the application that uses such a query. This permits application level auditing to be performed in the database, and not require that the auditor is educated in the full set of statements that may be performed by the application. This method of auditing can be done in addition to application level auditing functionality.

Comments are also useful in the enterprise manager to look up statements that are candidates for automated sql tuning.

To add comments to the statement use the following syntax -

SELECT /* [text] */ .....

When the statements require the use of hints ensure the comments do not get in the way of the correct hint definition. Ensure the comments are of the form


{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint [text]]... */

Finding Day, Month, Year from a date in Oracle SQL using EXTRACT

In Oracle, to get the year, month, day or time from a date its not necessary to parse the date string using the SUBSTR function. Simply use the EXTRACT function and pass in the relevant keyword to get the value as in the following example:

SELECT EXTRACT (year FROM sysdate) "Year" FROM DUAL;

Other keywords that can be passed in are YEAR, MONTH, DAY, HOUR., MINUTE, SECOND. There are also timezone keywords that can be used with timestamps.

The function is useful when used with GROUP BY. For Example:

GROUP BY EXTRACT(month FROM start_date)

Setting the value of a sequence in pl/sql in Oracle

In Oracle there is no direct way to set the value of a sequence once the sequence has been created. To set the sequence value to a specific value it is necessary to either drop and recreate the sequence, not forgetting all of the options specified in the create statement, or to increment the sequence by some positive or negative value to set the current value to the desired value.

To find out the current value of a sequence you can use the currval function on the sequence, but this will only return successfully if the sequence has already been initialized with a call to nextval. Another way to find the value for the sequence is to query the database as follows:

select sequence_name, last_number from user_sequences;

Below is sample pl/sql to set a sequence to the desired value, it defines two variables to store the current sequence and the desired sequence then alters the increment to the difference of those, increments the sequence then resets the sequence increment to the previous value. -


  procedure set_seq is
    l_max_row NUMBER(38,0) := 0;
    l_cur_seq NUMBER(38,0) := 0;
  begin

  --- Now change the sequence
   select max(id_col) into l_max_row from my_tab;
   select my_seq.nextval into l_cur_seq from dual;
   execute immediate 'alter sequence my_seq increment by ' || (l_max_row-l_cur_seq) || ' nocache';
   select my_seq.nextval into l_cur_seq from dual;
   execute immediate 'alter sequence my_seq increment by 1 nocache';
   select my_seq.nextval into l_cur_seq from dual;

Oracle PL/SQL Merge with sequence

In Oracle 11g it is not possible to use a sequence in the USING clause of a MERGE statement. To get around this limitation enter a dummy integer in the USING clause and specify the sequence in the VALUES clause.

Putting Sequence in the subquery following USING in a MERGE statement gives an error
-- Eg>

MERGE INTO mytab tab
USING (SELECT 1 as col1,  myseq.nextval seq from dual) q1
ON (tab.col1 = q1.col1)  -- The ON clause will take ANDs eg. ON (t1.c1 = t2.c1 AND t1.c2=t2.c2)
WHEN NOT MATCHED THEN
INSERT (
  col1
)
VALUES (
  myseq.nextval
);

Gives

Error report:
SQL Error: ORA-02287: sequence number not allowed here
02287. 00000 -  "sequence number not allowed here"
*Cause:    The specified sequence number (CURRVAL or NEXTVAL) is inappropriate
           here in the statement.
*Action:   Remove the sequence number.

Use a dummy value eg -999 instead

MERGE INTO mytab tab
USING (SELECT 1 as col1, -999 seq from dual) q1
ON (tab.col1 = q1.col1)
WHEN NOT MATCHED THEN
INSERT (
  col1
)
VALUES (
  myseq.nextval
);

Oracle 11G Shrinking Temp Tablespace

There are a few methods to shrink the temporary tablespace in Oracle 11G. In some cases you can directly resize or shrink the tablespace. If that is not possible then create a new temporary tablespace, reassign the default temp space and drop the old tablespace.


SELECT tablespace_name, file_name, bytes
FROM dba_temp_files WHERE tablespace_name like 'TEMP%';


alter database tempfile '/the/full/path/to/temp01.dbf' resize 256M;
alter database tempfile '/the/full/path/to/temp01.dbf' resize 256M

-- Can give -
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

-- If that fails, on 11G do -
alter tablespace TEMP shrink space keep 256M;

-- If that fails do

CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/datapath/temp2_01.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

CREATE TEMPORARY TABLESPACE temp
2  TEMPFILE '/datapath/temp01.dbf' SIZE 256M REUSE
3  AUTOEXTEND ON NEXT 128M MAXSIZE unlimited
4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

From http://stackoverflow.com/questions/1824572/how-to-shrink-temp-tablespace-in-oracle