How Much time recovery after Killing the Long Running Session will take

select state,undoblocksdone,undoblockstotal,undoblocksdone / undoblockstotal * 100
from v$fast_start_transactions;
************************
The FAST_START_PARALLEL_ROLLBACK parameter controls how much juice SMON will take. The three possible parameters are: FALSE, LOW, and HIGH. A setting of FALSE will tell SMON to do the recovery on its own without parallel assistance. This could work to allow your app to remain online while SMON does its job, but could still cause contention issues. In the end, it is better to let SMON finish the work while other processes try to lay off UNDO as much as possible. A setting of LOW will tell SMON to use 2 * CPU_COUNT parallel processes to do the work, and a setting of high will use 4 * CPU_COUNT. In order to allow SMON to finish its work as quickly as possible, we turned off their app and set the FAST_START_PARALLEL_ROLLBACK parameter to HIGH, allowing SMON all the resources it needed to finish. To determine how long it would take to finish, we consulted the V$FAST_START_TRANSACTIONS view

Delete duplicate row from table

delete from TABLE_NAME where rowid in (select min(rowid) from TABLE_NAME group by (empid, LOCATION) having count(empid)>1 );

Oracle to SQL connection

http://www.databasejournal.com/features/oracle/article.php/10893_3442661_1

1) Create DSN
2) change $ORACLE_HOME/hs/admin/inithsodbc.
3) Change listner.ora as per D:\oracle\product\10.1.0\db_1\hs\admin
4) change tnsname.ora as per D:\oracle\product\10.1.0\db_1\hs\admin
5) Now Start Listner ( Create Database link using SQL server User / Pass )

Oracle Installation on SUN

/etc/system
************************************
set shmsys:shminfo_shmmax=17179869184
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=4096
set shmsys:shminfo_shmseg=10
set semsys:seminfo_semmns= 32000
set semsys:seminfo_semmsl=256
set semsys:seminfo_semmni=128
set semsys:seminfo_semvmx=32767
set noexec_user_stack=1

***************SUN AMD 64xDDD*********

set shmsys:shminfo_shmmax=17179869184
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=4096
set shmsys:shminfo_shmseg=10
set semsys:seminfo_semmns= 32000
set semsys:seminfo_semmsl=256
set semsys:seminfo_semmni=128
set semsys:seminfo_semvmx=32767
set noexec_user_stack=1

/export/home/oracle:/usr/bin/bash
**********************************************

***************************
set semsys:seminfo_semopm= 100
set semsys:seminfo_shmall= 2097152
set semsys:seminfo_file-max= 65536
set semsys:seminfo_ip_local_port_range= 1024 65000
set semsys:seminfo_rmem_default= 262144
set semsys:seminfo_rmem_max= 262144
set semsys:seminfo_wmem_default= 262144
set semsys:seminfo_wmem_max= 262144

Step-2
**********************************
groupadd oinstall
groupadd dba
groupadd oper
userdel oracle
useradd -d /export/home/oracle -g oinstall -G dba,oper -m -s /usr/bin/bash oracle
passwd -r files oracle
**********************************
Step-3
editing the /export/home/oracle/.profile
**********************************
umask 022
TMP=/tmp
TMPDIR=$TMP
DISPLAY=localhost:0.0
export TMP TMPDIR DISPLAY
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE ORACLE_HOME PATH
**********************************
Step-4
**********************************
su - oracle
env
-------------------------------
mkdir /oracle
mkdir /oracle/10g
chown -R oracle:oinstall /ora_soft
chmod -R 775 /ora_soft
**********************************
Step-5
**********************************
projadd oracle
/etc/user_attr
****************
Add the line
****************
oracle::::project=oracle
****************
# su - oracle
Sun Microsystems Inc. SunOS 5.10 Generic January 2005

$ id -p
uid=100(oracle) gid=100(oinstall) projid=100(oracle)
****************************************************

Extracting SQL from an EXPORT file

TIP: Click help for a detailed explanation of this page.
Bookmark Go to End

Subject: Unix Script: IMPSHOW2SQL - Extracting SQL from an EXPORT file
Doc ID: Note:29765.1 Type: SCRIPT
Last Revision Date: 25-APR-2001 Status: PUBLISHED


This is a short script that allows you to extract the SQL from an export
file. It is intended for use on UNIX machines. Instructions are in the
header of the script.

When entering the script ensure:

A> The first line in the file should be a single colon (:)

Please note this is a proto-type script so there may be some statements
in the resultant file that need manual editting.

:
# impshow2sql Tries to convert output of an IMP SHOW=Y command into a
# usage SQL script.
#
# To use:
# Start a Unix script session and import with show=Y thus:
#
# $ imp user/password file=exportfile show=Y log=/tmp/showfile
#
# You now have the SHOW=Y output in /tmp/showfile .
# Run this script against this file thus:
#
# $ ./impshow2sql /tmp/showfile > /tmp/imp.sql
#
# The file /tmp/imp.sql should now contain the main SQL for
# the IMPORT.
# You can edit this as required.
# Note: This script may split lines incorrectly for some statements
# so it is best to check the output.
#
# CONSTRAINT "" problem:
# You can use this script to help get the SQL from an export
# then correct it if it includes bad SQL such as CONSTRAINT "".
# Eg:
# Use the steps above to get a SQL script and then
# $ sed -e 's/CONSTRAINT ""//' infile > outfile
# Now precreate all the objects and import the export file.
#
# Extracting Specific Statements only:
# It is fairly easy to change the script to extract certain statements
# only. For statements you do NOT want to extract change N=1 to N=0
# Eg: To extract CREATE TRIGGER statements only:
# a) Change all lines to set N=0.
# Eg: / \"CREATE / { N=0; }
# This stops CREATE statements being output.
#
# b) Add a line (After the general CREATE line above):
# / \"CREATE TRIGGER/ { N=1; }
# This flags that we SHOULD output CREATE TRIGGER statements.
#
# c) Run the script as described to get CREATE TRIGGER statements.
#

awk ' BEGIN { prev=";" }
/ \"CREATE / { N=1; }
/ \"ALTER / { N=1; }
/ \"ANALYZE / { N=1; }
/ \"GRANT / { N=1; }
/ \"COMMENT / { N=1; }
/ \"AUDIT / { N=1; }
N==1 { printf "\n/\n\n"; N++ }
/\"$/ { prev=""
if (N==0) next;
s=index( $0, "\"" );
if ( s!=0 ) {
printf "%s",substr( $0,s+1,length( substr($0,s+1))-1 )
prev=substr($0,length($0)-1,1 );
}
if (length($0)<78) printf( "\n" );
}' $*

To restrict sqlplus / Toad to connect Oracle

create or replace trigger restrict_login
after logon on database
declare
flag number := 0;
begin
select 1 into flag
from sys.v_$session
where lower(program) like '%sqlplus%' or
lower(program) like '%toad%' and
rownum=1 ;
if flag = 1 then
raise_application_error(-20000, 'You are not Authorised to Access from TOAD/SQLPLUS');
end if;
end;
***********************************
One can check Module_hash /Module from V$session instead of checking the program name. The Module_hash /Module wont change by renaming the exe

Cascade Update

Prviledge to User Creating Package=
• CREATE TRIGGER
• CREATE PROCEDURE
********************************************
@ create package
SQL> update emp set empno = 8000 where empno = 7698;
update emp set empno = 8000 where empno = 7698
*
ERROR at line 1:
ORA-02292: integrity constraint (UCDEMO.SYS_C005186) violated - child record
found


SQL> exec update_cascade.on_table('emp')

PL/SQL procedure successfully completed.

SQL> update emp set empno = 8000 where empno = 7698;
*********************************************


create or replace package update_cascade
as

procedure on_table( p_table_name in varchar2,
p_preserve_rowid in boolean default TRUE,
p_use_dbms_output in boolean default FALSE );

end update_cascade;
/

create or replace package body update_cascade
as

type cnameArray is table of user_cons_columns.column_name%type
index by binary_integer;

sql_stmt varchar2(32000);
use_dbms_output boolean default FALSE;
preserve_rowid boolean default TRUE;

function q( s in varchar2 ) return varchar2
is
begin
return '"'||s||'"';
end q;

function pkg_name( s in varchar2 ) return varchar2
is
begin
return q( 'u' || s || 'p' );
end pkg_name;


function view_name( s in varchar2 ) return varchar2
is
begin
return q( 'u' || s || 'v' );
end view_name;

function trigger_name( s in varchar2, s2 in varchar2 ) return varchar2
is
begin
return q( 'u' || s || s2 );
end trigger_name;


function strip( s in varchar2 ) return varchar2
is
begin
return ltrim(rtrim(s));
end strip;

procedure add( s in varchar2 )
is
begin
if ( use_dbms_output ) then
dbms_output.put_line( chr(9) || s );
else
sql_stmt := sql_stmt || chr(10) || s;
end if;
end add;



procedure execute_immediate
as
exec_cursor integer default dbms_sql.open_cursor;
rows_processed number default 0;
begin
if ( use_dbms_output ) then
dbms_output.put_line( '/' );
else
dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
rows_processed := dbms_sql.execute(exec_cursor);
dbms_sql.close_cursor( exec_cursor );
dbms_output.put_line(
substr( sql_stmt, 2, instr( sql_stmt,chr(10),2)-2 ) );
sql_stmt := NULL;
end if;
exception
when others then
if dbms_sql.is_open(exec_cursor) then
dbms_sql.close_cursor(exec_cursor);
end if;
raise;
end;

procedure get_pkey_names
( p_table_name in out user_constraints.table_name%type,
p_pkey_names out cnameArray,
p_constraint_name in out user_constraints.constraint_name%type )
is
begin
select table_name, constraint_name
into p_table_name, p_constraint_name
from user_constraints
where ( table_name = p_table_name
or table_name = upper(p_table_name) )
and constraint_type = 'P' ;

for x in ( select column_name , position
from user_cons_columns
where constraint_name = p_constraint_name
order by position ) loop
p_pkey_names( x.position ) := x.column_name;
end loop;
end get_pkey_names;


procedure write_spec( p_table_name in user_constraints.table_name%type,
p_pkey_names in cnameArray )
is
l_comma char(1) default ' ';
begin
add( 'create or replace package ' || pkg_name(p_table_name) );
add( 'as' );
add( '--' );
add( ' rowCnt number default 0;' );
add( ' inTrigger boolean default FALSE;' );
add( '--' );

for i in 1 .. 16 loop
begin
add( ' type C' || strip(i) || '_type is table of ' ||
q(p_table_name) || '.' || q(p_pkey_names(i)) ||
'%type index by binary_integer;' );
add( '--' );
add( ' empty_C' || strip(i) || ' C' || strip(i) || '_type;' );
add( ' old_C' || strip(i) || ' C' || strip(i) || '_type;' );
add( ' new_C' || strip(i) || ' C' || strip(i) || '_type;' );
add( '--' );
exception
when no_data_found then exit;
end;
end loop;

add( '--' );
add( ' procedure reset;' );
add( '--' );
add( ' procedure do_cascade;' );
add( '--' );

add( ' procedure add_entry' );
add( ' ( ' );
for i in 1 .. 16 loop
begin
add( ' ' || l_comma || 'p_old_C' || strip(i) || ' in ' ||
q(p_table_name) || '.' || q(p_pkey_names(i)) || '%type ' );
l_comma := ',';
add( ' ,p_new_C' || strip(i) || ' in out ' ||
q(p_table_name) || '.' || q(p_pkey_names(i)) || '%type ' );
exception
when no_data_found then exit;
end;
end loop;
add( ' );' );
add( '--' );
add( 'end ' || pkg_name(p_table_name) || ';' );

end write_spec;


procedure write_body
( p_table_name in user_constraints.table_name%type,
p_pkey_names in cnameArray,
p_constraint_name in user_constraints.constraint_name%type )
is
l_col_cnt number default 0;
l_comma char(1) default ' ';
l_pkey_str varchar2(2000);
l_pkey_name_str varchar2(2000);
l_other_col_str varchar2(2000);
begin
add( 'create or replace package body ' || pkg_name(p_table_name) );
add( 'as' );
add( '--' );

add( ' procedure reset ' );
add( ' is' );
add( ' begin' );
add( '--' );
add( ' if ( inTrigger ) then return; end if;' );
add( '--' );
add( ' rowCnt := 0;' );
for i in 1 .. 16 loop
begin
if (p_pkey_names(i) = p_pkey_names(i)) then
l_col_cnt := l_col_cnt+1;
end if;
add( ' old_C' || strip(i) || ' := empty_C' || strip(i) || ';' );
add( ' new_C' || strip(i) || ' := empty_C' || strip(i) || ';' );
exception
when no_data_found then exit;
end;
end loop;
add( ' end reset;' );
add( '--' );

add( ' procedure add_entry ' );
add( ' ( ' );
for i in 1 .. 16 loop
begin
add( ' ' || l_comma || 'p_old_C' || strip(i) || ' in ' ||
q(p_table_name) || '.' || q(p_pkey_names(i)) || '%type ' );
l_comma := ',';
add( ' ,p_new_C' || strip(i) || ' in out ' ||
q(p_table_name) || '.' || q(p_pkey_names(i)) || '%type ' );
exception
when no_data_found then exit;
end;
end loop;
add( ' )' );
add( ' is' );
add( ' begin' );
add( '--' );
add( ' if ( inTrigger ) then return; end if;' );
add( '--' );
add( ' if ( ' );
for i in 1 .. l_col_cnt loop
if ( i <> 1 ) then
add( ' OR' );
end if;
add( ' p_old_C' || strip(i) || ' <> ' ||
'p_new_C' || strip(i) );
end loop;
add( ' ) then ' );
add( ' rowCnt := rowCnt + 1;' );

