How to restore archive logs to a new destination

This example restores all archived redo logs to the /oracle/temp_restore directory:

RMAN> RUN
{ 
  SET ARCHIVELOG DESTINATION TO '/oracle/temp_restore/'; <-- note the last /
  RESTORE ARCHIVELOG ALL;
}

If the file is already on disk you will get an error message from RMAN.

To override this, use the force option:

RMAN> run {
2> allocate channel t1 device type 'sbt';
3> set archivelog destination to '/oracle/temp_restore/';
4> restore archivelog logseq 15572 force;
5> }

How is the database time zone set?

Answer: it is set at creation time.
If not explicitly defined by the DBA, it will use the time zone of the server's operating system.

If you want to set it explicitly, do so in the CREATE DATABASE statement:

CREATE DATABASE PRODDB
.
.
SET TIME_ZONE='-05:00';

Or set it to a named region, like this:

CREATE DATABASE PRODDB
.
.
SET TIME_ZONE='Europe/Zurich';

The database time zone is relevant only for TIMESTAMP WITH LOCAL TIME ZONE columns.

You can change the database time zone by using the SET TIME_ZONE clause of the ALTER DATABASE statement:

ALTER DATABASE SET TIME_ZONE='05:00';
ALTER DATABASE SET TIME_ZONE='Europe/Zurich';

The ALTER DATABASE SET TIME_ZONE statement will return an error if the database contains a table using a TIMESTAMP WITH LOCAL TIME ZONE column and the column contains data. You will also have to restart the database.


To see the current time zone of the database:
SELECT DBTIMEZONE,SESSIONTIMEZONE FROM DUAL;

DBTIMEZONE	SESSIONTIMEZONE
+01:00	Europe/Zurich

For a database used globally, it may be beneficial to set the database time to UTC (0:00) regardless of where it is physically hosted.

Source: Oracle Documentation

How does Oracle Data Pump Import handles referential integrety constraint violations?

How does Oracle Data Pump Import handles referential integrety constraint violations?

To find out, let's use the two tables SHIPS and PORTS, which are connected by a referential constraint from SHIPS to PORTS, called FK_SHIPS_PORTS.

CREATE TABLE SHIPS
(
  SHIP_ID       NUMBER,
  SHIP_NAME     VARCHAR2(20 BYTE),
  CAPACITY      NUMBER,
  LENGTH        NUMBER,
  HOME_PORT_ID  NUMBER,
  LIFEBOATS     NUMBER(3),
  CONSTRAINT PK_SHIPS PRIMARY KEY (SHIP_ID),
  CONSTRAINT FK_SHIPS_PORTS FOREIGN KEY (HOME_PORT_ID) 
  REFERENCES PORTS (PORT_ID)
);

CREATE TABLE PORTS
(
  PORT_ID    NUMBER,
  PORT_NAME  VARCHAR2(20 BYTE),
  COUNTRY    VARCHAR2(40 BYTE),
  CAPACITY   NUMBER,
  CONSTRAINT PK_PORTS PRIMARY KEY (PORT_ID)
);

In other words, you cannot add a ship without a valid PORT_ID that already exist in table PORTS.

PORT_ID	PORT_NAME	COUNTRY	CAPACITY
1	Baltimore	USA
2	Charleston	USA
3	Tampa	USA
4	Miami	USA

SHIP_ID	SHIP_NAME	CAPACITY	LENGTH	HOME_PORT_ID	LIFEBOATS
1	Codd Crystal	2052	855	1	80
15	Codd Champion	2000	650		30
2	Codd Elegance	2974	952	2	95
16	Codd Victorious	2055	876	2
17	Codd Grandeur	2030	840	4
18	Codd Prince	1500	550	2	32
20	Codd Norway	1500	900	3	80

I now remove one row from the PORTS table, so that new rows being imported will have a missing parent key. In order to do that I need to also remove any child record from SHIPS, in my case, only one:

DELETE TESTUSER.SHIPS WHERE HOME_PORT_ID = 1;
DELETE TESTUSER.PORTS WHERE PORT_ID = 1;
COMMIT;

1 row deleted.
1 row deleted.
Commit complete.

SCENARIO 1: "What happens if we use TABLE_EXISTS_ACTION=REPLACE when the table being replaced has a referential constraint to another table?"

Result:

• The operation will report an error because of a constraint violation issue.
• All rows are imported into SHIPS, including the offending ones
• The referential constraint is dropped.

Error message:
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER"."SHIPS" 7.289 KB 7 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-02298: cannot validate (TESTUSER.FK_SHIPS_PORTS) - parent keys not found
Failing sql is:
ALTER TABLE "TESTUSER"."SHIPS" ADD CONSTRAINT "FK_SHIPS_PORTS" FOREIGN KEY ("HOME_PORT_ID") REFERENCES "TESTUSER"."PORTS" ("PORT_ID") DEFERRABLE ENABLE


