How to create a startup-trigger

In this example, I needed to write a simple trigger that opens the wallet upon startup of the instance:

create or replace trigger
   open_wallet
after startup on database
begin
   execute immediate 'ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "mysecretpassword"';
end;
/

How to solve ORA-01031: insufficient privileges when creating a materialized view

At first, I got the following error from the Oracle server when exeuting the "CREATE MATERIALIZED VIEW" script:

where    tab2.year >   to_number(to_char(sysdate, 'YYYY')) - 4
                                       *
ERROR at line 36:
ORA-01031: insufficient privileges

Solution:
If you try to create a materialized view based on tables in a different schema, you need the privilege

GLOBAL QUERY REWRITE

as well as

CREATE TABLE
CREATE MATERIALIZED VIEW

Grant the privileges:

grant GLOBAL QUERY REWRITE to scott;
grant CREATE TABLE to scott;
grant CREATE MATERIALIZED VIEW to scott;

Verify your users privileges:

connect scott/tiger

select * from user_sys_privs;


USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
EKSTERN                        CREATE SESSION                           NO
EKSTERN                        CREATE TABLE                             NO
EKSTERN                        CREATE MATERIALIZED VIEW                 NO
EKSTERN                        GLOBAL QUERY REWRITE                     NO

This should fix your ORA-01031 problems.

Source: Oracle Support "Create Local Materialized View with Query Rewrite Option Fails with ORA-1031 Insufficient Privileges" (Doc ID 1079983.6)

How to redefine a table that is created with an identity column

You have the following non-partitioned table defined:

CREATE TABLE MYTABLE
(
  ENTRY_SEQ_NUM        NUMBER Generated as Identity ( START WITH 1180965539 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP) NOT NULL,
  ENTRY_ID             VARCHAR2(50 BYTE)            NULL,
  DOC_NAME             VARCHAR2(100 BYTE)           NULL,
  CREATED              TIMESTAMP(6)                 NULL,
  REVISION#            INTEGER                      NULL,
  APPLICABLE_YEAR      INTEGER                      NULL,
  PERIOD               VARCHAR2(10 BYTE)            NULL,
  DOCUMENT_STATUS      VARCHAR2(50 BYTE)            NULL,
  PHONE#               VARCHAR2(11 BYTE)            NULL
);

The table is growing, and your customer want it partitioned.

Solution:

Create an interim table. Make sure to use a datatype of NUMBER for the column ENTRY_SEQ_NUM, instead of the Identity column:

CREATE TABLE MYTABLE_INTERIM
(
  ENTRY_SEQ_NUM        NUMBER,
  ENTRY_ID             VARCHAR2(50 BYTE),
  DOC_NAME             VARCHAR2(100 BYTE)           NULL,
  CREATED              TIMESTAMP(6)                 NULL,
  REVISION#            INTEGER                      NULL,
  APPLICABLE_YEAR      INTEGER                      NULL,
  PERIOD               VARCHAR2(10 BYTE)            NULL,
  DOCUMENT_STATUS      VARCHAR2(50 BYTE)            NULL,
  PHONE#               VARCHAR2(11 BYTE)            NULL
)
PARTITION BY RANGE (PERIOD)
INTERVAL( NUMTOYMINTERVAL(1,'MONTH'))
(
  PARTITION P_INIT VALUES LESS THAN (TO_DATE('2013-01', 'YYYY-MM'))
)
TABLESPACE USERS
;

Start redefinition. Note that you need to use the TO_DATE function on the partition key column, together with proper masking of the date format string:

begin
DBMS_REDEFINITION.start_redef_table(uname=>'SCOTT',
orig_table=>'MYTABLE',
int_table=>'MYTABLE_INTERIM',
col_mapping =>'ENTRY_SEQ_NUM ENTRY_SEQ_NUM,ENTRY_ID ENTRY_ID,DOC_NAME DOC_NAME,CREATED CREATED,REVSION# REVSION#,APPLICABLE_YEAR APPLICABLE_YEAR,TO_DATE(PERIOD,''YYYY-MM'') PERIOD,DOCUMENT_STATUS DOCUMENT_STATUS,PHONE# PHONE#',
options_flag=>dbms_redefinition.cons_use_pk);
end;
/

Finish the redefinition:

begin
 DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>'SCOTT',orig_table=>'MYTABLE',int_table=>'MYTABLE_INTERIM);
end;
/

Add a new column, of type "Identity":

