DML operations based on another table

Copy rows from another table
SQL> create table mytest (tno number(3), tname varchar(20), tloc varchar(10));

Table created.

SQL> insert into mytest
select * from test;

4 rows created.

Note that the 'test' table has the same number of columns as that of 'mytest'. Otherwise you should specify the column names. You should match the number of columns in the INSERT clause with that in the subquery.
SQL> insert into mytest (tno, tname)
select (no, name) from test;

Update two columns:
SQL> update mytest set tname='sample', tloc='CA' where tno=4;

1 row updated.

SQL> select * from mytest;

TNO TNAME TLOC
---------- -------------------- ----------
1 vinay NJ
2 suman NY
3 ravi KY
4 sample CA

SQL> update mytest set
tname=(select tname from mytest where tno=1),
tloc=(select tloc from mytest where tno=2)
where tno=4;

1 row updated.

SQL> select * from mytest;

TNO TNAME TLOC
---------- -------------------- ----------
1 vinay NJ
2 suman NY
3 ravi KY
4 vinay NY

Delete rows based on another table:
SQL> delete from mytest
where tname=(select name from test where tno=3);

1 row deleted.

Using MERGE statement

It performs an UPDATE if the row exists, and an INSERT if it is a new row. Especially useful in data warehousing applications.

SQL> select * from test;

TNO NAME LOC
---------- --------------- ----------
1 vinay NJ
2 suman NY
3 ravi KY
4 sai MO

SQL> select * from sample;

SNO SNAME SALARY
---------- -------------------- ----------
1 vinay 3000
2 kumar 4000
3 suman 5000
4 ravi 8000

SQL> merge into sample s
using test t
on (t.tno=s.sno)
when matched then
update set s.sname=t.name
when not matched then
insert values (t.tno, t.name, 3000)

4 rows merged.

SQL> select * from sample;

SNO SNAME SALARY
---------- -------------------- ----------
5 test 1000
1 vinay 3000
2 suman 4000
3 ravi 5000
4 sai 8000

SQL> select * from test;

TNO NAME LOC
---------- --------------- ----------
1 vinay NJ
2 suman NY
3 ravi KY
4 sai MO


NOTE that MERGE statement is not allowed on external tables. Below 'test' is an external table.

merge into test t
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table

Get object_id of Database Object

To get the object id of an object you can query user_objects. Note that object_name is case-sensitive.

SQL> select object_id from user_objects where object_name='sample';

no rows selected

SQL> select object_id from user_objects where object_name='SAMPLE';

OBJECT_ID
----------
53673

[OR]

You can query dba_objects. Note that owner name is case-sensitive
SQL> select object_id, object_name from dba_objects where owner='SCOTT';

OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------
53930 MASTER
53931 MASTER_MNO_PK
53932 CHILD1
53933 BIN$HfQPV+0lQOWmj9HXRXQ01w==$0
53934 CHILD2
51250 PK_DEPT
51249 DEPT
51251 EMP
51252 PK_EMP
51253 BONUS
51254 SALGRADE

OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------
53673 SAMPLE
53674 BIN$aSuwOl8AQFCCphpKWoCRkg==$0
53675 BIN$Acsdzvs0RMewwv6ntnvFxQ==$0
54148 TEST

15 rows selected.

Using External Tables (Walk-through with an example)

External tables are read-only tables whose metadata is stored in the database and actual data is stored outside the database. You can query the data in external table using SQL, PL/SQL and Java. Since these tables are read-only no DML operations are possible and no indexes can be created on them .

Syntax:
CREATE TABLE table_name ( col_name datatype, … )
ORGANIZATION EXTERNAL
(TYPE access_driver_type
DEFAULT DIRECTORY dir_name
ACCESS PARAMETERS
(… ) )
LOCATION ('loc_specifier') )
REJECT LIMIT [0 | number | UNLIMITED];


access_driver_type can be either ORACLE_LOADER or ORACLE_DATAPUMP. The access driver ensures that the data from data source is processed so that it matches the definition of external table. ORACLE_LOADER is default access driver type and is used for reading of data from external files whose format can be interpreted by SQL*Loader utility. ORACLE_DATAPUMP is used to both import and export data using a platform-independent format.

ACCESS PARAMETERS: optional clause that enables you to assign values to the parameters of specific access driver for this external table

LOCATION: specify the external data source file

REJECT_LIMIT: how many conversion queries can occur during a query of external data befors an Oracle error is returned and the query is aborted. Default is 0.

Walk-through on using external tables:

1) Assume that you have a flat file (test.dat) that has records as shown below:

1, vinay, NJ
2, suman, NY
3, ravi, KY

I have this file located at 'D:\Oracle\OCP' on my system.

2) Create directory object that will point to the directory location where the flat file exists. Use directory names when referring to external data sources, instead of hard-coding the OS path name for greater file management flexibility.

SQL> create directory test_dir as 'D:\Oracle\OCP';

Directory created.

3) Now create the table using organization as external
SQL> create table test (tno number(3), name varchar(15), loc varchar(10))
organization external
(type oracle_loader
default directory test_dir
access parameters
(records delimited by newline
nobadfile
nologfile
fields terminated by ',')
location ('test.dat'))
parallel 2
reject limit 100;

Table created.

4) SQL> select * from test;

TNO NAME LOC
---------- --------------- ----------
1 vinay NJ
2 suman NY
3 ravi KY

5) If you try to perform DML operations on this table you will see the following error:
SQL> insert into test values (4,'sample', 'MO');
insert into test values (4,'sample', 'MO')
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table

