csscan utility

cssan utility to change the Character set of database:-

Beware: - Before doing this take a full database backup

1. Create data_file_dir and log_file_dir directories with below command:-
SQL> create directory data_file_dir as ‘some_directory_location’;
SQL> create directory log_file_dir as ‘some_directory_location’;

2. And execute the csminst.sql file as a sys user:-
SQL> @$ORACLE_HOME/rdbms/admin/csminst.sql
It will create CSMIG user.

3. Execute the below command in OS level:-
$csscan full=Y fromchar=WE8MSWIN1252 tochar=WE8IS08859P15 log=WE8_TO_WE8
capture=Y array=100000
(later it will prompt for processes, enter some number between 1 to 32)

4. Then shutdown the database and startup database in restrict mode.
SQL>shutdown immediate;
SQL>startup restrict;

5. Execute the below file to change the Character Set:-
SQL>@$ORACLE_HOME/rdbms/admin/csalter.plb;
(it will ask for Y/N, press Y)

6. Then shutdown the database and start it normally:-
SQL>shutdown;
SQL>startup;
Check the character set with the below query:-
SQL> select * from v$nls_parameters;
* if there is any error, we can check with below query:-
first login to the CSMIG user and execute it
SQL> select count(*) from csm$errors;
if the output is '0' then it is fine.

tkprof

How to get queries, which is executed by particular user:-

1. Change the timed_statistics parameter to TRUE:-
SQL> alter system set TIMED_STATISTICS=TRUE;

2. Turn tracing on user for session level:-
SQL> alter session set SQL_TRACE=TRUE;

As a DBA, execute the below command to enable sql trace for particular user:-
SQL> exec DBMS_SESSION.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);
Get the sid and serial# from the V$session view.

3. Then, see the user dump destination for trace file.

4. Execute the below command to analyze & create insert script for the trace file:-
$tkprof (sql_trace_file_name) (any_text_file_name) insert=tkprof_table.sql
It will create in text file and script for to create tkprof_table.

5. Execute the tkprof_table.sql file in sys user schema.

6. It will create tkprof_table, with contents.

7. Execute the below query to get the queries of particular user.
(before get the user_id from the dba_users view)
set long 5000
select sql_statement from tkprof_table where user_id =;

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.

Oracle 10g

Download

Using Network_Link in Datapump

Oracle's export and import utilities have historically used a disk file as intermediate storage when unloading or reloading the database. For large databases, this "dump file" was an issue because operating system limits on file size could be exceeded, making export impossible.

Creative DBAs have used file compression utilities, such as compress on UNIX, to get the most capacity from the dump file. Later versions of import and export allowed the use of multiple dump files to get around the limits.

In Oracle 10g, the Data Pump version of import can eliminate the dump file entirely by importing directly from another database instance.

Example:

1. Create tnsnames for remote database from where the database has to be imported.[Target]
conn_151 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = wisedba.com)
)
)

2. Create a directory for user to access during Datapump job[Target]

SYS> create directory defdir as '/home/ez10g/wisedba';
SYS> grant read, write on directory defdir to scott;

3. Grant user to create database link[Target}

SYS> grant create database link to scott;

4. Create database link as Scott user (to the remote database)[Target]

SCOTT> Create database link conn_151
2 connect to scott identified by tiger
3 using 'conn_151';

5. Checking tables in scott user (in local database)[Target]
SCOTT> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE

6. Import 'EMP' table from remote database (without creating any dump file)[Target]

$ impdp scott/tiger tables=emp directory=defdir network_link=conn_151

Import: Release 10.2.0.1.0 - Production on Monday, 02 April, 2007 18:04:53

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** tables=emp directory=defdir network_link=conn_151
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SCOTT"."EMP" 114688 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 18:05:12

7. Check whether the table has been imported to local database

SCOTT> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMP TABLE <<-- Table has been imported
BONUS TABLE