Archive for the ‘Dictionary Tables’ Category

Finding time for Specific SCN

 

SELECT CURRENT_SCN FROM V$DATABASE;

To get the current SCN

SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL

1.1 ORA_ROWSCN

Is a pseudocolumn of any table that is not fixed or external. It represents
the SCN of the most recent change to a given row, that is, the latest COMMIT operation for the row. For example:

SELECT ora_rowscn, last_name, salary FROM employees
WHERE employee_id = 7788;
ORA_ROWSCN NAME SALARY
———- —- ——
202553 Fudd 3000

1.2 SCN_TO_TIMESTAMP( nSCN NUMBER)

Converts a SCN to TIMESTAMP
It has a precision of +/- 3 seconds

SQL> select scn_to_timestamp(884871) as timestamp from dual;

TIMESTAMP
—————————————————————————
09/03/2007 14:52:02,000000000

1.3 TIMESTAMP_TO_SCN(dTimestamp)

Converts TIMESTAMP to SCN
It has a precision of +/- 3 seconds

SQL> select timestamp_to_scn(to_timestamp(’08/03/2007 14:24:54′,’DD/MM/YYYY HH24:MI:SS’)) as scn from dual;

SCN
———-
845396

 

1.4 Table: SMON_SCN_TIME

This table translate time to SCN approximately.

When you use time based flashback you get the data in a period between +- 5 mn.
DBMS_FLASHBACK.ENABLE_AT_TIME and AS OF TIMESTAMP maps to an SCN value.  As the SCN-time is recorded every X minutes. The time you specify is rounded down by up to X minutes from database startup.
This situation could create the ORA-01466 unable to read data – table definition has changed. There is only track of times up to a maximum of 5 days (Database up time).

SELECT TO_CHAR(TIME_DP,’DDMONYYYY HH24:MI’) DATE_TIME,SCN
FROM SMON_SCN_TIME
Date_time               SCN
04MAY2006 16:20 576601
04MAY2006 16:26 576799
04MAY2006 16:30 577003
04MAY2006 16:36 577393
04MAY2006 16:41 577585
04MAY2006 16:46 577790

LINK WIKI ORACLE
Advertisements

Constraints

Several types of Oracle constraints can be applied to Oracle tables to enforce data integrity, including:

  • Oracle "Check" Constraint: This constraint validates incoming columns at row insert time. For example, rather than having an application verify that all occurrences of region are North, South, East, or West, an Oracle check constraint can be added to the table definition to ensure the validity of the region column.
  • Not Null Constraint: This Oracle constraint is used to specify that a column may never contain a NULL value. This is enforced at SQL insert and update time.
  • Primary Key Constraint: This Oracle constraint is used to identify the primary key for a table. This operation requires that the primary columns are unique, and this Oracle constraint will create a unique index on the target primary key.
  • References Constraint: This is the foreign key constraint as implemented by Oracle. A references constraint is only applied at SQL insert and delete times.  At SQL delete time, the references Oracle constraint can be used to ensure that an employee is not deleted, if rows still exist in the DEPENDENT table.
  • Unique Constraint: This Oracle constraint is used to ensure that all column values within a table never contain a duplicate entry.

Important Constraint Views

dba_cons_columns
dba_constraints

 

CREATE TABLE Dept_tab (
    Deptno  NUMBER(3) CONSTRAINT Dept_pkey PRIMARY KEY, 
    Dname   VARCHAR2(15), 
    Loc     VARCHAR2(15))
CREATE TABLE Emp_tab ( 
    Empno    NUMBER(5) CONSTRAINT Emp_pkey PRIMARY KEY, 
    Ename    VARCHAR2(15) NOT NULL, 
    Job      VARCHAR2(10), 
    Mgr      NUMBER(5) CONSTRAINT Mgr_fkey REFERENCES Emp_tab,
    Hiredate DATE, 
    Sal      NUMBER(7,2), 
    Comm     NUMBER(5,2), 
    Deptno   NUMBER(3) NOT NULL 
             CONSTRAINT dept_fkey REFERENCES Dept_tab ON DELETE CASCADE);

alter table  table_name add constraint constraint_name;

Primary and Foreign Key Constraints
ALTER TABLE Dept_tab 
    ADD CONSTRAINT PK_Dept PRIMARY KEY (deptno); 
ALTER TABLE Emp_tab 
    ADD CONSTRAINT FKEY_Dept FOREIGN KEY (Deptno) REFERENCES Dept_tab;