for i in 1 .. l_col_cnt loop
add( ' old_C' || strip(i) ||
'( rowCnt ) := p_old_C' || strip(i) || ';' );
add( ' new_C' || strip(i) ||
'( rowCnt ) := p_new_C' || strip(i) || ';' );
add( ' p_new_C' || strip(i) ||
' := p_old_C' || strip(i) || ';' );
end loop;

add( ' end if;' );
add( ' end add_entry;' );


add( '--' );

l_comma := ' ';
for i in 1 .. l_col_cnt loop
l_pkey_str := l_pkey_str||l_comma||'$$_C' || strip(i) || '(i)';
l_pkey_name_str := l_pkey_name_str || l_comma || q(p_pkey_names(i));
l_comma := ',';
end loop;

for x in ( select column_name
from user_tab_columns
where table_name = p_table_name
and column_name not in
( select column_name
from user_cons_columns
where constraint_name = p_constraint_name )
order by column_id )
loop
l_other_col_str := l_other_col_str || ',' || q(x.column_name);
end loop;

add( ' procedure do_cascade' );
add( ' is' );
add( ' begin' );
add( '--' );
add( ' if ( inTrigger ) then return; end if;' );
add( ' inTrigger := TRUE;' );
add( '--' );
add( ' for i in 1 .. rowCnt loop' );
add( ' insert into ' || p_table_name || ' ( ' );
add( ' ' || l_pkey_name_str );
add( ' ' || l_other_col_str || ') select ');
add( ' ' || replace( l_pkey_str, '$$', 'new' ) );
add( ' ' || l_other_col_str );

add( ' from ' || q(p_table_name) || ' a' );
add( ' where (' || l_pkey_name_str || ' ) = ' );
add( ' ( select ' || replace(l_pkey_str,'$$','old') );
add( ' from dual );' );
add( '--' );
if ( preserve_rowid ) then
add( ' update ' || q(p_table_name) || ' set ' );
add( ' ( ' || l_pkey_name_str || ' ) = ' );
add( ' ( select ' );
for i in 1 .. l_col_cnt loop
if ( i <> 1 ) then add( ' ,' ); end if;
add( ' decode( ' || q(p_pkey_names(i)) ||
replace(', old_c$$(i), new_c$$(i), old_c$$(i) )', '$$',strip(i)) );
end loop;
add( ' from dual )' );
add( ' where ( ' || l_pkey_name_str || ' ) =' );
add( ' ( select ' || replace(l_pkey_str,'$$','new') );
add( ' from dual )' );
add( ' OR ( ' || l_pkey_name_str || ' ) =' );
add( ' ( select ' || replace(l_pkey_str,'$$','old') );
add( ' from dual );' );
end if;

for x in ( select table_name, constraint_name
from user_constraints
where r_constraint_name = p_constraint_name
and constraint_type = 'R' ) loop

l_comma := ' ';
l_other_col_str := '';
for y in ( select column_name
from user_cons_columns
where constraint_name = x.constraint_name
order by position ) loop
l_other_col_str := l_other_col_str || l_comma || q(y.column_name);
l_comma := ',';
end loop;

add( '--' );
add( ' update ' || q( x.table_name ) || ' set ');
add( ' ( ' || l_other_col_str || ' ) = ' );
add( ' ( select ' || replace( l_pkey_str, '$$', 'new' ) );
add( ' from dual )' );
add( ' where ( ' || l_other_col_str || ' ) = ' );
add( ' ( select ' ||
replace( l_pkey_str, '$$', 'old' ) );
add( ' from dual );' );

end loop;

add( '--' );
add( ' delete from ' || q(p_table_name) );
add( ' where ( ' || l_pkey_name_str || ' ) = ' );
add( ' ( select ' ||
replace( l_pkey_str, '$$', 'old' ) );
add( ' from dual);' );
add( ' end loop;' );

add( '--' );
add( ' inTrigger := FALSE;' );
add( ' reset;' );
add( ' exception' );
add( ' when others then' );
add( ' inTrigger := FALSE;' );
add( ' reset;' );
add( ' raise;' );
add( ' end do_cascade;' );
add( '--' );
add( 'end ' || pkg_name( p_table_name ) || ';' );

end write_body;


procedure write_bu_trigger( p_table_name in user_constraints.table_name%type,
p_pkey_names in cnameArray )
is
l_comma char(1) default ' ';
begin
add( 'create or replace trigger ' || trigger_name( p_table_name, '1' ) );
add( 'before update of ' );
for i in 1 .. 16 loop
begin
add( ' ' || l_comma || q(p_pkey_names(i)) );
l_comma := ',';
exception
when no_data_found then exit;
end;
end loop;
add( 'on ' || q(p_table_name) );
add( 'begin ' || pkg_name(p_table_name) || '.reset; end;' );
end write_bu_trigger;


procedure write_bufer_trigger
( p_table_name in user_constraints.table_name%type,
p_pkey_names in cnameArray )
is
l_comma char(1) default ' ';
begin
add( 'create or replace trigger '||trigger_name( p_table_name, '2' ) );
add( 'before update of ' );

for i in 1 .. 16 loop
begin
add( ' ' || l_comma || q(p_pkey_names(i)) );
l_comma := ',';
exception
when no_data_found then exit;
end;
end loop;
add( 'on ' || q(p_table_name) );
add( 'for each row' );
add( 'begin ' );
add( ' ' || pkg_name(p_table_name) || '.add_entry(' );

l_comma := ' ';
for i in 1 .. 16 loop
begin
add( ' ' || l_comma || ':old.' || q(p_pkey_names(i)) );
add( ' ,:new.' || q(p_pkey_names(i)) );
l_comma := ',';
exception
when no_data_found then exit;
end;
end loop;
add( ' );' );
add( 'end;' );
end write_bufer_trigger;

procedure write_au_trigger( p_table_name in user_constraints.table_name%type,
p_pkey_names in cnameArray )
is
l_comma char(1) default ' ';
begin
add( 'create or replace trigger ' || trigger_name( p_table_name, '3' ) );
add( 'after update of ' );
for i in 1 .. 16 loop
begin
add( ' ' || l_comma || q(p_pkey_names(i)) );
l_comma := ',';
exception
when no_data_found then exit;
end;
end loop;
add( 'on ' || q(p_table_name) );
add( 'begin ' || pkg_name(p_table_name) || '.do_cascade; end;' );
end write_au_trigger;

procedure on_table( p_table_name in varchar2,
p_preserve_rowid in boolean default TRUE,
p_use_dbms_output in boolean default FALSE )
is
l_table_name user_constraints.table_name%type default p_table_name;
l_constraint_name user_constraints.constraint_name%type;
l_pkey_names cnameArray;
l_comma char(1) default ' ';
begin
use_dbms_output := p_use_dbms_output;
preserve_rowid := p_preserve_rowid;

get_pkey_names( l_table_name, l_pkey_names, l_constraint_name );

sql_stmt := NULL;
write_spec( l_table_name, l_pkey_names );
execute_immediate;
write_body( l_table_name, l_pkey_names, l_constraint_name );
execute_immediate;
write_bu_trigger( l_table_name, l_pkey_names );
execute_immediate;
write_bufer_trigger( l_table_name, l_pkey_names );
execute_immediate;
write_au_trigger( l_table_name, l_pkey_names );
execute_immediate;


end on_table;

end update_cascade;
/
show errors

Time Diff fn

create or replace FUNCTION DTDIFF (
DATE_1 IN DATE, DATE_2 IN DATE) RETURN NUMBER IS
NDATE_1 NUMBER;
NDATE_2 NUMBER;
NSECOND_1 NUMBER(5,0);
NSECOND_2 NUMBER(5,0);
BEGIN
-- Get Julian date number from first date (DATE_1)
NDATE_1 := TO_NUMBER(TO_CHAR(DATE_1, 'J'));
-- Get Julian date number from second date (DATE_2)
NDATE_2 := TO_NUMBER(TO_CHAR(DATE_2, 'J'));
-- Get seconds since midnight from first date (DATE_1)
NSECOND_1 := TO_NUMBER(TO_CHAR(DATE_1, 'SSSSS'));
-- Get seconds since midnight from second date (DATE_2)
NSECOND_2 := TO_NUMBER(TO_CHAR(DATE_2, 'SSSSS'));
RETURN (((NDATE_2 - NDATE_1) * 86400)+(NSECOND_2 - NSECOND_1));
END;

creating Repository using emca

http://arjudba.blogspot.com/2008/04/stack-of-problems-while-creating.html

Limit the Quota on the Tablespace of user2

While import to user2 limit the quota on user2 so that no objects created on users1
**************************************
alter user rbpc quota 0 on evtprjnew;
**************************************

default tablespace when imp. using the INDEXFILE

How to change the default tablespace when importing using the INDEXFILE option
Doc ID: Note:1068183.6 Type: PROBLEM
Last Revision Date: 18-JAN-2004 Status: PUBLISHED


Problem Description:
====================

You are importing a table and receive the following error:

ORA-01658: unable to create INITIAL extent for segment in tablespace %s
Cause: Failed to find sufficient contiguous space to allocate INITIAL
extent for segment being created.
Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the
tablespace or retry with a smaller value for INITIAL

You realize that you do not have enough space in the tablespace for the table,
but do not want to add a datafile to that tablespace. So, you need to find out
how to change the default tablespace when importing.


Problem Explanation:
====================

By default an import will try to import tables into the same tablespace they
were exported from. There is not an equivalent FROM_TABLESPACE/TO_TABLESPACE
similar to FROMUSER/TOUSER, so you have to force the import utility into
placing the objects into another tablespace.


Problem References:
===================

Note 61949.1 Export and Import Bulletin
Note 1012307.6 Moving Tables Between Tablespaces Using Export/Import

Solution Description:
=====================

You need to use the INDEXFILE option, edit the file to change the default
tablespace, use script to precreate the table in the new tablespace, and then
do the import.


EXAMPLE TEST TO CHANGE DEFAULT TABLESPACE USING INDEXFILE OPTION:
================================================================

1) Create user REPORTS with default tablespace of TOOLS.

Oracle Server Manager Release 2.3.4.0.0 - Production

Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.

Oracle7 Server Release 7.3.4.1.0 - Production
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 2.3.4.1.0 - Production

SVRMGR> connect internal
Connected.
SVRMGR> CREATE USER reports IDENTIFIED BY reports;
Statement processed.
SVRMGR> GRANT DBA TO reports;
Statement processed.
SVRMGR> GRANT UNLIMITED TABLESPACE TO reports;
Statement processed.
SVRMGR> ALTER USER reports IDENTIFIED BY reports DEFAULT TABLESPACE tools;
Statement processed.
SVRMGR> ALTER USER reports QUOTA UNLIMITED ON tools;
Statement processed.
SVRMGR> CONNECT reports/reports;
Connected.
SVRMGR> CREATE TABLE test_table AS SELECT * FROM all_objects;
Statement processed.
SVRMGR> SELECT owner, table_name, tablespace_name FROM DBA_TABLES where owner =
'REPORTS';

OWNER TABLE_NAME TABLESPACE_NAME

------------------------------ ------------------------------

REPORTS TEST_TABLE TOOLS

1 row selected.


2) Do export of TEST_TABLE.

[otchp1]/u02/home/usupport> exp reports/reports tables="(test_table)" file=
sadavid.dmp log=exp1.log

Export: Release 7.3.4.1.0 - Production on Wed Feb 24 19:39:24 1999

Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.


Connected to: Oracle7 Server Release 7.3.4.1.0 - Production
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 2.3.4.1.0 - Production
Export done in US7ASCII character set

About to export specified tables via Conventional Path ...
. . exporting table TEST_TABLE 1891 rows exported
Export terminated successfully without warnings.


3) Create user SADAVID with default tablespace of USERS.

SVRMGR> CREATE USER sadavid IDENTIFIED BY sadavid;
Statement processed.
SVRMGR> GRANT DBA TO sadavid;
Statement processed.
SVRMGR> GRANT UNLIMITED TABLESPACE TO sadavid;
Statement processed.
SVRMGR> ALTER USER sadavid IDENTIFIED BY sadavid DEFAULT TABLESPACE users;
Statement processed.
SVRMGR> ALTER USER sadavid QUOTA UNLIMITED ON users;
Statement processed.


4) Do import as SADAVID. Note that the default tablespace does not change.

[otchp1]/u02/home/usupport> imp sadavid/sadavid file=sadavid.dmp tables=
"(test_table)"

Import: Release 7.3.4.1.0 - Production on Wed Feb 24 19:44:08 1999

Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.


Connected to: Oracle7 Server Release 7.3.4.1.0 - Production
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 2.3.4.1.0 - Production

Export file created by EXPORT:V07.03.04 via conventional path

Warning: the objects were exported by REPORTS, not by you

. importing REPORTS's objects into SADAVID
. . importing table "TEST_TABLE" 1891 rows imported
Import terminated successfully without warnings.


5) NOTE: The table was created in TOOLS tablespace instead of USERS
tablespace.

SVRMGR> select owner, table_name, tablespace_name from dba_tables
2> where owner = 'SADAVID';

OWNER TABLE_NAMETABLESPACE_NAME

------------------------------ ------------------------------
SADAVID TEST_TABLE TOOLS

1 row selected.
SVRMGR> connect sadavid/sadavid
Connected.


6) Do import using INDEXFILE option.

