How to insert large amount of data in oracle table faster way

How to insert large amount of data in oracle table faster way
===========================================

For this can be Use Direct Load Insert
-------------------------------------------------------
Direct Load Insert is a faster way of running an INSERT statement.Direct Load Insert
differs from Conventional Insert in that it bypasses the buffer cache.

To use Direct Load Insert, add the APPEND hint to your INSERT statement. like below.....

INSERT /*+ APPEND*/
INTO target_table
SELECT * FROM source_table

Need to know for Direct load Insert
---------------------------------------------------
1. Data is appended to the table. Existing free space is not re-used.
2. Direct Load Insert only works for INSERT INTO .. SELECT .... Inserts
3. Direct Load Insert uses rollback segments to maintain indexes as the data is loaded
4. Direct Load Insert can be run with a NOLOGGING option making it even faster.
5. Direct Load Insert locks the table in exclusive mode. No other session can insert, update, or delete data, or maintain any indexes.
6. Referential Integrity (Foreign Key) constraints and triggers must be disabled before running Direct Path Insert
7. Direct Load Insert cannot occur on:
o Index Organised Tables
o Tables with LOB Columns
o Tables with Object Columns
o Clustered Tables


DEMO
=====


SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jul 31 12:28:13 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn basel2@test107
Enter password:
Connected.
SQL>
SQL>
SQL>
SQL> set timing on
SQL> set time on
12:28:41 SQL>
12:28:42 SQL>
12:28:42 SQL> create table direct_load_insert as select * from TABLE_OF_VIEW_BACKUP
12:30:57 2 where brancd=0 ;

Table created.

Elapsed: 00:00:04.03
12:31:28 SQL>
12:31:31 SQL> desc direct_load_insert
Name Null? Type
----------------------------------------- -------- ----------------------------
BRANCD VARCHAR2(3)
ACTYPE VARCHAR2(3)
ACTNUM VARCHAR2(12)
CURBAL NUMBER
LONCON VARCHAR2(3)
ACTTIT VARCHAR2(60)
CUSCOD VARCHAR2(10)
SHDESC VARCHAR2(10)
OPNDAT DATE
EXPDAT DATE
SECURITY_BAL NUMBER
SECURITY_TYPE VARCHAR2(4000)
VALDAT DATE
CATGRY VARCHAR2(1)
VALPRD NUMBER(4)
LCAAMT NUMBER(16,3)
REMAMT NUMBER(16,3)
BANCOD VARCHAR2(3)
CMPIND VARCHAR2(1)
REMARK VARCHAR2(50)

12:31:48 SQL>
12:31:50 SQL>
12:31:51 SQL> INSERT /*+ APPEND*/
12:32:19 2 INTO direct_load_insert
12:32:44 3 SELECT * FROM TABLE_OF_VIEW_BACKUP ;

3706656 rows created.

Elapsed: 00:01:13.31
12:34:17 SQL>
12:34:23 SQL>
12:34:23 SQL>
12:34:23 SQL> ROLLBACK ;

Rollback complete.

Elapsed: 00:00:00.06
12:34:53 SQL>
12:34:54 SQL>
12:34:54 SQL> INSERT INTO direct_load_insert
12:35:31 2 SELECT * FROM TABLE_OF_VIEW_BACKUP ;

3706656 rows created.

Elapsed: 00:01:53.36
12:37:30 SQL>
12:38:06 SQL>
12:38:06 SQL> ROLLBACK
12:39:44 2 /

Rollback complete.

Elapsed: 00:01:25.35
12:41:12 SQL>
12:41:14 SQL>
12:41:14 SQL> DROP TABLE direct_load_insert ;

Table dropped.

Elapsed: 00:00:28.17
12:41:57 SQL>
12:41:59 SQL>
12:41:59 SQL>

What are the differences in syntax between a CASE statement and a CASE expression ?

What are the differences in syntax between a CASE statement
and a CASE expression ?
===========================================================


The CASE expression was first added to SQL in Oracle8i.
Oracle9i extends its support to PL/SQL to allow CASE to
be used as an expression or statement:


1. A CASE statement is terminated with END CASE, while a CASE
expression is terminated with END.

2. The code within a WHEN clause of a CASE statement must terminate
with a semi-colon; the code within a WHEN clause of a CASE expression
must not terminate with a semi-colon.


Example:=

Case expression:
==================
[code]
SELECT ename, empno,
(CASE
WHEN sal < 1000 THEN 'Low'
WHEN sal BETWEEN 1000 AND 3000 THEN 'Medium'
WHEN sal > 3000 THEN 'High'
ELSE 'N/A'
END) salary
FROM emp
ORDER BY ename;
[/code]

Case statement:
==================
[code]
SET SERVEROUTPUT ON
BEGIN
FOR cur_rec IN (SELECT ename, empno, sal FROM emp ORDER BY ename) LOOP
DBMS_OUTPUT.PUT(cur_rec.ename || ' : ' || cur_rec.empno || ' : ');
CASE
WHEN cur_rec.sal < 1000 THEN
DBMS_OUTPUT.PUT_LINE('Low');
WHEN cur_rec.sal BETWEEN 1000 AND 3000 THEN
DBMS_OUTPUT.PUT_LINE('Medium');
WHEN cur_rec.sal > 3000 THEN
DBMS_OUTPUT.PUT_LINE('High');
ELSE
DBMS_OUTPUT.PUT_LINE('Unknown');
END CASE;
END LOOP;
END;
/
[/code]