SCENARIO 2: "What happens if we use TABLE_EXISTS_ACTION=TRUNCATE when the table being truncated has a referential constraint to another table?"

Result:

• The operation will report an error because of a constraint violation issue.
• The SHIPS table is left truncated, no rows are loaded.
• The referential constraint stays.

Error message:
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "TESTUSER"."SHIPS" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "TESTUSER"."SHIPS" failed to load/unload and is being skipped due to error:
ORA-02291: integrity constraint (TESTUSER.FK_SHIPS_PORTS) violated - parent key not found


Both cases requires manual intervention afterwards to cleanup offending rows, and then to recreate the constraint.
One way to do so would be to allow for orphan rows through temporarily disabling the index, loading the rows, then reenabling the index using the NOVALIDATE option.

First disable the constraint:
SQL> ALTER TABLE SHIPS DISABLE CONSTRAINT FK_SHIPS_PORTS;

Table altered.

Load the data once more:
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "TESTUSER"."SHIPS" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER"."SHIPS" 7.289 KB 7 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Finally, enable the constraint again. However, tell oracle to disregard existing rows, only apply the constraint to new rows:
SQL> ALTER TABLE SHIPS ENABLE NOVALIDATE CONSTRAINT FK_SHIPS_PORTS;

Table altered.

SQL>

Example is based on Steve O'Heam's "SQL Certified SQL Expert Exam Guide"

How to drop a table partition without invalidating global indexes

My interval range partitioned table looks as follows:

CREATE TABLE SEGMENT_SIZES(
  SEGMENT_SIZES_ID NUMBER,
  STIMESTAMP      DATE,
  OWNER           VARCHAR2(30 BYTE),
  SEGMENT_NAME    VARCHAR2(30 BYTE),
  PARTITION_NAME  VARCHAR2(30 BYTE),
  SEGMENT_TYPE    VARCHAR2(20 BYTE),
  BYTES           NUMBER,
  CONSTRAINT SEGMENT_SIZES_PK PRIMARY KEY (SEGMENT_SIZES_ID)
)
PARTITION BY RANGE (STIMESTAMP )
-- Use 11gR1 Interval Partitioning
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
   PARTITION P062013 VALUES LESS THAN (TO_DATE('01.07.2013','DD.MM.YYYY'))
)
TABLESPACE TOOLS
ENABLE ROW MOVEMENT
COMPRESS FOR ALL OPERATIONS;

It was populated with data, which created the needed partititions automatically.
The primary key defined on the table will of course create a global index spanning all partitions.
Consequently, during partition maintenance operations, you will end up with an UNUSUABLE primary key index if you drop a partition, as follows:

ALTER TABLE SEGMENT_SIZES DROP PARTITION SYS_P42;

However, if you you add the "UPDATE INDEXES" clause, oracle will update the global index; the index will remain USABLE:

ALTER TABLE SEGMENT_SIZES DROP PARTITION SYS_P42 UPDATE INDEXES;

Keep in mind that you cannot drop the highest range partition of an interval-partitioned table!

Source: Oracle Documentation

why am I getting ORA-14006: invalid partition name when attempting to drop a partition in TOAD?

In TOAD you are trying to drop a partition, as follows:

alter table segment_sizes drop partition SYS_P41;

But you get ORA-14006: invalid partition name as a result.

Solution: remove the ";" at the end of the statement, and try again.
Alternatively, execute the statement through SQL*plus.

Source: Derya Oktay's Oracle Weblog

How to use the sqlplus "autotrace" facility

SET AUTOT ON
Executes the SQL and returns the execution plan and statistics (shorthand for "set autotrace on")

SET AUTOT TRACE
Executes the SQL and returns the execution plan and statistics (shorthand for "set autotrace traceonly")

Note: Using ON or TRACEONLY with no explicit options defaults to EXPLAIN STATISTICS.

SET AUTOT TRACE EXP
Explains the SQL, omits statistics and does not execute the SQL(shorthand for "set autotrace traceonly explain")
The SQL statement is never execute for real, only explained.

SET AUTOT TRACE EXP STAT
Executes the SQL, displays the execution plan, displays statistics. Executes the SQL, but supresses the output (shorthand for "set autotrace traceonly explain statistics")

SET AUTOT OFF
Disables autotraceing (shorthand for "set autotrace off")

If you have trouble getting the autotrace feature to work, make sure you have created the PLUSTRACE role.

Source: Oracle 19c Documentation

sdtperfmeter - a graphical monitoring of cpu, paging and I/O on Solaris

Create a file called monitor.sh:

#!/usr/bin/bash

/usr/dt/bin/sdtperfmeter -t page &
/usr/dt/bin/sdtperfmeter -t cpu &
/usr/dt/bin/sdtperfmeter -t disk &

exit

Start your X server, set display to your client, then execute in background:

./monitor.sh &

Works on Solaris 10 (and several previous versions)