World of Oracle DBA

Problem Description $expdp parfile=pfile_maxim_history_sel.par directory=d Export: Release 10.2.0.1.0 - 64bit Production on Monday, 05 January, 2009 17:23:52 Copyright (c) 2003, 2005, Oracle. All rights reserved. Username: system Password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** parfile=pfile_maxim_history_sel.par directory=d Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 964 MB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT 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_EXPO...

It is sometimes a time consuming task if you like to create index with much number of rows. For example you are asked to created an index over 1 billion of data. It may take over 6 hours on your computer and you want to make it faster. With providing several options while creating index you can improve index creation speed dramatically. 1)PARALLEL Option: While creating index oracle must first collect the symbolic key/ROWID pairs with a full-table scan. With Parallel option supplied to the create index script oracle will scan full table parallel based on the number of CPUs, table partitioning and disk configuration and thus do the tasks faster. On a server that have 6 CPUs you may give parallel 5 as below. create index table_1_I on table_1(id,code) parallel 5; 2)NOLOGGING Option: With the NOLOGGING option provided while creating index you can restrict database to generate a large redo log. NOLOGGING option generates minimal redo. So you will achieve higher performance. create index ...

Problem Description I wanted to update a table column based on another table data but it fails with error ORA-01779: cannot modify a column which maps to a non key-preserved table. SQL> update (select t1.code col1, t2.code col2 from table_1 t1 JOIN table_2 t2 ON t1.id=t2.id) set col1=col2; set col1=col2 * ERROR at line 4: ORA-01779: cannot modify a column which maps to a non key-preserved table Cause, Description and Solution of the Problem Let's look both of table's data. SQL> select * from table_1; ID CODE ---------- -------------------- 1 First Row 2 Rows to be updated SQL> select * from table_2; ID CODE ---------- -------------------- 2 Second Row From the above update query, the select part return following rows, SQL> select t1.code col1, t2.code col2 from table_1 t1 JOIN table_2 t2 ON t1.id=t2.id; COL1 COL2 -------------------- -------------------- Rows to...

Problem Description SQL> INSERT /*+APPEND NOLOGGING */ INTO STATION_RATE(ID,COUNTRY,CODE,PURCHASE_RATE,STATION_ID) SELECT ID.NEXTVAL, COUNTRY,CODE,PURCHASE_RATE, 4 from STATION_RATE_DUMMY; 7561 rows created. SQL> INSERT /*+APPEND NOLOGGING */ INTO STATION_RATE(ID,COUNTRY,CODE,PURCHASE_RATE,STATION_ID) SELECT ID.NEXTVAL, COUNTRY,CODE,PURCHASE_RATE, 5 from STATION_RATE_DUMMY; INSERT /*+APPEND NOLOGGING */ INTO STATION_RATE(ID,COUNTRY,CODE,PURCHASE_RATE,STATION_ID) SELECT * ERROR at line 1: ORA-12838: cannot read/modify an object after modifying it in parallel Cause of the Problem A table is modified in parallel or with direct path load in a transaction. Now within the same transaction if an attempt was made to read or modification statements on a table then ORA-12838 will occur. In oracle within same transaction table is modified with direct path load or parallel and then access of it is not permitted. Solution of the Problem Break up the t...