Insert into views - Restrictions

You can also insert into a view, as long as the view does not contain one of the following:

• A DISTINCT operator

• A set operator (UNION, MINUS, and so on)

• An aggregate function (SUM,COUNT,AVG, and so on)

• A GROUP BY,ORDER BY, or CONNECT BY clause

• A subquery in the SELECT list

Note:
If violating the constraints, insert will also fail, for example:


scott@ORCL> create view emp_v3 as select ename, job from emp;

View created.

scott@ORCL> select * from emp_v3;

ENAME JOB
---------- ---------
Denis
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK

15 rows selected.

scott@ORCL> insert into emp_v3 values('David', 'Clerk');
insert into emp_v3 values('David', 'Clerk')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."EMP"."EMPNO")

Binary_float and binary_double

With Oracle 6 through Oracle 9i, infinity could not be represented in the database. With Oracle 10g, however, binary_float and binary_double can represent infinity, not a number (NAN), as well as several other special values.

Oracle 10g - Drop database

Following tests show that drop database works in 10g, but not in 9i

tx2qdfsdb1:TOY10G: /ora01/orabkup/MYDBQ/.temp_toy10g/oracle/admin/TOY10G > sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Sat Sep 8 15:32:24 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

sys@TOY10G> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@TOY10G> startup mount;

ORACLE instance started.

Total System Global Area 268435456 bytes
Fixed Size 2029392 bytes
Variable Size 167774384 bytes
Database Buffers 92274688 bytes
Redo Buffers 6356992 bytes
Database mounted.
sys@TOY10G> drop database;
drop database
*

ERROR at line 1:

ORA-12719: operation requires database is in RESTRICTED mode


sys@TOY10G> alter system enable restricted session;

System altered.

sys@TOY10G> drop database;

ERROR:

ORA-01034: ORACLE not available

Database dropped.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options
------------------

tx2qdfsdb1:TOY9I: /ora01/orabkup/MYDBQ/.temp_toy10g/ora01/oradata/TOY9I > sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Sep 8 15:38:42 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

idle> conn / as sysdba

Connected to an idle instance.

idle> startup mount;

ORACLE instance started.

Total System Global Area 589269776 bytes
Fixed Size 731920 bytes
Variable Size 553648128 bytes
Database Buffers 33554432 bytes
Redo Buffers 1335296 bytes
Database mounted.
idle> drop database
2 ;

*

ERROR at line 2:
ORA-02026: missing LINK keyword

Restore a RMAN backup from disk to a new host with different pathname

We have a requirement to restore an 8 months old GENP RMAN backup from tape. In order to be familiar with the procedure, I tested restoring a recent GENP RMAN backup from disk to a new host with different pathname. The steps are outlined as follows:

 

 

1. Copy init.ora from old host to new host

   edit *dump, controlfile

 

2. Copy current controlfile to the new host

 

 

3. Start the instance and mount the database

    startup mount

 

4. Use rman without catalog

   rman target / nocatalog

 

5. Issue following command:

 

run {

     set until time '08-SEP-2007 05:20:02'; 

     allocate channel ch1 type disk;

     allocate channel ch2 type disk;

     set newname for datafile 1          to '/ora01/orabkup/GEN3S/GENP/genp_system01.dbf';

     set newname for datafile 2          to '/ora01/orabkup/GEN3S/GENP/genp_tools01.dbf';

     ( ...  DS: omit 3 - 50)

 

     set newname for datafile 51         to '/ora01/orabkup/GEN3S/GENP/gen_indx_12.dbf';

     set newname for datafile 52         to '/ora01/orabkup/GEN3S/GENP/gen_data_16.dbf';

 

     restore database;

     switch datafile all;  # points the control file to the renamed datafiles

     recover database;

     alter database open resetlogs;

} 

 

 

6.  Failure at 'alter database open resetlogs'

...

 

RMAN-08515: archivelog filename=/ora01/orabkup/GEN3S/GENP1/arch_GENP_000111585.arc thread=1 sequence=111585

RMAN-08515: archivelog filename=/ora01/orabkup/GEN3S/GENP1/arch_GENP_000111586.arc thread=1 sequence=111586

RMAN-08515: archivelog filename=/ora01/orabkup/GEN3S/GENP1/arch_GENP_000111587.arc thread=1 sequence=111587

RMAN-08515: archivelog filename=/ora01/orabkup/GEN3S/GENP1/arch_GENP_000111588.arc thread=1 sequence=111588

RMAN-08055: media recovery complete

 

RMAN-03022: compiling command: alter db

