Oracle RESTRICTED SESSION error

This is probably as complete a way of killing an Oracle database as you could wish for. The sys tables contain all the metadata about every object in the database -- objects, segments, extents ... so the database now contains no information on what user tables it stores, including the tables that store the data about that.

New database, I think.

And no more sys connection accidents.

Unfortunately present SQL or its implementations in all RDBMS products does not fully support temporal relations.

Only Teradata and DB2 have some features implementing constraints having time in the two or more of columns.

My example:

SET SCHEMA TEST;

CREATE TABLE PRODUCT (
product_ID INT NOT NULL,
suplier NVARCHAR(50),
PRIMARY KEY (product_ID)
) ;

CREATE TABLE CAMPAIGN (
campaign_ID INT NOT NULL,
product_ID INT NOT NULL,
price DECIMAL,
PRIMARY KEY (campaign_ID)
)
;

ALTER TABLE CAMPAIGN
ADD CONSTRAINT XFK_campaign_productid
FOREIGN KEY (product_ID) REFERENCES PRODUCT (product_id)
ON DELETE NO ACTION;

If you add temporal columns business_startdate and business_enddate and optionally
transaction_starttime and transaction_endtime, you cannot enforce referential integrity anymore.

You might have to implement triggers or stored procedures or application level coding if you want to implement a constraint like this:

CREATE TABLE CAMPAIGN (
campaign_ID INT NOT NULL,
product_ID INT NOT NULL,
business_startdate DATE NOT NULL,
business_enddate DATE NOT NULL,
transaction_starttime TIMESTAMP NOT NULL,
transaction_endtime TIMESTAMP NOT NULL,
price DECIMAL,
PRIMARY KEY (campaign_ID,business_startdate,transaction_starttime)
)
;

For DB2 there exist a temporal primary key constaint in the following syntax:

CREATE TABLE CAMPAIGN (
campaign_ID INT NOT NULL,
product_ID INT NOT NULL,
business_startdate DATE NOT NULL,
business_enddate DATE NOT NULL,
price DECIMAL,
PERIOD BUSINESS_TIME (business_startdate,business_enddate),
PRIMARY KEY (campaign_ID,BUSINESS_TIME WITHOUT OVERLAPS)
)
;