EXP-00008: ORACLE error 904 encountered ORA-00904: "MAXSIZE": invalid identifier ORA-01003: no statement parsed

EXP-00008: ORACLE error 904 encountered ORA-00904: "MAXSIZE": invalid identifier ORA-01003: no statement parsed
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

=============================================================
When I execute a export (EXP) in this version I found this result............................
----------------------------------------------------------------------------
---------------------------------------------------------------------------
==============================================================

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jul 8 13:00:12 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\era-1>exp userid=basel2/basel2@test107 owner=basel2
file=D:\basel_dump\basel2_08072010_test107.dmp LOG=D:\basel_dump\basel2_08072010_test107.LOG

Export: Release 11.1.0.6.0 - Production on Thu Jul 8 13:00:19 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Produc
tion
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engin
e options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user BASEL2
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user BASEL2
About to export BASEL2's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export BASEL2's tables via Conventional Path ...
. . exporting table AUDIT_SOURCE_HIST
EXP-00008: ORACLE error 904 encountered
ORA-00904: "MAXSIZE": invalid identifier
. . exporting table AUDIT_TABLE_BASEL2
EXP-00008: ORACLE error 1003 encountered
ORA-01003: no statement parsed
. . exporting table D_TABLE_OF_VIEW
EXP-00008: ORACLE error 904 encountered
ORA-00904: "MAXSIZE": invalid identifier
. . exporting table LK_BANK_RATING
EXP-00008: ORACLE error 1003 encountered
ORA-01003: no statement parsed
. . exporting table LK_CUS_RATING
EXP-00008: ORACLE error 904 encountered
ORA-00904: "MAXSIZE": invalid identifier
. . exporting table LK_GL_ELI_CAPITAL
EXP-00008: ORACLE error 1003 encountered
ORA-01003: no statement parsed
====================================================================================

Note: As I know this is a bug of 11.1.0.6 oracle version

Look at metalink note 741984.1.
Bug number is 5872788 - there exists patch as well.

solution for another oracle versions (not sure)
================================
If there been any changes made to the database. Like catexp.sql
then you should be re-running catalog.sql which will run catexp.sql.

=====================================================================================

========================================================
But When I execute same comman here I found this result............................
----------------------------------------------------------------------------
---------------------------------------------------------------------------
=========================================================

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 8 13:06:51 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn stlbas/stlbas@test107
Connected.
SQL>
SQL>
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\era-1>
C:\Documents and Settings\era-1>exp userid=basel2/basel2@test107 owner=basel2
file=D:\basel_dump\basel2_08072010_test107.dmp LOG=D:\basel_dump\basel2_08072010_test107.LOG

Export: Release 10.2.0.1.0 - Production on Thu Jul 8 13:07:28 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user BASEL2
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user BASEL2
About to export BASEL2's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export BASEL2's tables via Conventional Path ...
. . exporting table AUDIT_SOURCE_HIST 138789 rows exported
. . exporting table AUDIT_TABLE_BASEL2 457 rows exported
. . exporting table D_TABLE_OF_VIEW 0 rows exported
. . exporting table LK_BANK_RATING 193 rows exported
. . exporting table LK_CUS_RATING 27 rows exported
. . exporting table LK_GL_ELI_CAPITAL 18 rows exported
. . exporting table LK_GL_MKR 46 rows exported
. . exporting table LK_GL_OFF 1419 rows exported
. . exporting table LK_GL_OFF_1 33 rows exported
. . exporting table LK_GL_ON 504 rows exported
. . exporting table LK_GL_ON_10122009 18221 rows exported
. . exporting table LK_GL_ON_30062010_BCK 499 rows exported

how to show user profile and change its limit ?

how to show user profile and change its limit ?
==============================================
1. First see the profile which are using my user ;

SELECT *
FROM dba_users
WHERE USERNAME='ISLBAS';

2. For showing the specific profiles property

select * from dba_profiles
where profile='DEFAULT'

--profile attribute
select * from profile$

---profile names
select * from profname$

SELECT name, lcount
FROM user$
WHERE lcount <> 0;

3.In order to track password related profile limits, Oracle stores the history
of passwords for a user in user_history$.

select * from user_history$

4. To change profile limit use

ALTER PROFILE default limit
failed_login_attempts UNLIMITED;

How to Recover oracle Database until a Previous position by RMAN

How to back Database until a Previous position by RMAN

Database Point-in-time recovery (Time based incomplete recovery)

# This scenario assumes that all initialization files,RMAN backup of database and the current
# controlfile are in place and you want to recover to a point in time '03-07-2010:14:20:00' .
#

RMAN> shutdown immediate

RMAN>STARTUP MOUNT FORCE;

RMAN> RUN
{
SET UNTIL TIME "TO_DATE('03-07-2010:14:20:00','dd-mm-yyyy:hh24:mi:ss')";
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}