Example on how to use WITH CHECK OPTION in an INSERT statement

The table "ALBUM" has the following columns:

Name         Null     Type                        
------------ -------- --------------------------- 
ALBUM_ID     NOT NULL NUMBER                      
ALBUM_TITLE  NOT NULL VARCHAR2(50 CHAR)           
ARTIST_ID             NUMBER                      
RELEASE_DATE          TIMESTAMP(6) WITH TIME ZONE 
GENRE                 VARCHAR2(30)                
NUM_SOLD              NUMBER                      
COLLECTION            CHAR(1)        

Extract all albums by the band Kiss:

SELECT ARTIST_ID, ALBUM_TITLE
 FROM ALBUM
 WHERE ARTIST_ID = (SELECT ARTIST_ID FROM ARTIST WHERE ARTIST_NAME='Kiss');

 ARTIST_ID ALBUM_TITLE                                      
---------- ------------
        13 Dynasty            

Add another entry to the "ALBUM" table:

SQL> INSERT INTO
  2  (
  3   SELECT album_id, album_title, artist_id,release_date, genre, num_sold
  4   FROM album
  5   WHERE num_sold BETWEEN 10000 AND 50000
  6   AND artist_id=13 WITH CHECK OPTION
  7   )
  8   VALUES( album_seq.nextval, '&album_title', &artist_id, to_date('&release_date','dd.mm.yyyy'), 'HARD ROCK', 20000 );
Enter value for album_title: Love Gun
Enter value for artist_id: 13
Enter value for release_date: 30.06.1977
old   8:  VALUES( album_seq.nextval, '&album_title', &artist_id, to_date('&release_date','dd.mm.yyyy'), 'HARD ROCK', 20000 )
new   8:  VALUES( album_seq.nextval, 'Love Gun', 13, to_date('30.06.1977','dd.mm.yyyy'), 'HARD ROCK', 20000 )

1 row inserted.

COMMIT;

Run the SELECT above once more to confirm the row has been entred:

ARTIST_ID ALBUM_TITLE                                      
---------- -----------------------
        13 Love Gun                             
        13 Dynasty            

Any attempt to enter values that is not included in the subquery would cause an

ORA-01402: view WITH CHECK OPTION where-clause violation

to be raised.
For example, try entering 15 as the artist_id:

Enter value for album_title: Creatures of the Night
Enter value for artist_id: 15
Enter value for release_date: 13.10.1982
old   8:  VALUES( album_seq.nextval, '&album_title', &artist_id, to_date('&release_date','dd.mm.yyyy'), 'HARD ROCK', 20000 )
new   8:  VALUES( album_seq.nextval, 'Creatures of the Night', 15, to_date('13.10.1982','dd.mm.yyyy'), 'HARD ROCK', 20000 )
 VALUES( album_seq.nextval, 'Creatures of the Night', 15, to_date('13.10.1982','dd.mm.yyyy'), 'HARD ROCK', 20000 )
         *
ERROR at line 8:
ORA-01402: view WITH CHECK OPTION where-clause violation

Note that the subquery, when used in an INSERT statement as above, replaces the table name, as well as the column list.

Source: Oracle documentation

How to check for active usage of an UNDO tablespace

SELECT a.name,n.status
FROM   v$rollname a inner join v$rollstat n
USING(usn)
WHERE a.name IN (
                  SELECT segment_name
                  FROM dba_segments
                  WHERE tablespace_name = 'UNDOTBS1'
)
;

NAME       STATUS
---------- ---------------
_SYSSMU8$  PENDING OFFLINE

Status can be either ONLINE, OFFLINE, PENDING OFFLINE or FULL.

* ONLINE means that the undo segments in the UNDO tablespace is being actively used
* OFFLINE means that the undo segments are not accessible for usage
* FULL means that the tablespace in which the undo segments reside has reached its limit, and you will most likely have seen the error

ORA-30036: unable to extend segment by  in undo tablespace 'UNDOTBS1'

at this point

* PENDING OFFLINE means there are still uncommitted transactions depending on the information in the undo segments in this particular UNDO tablespace.

How to find the default trace file for the session

Use the v$diag_info view, which "describes the state of Automatic Diagnostic Repository (ADR) functionality"

SELECT value
FROM   v$diag_info
WHERE  name = 'Default Trace File';

Example output:

VALUE
/u01/oracle/diag/rdbms/proddb01/proddb01/trace/proddb01_ora_45082.trc

A real easy way to start a trace of your current session is

exec dbms_monitor.session_trace_enable;

Then execute your statements

When you're done, stop tracing with

exec dbms_monitor.session_trace_disable;

How to rename or move a datafile in oracle.

The basic steps in relocating a datafile is as follows


1. Take the tablespace offline:

alter tablespace data offline normal;

2. Move the datafile physically, using an os command like cp:

cp data/oracle/proddb01/tmp/datafiles/data01.dbf /data/oracle/proddb01/datafile/data01.dbf

3. Inform the controlfile about your change:

alter database rename file '/data/oracle/proddb01/tmp/datafiles/data01.dbf' to '/data/oracle/proddb01/datafile/data01.dbf';

4. Make the tablespace available for usage again:

alter tablespace data online;

INTERVAL conversion functions