Unique Check and Not Null Constraints
ALTER TABLE Dept_tab CONSTRAINT UQ_Dname UNIQUE (Dname, Loc);
ALTER TABLE Dept_tab CONSTRAINT Loc_check1
CHECK (loc IN ('NEW YORK', 'BOSTON', 'CHICAGO'));
ALTER TABLE Emp_tab MODIFY (Ename VARCHAR2(15) NOT NULL);
Enabling Disabling Dropping Constraints

ALTER TABLE Dept  ENABLE CONSTRAINT UQ_Dname;

ALTER TABLE Dept_tab  DISABLE CONSTRAINT UQ_Dname;

ALTER TABLE Dept_tab  DROP CONSTRAINT  UQ_Dname;

 

Constraint State

DEFERRABLE Clause

The DEFERRABLE and NOT DEFERRABLE parameters indicate whether or not, in subsequent transactions, constraint checking can be deferred until the end of the transaction using the SET CONSTRAINT(S) statement. If you omit this clause, then the default is NOT DEFERRABLE.

  • Specify NOT DEFERRABLE to indicate that in subsequent transactions you cannot use the SET CONSTRAINT[S] clause to defer checking of this constraint until the transaction is committed. The checking of a NOT DEFERRABLE constraint can never be deferred to the end of the transaction.

    If you declare a new constraint NOT DEFERRABLE, then it must be valid at the time the CREATE TABLE or ALTER TABLE statement is committed or the statement will fail.

  • Specify DEFERRABLE to indicate that in subsequent transactions you can use the SET CONSTRAINT[S] clause to defer checking of this constraint until after the transaction is committed. This setting in effect lets you disable the constraint temporarily while making changes to the database that might violate the constraint until all the changes are complete.

The following statement creates table games with a NOT DEFERRABLE INITIALLY IMMEDIATE constraint check (by default) on the scores column:

CREATE TABLE games (scores NUMBER CHECK (scores >= 0));

CREATE TABLE games (scores NUMBER, CONSTRAINT unq_num UNIQUE (scores) INITIALLY DEFERRED DEFERRABLE);

VALIDATE | NOVALIDATE

The behavior of VALIDATE and NOVALIDATE always depends on whether the constraint is enabled or disabled, either explicitly or by default.

ALTER TABLE sales ADD CONSTRAINT sales_time_fk
FOREIGN KEY (time_id) REFERENCES times (time_id)
ENABLE VALIDATE;

 

RELY Constraints

The ETL process commonly verifies that certain constraints are true. For example, it can validate all of the foreign keys in the data coming into the fact table. This means that you can trust it to provide clean data, instead of implementing constraints in the data warehouse. You create a RELY constraint as follows:

ALTER TABLE sales ADD CONSTRAINT sales_time_fk
FOREIGN KEY (time_id) REFERENCES times (time_id) 
RELY DISABLE NOVALIDATE;

This statement assumes that the primary key is in the RELY state. RELY constraints, even though they are not used for data validation, can:

  • Enable more sophisticated query rewrites for materialized views. See Chapter 18, " Query Rewrite" for further details.

  • Enable other data warehousing tools to retrieve information regarding constraints directly from the Oracle data dictionary.

Creating a RELY constraint is inexpensive and does not impose any overhead during DML or load. Because the constraint is not being validated, no data processing is necessary to create it.

DYNAMIC SQL FOR CONSTRAINTS

To disable constraints in a schema for all tables

SELECT ‘alter table ‘||owner||’.’||table_name||’ disable constraint ‘||constraint_name||’ ;’

FROM dba_constraints where owner = ‘HR’

To enable constraints, make sure that referential integrity constraints are enabled after primary key constraints.

SELECT ‘alter table ‘||owner||’.’||table_name||’ enable constraint ‘||constraint_name||’ ;’

FROM dba_constraints  where owner = ‘HR’

order by constraint_type

QUERIES FOR CHECKING CONSTRAINTS

  SELECT Constraint_name, Search_condition  
    FROM User_constraints  
    WHERE (Table_name = 'DEPT_TAB' OR Table_name = 'EMP_TAB') AND  
        Constraint_type = 'C';

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS, LAST_CHANGE

FROM DBA_CONSTRAINTS

WHERE TABLE_NAME = ‘EMPLOYEES’

SELECT Owner,Constraint_name, Table_name, Column_name 
FROM dba_cons_columns

where  table_name = ‘EMPLOYEES’

and owner = ‘HR_DUP’