6) You can add or delete records from your flat file on disk. I have added the following line to 'test.dat'

4, sai, MO

7) SQL> select * from test;

TNO NAME LOC
---------- --------------- ----------
1 vinay NJ
2 suman NY
3 ravi KY
4 sai MO

8) Once your external table is created, it can be queried like a relational table

NOTE: You can use CREATE TABLE AS SELECT statement to unload data into regular table in the database from an external table.

Ex: In the following example 'test' is an external table.
SQL> create table mytab
as select tno, name from test;

Table created.

SQL> select * from mytab;

TNO NAME
---------- ---------------
1 vinay
2 suman
3 ravi
4 sai
5 sample

Querying V$DATABASE

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL> select platform_id, platform_name from v$database;

PLATFORM_ID
-----------
PLATFORM_NAME
---------------------------------------------------------------------
7
Microsoft Windows IA (32-bit)


SQL> select current_scn from v$database;

CURRENT_SCN
-----------
901163

ORA-30040

Problem:
I have taken the undo tablespace offline through spfile. I shutdown the database and start the db next day from pfile

"IT GIVE ME THE SYSTEM ERROR, DISCONNECTION FORCED"

I tried to start database in nomount stage (it works)
alter database mount (it works)

But when I issue

alter database open OR simply type the "startup" command it gives the below error

Errors in file d:\sam\udump\sam_ora_3700.trc:
ORA-30040: Undo tablespace is offline

Thu Jan 22 00:00:10 2009
Error 30040 happened during db open, shutting down database
USER: terminating instance due to error 30040
Instance terminated by USER, pid = 3700
ORA-1092 signalled during: alter database open...

Solution:
Check UNDO_TABLESPACE (must be set to ur undo tablespace name) and UNDO_MANAGEMENT (set it to AUTO) parameters in PFILE or SPFILE

Using ALTER TABLE....SET UNUSED Statement

Use SET UNUSED option to mark one or more columns as unused.
ALTER TABLE table_name
SET UNUSED(column_name);

[OR]

ALTER TABLE table_name
SET UNUSED COLUMN column_name;

Ex: SQL> select * from sample;

SNO SNAME SALARY
---------- -------------------- ----------
1 vinay 3000
2 kumar 4000
3 suman 5000
4 ravi 8000

SQL> alter table sample set unused (salary);

Table altered.

SQL> select * from sample;

SNO SNAME
---------- --------------------
1 vinay
2 kumar
3 suman
4 ravi

Scenario: To undo the above operation follow the below steps:

SQL> select * from sample;

SNO SNAME SALARY
---------- -------------------- ----------
1 vinay 3000
2 kumar 4000
3 suman 5000
4 ravi 8000

Note the object_id from below query for 'sample' table:
SQL> select object_id, object_name from dba_objects where owner='SCOTT';

OBJECT_ID OBJECT_NAME
---------- ----------------------------------------------------------------
51250 PK_DEPT
51249 DEPT
51251 EMP
51252 PK_EMP
51253 BONUS
51254 SALGRADE
51729 TEST
51966 SAMPLE
51976 SYS_C005218

9 rows selected.

Login as sysdba
SQL> conn sys/manager as sysdba
Connected.

Another way to get the object_id for 'sample' table. Note that name is case-sensitive.
SQL> select obj# from obj$ where name='SAMPLE';

OBJ#
----------
51966

Note the values for COLS in tab$
SQL> select cols from tab$ where obj#=51966;

COLS
----------
3

Note the values for COL#, INTCOL#, PROPERTY, and NAME in col$
SQL> select col#, intcol#, property, name from col$ where obj#=51966;

COL# INTCOL# PROPERTY NAME
---------- ---------- ---------- ------------------------------
1 1 0 SNO
2 2 0 SNAME
3 3 0 SALARY

SQL> conn scott/tiger
Connected.

Use ALTER TABLE ... SET UNUSED to mark a column unused.
SQL> alter table sample set unused (salary);

Table altered.

Select from the table to confirm the column is unavailable. Now, you will not see the 'salary' column.
SQL> select * from sample;

SNO SNAME
---------- --------------------
1 vinay
2 kumar
3 suman
4 ravi

Now, update tab$ and col$ to before the ALTER conditions (see above for values), and commit.
SQL> conn sys/manager as sysdba
Connected.

SQL> update col$
set col#=3, property=0 where name='SYS_C00003_09012712:53:28$';

1 row updated.

SQL> select col#, intcol#, property, name from col$ where obj#=51966;

COL# INTCOL# PROPERTY NAME
---------- ---------- ---------- ------------------------------
1 1 0 SNO
2 2 0 SNAME
3 3 0 SYS_C00003_09012712:53:28$

SQL> update col$
set name='SALARY' where col#=3 and obj#=51966;

1 row updated.

SQL> select col#, intcol#, property, name from col$ where obj#=51966;

COL# INTCOL# PROPERTY NAME
---------- ---------- ---------- ------------------------------
1 1 0 SNO
2 2 0 SNAME
3 3 0 SALARY

SQL> update tab$ set cols=3 where obj#=51966;

1 row updated.

SQL> select cols from tab$ where obj#=51966;

COLS
----------
3

SQL> select cols from tab$ where obj#=51966;

COLS
----------
3

SQL> commit;

Commit complete.

Flush the shared pool and buffer cache.
SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> select * from sample;

SNO SNAME SALARY
---------- -------------------- ----------
1 vinay 3000
2 kumar 4000
3 suman 5000
4 ravi 8000