SQL loader

SQL> conn a1/a1
Connected.

SQL> create table stu
2 (
3 sno number(5),
4 sname varchar2(15),
5 adds varchar2(10)
6 );
Table created.

SQL> desc stu
Name Null? Type
----------------------- -------- -----------------------------------
SNO NUMBER(5)
SNAME VARCHAR2(15)
ADDS VARCHAR2(10)

Creating datafile:-
[vamsi@vamsi]$ vi stu.txt
10,sharan,a
34,manoj,b
76,ram,f
69,ajay,t
40 ragu,r
20krishr

:wq!

Creating control file:
[vamsi@vamsi]$ vi c1.ctl
load data
infile 'stu.txt'
badfile 'bad.txt'
discardfile 'dis.txt'
insert into table stu
fields terminated by ','
(sno,sname,adds)
:wq!

[vamsi@vamsi]$ sqlldr userid=a1/a1 control=c1.ctl
SQL*Loader: Release 9.2.0.1.0 - Production on Tue Apr 15 12:28:13 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 6

[vamsi@vamsi sqlldr]$ sqlplus '/as sysdba'
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Apr 15 12:30:26 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> conn a1/a1
Connected.
SQL> select * from stu;

SNO SNAME ADDS
------- ---------- ----------
10 sharan a
34 manoj b
76 ram f
69 ajay t
[vamsi@vamsi sqlldr]$ vi bad.txt
40 ragu,r
20krishr

Flash back in Oracle 9i

sql>select username from dba_users;

USERNAME
------------------------------
SYS
SYSTEM
DBSNMP
OUTLN
SCOTT
A1
6 rows selected.

sql>conn a1/a1
Connected.

sql>select * from tab;

TNAME TABTYPE CLUSTERID
--------------------------- ------- ---------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE

sql>select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- ------ ----- ---- --------- -------- -------- --------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN ALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.

sql>conn /as sysdba
Connected.

sql>grant execute on dbms_flashback to a1;
Grant succeeded.
sql>conn a1/a1
Connected.

sql>select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
122593

sql>delete from emp where deptno=10;
3 rows deleted.

sql>select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- ------ ----- ---- --------- -------- -------- --------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN ALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
11 rows selected.

sql>execute dbms_flashback.enable_at_system_change_number(122592);
PL/SQL procedure successfully completed.
(or)
sql>execute dbms_flashback.enable_at_time(timestamp '2008-04-10
10:15:00');
PL/SQL procedure successfully completed.


sql>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- ------ ----- ---- --------- -------- -------- --------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN ALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.

sql>execute dbms_flashback.disable;
PL/SQL procedure successfully completed.

sql>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- ------ ----- ---- --------- -------- -------- --------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN ALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
11 rows selected.