CREATE OR REPLACE PROCEDURE DROP_OBJECTS(P_Owners varchar2) IS
cursor obj_crs is
select owner, object_name, object_type
from all_objects
where owner = upper(P_Owners);
obj_REC obj_CRS%ROWTYPE;
l_grants_objects varchar2(500);
BEGIN
dbms_output.put_line('Begining to drop all objects');
Case upper(P_Owners)
when 'SYS' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
when 'SYSTEM' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
when 'WMSYS' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
when 'TSMSYS' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
when 'OUTLN' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
when 'DBSNMP' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
ELSE
Begin
FOR obj_REC IN obj_CRS LOOP
begin
dbms_output.put_line('OUT-1 -> ' || obj_REC.object_type);
CASE obj_REC.object_type
WHEN 'TABLE' THEN
l_grants_objects := 'drop table '||obj_REC.owner||'.'||obj_REC.object_name||' cascade constraints PURGE';
EXECUTE IMMEDIATE l_grants_objects;
-- WHEN 'MATERIALIZED VIEW' THEN
-- dbms_output.put_line('OUT-2 -> INSIDE MATERIALIZED VIEW');
-- l_grants_objects := 'drop MATERIALIZED VIEW ' || obj_REC.owner||'.'||obj_REC.object_name ;
-- dbms_output.put_line(' OUT-2 -> ' || l_grants_objects);
-- EXECUTE IMMEDIATE l_grants_objects;
WHEN 'INDEX' THEN l_grants_objects := '';
WHEN 'TYPE' THEN
l_grants_objects := 'drop type '||obj_REC.owner||'.'||obj_REC.object_name||' force';
EXECUTE IMMEDIATE l_grants_objects;
ELSE
dbms_output.put_line('OUT-2222** -> INSIDE MATERIALIZED VIEW');
l_grants_objects := 'drop '||obj_REC.object_type||' '||obj_REC.owner||'.'||obj_REC.object_name;
EXECUTE IMMEDIATE l_grants_objects;
END CASE;
dbms_output.put_line(l_grants_objects);
Exception
when others then
l_grants_objects := '';
end;
end loop;
end;
end case;
dbms_output.put_line('All objects are dropped');
End;
/
Ex:- exec DROP_OBJECTS('user_name');
Monday, January 19, 2009
Friday, January 9, 2009
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 =;
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 =
Monday, August 11, 2008
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
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.
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.
Subscribe to:
Posts (Atom)