alter session force parallel ddl;
alter session force parallel dml;
alter table mytable
add ENTRY_SEQ_NUM_X NUMBER Generated as Identity ( START WITH 1
                                                   MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20
                                                   NOORDER NOKEEP);

Drop the old column:

alter table mytable drop column ENTRY_SEQ_NUM;

Rename the Identity column back to the name of the column you just dropped:

alter table mytable rename column ENTRY_SEQ_NUM_X to ENTRY_SEQ_NUM;

The syntax for enabling and disabling a constraint

To enable a constraint:

ALTER TABLE mytable ENABLE CONSTRAINT mytable_c1;

or, if you wish to enable the constraint and validate its correctness at the same time:

ALTER TABLE mytable MODIFY CONSTRAINT mytable_c1 ENABLE VALIDATE;

Note that the following syntax is invalid and will throw ORA-00933: SQL command not properly ended:

ALTER TABLE mytable ENABLE CONSTRAINT mytable_c1 VALIDATE;

To disable a constraint:

ALTER TABLE mytable DISABLE CONSTRAINT mytable_c1;

or

ALTER TABLE mytable MODIFY CONSTRAINT mytable_c1 DISABLE;

How to add a check constraint to a table using ALTER TABLE

The following statement adds a check constraint to the table and allows Oracle to give it a system generated name:

ALTER TABLE MYTABLE ADD 
 CHECK (COL1 IS NOT NULL)
 ENABLE
 VALIDATE;

Note that when you execute the following DDL, Oracle will throw ORA-02438: Column check constraint cannot reference other columns:

ALTER TABLE MYTABLE ADD
  CONSTRAINT CHECK (COL1 IS NOT NULL)
  ENABLE VALIDATE;

The above error only happens when you let Oracle give the constraint a system generated name.


If you are explisitly naming your constraint, the use of the CONSTRAINT keyword will work:

ALTER TABLE MYTABLE ADD
  CONSTRAINT mytable_cc2 CHECK (COL1 IS NOT NULL)
  ENABLE VALIDATE;

How to find wait information about a session

Yesterday, I had started a script with two long-running queries before I my shift ended, and it was finished when I got back, as expected. The session was still connected to the database and waiting for some time. I was curious how long time the SQL took, and I forgot to set timing on in sqlplus.

SELECT 
    S.SID,
    S.SERIAL#,
    S.STATUS "status",
    S.LOGON_TIME "logon time",
    S.PREV_EXEC_START "last op started",
    Q.LAST_ACTIVE_TIME "last op ended", 
    S.STATE "state",
    S.EVENT "event",
    ROUND((S.WAIT_TIME_MICRO/1000000)/60/60,1) "total wait hrs",
    DECODE(S.TIME_REMAINING_MICRO,'-1', 'indefinite', '0','Wait timed out',NULL,'session not waiting') "remaining wait time", 
    DECODE(S.TIME_SINCE_LAST_WAIT_MICRO, '0','Still waiting') "current status"
FROM V$SESSION S  JOIN V$SQL Q 
ON (S.SQL_ID = Q.SQL_ID)
WHERE SID=1758;

The output below shows that my session was established at 14:33, and the last operation ended at 19:20. In other words, it took approximately 5 hours for the two statements in my script to finish:

SID	SERIAL#	status	logon time	last op started	last op ended	state	event	total wait hrs	remaining wait time	current status
1758	35360	INACTIVE	14.03.2017 14:33:42	14.03.2017 19:10:31	14.03.2017 19:20:52	WAITING	SQL*Net message from client	13,2	indefinite	Still waiting

Note that v$session column "seconds_in_wait" has been deprecated. You should use "wait_time_micro" instead.

How to use the Oracle 12c Row-limiting clauses

One of the most useful features of Oracle 12c is the ability to limit the number of rows from a query using top-N queries. Amongst others, they provide a great way to populate test tables.

It can be used in several ways, by specifying an offset of rows to skip, the number of rows to return or the percentage of rows to return.

A couple of simple examples:

Fetch the first 5 rows from the result set:

select * 
from scott.emp
fetch first 5 rows only;

Fetch the next 10 rows after the first five rows have been skipped:

select *  
from Scott.emp 
offset 5 rows fetch next 10 rows only;

Load the first few rows of data from a specific partition, into a new table:

insert into test_table
SELECT  *
FROM       original_table
subpartition (other_sp_default)
fetch first 10 rows only;

Sources:

http://docs.oracle.com/database/121/SQLRF/statements_10002.htm#BABHFGAA

and

http://docs.oracle.com/database/121/SQLRF/statements_10002.htm#BABEAACC