[otchp1]/u02/home/usupport> imp sadavid/sadavid file=sadavid.dmp tables=
"(test_table)" INDEXFILE=indexfile.log

Import: Release 7.3.4.1.0 - Production on Wed Feb 24 20:04:39 1999

Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.


Connected to: Oracle7 Server Release 7.3.4.1.0 - Production
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 2.3.4.1.0 - Production

Export file created by EXPORT:V07.03.04 via conventional path

Warning: the objects were exported by REPORTS, not by you

. . skipping table "TEST_TABLE"

Import terminated successfully without warnings.


7) Edit "indexfile.sql", log in as sadavid, and run script to precreate the
table.


CREATE TABLE "SADAVID"."TEST_TABLE" ("OWNER" VARCHAR2(30) NOT NULL,
"OBJECT_NAME" VARCHAR2(30) NOT NULL, "OBJECT_ID" NUMBER NOT NULL,
"OBJECT_TYPE" VARCHAR2(12), "CREATED" DATE NOT NULL, "LAST_DDL_TIME"
DATE NOT NULL, "TIMESTAMP" VARCHAR2(75), "STATUS" VARCHAR2(7))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 266240
NEXT 126976 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1
FREELIST GROUPS 1) TABLESPACE "USERS" ;

SVRMGR> connect sadavid/sadavid
Connected.
SVRMGR> drop table test_table;
Statement processed.
SVRMGR> @indexfile.sql
Statement processed.


8)Run import with IGNORE=Y to populate the new table:

[otchp1]/u02/home/usupport> imp sadavid/sadavid file=sadavid.dmp tables=
"(test_table)" ignore=y

Import: Release 7.3.4.1.0 - Production on Wed Feb 24 20:12:42 1999

Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.


Connected to: Oracle7 Server Release 7.3.4.1.0 - Production
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 2.3.4.1.0 - Production

Export file created by EXPORT:V07.03.04 via conventional path

Warning: the objects were exported by REPORTS, not by you

. importing REPORTS's objects into SADAVID
. . importing table "TEST_TABLE" 1891 rows imported
Import terminated successfully without warnings.


9) NOTE TEST_TABLE HAS A NEW TABLESPACE OF USERS:

SVRMGR> select owner, table_name, tablespace_name from dba_tables where owner =
'SADAVID';

OWNER TABLE_NAME TABLESPACE_NAME

------------------------------ ------------------------------
SADAVID TEST_TABLE USERS

1 row selected.


Solution Explanation:
=====================

By default, an import will try to import tables into the same tablespace they
were exported from. There is not an equivalent FROM_TABLESPACE/TO_TABLESPACE
similar to FROMUSER/TOUSER. So you have to force the import utility into
placing the objects into another tablespace.

FN . (Extract Numeric Value from a Column)

CREATE OR REPLACE FUNCTION GET_NUMBER
(NM VARCHAR2)
RETURN
VARCHAR2
IS
ST NUMBER(4) := 0;
EN NUMBER(4) := 0;
BEGIN
IF(nm IS NOT NULL) THEN

FOR i IN 1..LENGTH(nm) LOOP
IF ASCII(SUBSTR(nm,i,1)) >48 AND ASCII(SUBSTR(nm,i,1)) <57THEN
st:=i;
EXIT;
END IF;
END LOOP;

FOR i IN st..LENGTH(nm) LOOP
IF ASCII(SUBSTR(nm,i,1)) <48 THEN
EN:=i;
EXIT;
END IF;
END LOOP;
IF(st!=0) THEN
RETURN (SUBSTR(nm,st,EN-st));
ELSE
RETURN ' ';
END IF;
ELSE
RETURN ' ';
END IF;

END;
/

LOG ON - LOG OFF Trigger for User

*********************************************************************************
create table
stats$user_log
(
user_id varchar2(30),
session_id number(8),
host varchar2(30),
last_program varchar2(48),
last_action varchar2(32),
last_module varchar2(32),
logon_day date,
logon_time varchar2(10),
logoff_day date,
logoff_time varchar2(10),
elapsed_minutes number(8)
)
;

*********************************************************************************

