How to import only users over a network link

Use the following parameter file to import only the user definitions from your source database to your test database:
Remember that the database link proddb01 must be correctly defined and working before you import the data.

userid='system/yourpassword
network_link=proddb1.mydomain.com
logfile=DATA_PUMP_DIR:user_definitions.log
full=Y
include=USER

To import a specific user only:

INCLUDE=USER:"='SCOTT'"

To include a specific user and remap it to another user name during import:

REMAP_SCHEMA=SCOTT:JIM
INCLUDE=USER:"='SCOTT'"

Remember to check if the SCOTT user is assigned a default tablespace that doesn't exist in the target database.
If this is the case, you need to use remap_schema directive to make it work:

REMAP_TABLESPACE=SCOTT_DATA:JIM_DATA

... or potentiall pre-create the tablespace SCOTT_DATA in the target database, but this may not be what you wish.

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.