Oracle : Unregister database from RMAN Catalog

Login as rman catalog owner.

SQLPLUS> select * from rc_database where dbid = DBID;

SQLPLUS> exec dbms_rcvcat.unregisterdatabase(DBKEY, DBID);

Oracle : Reference List of Critical Patch Update Availability Documents For Oracle Database and Fusion Middleware Product [ID 783141.1]

A good reference for the List of CPU for Oracle database. Metalink account is needed.

Reference List of Critical Patch Update Availability Documents For Oracle Database and Fusion Middleware Product [ID 783141.1]

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=HOWTO&id=783141.1

Oracle : Create database link on another schema

Even as an administrator for the database, it sometime possible that we don't have the password of the schema. Most of the time we can just log in as SYS and then create the objects for the particular schema.

For example we can create a table for a particular schema by just added the owner name before the table name.

Create table AAA.TEST (A CHAR);

The table will be create under the ownership of the schema AAA.

However this is not possible for database link. As the syntax for creating database link does not take owner into consideration.

If you would create a database link using

Create database link AAA.TESTLNK ....

You will end up with a database link "AAA.TESTLINK" owner by SYS.

So the trick now is to create a procedure under the schema that create the database link. This way the database link will be create under the ownership of the schema and not SYS. Script as below :


create or replace procedure AAA.CREATEDBLINK
is
begin
execute immediate 'create database link TESTLINK
connect to BBB identified by "Password"
using ''REMOTEINSTANCE''';
end;
/

Execute the procedure AAA.CREATEDBLINK to create the database link

exec AAA.CREATEDBLINK

Subsequently drop the procdure.

drop procedure AAA.CREATEDBLINK;


Original source : http://forums.oracle.com/forums/thread.jspa?threadID=507523

Oracle : List Table Names, Rows, and Created Date

During migration it is best to do a row count between the old and new database.
Below is a script that I have adapted from another author to do a role count for all the tables belonging to the schemas (object owners). We will run the script on the old and new database. Spool out the file and subsequently we can just do a diff ( unix command ) on the 2 files to see if there is a difference. If there are no difference we can safely assume the number of rows migrated from the old database to the new database are correct.

* replace ('AAA','BBB','CCC') with the schemas names.

set linesize 999
set trimspool on
set pagesize 999
set serveroutput on size 100000 feedback off

declare
rs integer;
cur integer;
rp integer;
trs integer;
n integer;
un varchar2(30);
begin
dbms_output.put_line(rpad('Owner',15)||rpad('Table Name',40)||' Number of Rows');
dbms_output.put_line(rpad('-',73,'-'));
cur:= dbms_sql.open_cursor;
for t in (select owner, table_name from dba_tables where owner in ('AAA','BBB','CCC') order by owner, table_name) loop
dbms_sql.parse(cur,'select count(*) from ' || t.owner || '.' || t.table_name, dbms_sql.v7);
dbms_sql.define_column(cur, 1, rs);
rp:= dbms_sql.execute(cur);
n:=dbms_sql.fetch_rows(cur);
dbms_sql.column_value(cur, 1, rs);
dbms_output.put_line(rpad(t.owner,23)||rpad(t.table_name,48,'.')||rpad(rs,15,'.'));
end loop;
dbms_sql.close_cursor(cur);

end;
/

set serveroutput off feedback on feedback 6


Orignal article : http://www.oracle.com/technology/oramag/code/tips2004/011104.html

Original Script :

This tip comes from Pasupuleti Sailaja, in Hyderabad, Andhra Pradesh, India.


Description: -- Aim : To display table names, number of rows existed in table
-- and table created date in current login user for any ORACLE version
-- Usage : Step 1) All lines from given program save with filename DIR.SQL
-- Step 2) Run the file DIR.SQL by giving START DIR (or) @ DIR at SQL *Plus prompt
-- i.e. SQL> @ DIR
-- Author : Pasupuleti Sailaja, ORACLE favorite, Hyderabad-500072, India.
-- E-mail : SAILAJAMAIL@YAHOO.COM
-- Program :

set serveroutput on size 100000 feedback off

declare
rs integer;
cur integer;
rp integer;
trs integer;
n integer;
un varchar2(30);
begin
dbms_output.put_line(rpad('Table Name',40)||' Number of Rows Created Date');
dbms_output.put_line(rpad('-',73,'-'));
cur:= dbms_sql.open_cursor;
for t in (select object_name, created from user_objects where object_type='TABLE') loop
dbms_sql.parse(cur,'select count(*) from ' || t.object_name, dbms_sql.v7);
dbms_sql.define_column(cur, 1, rs);
rp:= dbms_sql.execute(cur);
n:=dbms_sql.fetch_rows(cur);
dbms_sql.column_value(cur, 1, rs);
dbms_output.put_line(rpad(t.object_name,48,'.')||rpad(rs,15,'.')||t.created);
end loop;
dbms_sql.close_cursor(cur);
select count(*) into n from tab where tabtype='TABLE';
select user into un from dual;
dbms_output.put_line(rpad('-',73,'-'));
dbms_output.put_line(un||' User contain '||n||' Table(s)');
end;
/
set serveroutput off feedback on feedback 6