RMAN-03026: error recovery releasing channel resources

RMAN-08031: released channel: ch1

RMAN-08031: released channel: ch2

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure during compilation of command

RMAN-03013: command type: alter db

RMAN-06003: ORACLE error from target database: ORA-00344: unable to re-create online log '/ora01/oradata/GENP/genp_redo01a.log'

ORA-27040: skgfrcre: create error, unable to create file

SVR4 Error: 2: No such file or directory

RMAN-06097: text of failing SQL statement: alter database open resetlogs

RMAN-06099: error occurred in source file: krmk.pc, line: 22291

 

RMAN>

RMAN> **end-of-file**

 

 

7. Move the redo log file

   Issue the following statements, after that  database is opened successfully with resetlogs

 

alter database rename file '/ora01/oradata/GENP/genp_redo01a.log' to '/ora01/orabkup/GEN3S/GENP1/genp_redo01a.log';

alter database rename file '/ora01/oradata/GENP/genp_redo02a.log' to '/ora01/orabkup/GEN3S/GENP1/genp_redo02a.log';

alter database rename file '/ora01/oradata/GENP/genp_redo03a.log' to '/ora01/orabkup/GEN3S/GENP1/genp_redo03a.log';

alter database rename file '/ora01/oradata/GENP/genp_redo04a.log' to '/ora01/orabkup/GEN3S/GENP1/genp_redo04a.log';

alter database rename file '/ora01/oradata/GENP/genp_redo05a.log' to '/ora01/orabkup/GEN3S/GENP1/genp_redo05a.log';

alter database rename file '/ora01/oradata/GENP/genp_redo06a.log' to '/ora01/orabkup/GEN3S/GENP1/genp_redo06a.log';

alter database rename file '/ora02/oradata/GENP/genp_redo01b.log' to '/ora01/orabkup/GEN3S/GENP/genp_redo01b.log';

alter database rename file '/ora02/oradata/GENP/genp_redo02b.log' to '/ora01/orabkup/GEN3S/GENP/genp_redo02b.log';

alter database rename file '/ora02/oradata/GENP/genp_redo03b.log' to '/ora01/orabkup/GEN3S/GENP/genp_redo03b.log';

alter database rename file '/ora02/oradata/GENP/genp_redo04b.log' to '/ora01/orabkup/GEN3S/GENP/genp_redo04b.log';

alter database rename file '/ora02/oradata/GENP/genp_redo05b.log' to '/ora01/orabkup/GEN3S/GENP/genp_redo05b.log';

alter database rename file '/ora02/oradata/GENP/genp_redo06b.log' to '/ora01/orabkup/GEN3S/GENP/genp_redo06b.log';

 

 

 

Note: RMAN backup files have been already ftp'ed to the new host

External Table

1. Create a directory

Login as SYS

In Windows:
create or replace directory ext_dir as 'E:\Denis_Files\ORACLE\Practise_Oracle\External_Table'

grant read, write on directory ext_dir to SCOTT;

2. Test with cormma dilimited file

create table ext_table_csv (
i Number,
n Varchar2(20),
m Varchar2(20)
)
organization external (
type oracle_loader
default directory ext_dir
access parameters (
records delimited by newline
fields terminated by ','
missing field values are null
)
location ('tab_corma.csv')
)
reject limit unlimited;

scott@ORCL> select * from ext_table_csv;

I N M
---------- -------------------- --------------------
1 scott tiger
2 denis sun
3 fuxia jin
4 eric sun
5 david wang
6 jack paul


3. Test with fixed field length

create table ext_table_fixed (
field_1 char(4),
field_2 char(30)
)
organization external (
type oracle_loader
default directory ext_dir
access parameters (
records delimited by newline
fields (
field_1 position(1:4) char( 4),
field_2 position(5:30) char(30)
)
)
location ('tab_fixed.csv')
)
reject limit unlimited;


scott@ORCL> select * from ext_table_fixed;
FIEL FIELD_2
---- ------------------------------
b001 xyz
b002 denis
b003 alex


Note:
======
During testing encountered the following errors, after make the directory in which the flat files are saved rwx to all (chmod 777), problem solved

scott@ORCL> select * from ext_table_csv;
select * from ext_table_csv
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file EXT_TABLE_CSV_256_2996.log
OS error The data is invalid.
ORA-06512: at "SYS.ORACLE_LOADER", line 19



To use external table to do parallel direct-path load:
-------------------------------------------------------
a. create external_table
b. create table T nologging parallel as select * from external_table
ref: Tom Kyte, Effective Oracle by Design, p193