There are 4 convertions functions that deal with INTERVAL datatypes, as listed below.
The two first converts strings, the later two converts numbers. All functions returns data of datatype INTERVAL.

TO_DSINTERVAL(sql_format)

* Converts a string into a value of the INTERVAL DAY TO SECOND datatype
* sql_format is a character string in the required format, which is 'DAYS HH24:MI:SS.FF'
* For example, the string '2 8:30:15.444' would equal 2 days, 8 hours, 30 minutes, 15.444 seconds.

Output: A value of the INTERVAL DAY TO SECOND datatype

Example:

SELECT TO_DSINTERVAL(INTERVAL '4' HOUR) "HOUR"
FROM DUAL;

HOUR
+00 04:00:00.000000

TO_YMINTERVAL('y-m')
* Converts y and m into the years and months in a format recognized by the INTERVAL YEAR TO MONTH datatype
* y and m are numbers within a string

Output: A value of the INTERVAL YEAR TO MONTH datatype

Example:
SELECT TO_YMINTERVAL(INTERVAL '36' MONTH) "MONTH"
FROM DUAL;

MONTH
+03-00

NUMTOYMINTERVAL(n, u)

* Converts a number into a value that represents the u unit amount of time
n = number
u = 'YEAR' or 'MONTH'
* Output: a value of the INTERVAL YEAR TO MONTH datatype

Example:

SELECT NUMTOYMINTERVAL(4, 'MONTH') "MONTH"
FROM DUAL;

MONTH
+00-04

NUMTODSINTERVAL(n, u)
Converts a number into a value that represents the u unit amount of time
n = number
u = 'DAY', 'HOUR', 'MINUTE', 'SECOND'
Output: a value of the INTERVAL DAY TO SECOND datatype

Example:

SELECT NUMTODSINTERVAL(2,'MINUTE') "MINUTE"
FROM DUAL;

MINUTE
+00 00:02:00.000000

SYSTIMESTAMP and SYSDATE VS. CURRENT_TIMESTAMP and CURRENT_DAY

Most DBAs I've spoken to are familiar with the function SYSDATE, but there are some other functions, too, that may prove to be useful, even for DBAs that aren't directly involved in developing applications.
For example, when querying the data dictionary, it can certainly be an advantage to be familiar with some other built-in date functions that comes with the Oracle database.

The two first functions reflect the datetime settings of the operating system on which the database runs:

* The SYSTIMESTAMP function returns a value of the TIMESTAMP WITH TIME ZONE datatype. It includes fractional seconds and time zone.
* the SYSDATE function returns a value of the DATE datatype.It includes timestamp, but not fractional seconds, nor time stamp.

The three functions below reflects the settings of your session:

* The CURRENT_DATE fuction returns a value of the DATE datatype within the session time zone
* The CURRENT_TIMESTAMP function returns a value of the TIMESTAMP WITH TIME ZONE data type within the session time zone
* The LOCALTIMESTAMP function returns a value of the TIMESTAMP data type, within the session time zone

Sitting in Oslo and querying a database with dbtimezone set to +2:00:

select dbtimezone from dual;

DBTIME
+02:00

I get the following default output when I execute the queries at 11:45:

select sysdate, systimestamp, current_date,current_timestamp(5), localtimestamp(3) from dual

   
SYSDATE
   
SYSTIMESTAMP
   
CURRENT_DATE
   
CURRENTTIMESTAMP
   
LOCALTIMESTAMP
   
13.07.2016 11:45:52
   
13.07.2016 11.45.52,597707 +02:00 
   
13.07.2016 11:45:52 
 13.07.2016 11.45.52,59771 +02:00 
  
 13.07.2016 11.45.52,598

Now I will change my session time zone:

alter session set time_zone='America/New_York';

Session altered.

Execute the functions again, and the difference between the function becomes apparent:

select sysdate, systimestamp, current_date,current_timestamp(5), localtimestamp(3) from dual;

SYSDATE	SYSTIMESTAMP	CURRENT_DATE	CURRENTTIMESTAMP	LOCALTIMESTAMP
13.07.2016 11:49:15	13.07.2016 11.49.15,381888 +02:00	13.07.2016 05:49:15	13.07.2016 05.49.15,38189 AMERICA/NEW_YORK	13.07.2016 05.49.15,382

Current_date now returns the date and time as it would be in New York
Current_timestamp does the same, but adds the region name as the time zone indicator
Localtimestamp returns the timestamp as it would be in New York

The precision in the current_timestamp and localtimestamp (5 and 3, respectively) are set to override the default precision of 6 for fractional seconds.

Overview of role privileges

The following UNION can be handy to get an overview of what system privileges, object privileges and other roles a specific role may contain:

Select  Role || ' contains role:' "role privilege type", Granted_Role "privilege"   From Role_Role_Privs Where Role='ONLINE_USERS'
Union
Select Role || ' contains system priv:', Privilege      From Role_Sys_Privs  Where Role='ONLINE_USERS'
Union
Select Role || ' contains object priv:',  Privilege || ' ON ' || owner || '.' || table_name From Role_Tab_Privs Where Role='ONLINE_USERS'
order by 1;

Output:

role privilege type	privilege
ONLINE_USERS contains object priv:	UPDATE ON SCOTT.EMP
ONLINE_USERS contains role:	RESOURCE
ONLINE_USERS contains system priv:	UPDATE ANY TABLE