create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into stats$user_log values(
user,
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
null,
null,
null,
sysdate,
to_char(sysdate, 'hh24:mi:ss'),
null,
null,
null
);
END;
/
*********************************************************************************
---------------Designing the logoff trigger--------------------------------------
*********************************************************************************
create or replace trigger
logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
-- ***************************************************
-- Update the last action accessed
-- ***************************************************
update
stats$user_log
set
last_action = (select action from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
--***************************************************
-- Update the last program accessed
-- ***************************************************
update
stats$user_log
set
last_program = (select program from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the last module accessed
-- ***************************************************
update
stats$user_log
set
last_module = (select module from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff day
-- ***************************************************
update
stats$user_log
set
logoff_day = sysdate
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff time
-- ***************************************************
update
stats$user_log
set
logoff_time = to_char(sysdate, 'hh24:mi:ss')
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Compute the elapsed minutes
-- ***************************************************
update
stats$user_log
set
elapsed_minutes =
round((logoff_day - logon_day)*1440)
where
sys_context('USERENV','SESSIONID') = session_id;
END;
/

*******Here is a script to track the activity of a specific user:*****************

CREATE OR REPLACE TRIGGER "LOGON_AUDIT_TRIGGER" AFTER
LOGON ON DATABASE
DECLARE
sess number(10);
prog varchar2(70);
BEGIN
IF sys_context('USERENV','BG_JOB_ID') is null and user = 'MYUSERNAME' THEN
sess := sys_context('USERENV','SESSIONID');
SELECT program INTO prog FROM v$session WHERE audsid = sess
and rownum<=1;
INSERT INTO stats$user_log VALUES (
user,sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
prog,
sysdate,
sys_context('USERENV','OS_USER'));
END IF;
END;
/

Split Procedure (Token) Fn

CREATE OR REPLACE FUNCTION get_new(
the_list VARCHAR2,
the_index NUMBER,
delim VARCHAR2
)
RETURN VARCHAR2
IS
start_pos NUMBER;
end_pos NUMBER;
BEGIN
IF the_index = 1 THEN
start_pos := 1;
ELSE
start_pos := INSTR(the_list,delim,1,the_index - 1);
IF start_pos = 0 THEN
RETURN NULL;
ELSE
start_pos := start_pos + LENGTH(delim);
END IF;
END IF;

end_pos := INSTR(the_list,delim,start_pos,1);

IF end_pos = 0 THEN
RETURN SUBSTR(the_list,start_pos);
ELSE
RETURN SUBSTR(the_list,start_pos,end_pos - start_pos);
END IF;

END get_new;
/
*************************************************************************************
*************************************************************************************
-- The pl/sql Oracle version for the Tokenizer procedure
Create or Replace
procedure tokenizer ( iStart IN NUMBER,
sPattern in VARCHAR2,
sBuffer in VARCHAR2,
sResult OUT VARCHAR2,
iNextPos OUT NUMBER)
AS
nPos1 number;
nPos2 number;
BEGIN
nPos1 := Instr (sBuffer ,sPattern ,iStart);
IF nPos1 = 0 then
sResult := NULL ;
ELSE
nPos2 := Instr (sBuffer ,sPattern ,nPos1 + 1);
IF nPos2 = 0 then
sResult := Rtrim(Ltrim(Substr(sBuffer ,nPos1+1)));
iNextPos := nPos2;
else
sResult := Substr(sBuffer ,nPos1 + 1 , nPos2 - nPos1 - 1);
iNextPos := nPos2;
END IF;
END IF;
END tokenizer ;
/

-- a Procedure to Test the Tokenizer
Create or Replace procedure sp_test_tokenizer
as
sepr varchar2(1);
sbuf varchar2(200);
sres varchar2(200);
pos number;
istart number;
begin

sbuf := '@0@11@222@3333@44444@555555@6666666@77777777@888888888';
sepr := '@';
istart := 1;
tokenizer (istart ,sepr,sbuf,sres,pos);
if (pos <> 0) then
dbms_output.put_line (sres);
end if;
while (pos <> 0)
loop
istart := pos;
tokenizer (istart ,sepr,sbuf,sres,pos );
dbms_output.put_line (sres);
end loop;
END sp_test_tokenizer;
/

-- Test the Procedure From sqlPlus
set serveroutput on
exec sp_test_tokenizer;

Data Guard

Set Oraclesid=oms
sqlplus /nolog
conn sys/sys as sysdba
17:00:23 SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

Elapsed: 00:00:00.01
17:00:32 SQL> alter system set DG_BROKER_START = true;
(both on primary and standby)

System altered.

Elapsed: 00:00:01.45
17:00:35 SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

C:\Documents and Settings\oracle.dba>dgmgrl
DGMGRL for 32-bit Windows: Version 10.2.0.1.0 - Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys
Connected.

DGMGRL> create configuration 'omssty' as primary database is 'oms' connect identifier is oms;
Configuration "omssty" created with primary database "OMS"

DGMGRL> add database 'testdb' as connect identifier is testdb maintained as physical;
Database "testdb" added

DGMGRL> enable configuration
Enabled.

DGMGRL> show configuration;

Configuration
Name: omssty
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
OMS - Primary database
testdb - Physical standby database

Current status for "omssty":

DGMGRL> REMOVE CONFIGURATION; (For Deleting the configuration)

DGMGRL> SHOW DATABASE VERBOSE OMS;

DGMGRL> SHOW DATABASE VERBOSE testdb;

StandBy database

Primary init.ora file
NOTE:- LOG_ARCHIVE_DEST_STATE_2=ENABLE parameter should be Defer and Enable only at the time of creation of standby Controle file.
***************************************
*.db_cache_size=324288000
*.java_pool_size=4194304
*.large_pool_size=4194304
*.shared_pool_size=192937984
*.streams_pool_size=0
*.audit_file_dest='D:\oracle\product\10.2.0\admin\OMS\adump'
*.background_dump_dest='D:\oracle\product\10.2.0\admin\OMS\bdump'
*.compatible='10.2.0.1.0'
*.control_files='D:\oracle\product\10.2.0\oradata\OMS\control01.ctl','D:\oracle\product\10.2.0\oradata\OMS\control02.ctl','D:\oracle\product\10.2.0\oradata\OMS\control03.ctl'
*.core_dump_dest='D:\oracle\product\10.2.0\admin\OMS\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='OMS'
*.db_recovery_file_dest='D:\oracle\product\10.2.0\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=OMSXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=634003200
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\product\10.2.0\admin\OMS\udump'
DB_UNIQUE_NAME=OMS
LOG_ARCHIVE_CONFIG= 'DG_CONFIG=(OMS,TESTDB)'
LOG_ARCHIVE_DEST_1= 'LOCATION=D:\oracle\product\10.2.0\flash_recovery_area\OMS
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=OMS'
LOG_ARCHIVE_DEST_2='SERVICE=TESTDB
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=TESTDB'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
STANDBY_ARCHIVE_DEST='E:\PROD\flash_recovery_area\TESTDB'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
DB_FILE_NAME_CONVERT= ('D:\oracle\product\10.2.0\oradata\OMS\' , 'E:\PROD\ORADATA\TESTDB')
LOG_FILE_NAME_CONVERT= ('D:\oracle\product\10.2.0\oradata\OMS\' , 'E:\PROD\ORADATA\TESTDB')
STANDBY_FILE_MANAGEMENT=auto
FAL_SERVER=TESTDB
FAL_CLIENT=OMS


set oracle_sid=oms
sql
conn sys/sys as sysdba
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;


ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
('D:\oracle\product\10.2.0\oradata\OMS\stylog4.ora')SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
('D:\oracle\product\10.2.0\oradata\OMS\stylog5.ora')SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
('D:\oracle\product\10.2.0\oradata\OMS\stylog6.ora')SIZE 500M;

SHUTDOWN IMMEDIATE;

$copy D:\oracle\product\10.2.0\oradata\OMS\* E:\PROD\oradata\TESTDB

STARTUP MOUNT pfile=d:\ as mention above' ;
ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'D:\oracle\product\stdby.ctl';
ALTER DATABASE OPEN;


*******on standby******

Standby init.ora file
***************************************
*.db_cache_size=324288000
*.java_pool_size=4194304
*.large_pool_size=4194304
*.shared_pool_size=192937984
*.streams_pool_size=0
*.audit_file_dest='E:\PROD\admin\testdb\adump\'
*.background_dump_dest='E:\PROD\admin\testdb\bdump\'
*.compatible='10.2.0.1.0'
*.control_files='E:\PROD\oradata\testdb\control01.ctl','E:\PROD\oradata\testdb\control02.ctl','E:\PROD\oradata\testdb\control03.ctl'
*.core_dump_dest='E:\PROD\admin\testdb\cdump\'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='OMS'
*.db_recovery_file_dest='E:\PROD\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=OMSXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=634003200
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='E:\PROD\admin\testdb\udump\'
DB_UNIQUE_NAME=TESTDB
LOG_ARCHIVE_CONFIG= 'DG_CONFIG=(TESTDB,OMS)'
LOG_ARCHIVE_DEST_1= 'LOCATION=E:\PROD\flash_recovery_area\testdb
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=TESTDB'
LOG_ARCHIVE_DEST_2='SERVICE=OMS
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=OMS'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
STANDBY_ARCHIVE_DEST='E:\PROD\flash_recovery_area\TESTDB'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
DB_FILE_NAME_CONVERT= ('D:\oracle\product\10.2.0\oradata\OMS\','E:\PROD\oradata\TESTDB\')
LOG_FILE_NAME_CONVERT= ('D:\oracle\product\10.2.0\oradata\OMS\','E:\PROD\oradata\TESTDB\')
STANDBY_FILE_MANAGEMENT=auto
FAL_SERVER=OMS
FAL_CLIENT=TESTDB
****************************
Rename controle file
'D:\oracle\product\stdby.ctl' to location E:\PROD\oradata\TESTDB\

STARTUP MOUNT pfile='d:\as mention above';

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;


Test Real time apply :

Primary : create a table 'test' and insert a record.
INSERT INTO test VALUES ( 101, 'testing');
COMMIT;
Do not switch a log
On the standby :
SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;

Notice the block# for the RFS and MRP0 increasing
Cancel out of real time apply and open it in read only mode

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN READ ONLY;
SELECT * FROM.test;
You will see the committed record.
Place the standby back in managed recover mode
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENTLOGFILE DISCONNECT;


NOTE : Be Care full While Creating the Password File At Stand by location should be same Pass as Primary

Corruption in block

Full details of using DBMS_REPAIR for this can be found in the
documentation but in summary the steps are:

- Use DBMS_REPAIR.ADMIN_TABLES to create the admin tables
- Use DBMS_REPAIR.CHECK_OBJECT to find problem blocks
- Get any good data out of problem blocks before corrupting them.
- Use DBMS_REPAIR.FIX_CORRUPT_BLOCKS to mark the found problem
blocks as corrupt so that they will then signal ORA-1578
- If required use DBMS_REPAIR.SKIP_CORRUPT_BLOCKS to skip corrupt
blocks on the table.

Hot Backup Script

TIP: Click help for a detailed explanation of this page.
Bookmark Go to End

Subject: Scripts To Perform Dynamic Hot/Online Backups
Doc ID: Note:152111.1 Type: BULLETIN
Last Revision Date: 20-MAY-2007 Status: PUBLISHED


PURPOSE
-------

For this article basis database knowledge is necessary.
This article will explain how you can create online backups and it contains 2
scripts which can be used to create you hotbackup's.
In this article the terms hotbackup and online backup will be used.

The topics explained are:
1) check archive log mode
2) change archive log mode
3) script hotbackup.bat + explanation
4) script hotbackup.sql + explanation
5) Scheduling


How to create online backups.
-----------------------------

--- 1 CHECK ARCHIVE LOG MODE ---

To create online backups you database has to be in archivelog mode.
To check if this is the case execute the next query in sqlplus connecting as
system

select log_mode from v$database;

If the the result is NOARCHIVELOG this must be changed, see topic 2. If the
result is ARCHIVELOG go to topic 3.

--- 2 CHANGE ARCHIVE LOG MODE ---

To change you settings from noarchivelog mode to archive log mode read Note 69739.1
This note explains in detail how to change from noarchive log to archivelog and visa versa.


--- 3 SCRIPT HOTBACKUP.BAT ---

The script will start with setting 4 parameters. These parameters must be changed to the values
you would like to use.
parameter SCRIPTS => location of the hotbackup.bat and hotbackup.sql
parameter ORACLE_SID => SID name of your database
parameter TEMP_DIR => location of a temporary directory (example c:\temp)
parameter DEST_DIR => location where the copys of the datafiles, controlfile will be created

After this the script will check for old logfiles, datafiles and will create the destination
directory if it doesn't exists.

The next part of deleting or renaming old datafiles can be changed. In this script the renaming of old
datafiles isn't used. But if you want to save a hotbackup of the day before remove the REM and added the
rem the next part.

Now the hotbackup.sql script is called and the parameters are also passed. For more info about this
script see topic 4.

When the hotbackup.sql is executed, the log files (2 in total) will be merged together.
When done the program will exit.


Rem ****************************************************************************************
Rem
Rem Script to create hot backup of database
Rem
Rem 09-07-2001 B. de Cock Buning
Rem created script for hotbackup.
Rem 11-07-2001 B. de Cock Buning
Rem changed script for dynamic locations (add parameters)
Rem
Rem

Set SCRIPTS=d:\oracle\admin
Set ORACLE_SID=prod
Set TEMP_DIR=c:\temp
Set DEST_DIR=E:\oracle\backup

Rem
Rem Delete old hotbackup.log file
Rem
if exist %TEMP_DIR%\hotbackup.log del %TEMP_DIR%\hotbackup.log /q
if exist %DEST_DIR%\%ORACLE_SID%\Log\backup.log del %DEST_DIR%\%ORACLE_SID%\Log\backup.log

Rem
Rem Create backup location
Rem
if not exist %DEST_DIR%\%ORACLE_SID% md %DEST_DIR%\%ORACLE_SID% >> %TEMP_DIR%\hotbackup.log


Rem
Rem Go to the log directory
Rem
if not exist %DEST_DIR%\%ORACLE_SID%\Log md %DEST_DIR%\%ORACLE_SID%\Log >> %TEMP_DIR%\hotbackup.log
cd %DEST_DIR%\%ORACLE_SID%\Log
cd


Rem
Rem Delete old log files
Rem
if exist *.log del *.log /q >> %TEMP_DIR%\hotbackup.log
if exist *.lst del *.lst /q >> %TEMP_DIR%\hotbackup.log

Rem
Rem Rename the old backup files
Rem Create a copy of the current backup files
Rem
Rem if exist %DEST_DIR%\%ORACLE_SID%\*.ctl ren %DEST_DIR%\%ORACLE_SID%\*.ctl *.ctl.old >> %TEMP_DIR%\hotbackup.log
Rem if exist %DEST_DIR%\%ORACLE_SID%\*.log ren %DEST_DIR%\%ORACLE_SID%\*.log *.log.old >> %TEMP_DIR%\hotbackup.log
Rem if exist %DEST_DIR%\%ORACLE_SID%\*.dbf ren %DEST_DIR%\%ORACLE_SID%\*.dbf *.dbf.old >> %TEMP_DIR%\hotbackup.log

Rem
Rem Delete the old backup files
Rem Saves no copy of current backup
Rem
if exist %DEST_DIR%\%ORACLE_SID%\*.ctl del %DEST_DIR%\%ORACLE_SID%\*.ctl /q >> %TEMP_DIR%\hotbackup.log
if exist %DEST_DIR%\%ORACLE_SID%\*.log del %DEST_DIR%\%ORACLE_SID%\*.log /q >> %TEMP_DIR%\hotbackup.log
if exist %DEST_DIR%\%ORACLE_SID%\*.dbf del %DEST_DIR%\%ORACLE_SID%\*.dbf /q >> %TEMP_DIR%\hotbackup.log


Rem
Rem Start the hot backup script
Rem
D:\Oracle\Oracle817\Bin\sqlplus "sys/oracle@%ORACLE_SID% as sysdba" @%SCRIPTS%\hotbackup.sql %ORACLE_SID% %TEMP_DIR% %DEST_DIR%

type %TEMP_DIR%\hotbackup.log >> %DEST_DIR%\%ORACLE_SID%\Log\backup.log

exit

Rem ******************************************************************************************************************************
Rem end of script


--- 4 SCRIPT HOTBACKUP.SQL ---

This script will create a spool file wich will be save in your temporary location.
The spool file will be used to finally execute the statement for the backup.
The spool file is filled with the result of the queries.

NOTE: when you have more then 2 controlfiles or the names are different then mentioned
in the script (remark 3) change the names to the names you use or add a controlfile.
This because when recovery is needed you will not have any problem with the naming used
and the names define in your parameter file of the parameter control_files.


remark HOTBACKUP.sql
remark
remark Make a hot backup of the database.
remark
remark Parameters: 1 - SID for the database
remark 2 - Temp for temporary directory
remark 3 - Destination for locatie of the backup
remark
remark
remark 1- for all tablespaces which are not ACTIVE
remark make tablespaces active
remark 2- for all loggroups
remark logswitch, so all changes/ inserts in redologfiles are archiveerd
remark 3- for every controlfile
remark make backup of the controlfile
remark 4- backup of the controlfile to trace directory
remark 5- for every tablespace
remark set tablespace in backup mode
remark copy datafile to destination directory
remark set tablespace in active mode
remark 6- show status of tablespaces
remark
remark 09-07-2001 B. de Cock Buning
remark Create script for dynamic hotbackup
remark


set pagesize 0
set feedback off
set linesize 132
set trimspool on
set verify off

define dbsid=&1
define temp =&2
define destination=&3

spool &temp\backup_prod.sql


select 'Rem Start tijd:' || to_char( sysdate, 'dd-mm-yyyy hh24:mi' ) from dual;
prompt Spool &destination\&dbsid\log\backup.log;

remark 1
select 'alter tablespace '||t.name||' end backup;'
from sys.file$ f, v$backup v, sys.ts$ t
where v.file# = f.file#
and f.ts# = t.ts#
and v.status = 'ACTIVE';

remark 2
select 'alter system switch logfile;'
from sys.v_$log;

remark 3
prompt alter database backup controlfile to '&destination\&dbsid\control01.ctl';;
prompt alter database backup controlfile to '&destination\&dbsid\control02.ctl';;

remark 4
prompt alter database backup controlfile to trace;;

remark 5
select 'alter tablespace '||tablespace_name||' begin backup;' || chr(10) ||
'host copy '||file_name||' &destination\&dbsid\ >> &temp\hotbackup.log' || chr(10) ||
'alter tablespace '||tablespace_name||' end backup;' || chr(10)
from dba_data_files;

remark 6
prompt select file#,status, to_char(time,'DD-MM-YYYY HH24:Mi') TIME from v$backup;;

prompt Spool off;;
prompt exit;;

spool off

set feedback on
set heading on
set pagesize 40
set linesize 80


--- 5 SCHEDULE SCRIPT ---

To schedule the scripts for execution at night you needed to use a tool to schedule it.
One easy to use tool is winat. This tool is included in the windows recourse kit.

When you install this tool, make sure the service schedule is running and also starts
automaticly. To check this after the installation open your control panel with subtopic services
and check the service schedule it must be up and running.


After all these steps you have a good hotbackup senario. Of course you have to
test your script for errors.
At the destination location there will be a subfolder with the name log containing
a file called backup.log. This file will display the result of the backup. If there
is any kind of error during the execution check this file and solve the problem.

Data Pump (Oracle 10g New Feature)

***********************************************************************************For DataPump YOu need to create the Directory where BAckup will be stored.
CREATE DIRECTORY ABC_dir AS '/oradata/backup/backup_ABC';
Then Grant the User who will perfomr the Export (sys is by default granted).
grant read, write on directory ABC_dir to system;grant read, write on directory ABC_dir to ilrpoly;
expdp system/sys@ilrpoly directory=ABC_dir DUMPFILE=ABC_FULLEXP_TODAY.dmp LOGFILE=ABC_FULLEXP_TODAY.log full=y;****************************************************************************************************************For Importing user need to copy the file in the Creted directory .This will Not Import from any path.
impdp system/sys directory=ABC_backup dumpfile=EXP_ABC_WED_MORNING.DMP tables="USSTATE" *****************************************************************************************Ref = Part No. B10825-01
*******************************************************************************************

To Check the Performance

rem ***********************************************************************rem Program: dba_dbperf_w.sqlrem Purpose: Oracle 9.X Weekly Reportrem Author: Aleksandr Lembrikov, Ernst & Young, LLP, IT IAS (created)rem Aramando Plascencia, WEA Corporation, Time Warner Music (tested)rem Date: 31-Aug-2006rem Comments:rem This script will run against Oracle Version 9.x and is usedrem to assist the auditor or DBA in documenting the databaserem environment. Most statements use dba tables or the v$ viewsrem so it is recommended this script be run by a DBA.remrem Note: Some sql scripts have been gathered from Oracle manuals andrem the author wishes to acknowledge this fact upfront.remrem Parameters:rem This script takes as a parameter the database sid.rem Audit Trail:rem **************************************************************************remset linesize 90set pagesize 999set verify offset feedback offset maxdata 4000set arraysize 1rem
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
accept s_id prompt 'Enter Oracle SID:'
spool dba_dbperf_w_&&s_id
set heading offselect 'V9 Weekly Database Report for SID=&&s_id generated on ' to_char(sysdate,'DD-MON-YY HH24:MI:SS')' by ' userfrom sys.dual/set heading onpromptprompt *********************************************************************prompt * Tuning: Contention for dispatchersprompt *** Add more dispatchers if busy rate is > 50%prompt *prompt *********************************************************************column name format a15column status format a8 heading "STATUS"column pct_busy format 999.99 heading "%Busy"
select name, network, status, (busy/(busy+idle))*100 Pct_Busyfrom v$dispatcherorder by name, network/promptprompt *********************************************************************prompt * Tuning: Contention for multi-threaded serversprompt * Add more mts servers if busy rate is > 50%prompt * prompt *********************************************************************column name format a15column status format a8 heading "STATUS"column pct_busy format 999.99 heading "%Busy"
select name, status, requests, (busy/(busy+idle))*100 Pct_Busyfrom v$shared_serverorder by name/promptprompt *********************************************************************prompt * Database export informationprompt *********************************************************************
select * from sys.dba_exp_filesorder by timestamp/promptprompt *********************************************************************prompt * Buffer cache hit ratioprompt * If hit ratio is less than 70% then increase db_block_buffers.prompt * HitRatio=(1-(physical reads/(db block gets+consistent gets)))*100prompt *********************************************************************column name format a33 heading "TYPE"column value format 999,999,999,999column hitratio format 999.99 heading "Hit Ratio"
select name, valuefrom v$sysstatwhere name in ('db block gets', 'consistent gets', 'physical reads')order by name/select ((cur.value+con.value)/(( cur.value+con.value)+phy.value))*100 hitratiofrom v$sysstat cur, v$sysstat con, v$sysstat phywhere cur.name = 'db block gets' and con.name = 'consistent gets' and phy.name = 'physical reads'/promptprompt *********************************************************************prompt * Library cache reload ratioprompt * This ratio should be very low indicating that the library cacheprompt * is large enough. If high, increase shared_pool_size.prompt *********************************************************************column libcache format 99.99 heading 'Library CacheReload %'
select sum(reloads)/sum(pins) *100 libcachefrom v$librarycache/promptprompt *********************************************************************prompt * Library cache hit ratioprompt * GETHITRATIO is number of GETHITS/GETSprompt * PINHIT RATIO is number of PINHITS/PINS prompt * A number close to 1 indicates that most objects have been cachedprompt *********************************************************************column namespace format a20 heading 'NAME'column gets format 9999999999 heading 'GETS'column gethits format 9999999999 heading 'GETHITS'column gethitratio format 999.99 heading 'GET HITRATIO'column pins format 9999999999 heading 'PINS'column pinhits format 999999999 heading 'PINHITS'column pinhitratio format 999.99 heading 'PIN HITRATIO'
select namespace, gets, gethits, gethitratio, pins, pinhits, pinhitratiofrom v$librarycache/promptprompt *********************************************************************prompt * Data dictionary cache miss ratioprompt * Miss ratio should be below 5% to keep the data dictionaryprompt * cached in the SGA. Increase shared_pool_size to improve.prompt *********************************************************************column dictcache format 999.99 heading 'Dictionary CacheMiss Ratio %'
select sum(getmisses)/sum(gets) * 100 dictcachefrom v$rowcache/promptprompt *********************************************************************prompt * Data dictionary cache hit ratioprompt * Hit Ratio = sum(gets)/(sum(gets)+sum(getmisses))prompt *********************************************************************column hitratio format 999.99 heading "Hit Ratio"
select sum(gets)/(sum(gets)+sum(getmisses)) hitratiofrom v$rowcache/promptprompt *********************************************************************prompt * Data dictionary cache hit ratio by componentprompt *********************************************************************column a format a17 heading 'Cache'column b format 99999990 heading 'Gets'column c format 99999990 heading 'Misses'column d format 990 heading '%Miss'column e format 9990 heading 'Usage'column f format 99990 heading 'Count'column g format 990 heading '%Usage'select substr(parameter,4,17) a, gets b, getmisses c, decode(gets,0,0,(getmisses*100)/gets) d, usage e, count f, (usage*100)/count gfrom v$rowcachewhere count <> 0order by 1/promptprompt *********************************************************************prompt * In memory and disk sortsprompt * To make best use of sort memory the initial extent of yourprompt * sort-work tablespace should be sufficient to hold atprompt * least one sort run from memory, to reduce dynamic spaceprompt * allocation. Ratio of disk sorts to memory sorts shouldprompt * be less than 5%. Increase sort area size if higher.prompt *********************************************************************column name format a33 heading "SORT TYPE"column value format 999,999,999,999column parmval format a20
select name, valuefrom v$sysstatwhere name in ('sorts (memory)', 'sorts (disk)','sorts (rows)')/select round((100*b.value)/decode((a.value+b.value),0,1,(a.value+b.value)),2) "Pct Memory Sorts"from v$sysstat a, v$sysstat bwhere a.name = 'sorts (disk)' and b.name = 'sorts (memory)'/set heading offselect 'init.ora sort_area_size :', value "parmval"from v$parameter where name = 'sort_area_size'/promptprompt *********************************************************************prompt * Display cursor memoryprompt *********************************************************************
set heading onselect sum(250 * users_opening) "Open Cursor Memory"from v$sqlarea/promptprompt *********************************************************************prompt * Display memory set aside for packages prompt *********************************************************************
select sum(sharable_mem) "Shareable Memory"from v$db_object_cachewhere type = 'PACKAGE' OR type = 'PACKAGE_BODY'/promptprompt *********************************************************************prompt * Loads to db object cacheprompt *********************************************************************
select sum(loads), sum(executions), round((sum(loads) / sum(executions)) * 100, 4) Load_Percentfrom sys.v_$db_object_cache/promptprompt *********************************************************************prompt * Free buffer waitsprompt * The following script lists the number of waits forprompt * free buffers to be made available for new data being prompt * brought into the buffer cache. The output is often a sign prompt * of an untuned DBWR or a buffer cache that is too small. prompt *********************************************************************
select name, value from v$sysstat where name = 'free buffer waits'/promptprompt *********************************************************************prompt * Library cache contentionprompt *********************************************************************column name format a25 heading " TYPE"
select substr(name,1,25) name, gets, misses,immediate_gets, immediate_misses from v$latchwhere (misses > 0 or immediate_misses > 0) and name like 'library cach%'/promptprompt *********************************************************************prompt * Redo latch contentionprompt * There is contention if ratio of misses to getsprompt * exceeds 1%, or ratio of immediate_misses to theprompt * sum of immediate_gets + immediate_misses exceeds 1%prompt *********************************************************************column name format a25 heading 'Latch Type'column gets format 9999999999 heading 'Gets'column misses format 9999999 heading 'Misses'column immediate_gets format 999999999 heading 'Immediate Gets 'column immediate_misses format 999999999 heading 'Immediate Misses'
select name, gets, misses, immediate_gets, immediate_missesfrom v$latchwhere name like 'redo%'/promptprompt *********************************************************************prompt * Wait statisticsprompt * These numbers will tell you where there is contention inprompt * in the system. There will usually be some contention inprompt * any system, but if the ratio of waits for a particular operationprompt * starts to rise, you may need to add additional resources,prompt * such as more database buffers, log buffers, or rollbacks.prompt * Waits for any class should not be more 1% of total requests.prompt *********************************************************************column class heading 'Class Type'column count format 99,999,999 heading 'Occurrences'
select class, countfrom v$waitstatwhere count > 0order by class/promptprompt *********************************************************************prompt * Tablespace storage allocation and percent of space used ifprompt * used greater than 60%prompt *********************************************************************column "TBLSPC" format A25 heading 'TABLESPACE'column "ALLOC" format 999,999,999,999 heading 'ALLOCATED'column "USED" format 999,999,999,999 heading 'USED'column "UNUSED" format 999,999,999,999 heading 'UNUSED'column "USEDPCT" format 999.99
select u.tblspc "TBLSPC", a.fbytes "ALLOC", u.ebytes "USED", a.fbytes - u.ebytes "UNUSED", (u.ebytes/a.fbytes)*100 "USEDPCT"from (select tablespace_name tblspc, sum(bytes) ebytes from sys.dba_extents group by tablespace_name) u, (select tablespace_name tblspc, sum(bytes) fbytes from sys.dba_data_files group by tablespace_name) awhere u.tblspc = a.tblspc and (u.ebytes/a.fbytes)*100 > 60order by USEDPCT desc/promptprompt **************************************************************prompt * Tablespace fragmentation information (chunks > 2)prompt * If a ts has a large number of free chunks, thisprompt * indicates that it is a candidate for re-organization, asprompt * there may still be a lot of free space within the ts butprompt * it is all in small pieces (a Swiss cheese ts )!! Oracleprompt * will attempt to coalesce small contiguous free spaceprompt * extents but it incurs significant overhead to do so.prompt**************************************************************column fbytes format 999,999,999,999 heading 'Free Bytes'column fblocks format 999,999,999 heading 'Free DB Blocks'column kount format 99,999 heading 'Free Chunks'
select tablespace_name, sum(bytes) fbytes, sum(blocks) fblocks, count(*) kountfrom dba_free_spacegroup by tablespace_namehaving count(*) > 2order by tablespace_name/promptprompt**************************************************************prompt* Log history for the past dayprompt**************************************************************
select sequence#, first_change#, first_time, next_change#from sys.v_$log_historywhere first_time > sysdate-1/promptprompt *********************************************************************prompt * Tuning I/O: Physical reads, writesprompt * If phyblkrd is significantly larger than phyrds then a fullprompt * tablespace scan is occurring......prompt * prompt ********************************************************************* column fname format a25 trunc heading "Database Data Files"column phyrds format 999999999column phyblkrd format 999999999column phywrts format 999999999column phyblkwrt format 999999999column IO format 999,999,999 heading "TOTAL IO"
select substr(df.name,1,6)substr(df.name,15,19) fname, fs.phyrds, fs.phyblkrd, fs.phywrts, fs.phyblkwrt, fs.phyrds+fs.phywrts IOfrom v$datafile df, v$filestat fswhere df.file# = fs.file#order by IO desc/promptprompt *********************************************************************prompt * Detailed information on SGA and memoryprompt *********************************************************************column name format a33 heading "SGA Component"
select * from v$sgastatorder by pool, name/promptprompt **********************************************************************prompt * Executions of objects in the shared pool - most executions firstprompt **********************************************************************column owner format a10column oname format a40 trunc heading "OBJECT"
select owner, name' - 'type oname, executions, decode(substr(kept,1,1),'Y', ' ', '<<<> 50 and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')order by executions desc/promptprompt *********************************************************************prompt * Loads of objects in the shared pool - most loads firstprompt *********************************************************************column owner format a10column oname format a40 heading "OBJECT"
select owner, name' - 'type oname, loads, sharable_mem from v$db_object_cachewhere loads > 3 and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')order by loads desc/promptprompt *********************************************************************prompt * Memory usage of shared pool - biggest firstprompt *********************************************************************column owner format a10column oname format a40 heading "OBJECT"
select owner, name' - 'type oname, sharable_memfrom v$db_object_cachewhere sharable_mem > 10000 and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')order by sharable_mem desc/promptprompt *********************************************************************prompt * Objects that are keptprompt *********************************************************************column owner format a10column name format a25 heading "OBJECT"
select owner, name, executions, pins, keptfrom v$db_object_cachewhere kept = 'YES'order by owner, name/promptprompt *********************************************************************prompt * Rollback segment contentionprompt *********************************************************************column name format a20 heading "ROLLBACK SEGMENT"
select name, gets, waits, ((gets - waits) * 100)/gets hitratiofrom v$rollstat S, v$rollname Rwhere S.usn = R.usnorder by 1;promptprompt *********************************************************************prompt * Recommendation to add rollback segmentsprompt *********************************************************************column n1 format a15set heading off
select 'Try adding ' sum(decode(waits,0,0,1)) nl, 'rollback segments to avoid rollback header contention. 'from v$rollstat/promptprompt *********************************************************************prompt * Total number of rollback waits since the instance startedprompt *********************************************************************set heading on
select class, count from v$waitstatwhere class like '%undo%'/promptprompt *********************************************************************prompt * Rollback segment extends/shrinksprompt *********************************************************************
select usn, extends, shrinks, wraps, hwmsize, aveshrinkfrom v$rollstat;promptprompt *********************************************************************prompt * Redo log size problems (values should be near 0)prompt *********************************************************************column name format a35 heading 'Redo Log Stats'
select name, valuefrom v$sysstatwhere name in ('redo log space requests','redo buffer allocation retries')/promptprompt *********************************************************************prompt * Oracle User Ids with SYSTEM as their default or temp tablespacesprompt *prompt * Ensure that the default tablespace is NOT SYSTEM for any userprompt * except for SYSprompt *********************************************************************column username format a15 heading "USER"column default_ts format a15 heading "DEFLTTS"column temp_ts format a10 heading "TEMPTS"
select username, user_id, default_tablespace default_ts, temporary_tablespace temp_ts, createdfrom sys.dba_userswhere default_tablespace = 'SYSTEM' OR temporary_tablespace = 'SYSTEM'order by username/set heading off
select 'V9 End of Weekly Report 'to_char(sysdate,'DD-MON-YY HH24:MI:SS')' by 'userfrom sys.dual/spool offexit

IMP- EXP

CREATE DIRECTORY data_pump_dir AS '/usr/dba/dpumpfiles'
expdp hr/hr DUMPFILE=emp.dmp LOGFILE=emp.log TABLES=hr.employees
create directory pilot_bkp as 'I:\Pilot_Backup';select DIRECTORY_NAME,substr(DIRECTORY_PATH,1,100) from DBA_DIRECTORIES;
create directory bkpilrpoly as '/oradata/backup/backup_ilrpoly/';grant
grant read, write on directory ORCL_bkp to system;
CREATE DIRECTORY ilrpoly_dir AS '/oradata/backup/backup_ilrpoly';grant ilrpoly read, write on directory ilrpoly_dir to system;grant read, write on directory exp_dump to ilrpoly;
expdp system/sys@ilrpoly directory=ilrpoly_dir DUMPFILE=ILRPOLY_FULLEXP_TODAY.dmp LOGFILE=ILRPOLY_FULLEXP_TODAY.log full=y;
expdp system/sys@pilot directory=pilot_bkp DUMPFILE=Pilot_test.dmp LOGFILE=Pilot_test.log full=y;
SELECT OBJECT_PATH, COMMENTS FROM SCHEMA_EXPORT_OBJECTS2 WHERE OBJECT_PATH LIKE '%GRANT';
impdp sde/sde network_link=osiidb2rds tables=rds_photo.USSTATE remap_schema=rds_photo:sde EXCLUDE=CONSTRAINT REMAP_TABLESPACE=PHOTO_rds:sde
impdp wg5386/wg5386 network_link=pilot2rds tables=rds_photo.USSTATE remap_schema=rds_photo:wg5386 EXCLUDE=CONSTRAINT REMAP_TABLESPACE=PHOTO_rds:wg5386
create directory rds_backup as 'd:\rds_Backup';
create directory rds_backup as 'L:\';
create directory rds_backup as '/prod/backup/RDS/logical_backup/';grant read, write on directory rds_backup to system;
create directory pizza_backup as '/prod/backup/pizzahut/logical_backup/';grant read, write on directory rds_backup to system;
create directory rdsbackup as '/prod/data_backup/rds/';grant read, write on directory rdsbackup to system;
create directory ligbackup as '/prod/data_backup/lig/';grant read, write on directory rdsbackup to system;
grant read, write on directory pilot_bkp to system;
create directory geozip_bkp as 'F:\GEOZIP_BKP'grant read, write on directory geozip_bkp to system;expdp system/sys@pilot directory=geozip_bkp DUMPFILE=geozip_bkp_test.dmp LOGFILE=geozip_bkp_test.log schemas=GEOZIP
impdp system/sys directory=rds_backup dumpfile=EXP_RDS_WED_MORNING.DMP tables="USSTATE" REMAP_SCHEMA=RDS_PHOTO:sde REMAP_TABLESPACE=PHOTO_rds:sde
impdp system/sys directory=rds_backup dumpfile=EXP_RDS_WED_MORNING.DMP tables=rds_photo.USSTATE REMAP_SCHEMA=RDS_PHOTO:sde REMAP_TABLESPACE=PHOTO_rds:sde
exp system/system@admin1 file=fullexp.dmp log=fullexp.log owner='att','subtest','s7_db','sb_db','depr'
impdp rds/rds@rds directory=rds_backup dumpfile=EXP_RDS_MON_MORNING.DMP tables=rds.FARES_SC_KY_0207_V3 cONTENT=DATA_ONLY
exp system/sys@ilrpoly file=I:\ilrpoly_fullexp_18th_Apr.dmp log=I:\ilrpoly_fullexp_18th_Apr.log full=y buffer=200000 direct=y;imp sde/sde@osiiidb fromuser = sde touser=sde file=D:\OSIIIGC1_BACKUP\exp_OSIIIGC1_Mon_morning.dmp log =d:\uks_report.txt tables=tblinputvision ,tblgelsvision,tblerrorvision,tblqcerrorvision,tbluservision,tbloutputvision,tblprojectdetailvision

Trouble Shooting Script

create index IDX_COLMUN_NAME on FARES_INPUT_NATIONWIDE_V3(COLMUN_NAME);ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS1.DBF' AUTOEXTEND ON MAXSIZE UNLIMITEDALTER TABLESPACE "RDS" ADD DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS4.DBF' SIZE 10000M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
/*/*/*/*/*/*11:48 AM 6/8/2007*/*/*/*/*/*/CREATE SMALLFILE TABLESPACE "FOREST" DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\HLUD\FOREST.DBF' SIZE 200M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
drop index IDX_COLMUN_NAM;
create table Feb_19_FARES_INPUT_NATIONWIDE_V3 as select * from FARES_INPUT_NATIONWIDE_V3;
SELECT DBMS_METADATA.GET_DDL('INDEX',index_name) cr_index, index_name FROM user_INDEXES;SELECT dbms_metadata.get_ddl('INDEX', 'IDX_TBLTIMETRACKER_EMP_LOC') from dual;set long 600
select OWNER,object_name,object_type,status from dba_objects where status='INVALID' and OBJECT_TYPE <>'PROCEDURE';
ALTER TABLESPACE "RDS" ADD DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS1.dbf' SIZE 2000M
ALTER TABLESPACE "RDS" DEFAULT STORAGE ( INITIAL 5M MAXEXTENTS UNLIMITED );
ALTER TABLESPACE "RDS" ADD DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS1.dbf' SIZE 2000M
alter tablespace temp add tempfile 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\TEMP04.DBF' SIZE 9000M autoextend on ;
ALTER TABLESPACE "WH5847" DATAFILE 'D:\AAWORK\OSIIIDBF\WH5847_1.DBF' RESIZE 3000M;
ALTER DATABASE DATAFILE 'D:\AAWORK\OSIIIDBF\WVTES32.DBF' RESIZE 1000M
Wed Feb 21 11:36:23 2007Errors in file d:\oracle\product\10.1.0\admin\timeinc\udump\timeinc_ora_1708.trc:ORA-12571: TNS:packet writer failure
FARES_INPUT_NATIONWIDE_V3
ALTER TABLESPACE "TIMEINC" DEFAULT STORAGE ( MAXEXTENTS UNLIMITED )
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TIMEINC\TIMEINC.DBF' AUTOEXTEND ON MAXSIZE UNLIMITED
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TIMEINC\TIMEINC.DBF' RESIZE 1000M/*/*/*/*/*/*/*/*/*/*/*/3:17 PM 2/22/2007/*/*/*/*/*/orakill rds 1416/*/*/**/*/*/*/**/*/*/*/*/*/*/*/3:17 PM 2/22/2007/*/*/*/
CREATE USER "TELCOMWMS" ENTIFIED BY "TELCOMWMS" DEFAULT TABLESPACE "WVTEST6" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT CONNECT,resource,dba TO "TELCOMWMS";GRANT "DBA" TO "TELCOMWMS";GRANT "RESOURCE" TO "TELCOMWMS";
CREATE TABLESPACE "WG5386" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\PILOT\WG5386.dbf' SIZE 100M DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
ALTER TABLESPACE "WX" ADD DATAFILE '/oradata/ilrpolydata/wx1.dbf.dbf' SIZE 500M MAXEXTENTS UNLIMITED;
ALTER TABLESPACE "WX" DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
ALTER USER "WG5386" DEFAULT TABLESPACE "WG5386";
/*/*/*/*/*/*/*/*3:17 PM 2/22/2007/*/*/*/*/*/*/*/*/*/select max(maxquerylen) from v$undostat where begin_time>sysdate-10;/*/*/*/*/*/*///*3:18 PM 2/22/2007/*/*/*/*/*/*/*/*/*/
select round(bytes/1024/1024) from dba_segments where segment_name='FARES_INPUT_NATIONWIDE_V3';
analyze table FARES_INPUT_NATIONWIDE_V3 compute statistics;
dbms_stats.gather_table_stats(RDS, 'FARES_INPUT_NATIONWIDE_V3', granularity =>'DEFAULT',estimate_percent =>1);
SELECT 'rm ' name FROM v$archived_log WHERE registrar='RFS' AND applied='YES' AND completion_time < ownname="">'MY_USER',tabname=>'MY_TABLE',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,DEGREE=>2);
In Oracle 10 It Automatic Gather Statistics for the table who is not analyzed.
/*/*/*/*/*/*/*/*/**/*/*/*//*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
CREATE TABLESPACE "ATT" LOGGING DATAFILE 'D:\oracle\oradata\ilr\att1.dbf' SIZE 50M DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
ALTER USER "ATT" DEFAULT TABLESPACE "ATT";
ALTER TABLESPACE "WX" ADD DATAFILE '/oradata/ilrpolydata/wx1.dbf.dbf' SIZE 500M MAXEXTENTS UNLIMITED;
ALTER TABLESPACE "TEMP" ADD DATAFILE D:\ORACLE\ORADATA\ILR\TEMP02.DBF SIZE 500M MAXEXTENTS UNLIMITED autoextend on;
ALTER TABLESPACE "TEMP" ADD TEMPFILE 'D:\ORACLE\ORADATA\ILR\TEMP02.DBF' SIZE 500 M ;
ALTER TABLESPACE "ATT" DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
CREATE TABLESPACE "att" LOGGING DATAFILE 'D:\aawork\osiiidbf\att.dbf' SIZE 5M DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
ALTER USER "att" DEFAULT TABLESPACE "att";
CREATE TABLESPACE "RDSTEST" LOGGING DATAFILE 'D:\aawork\osiiidbf\RDSTEST1.dbf' SIZE 7000M DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
ALTER USER "RDSTEST" DEFAULT TABLESPACE "RDSTEST";
ALTER DATABASE TEMPFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\TEMP02.DBF' OFFLINE
/*/*/*/*/*/*/*/*/*/12:07 PM 2/26/2007/*/*/*/**/*/*/**/*/*/*/*/**/*/*/*/*/CREATE TABLESPACE "RDS_INDEX" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS_INDEX.dbf' SIZE 100M DEFAULT STORAGE ( NEXT 5M MAXEXTENTS UNLIMITED )
/*/*/*/*/*/*
ALTER TABLESPACE "RDS" ADD DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS1.DBF' SIZE 1000M AUTOEXTEND ON ;
ALTER TABLESPACE "RDS_INDEX" ADD DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS_INDEX2.DBF' SIZE 1000M AUTOEXTEND ON ;
/*/*/*/*/*/*
CREATE INDEX ord_customer_ix_demo ON orders (order_mode) nOSORT NOLOGGING;
tablespace rds_index

/*/*/*/*/*/*/*/*/*/*
i/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/**/*/*/*/**/*/*/**//*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/**/*/*/*/**/*/*/**/
CREATE PUBLIC DATABASE LINK ilr2databank CONNECT TO regp IDENTIFIED BY regp USING 'databank';
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/**/*/*/*/**/*/*/**//*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/**/*/*/*/**/*/*/**//*/*/*/*/
If you have access to the dba_users, then it's possible to brute force the password, how long it would take depends on how complicated it is.
Here is a list of brute force tools: http://www.red-database-security.com/whitepaper/oracle_password_cracker.html
Here is the best one: http://www.toolcrypt.org/index.html?orabf
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/ALTER SYSTEM SET pga_aggregate_target = 104857600 SCOPE=BOTHALTER SYSTEM DISCONNECT SESSION '158,3868' IMMEDIATE/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
exec dbms_stats.gather_table_stats(ownname='RDS',tabname='FARES_0207_WORK_V1')

DECLARE X NUMBER;BEGIN SYS.DBMS_JOB.SUBMIT ( job => X ,what => 'DELETE TBLFINALDIC;COMMIT;' ,next_date => TO_DATE('13/03/2007 12:28:15','dd/mm/yyyy hh24:mi:ss') ,INTERVAL => 'SYSDATE+05/1440 ' ,no_parse => TRUE );END;/
begin
dbms_stats.gather_schema_stats( ownname=> 'RDS', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO',options=> 'GATHER AUTO');
end;
/*/*/*/*/*5:44 PM 3/14/2007/*/*/*/*/*
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*Recover table from recyclebin /*/*/*/*/*/*/*/*/*/*/*/*/*/*
select * from user_recyclebin;
Flashback table "%^^%$^$##32333" to before drop;
ALTER TABLE TBLBUILDINGHISTORY_QC ENABLE ROW MOVEMENT;
FLASHBACK TABLE TBLBUILDINGHISTORY_QC TO TIMESTAMP to_timestamp('2007-12-14 05:00:00 PM', 'YYYY-MM-DD HH:MI:SS AM');
FLASHBACK TABLE DC_ENTRIES TO TIMESTAMP to_timestamp('2008-03-01 07:00:00 PM', 'YYYY-MM-DD HH:MI:SS AM');
196 rows selected.
SQL> create table TBLBUILDINGHISTORY_QC_1 as select * from TBLBUILDINGHISTORY_QC;
Table created.
SQL> ALTER TABLE TBLBUILDINGHISTORY_QC ENABLE ROW MOVEMENT;
Table altered.
SQL> FLASHBACK TABLE TBLBUILDINGHISTORY_QC TO TIMESTAMP to_timestamp('2007-12-14 05:00:00 PM', 'YYYY-MM-DD HH:MI:SS AM');
ALTER TABLE rds_photo.TBLRMSIFILEINFO ENABLE ROW MOVEMENT
ALTER TABLE timeinc.pra_folioinfo ENABLE ROW MOVEMENT;
ALTER TABLE RDS_RS.TBLFINALINFOTEST ENABLE ROW MOVEMENT;
FLASHBACK TABLE RDS_RS."BIN$O7606UdjIgPgRAAUTz/7kA==$0" TO BEFORE DROP
FLASHBACK TABLE RDS_PHOTO.TBLRMSIFILEINFO TO TIMESTAMP t5:56 PMo_timestamp('2007-03-21 11:30:05 AM', 'YYYY-MM-DD HH:MI:SS AM');FLASHBACK TABLE sde.TBLSTATUS TO TIMESTAMP to_timestamp('2007--13 11:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM');
select versions_startscn, to_char(versions_starttime, 'YYYY-MM-DD HH:MI:SS AM'), versions_xid, versions_operation, USERRIGHTS from RDS_TEST.BKP_TEST versions between scn minvalue and maxvalue where userrights=5 and versions_startscn is not null order by versions_startscn desc
select versions_startscn, to_char(versions_starttime, 'YYYY-MM-DD HH:MI:SS AM'), versions_xid, versions_operation, USERRIGHTS from RDS_TEST.BKP_TEST versions between scn minvalue and maxvalue where userrights=5 and versions_startscn is not null order by versions_startscn desc;
FLASHBACK TABLE RDS_TEST.BKP_TEST TO SCN 4325768411
FLASHBACK TABLE RDS_TEST.BKP_TEST TO TIMESTAMP to_timestamp('2007-04-07 03:01:05 PM', 'YYYY-MM-DD HH:MI:SS AM')FLASHBACK TABLE RDS_TEST.BKP_TEST TO TIMESTAMP to_timestamp('2007-04-07 04:15:36 PM', 'YYYY-MM-DD HH:MI:SS AM')FLASHBACK TABLE timeinc.pra_folioinfo TO TIMESTAMP to_timestamp('2007-11-22 23:30:00 PM', 'YYYY-MM-DD HH:MI:SS AM')
FLASHBACK TABLE RDS.TBLPOSTCLEANINGLOG, RDS.TBLPRECLEANINGLOG TO TIMESTAMP to_timestamp('2007-11-19 09:00:47', 'YYYY-MM-DD HH24:MI:SS')
ALTER TABLE RDS.TBLPOSTCLEANINGLOG ENABLE ROW MOVEMENT;ALTER TABLE RDS.TBLPRECLEANINGLOG ENABLE ROW MOVEMENT;impdp rds/rds@rds directory=rds_backup dumpfile=EXP_RDS_MON_MORNING.DMP tables=rds.FARES_SC_KY_0207_V3 cONTENT=DATA_ONLY
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*Flashback table "BIN$OD/gAPw9RwqZPUNt6B4z5w==$0" to before drop;Flashback table "BIN$n4V4DqACSlajRt8zXSQiNA==$0" to before drop;Flashback table "BIN$mw6Gq8vmSSaKXZszSwZBmg==$0" to before drop;Flashback table "BIN$kJnBEQnkQVSMsJuAdJFoFw==$0" to before drop;Flashback table "BIN$zijqO4wMQyOwt+iZGxHa5A==$0" to before drop;Flashback table "BIN$k3z2akzmQAKSBOElpsAoIg==$0" to before drop;Flashback table "BIN$Q/EsIWP9RAixKtEnOdMlaQ==$0" to before drop;Flashback table "BIN$l/3q25hKR9ixexbKY0k68A==$0" to before drop;Flashback table "BIN$MzW5BHK6RpSlFbq1nB1e6g==$0" to before drop;Flashback table "BIN$7AHt0aeWRY2UZ/+UAw86MA==$0" to before drop;Flashback table "BIN$/6lHeRUvTeGAsovq8NNcVw==$0" to before drop;Flashback table "BIN$F7k9GY45RZ611FCX0Deejw==$0" to before drop;Flashback table "BIN$c10hh6E2T1WgccbGB6D/Ug==$0" to before drop;Flashback table "BIN$DyUzVpAvTCyMtEema0nD3A==$0" to before drop;Flashback table "BIN$Vow8HBfDTuGMSIG70WlRmw==$0" to before drop;Flashback table "BIN$McQcdSZrTpWQU2uuGoOrjw==$0" to before drop;Flashback table "BIN$l/M5pYh6TgCG5qLg4FG1Hg==$0" to before drop;Flashback table "BIN$Inz27n++Svie2QyWdAC+5g==$0" to before drop;Flashback table "BIN$Fj01j+4jRcaIxjkQakWCjQ==$0" to before drop;/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/
declarecmd varchar2(400);begincmd := 'create index RDS.IDX$_00001 on RDS.SANBORN_BLDG_ALT_ADD("ROWNO")';EXECUTE IMMEDIATE cmd;END;
/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/**/*/*/**/*/*/12:33 PM 3/19/2007/*/*/*/*/*/*/
CREATE TABLESPACE "IR_WATER" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\PILOT\IR_WATER.DBF' SIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
CREATE TABLESPACE "IR_WATER" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\PILOT\IR_WATER.DBF' SIZE 50M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
ALTER USER "IR_WATER" DEFAULT TABLESPACE "IR_WATER"
/*/*/*/*/*/*/*
CREATE USER "IR_WATER" PROFILE "DEFAULT" IDENTIFIED BY "*******" ACCOUNT UNLOCKGRANT "CONNECT" TO "IR_WATER"
ALTER USER "IR_WATER" DEFAULT TABLESPACE "IR_WATER"
ALTER SYSTEM KILL SESSION '112,1250' IMMEDIATE;
CREATE USER "ENBRIDGE" PROFILE "DEFAULT" IDENTIFIED BY "enbridge" DEFAULT TABLESPACE "USERS" ACCOUNT UNLOCK;GRANT "CONNECT" TO "ENBRIDGE";GRANT "DBA" TO "ENBRIDGE";GRANT "RESOURCE" TO "ENBRIDGE";
CREATE TABLESPACE "ENBRIDGE" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\PILOT\ENBRIDGE1.dbf' SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
select 'create index' ' ' INDEX_NAME ' ' 'on' ' ' TABLE_NAME'('COLUMN_NAME')'';' from user_ind_columns where INDEX_NAME not like '%$%';select 'ALter index' ' ' INDEX_NAME ' Rebuild'';' from user_ind_columns where INDEX_NAME not like '%$%';
ALTER SYSTEM SET sga_max_size = 1468006400 SCOPE=SPFILE;ALTER SYSTEM SET pga_aggregate_target = 130023424 SCOPE=BOTH;ALTER SYSTEM SET shared_pool_size = 423624704 SCOPE=BOTH;select CONSTRAINT_NAME , CONSTRAINT_TYPE , R_CONSTRAINT_NAME,TABLE_NAME from user_constraints where table_name not like '%$%' and R_CONSTRAINT_NAME is not null order by table_name;
select a.owner,a.table_name,a.constraint_name,b.table_name,b.constraint_name from all_constraints a,user_constraints b where a.constraint_name=b.r_constraint_name and b.table_name='&CHILD_TABLE' ;
select a.owner,a.table_name,a.constraint_name,b.table_name,b.constraint_name from all_constraints a,user_constraints b where a.constraint_name=b.r_constraint_name and b.constraint_name='&FOREIGN_KEY'
GRANT SELECT ON "PHI"."TBLNEWPROJECT" TO "SUSER";GRANT SELECT ON "PHI"."TBLUSER" TO "SUSER";
CREATE USER "APPDEV" PROFILE "DEFAULT" IDENTIFIED BY "appdev" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;GRANT "CONNECT" TO "APPDEV";GRANT "DBA" TO "APPDEV";GRANT "RESOURCE" TO "APPDEV";/*/*/*/*/*/*/
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\DUMP_NEW2.DBF' RESIZE 10000M

CREATE TABLESPACE "EEE" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\EEE.ora' SIZE 5M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED TEMPORARY
CREATE TEMPORARY TABLESPACE dump_new_temp tempfile 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\dump_new_temp1.dbf' SIZE 1000M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED ;
ALTER USER "DUMP_NEW" DEFAULT TABLESPACE "DUMP_NEW" TEMPORARY TABLESPACE "DUMP_NEW_TEMP"/*/*/*/*/6:19 PM 4/5/2007/*/*/*/*/*/*/SQLNET.AUTHENTICATION_SERVICES= (NTS)
ALTER USER "RDS_TEST" DEFAULT TABLESPACE "RDS_TEST" TEMPORARY TABLESPACE "TEMP_RDS"/*/*/*/*/*/*/*/*/*/dbms_stats.gather_index_stats(ownname => 'DUMP_DATA', indname => 'BIN$ys9ufsdaQTWogIsk6x1UaQ==$0', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
ALTER DATABASE TEMPFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\TEMP.DBF' RESIZE 4000M;
ALTER DATABASE TEMPFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\TEMP.DBF' AUTOEXTEND ON NEXT 2M MAXSIZE UNLIMITED;
ALTER TABLESPACE "TEMP" ADD TEMPFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\temp2.dbf' SIZE 1000M AUTOEXTEND ON NEXT 2M MAXSIZE UNLIMITED;
/*/*/*
begin
dbms_stats.gather_schema_stats( ownname=> 'RDS', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> 'RDSUSER', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> 'RDS_FARES', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> 'RDS_PARTY', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> 'RDS_PHOTO', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> 'RDS_REMOTE', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
end;
/*/*/*/*/*/*/*/*///*/*/*/*11:30 AM 4/11/2007/*/*//*/*/*/*CREATE USER "SB_DB" PROFILE "DEFAULT" IDENTIFIED BY "sb_db" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;GRANT "CONNECT" TO "SB_DB";GRANT "DBA" TO "SB_DB";GRANT "RESOURCE" TO "SB_DB";/*/*/*/*/*/*/*/*/*/*/*/*/*CREATE TABLESPACE "SB_DB" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\PILOT\SB_DB.dbf' SIZE 50M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;/*/*/*/*/*/*/
ALTER SYSTEM SET db_cache_size = 738197504 SCOPE=BOTHALTER SYSTEM SET shared_pool_size = 423624704 SCOPE=BOTH
CREATE SMALLFILE TABLESPACE "RDS_BAVS" DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS_BAVS.DBF' SIZE 10000M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO BEGIN DBMS_SERVER_ALERT.SET_THRESHOLD(9000,NULL,NULL,NULL,NULL,1,1,NULL,5,'RDS_BAVS'); END;
ALTER USER "RDS_BAVS" DEFAULT TABLESPACE "RDS_BAVS" TEMPORARY TABLESPACE "TEMP_RDS"
/*/*/*/*//*/*/*/*/*//*/*/*/*/*/*/*/*/*/*/*/*/*/*/*//*
ALTER TABLESPACE "RDS_BAVS" ADD DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\rds_bavs1.dbf' SIZE 10000M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
begindbms_stats.gather_table_stats(ownname => 'RDS_BAVS', tabname => 'CLIENT_DATA2', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);dbms_stats.gather_table_stats(ownname => 'RDS_BAVS', tabname => 'CLIENT_DATA2_DUP1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
ALTER TABLE RDS_BAVS.CLIENT_DATA2_TEMP ADD CONSTRAINT PK_SNO_CLIENT_DATA2_TEMP PRIMARY KEY (SNO)end;/*/*/*/**/*/*/*/*/**/*/*/*/*/*/*/9:47 AM 4/15/2007/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
CREATE PUBLIC DATABASE LINK RDS_DATAVALCONNECT TO RDSIDENTIFIED BY RDSUSING 'dataval';
/*/*/*/*/*/*/**/*/*/*/*/*/*/9:59 AM 4/15/2007/*/*/*/*/
impdp system/sys@pilot directory=rds_backup dumpfile=EXP_RDS_WED_MORNING.DMP tables="USSTATE" REMAP_SCHEMA=RDS_PHOTO:wg5386
select OWNER,NEXT_RUN_DATE , JOB_NAME, JOB_TYPE from dba_scheduler_jobs;
/*/*/*/**/*/*/*/*/**/*/*/*/*/*/*/12:19 PM 4/15/2007/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
oradim.exe -edit -SID ilrpoly -startmode auto -srvcstart system
You will have to edit you oracle service by using oradim and execute this command:
oradim.exe -edit -sid (YOUR SID) -startmode auto -srvcstart system
Or even delete your current oracle service and create a new one:
Example: Delete oracle service: oradim -del -sid (your sid)
Creation:
oradim.exe -new -sid (YOUR SID) -startmode manual -spfile oradim.exe -edit -sid (YOUR SID) -startmode auto -srvcstart system oradim.exe -edit -sid osiiigc1 -startmode auto -srvcstart system oradim.exe -edit -sid OMS -startmode auto -srvcstart system
Now your database will start after rebooting your windows server.
Kind regards
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/**/*/select * from dba_scheduler_jobs;
stop the job: begin dbms_scheduler.stop_job( 'HLUDLAYER2.NEW_STATUS_REPORT' , TRUE); end; /
remove the job: begin DBMS_JOB.REMOVE(id); end; /
BEGINsys.dbms_scheduler.set_attribute( name => '"SYS"."DAILY_PURGE_SCHEDULE"', attribute => 'repeat_interval', value => 'FREQ=DAILY;BYHOUR=3;BYMINUTE=0;BYSECOND=0'); END;
DBMS_SCHEDULER.disable (name => 'HLUDLAYER2.NEW_STATUS_REPORT');
Elapsed: 00:00:00.0011:33:20 SQL> exec DBMS_SCHEDULER.disable (name => 'HLUDLAYER1.NEW_STATUS_REPORT');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.0011:33:48 SQL> exec DBMS_SCHEDULER.disable (name => 'HLUDLAYER3.NEW_STATUS_REPORT');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.0211:34:00 SQL> exec DBMS_SCHEDULER.disable (name => 'HLUDLAYER4.NEW_STATUS_REPORT');
PL/SQL procedure successfully completed.
exec DBMS_SCHEDULER.disable (name => 'TIMEINC.UJB_UPDATE_TIME_INVENTORY');
exec DBMS_SCHEDULER.enable (name => 'TIMEINC.UJB_UPDATE_TIME_INVENTORY');
select dbms_xmlquery.getxml('select * from emp where rownum <= 3') from dual;DBMS_XMLQUERY.GETXML('SELECT*FROMEMPWHEREROWNUM<=3') /*/*/*/*/*/*/*/*/*/*/*/*/*/***/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/exec dbms_stats.gather_table_stats('RDS_BAVS','FARES_FINAL1')create index idx_ADDRESS_fares_f on FARES_FINAL35381098(trim(ADDRESS)) tablespaces rds_index NOLOGGING;/*/*/*/*/*/*/*/*/*/*/8*//**/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/Consider gathering system statistics with sql > exec dbms_stats.gather_system_stats('start'); after a couple of hours with representative db activity sql > exec dbms_stats.gather_system_stats('stop');
create index idx_ADDRESS_fares_final2 on FARES_FINAL2(trim(ADDRESS)) tablespace rds_index nologging;
update --+ index(idx_ADDRESS_fares_final2) FARES_FINAL2 set address=trim(address) where
CREATE BITMAP INDEX IDX_property_state ON FARES_FINAL2(property_state) tablespace rds_index nologging;
CREATE BITMAP INDEX IDX_property_state ON FARES_FINAL2(property_state) tablespace rds_index nologging;/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
begindbms_stats.gather_table_stats(ownname => 'DUMP_NEW', tabname => 'LOCCVG', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);dbms_stats.gather_table_stats(ownname => 'DUMP_NEW', tabname => 'TBLMDF_RECORD', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);dbms_stats.gather_table_stats(ownname => 'DUMP_NEW', tabname => 'EDM_MASTER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);end;exec dbms_stats.gather_index_stats('DUMP_NEW','IDX_LOCID_LOCCVG')exec dbms_stats.gather_index_stats('DUMP_NEW','IDX_MDF_UID_LOCCVG')exec dbms_stats.gather_index_stats('DUMP_NEW','IDX_MDF_UID')exec dbms_stats.gather_index_stats('DUMP_NEW','idx_labelid_loccvg')
drop index IDX_LOCID_LOCCVG; drop IDX_MDF_UID_LOCCVG; drop IDX_MDF_UID;
create bitmap index IDX_LOCID_LOCCVG on LOCCVG (LOCID);create bitmap index IDX_MDF_UID_LOCCVG on LOCCVG (MDF_UID);create bitmap index IDX_MDF_UID on EDM_MASTER (MDF_UID);opt/oracle/oracle/product/10.2.0/db_2/rdbms/admin/@/opt/oracle/oracle/product/10.2.0/db_2/rdbms/admin/spreport.sql
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*Query To know how many update is being Done.
select c.username,a.sid,b.name,a.value,c.LOGON_TIME,c.OSUSER,c.status from v$sesstat a,v$statname b, v$session c whereb.statistic#=a.statistic# and b.statistic#=191 and a.sid=c.sid and c.type!='BACKGROUND';
SELECT sofar, totalwork FROM v$session_longops/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/***/*****/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
SELECT substr(OS_USERNAME,1,20) , substr(USERHOST,1,25) , ACTION_NAME,TO_CHAR(TIMESTAMP,'DD-MON-YY hh:MI:SS') FROM DBA_AUDIT_TRAIL;SELECT substr(owner,1,10), substr(OS_USERNAME,1,10) , substr(USERHOST,1,15) , substr(ACTION_NAME,1,15),substr(OBJ_NAME,1,35),TO_CHAR(TIMESTAMP,'DD-MON-YY hh:MI:SS') FROM DBA_AUDIT_TRAIL where action_name like '%TABLE%' and obj_name not like '%$%'
col OBJ_NAME format a25col OS_USERNAME format a15col ACTION_NAME format a15`col USERHOST format a15col username format a15delete from sys.aud$ where USERID='DBSNMP';SELECT username,OS_USERNAME,USERHOST,OBJ_NAME,ACTION_NAME,EXTENDED_TIMESTAMP FROM DBA_AUDIT_TRAIL;SELECT username,OS_USERNAME,USERHOST,OBJ_NAME,ACTION_NAME,EXTENDED_TIMESTAMP FROM uks_sys_audit where ACTION_NAME like '%USER%';select USERNAME,USER_ID,PASSWORD,LOCK_DATE from DBA_USERS order by username;SELECT OS_USERNAME , USERHOST , ACTION_NAME,EXTENDED_TIMESTAMP FROM DBA_AUDIT_TRAIL;AUDIT UPDATE table,INSERT TABLE , DELETE TABLE BY sde BY ACCESS;Audit update , delete , insert on phi.tbluser by access whenever successful;audit drop any table,create any table,alter any table,truncate any table by rds by access;audit drop any table,create any table,alter any table,truncate any table by dump_data by access;NOAUDIT UPDATE table,INSERT TABLE , DELETE TABLE BY phmap BY ACCESS;NOaudit drop any table,create any table,alter any table,truncate any table by phmap by access;noaudit drop any table,create any table,alter any table by rds_bavs by access;audit drop any table,create any table,alter any table by rds by access;audit table;audit ALTER DATABASE;Audit succeeded.Elapsed: 00:00:0000.00delete from sys.aud$ where TERMINAL='CP658GIS';delete from aud$ where USERHOST='Earth';truncate table sys.aud$;
12:28:31 SQL> audit drop any table,create any table by rds_bavs by access;Audit succeeded.Elapsed: 00:00:00.0012:28:44 SQL> show userUSER is "SYS"
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/alter system set audit_trail='db' scope=spfile;
ALTER SYSTEM SET pga_aggregate_target = 207618048 SCOPE=BOTH;
REVOKE "DBA" FROM "RMSIPMS";ALTER USER "RMSIPMS" DEFAULT ROLE ALL
/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*//*/*/*/*/*/*http://cp763gis.rmsi.com:5500/emCREATE USER "VISION" PROFILE "DEFAULT" IDENTIFIED BY "*******" ACCOUNT UNLOCKGRANT "CONNECT" TO "VISION"CREATE SMALLFILE TABLESPACE "VISION" DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\OSIIIGC1\VISION.DBF' SIZE 50M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO BEGIN DBMS_SERVER_ALERT.SET_THRESHOLD(9000,NULL,NULL,NULL,NULL,1,1,NULL,5,'VISION'); END;
emca -config dbcontrol db -repos create
dbms_stats.gather_index_stats(ownname => 'DUMP_DATA', indname => 'IDX_FLGPROCESS_TBLMFD_RECORD', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
ALTER DATABASE ADD LOGFILE GROUP 5 ( '/opt/oracle/oradata/ilrpoly/log5.ora') SIZE 512000KALTER DATABASE ADD LOGFILE GROUP 7 ( '/opt/oracle/oradata/ilrpoly/redo7.log')SIZE 512000KALTER SYSTEM SET log_buffer=2048576 SCOPE=SPFILE;
Recover database until cancel using backup controlfile;alter database open RESETLOGS; should be after the database is mounted.ALTER SYSTEM KILL SESSION '44,22298' IMMEDIATE
alter system flush shared_pool;
select /*+ parallel(
,4) */ count(1) from

;
/*************************** Space occupied by any segment Name ***********************/select TABLESPACE_NAME , to_char (sum (BYTES/(1024*1024*1024)),099999999.99999999) as "SPACE IN GB" from dba_data_files group by TABLESPACE_NAME;select table_name ,(BLOCKS*8)/(1024*1024) "Space In Gb" from dba_tables where OWNER='DUMP_DATA' order by table_name;select SEGMENT_NAME, SEGMENT_TYPE,BYTES/(1024*1024*1024) from DBA_segments where TABLESPACE_NAME like '%DATA%';SEGMENT_NAME, SEGMENT_TYPE,BYTES/(1024*1024*1024) from DBA_segments where TABLESPACE_NAME like '%DATA%' and SEGMENT_TYPE='TABLE' order by BYTES/(1024*1024*1024);
/*/*/*/*/*/*create user veer identified by veer;grant connect, resource, dba to veer;ALTER USER "VEER" DEFAULT TABLESPACE "VEER";/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/Consider running the Access Advisor to improve the physical schema design or creating the recommended index.CE.TBLTOWNLANDPOLY_PROD(TO_NUMBER("TOWNLANDID"))declarecmd varchar2(400);sname varchar2(400);begincmd := 'create index CE.IDX$$_8BA80001 on CE.TBLCOUNTY_TOWNLAND("OBJECTID")';EXECUTE IMMEDIATE cmd;END;/*/*/*/*/*/*/*/*/*/*/*/1:47 PM 5/28/2007Create sequence */*/*
GRANT CREATE ANY SEQUENCE TO "LH"GRANT DROP ANY SEQUENCE TO "LH"
CREATE SEQUENCE "LH"."TBLTOWNLANDPOLY_PROD_SEQ" INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E27 MINVALUE 1 NOCYCLE NOCACHE NOORDER;/*/*/*/*/*/*/*/*/*/*/*ROW CHAIN */*/*/*/*/*/*/create table CHAINED_ROWS ( owner_name varchar2(30), table_name varchar2(30), cluster_name varchar2(30), partition_name varchar2(30), subpartition_name varchar2(30), head_rowid rowid, analyze_timestamp date);/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/6:19 PM 6/4/2007*/*/*/*/
Net config Assistance
NETCA/*/*/****/*/*/*/*/*/*/*/*/**/*/*/*/*LOG BUFFER*/*/*/*/*/*/*/Select name, value from v$sysstat Where name = 'redo log space requests';LOG_BUFFER =alter system set log_buffer=4000000 scope=spfile;Select name, value from v$sysstatWhere name in ('redo log space requests', 'redo log space wait time');/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/****/*/*/*/*/*/*/*/***/**/*/**/*/*/*/*/*/*/begin
dbms_stats.gather_schema_stats(ownname=> '"FOREST"' , options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> '"HLUD"' , options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> '"HLUDLAYER1"' , options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> '"HLUDLAYER2"' , options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> '"HLUDLAYER3"' , options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> '"HLUDLAYER4"' , options=> 'GATHER AUTO');end;/*/*/*/*/*/*/*/*/*/ Write on XML and XLs*/*/*/**/*/*/*/*/*/*/*/*/*/***/*/*/*/*/*/*/*/*/**/*/*/*/*/SET MARKUP HTML ON spool d:\dept.xlsdesc TBLTEMP_MANISHA spool offSET MARKUP HTML off
/*/*/**/*/*/*/*/*/*/*/*/*/*/**/*/*/*/@ opt/oracle/oracle/product/10.2.0/db_2/rdbms/admin/spreport.sql/**/**/*/*/*/*/**/*/Shrink the table./*/*/*/*/*/*/*/*/*/alter table "SL"."TBLLINE_DETAILS" enable row movement;alter table "SL"."TBLLINE_DETAILS" shrink space;***************************************************************************************************SET UNDERLINE OFFSET COLSEP ','SET LINES 100 PAGES 100SET FEEDBACK off--(If you don’t want column headings in CSV file)SET HEADING off Spool C:\Export\EMP.csv--Now the actual querySELECT * FROM EMP;Spool OFF***************************************************************************************************SELECT 'PGA: pid 'p.spid pid,p.pga_alloc_mem bytes, p.username ps_user,p.program ps_program,s.logon_time,s.sid,s.serial#,s.username,s.machine,s.osuser,s.programFROM v$session s ,v$sesstat pcur,v$process p WHERE pcur.statistic#=20 AND s.paddr=p.addr AND pcur.sid=s.sid UNION ALL SELECT 'SGA: 'name pid,value bytes,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL FROM v$sgaORDER BY 2 DESC;****************************************************************************************************Where 'X' is the number of the job to be removed. Transfering Stats It is possible to transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data. First the statistics must be collected into a statistics table. In the following examples the statistics for the APPSCHEMA user are collected into a new table, STATS_TABLE, which is owned by DBASCHEMA:
SQL> EXEC DBMS_Stats.Create_Stat_Table('DBASCHEMA','STATS_TABLE'); SQL> EXEC DBMS_Stats.Export_Schema_Stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');
This table can then be transfered to another server using your preferred method (Export/Import, SQLPlus Copy etc.) and the stats imported into the data dictionary as follows:
SQL> EXEC DBMS_Stats.Import_Schema_Stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA'); SQL> EXEC DBMS_Stats.Drop_Stat_Table('DBASCHEMA','STATS_TABLE'); ***************************************************************************************************select substr(HOST_NAME,1,10) Host_name ,INSTANCE_NAME,STARTUP_TIME, SHUTDOWN_PENDING, DATABASE_STATUS from v$instance;
emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos create select sum(waits)*100/sum(gets) "Ratio" , sum(waits) "WAITS" , sum(gets) "GETS" from v$rollstat;
select 'alter system kill session ''' SID ',' SERIAL# ''';', USERNAME , MACHINE,substr (program,1,100) from v$session where machine ='cp900gis';
select 'alter system kill session ''' SID ',' SERIAL# ''';' from v$session where machine ='cp900gis';select segment_name, bytes/1024/1024,blocks from user_segments;
select owner ,SEGMENT_NAME , BYTES/(1024*1024) from dba_segments where OWNER not in ('SYS','SYSTEM','SYSMAN','WKSYS','MDSYS','WMSYS','DBSNMP','OLAPSYS','XDB','ORDSYS') and SEGMENT_NAME not like '%$%' and segment_type='TABLE' order by owner;
select table_name,(num_rows*avg_row_len)/(1024*1024) MB from user_tables where table_name='T1';
select sum(BYTES/(1024*1024*1024)) from dba_data_files;
select USERNAME,substr(MACHINE,1,25),substr (program,1,100) from v$session;select SID,serial#,status,to_char(LOGON_TIME,'DD-MON-YYYY HH24:MI:SS'),substr(USERNAME,1,10),substr(MACHINE,1,25),substr (program,1,24),osuser from v$session where username is not null order by osuser;
select substr(index_name,1,50),substr(table_name,1,50),substr(column_name,1,50) from user_ind_columns where index_name not like '%$%' order by table_name;**********************1:50 PM 1/7/2008*************************************select substr(TABLE_NAME,1,50) , substr(COLUMN_NAME,1,50) , substr( INDEX_NAME,1,50) from user_ind_columns;select TABLE_NAME,COLUMN_NAME from user_tab_columns where COLUMN_NAME like '%GEO%' order by COLUMN_NAME;
select substr(TABLE_NAME,1,50) , substr(COLUMN_NAME,1,50) , substr( INDEX_NAME,1,50) from user_ind_columns;select TABLE_NAME,COLUMN_NAME from user_tab_columns where COLUMN_NAME like '%GEO%' and TABLE_NAME not in (select TABLE_NAME from user_ind_columns);
select 'SELECT dbms_metadata.get_ddl(''TABLESPACE'',' ''''username''''')'' from dual;' from dba_users ;
set long 100000SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name) FROM USER_INDEXES u;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE',u.TABLESPACE_NAME) FROM dba_tablespaces u;