DETECTING MIGRATION AND CHAINING ROWS

1. ANALYZE TABLE OWNER.TABLE_NAME COMPUTE STATISTICS;
2. SELECT NUM_ROWS,CHAIN_CNT FROM DBA_TABLES WHERE TABLE_NAME=’TABLE_NAME’;

CHAIN_CNT= MIGRATED + CHAINED ROWS.

FOR LIST OF ROWS.

1. ANALYZE TABLE OWNER.TABLE_NAME LIST CHAINED ROWS;
2. SELECT OWNER_NAME,TABLE_NAME,HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME=’TABLE_NAME’;
TO CREATE CHAINED_ROWS TABLE RUN UTLCHAIN.SQL

ELIMINATION MIGRATED ROWS.

1. EXPORT / IMPORT
1. EXPORT THE TABLE
2. DROP OR TRUNCATE THE TABLE
3. IMPORT THE TABLE
2. MOVE TABLE COMMAND
1. ALTER TABLE TABLE_NAME MOVE;
3. COPY MIGRATED ROWS
1. FIND MIGRATED ROWS USING ANALYZE

ANALYZE TALBE TABLE_NAME LIST CHAINED ROWS;

2. COPY MIGRATED ROWS TO NEW TABLE

CREATE TABLE MIGRATED_ROWS AS
SELECT ORIG.*
FROM TABLE_NAME ORIG,CHAINED_ROWS CR
WHERE ORIG.ROWID=CR.ROWID
AND CR.TABLE_NAME=TABLE_NAME;

3. delete migrated rows from original table

delete from table_name
where rowid in
(select head_rowid from chained_rows);

4. copy rows from new table to original table

insert into table_name select * from migrated_rows;

Export – Import Taking long time.

We have Sales Order Processing System at HO & works

We are taking orders at HO and Billing and dispatch and etc. happening at works.

We have some tables which we need to import at HO base on dispatch and billing done at works.

But my import takes to much time to import those tables. I had try with different exp/imp parameters but can’t get success.

Then i get realized I have trigger on tables which are firing after insert on the tables which I am importing and that triggers are creating issue for me.

So I had disabled that trigger and import.

Now which import taking 30/45 min its now completing within 5 min.

Automatic Archiving Does Not Restart if Disk Space is Full

Compliments of Jeff Hunter, ideveleopment.info
Overview
Consider an Oracle database running in archive log mode with automatic archiving turned on. When the archive log destination disk space becomes full, the automatic archiving will stop. As per Oracle's documentation, this is the expected behaviour. At this point, a message should appear:
ORA-00257: archiver error. Connect internal only, until freed.
When all of the online redo logs are filled up, the database will be in a hung state.
If you attempt to manually archive the files, you may receive the following errors:
SVRMGR> archive log next
ORA-16014: log 1 sequence# 199 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/u03/app/oradata/DBADB/redo_g01a.log'

SVRMGR> archive log all
ORA-16020: less destinations available than specified
by LOG_ARCHIVE_MIN_SUCCEED_DEST
Solution
The following is a list of the possible ways to fix this problem:
1. Use the command:
alter system archive log all to '/u07/app/oradata/DBADB/archive';
NOTE: You MUST specify a location ref - ora-16014
2. Shutdown and restart the database. It may be necessary to do a Shutdown abort.

3. Use the REOPEN attribute of the LOG_ARCHIVE_DEST_n init.ora parameter:
LOG_ARCHIVE_DEST_n=' REOPEN=30'
Or run the following comand:
alter system set LOG_ARCHIVE_DEST_n = 'reopen'
4. Use the commands:
5. alter system archive log stop;

alter system archive log start;

Explanation
The error ORA-16014 is the real clue for this problem. Once the archive destination becomes full, the location also becomes invalid. Normally Oracle does not do a recheck to see if space has been made available.
Using the command
alter system archive log all to '/u07/app/oradata/DBADB/archive';
gives Oracle a valid location for the archive logs. Even after using this the archive log destination parameter is still invalid and automatic achive does not work. This solution is best for systems that do not archive regularly but cannot be shutdown. You can also use this to allow you to do a Shutdown immediate instead of Shutdown abort.

Shutdown and restart of the database resets the archive log destinstation parameter to be valid. Do not forget to make disk space available before starting the database.

Use the REOPEN attribute of the LOG_ARCHIVE_DEST_n parameter to determine whether and when ARCn attempts to re-archive to a failed destination following an error. REOPEN applies to all errors, not just OPEN errors. REOPEN=n sets the minimum number of seconds before ARCn should try to reopen a failed destination. The default value for n is 300 seconds. A value of 0 is the same as turning off the REOPEN option, in other words, ARCn will not attempt to archive after a failure. If you do not specify the REOPEN keyword, ARCn will never reopen a destination following an error.

WindowsXP with SP2 and Oracle Database

I have WindowsXP with SP2 and Oracle Database.

I am using that computer with Firewall on since couple of months and i am accessing database for my own purpose on clients are connecting to my computer.

Suddenly i need to allow one client to connect to my oracle database but trying to access the database after creating concern entry in tnsnames.ora still client not able to connect to database.

Then i realize after some efforts firewall is creating problem so i had switched off then try finally i get success but i want firewall should be on and also i want to allowed user to connect to my database for that i need to do below.

For normal oracle developer and client you need to do the following executables need to be added to the Windows Firewall exception list:

oracle.exe
tnslsnr.exe

how to restict allowed some user connect to oracle

Open your sqlnet.ora file and add below two entry.
after this only invited ip listed can connect to oracle.

TCP.VALIDNODE_CHECKING = YES
TCP.INVITED_NODES= (192.168.16.22, 192.168.16.120)

random number solution from tomkyte

more on unique no. January 07, 2006
Reviewer: Nikunj from india

Followup:

that is technically impossible, since Oracle numbers are 38 digits long - that
is not an infinite set of numbers.

what are you really trying to do here?

I am trying to do as below.
My user want to enter starting no., ending no. and list of random no. wants
between starting no. and ending no.

i.e. starting no. 200 and ending no. 400 he wants 50 random no. from list.

so i had created loop which will select random no. between the range and base on
user input on how much random no. he wants.

I had try but i am getting non unique nos.


Followup:

so, really what you want to do is

a) generate the set of all integers between "lo" and "hi"
b) get a random selection from that

no problem when stated like that - when stated the way you did the first time -
big problem!

Huge difference between stating what the goal is (then people can give
solutions) versus offering a partial solution and saying "finish it" :)



ops$tkyte@ORA10GR2> variable lo number
ops$tkyte@ORA10GR2> variable hi number
ops$tkyte@ORA10GR2> variable num_to_get number
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec :lo := 200

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> exec :hi := 400

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> exec :num_to_get := 5;

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> with data
2 as
3 (select level+:lo-1 l
4 from dual
5 connect by level <= (:hi-:lo+1)
6 )
7 select *
8 from (select * from data order by dbms_random.random)
9 where rownum <= :num_to_get;

L
----------
221
297
367
305
333

ops$tkyte@ORA10GR2> /

L
----------
287
344
332
315
277

ops$tkyte@ORA10GR2> /

L
----------
213
278
209
357
341

ops$tkyte@ORA10GR2> /

L
----------
347
397
249
295
354


That is but one approach.

order by conditional

order by decode( deptno, 10, ename ) desc,
decode( deptno, 20, job ) asc,
decode( deptno, 30, hiredate ) desc;

Media recovery

One day my oracle database server gives me below error message and my database running in no-archivelog mode.

ERROR at line 1:
ORA-01113: file 12 needs media recovery
ORA-01110: data file 12: 'D:\ORACLE\ORADATA\URMI\XDB01.DBF'

i am just searching for solution on tom kyte site and finally i get it which is as below.

ERROR at line 1:
ORA-01113: file 11 needs media recovery
ORA-01110: data file 11: 'C:\ORACLE\ORADATA\TEST\TEST01.DBF'

SQL> archive log list
Database log mode No Archive Mode <------------- NOARCHIVELOG MODE!
Automatic archival Disabled
Archive destination C:\oracle\ora92\RDBMS
Oldest online log sequence 2
Current log sequence 4

SQL> recover tablespace test; <---- hOW IS IT POSSIBLE IN NOARCHIVELOG MODE?
Media recovery complete.

SQL> select * from scott.t;

X
----------
10

Followup:

it just used the online redo logs. its always been "possible"

Attending Developer Days in Mumbai

During hands-on sessions.
Thanks - Pictures taken from Grant Ronald's Blog.

LOGMINER.

I am just testing logminer I have Oracle EE 9.2.0.1 installed on WinXP with noarchive mode.

connected with scott user.
scott@NIKUNJ> select count(*) from test;

COUNT(*)
----------
0

scott@NIKUNJ> insert into test select * from tab;
5 rows created.

scott@NIKUNJ> insert into test select * from tab;
5 rows created.

scott@NIKUNJ> insert into test select * from tab;
5 rows created.

scott@NIKUNJ> commit;
Commit complete.

scott@NIKUNJ> select count(*) from test;
COUNT(*)
----------
15

scott@NIKUNJ> delete from test;
15 rows deleted.

scott@NIKUNJ> commit;
Commit complete.

scott@NIKUNJ> select count(*) from test;
COUNT(*)
----------
0

scott@NIKUNJ>


Now I would like to get rows back.
connected with sys user and run below command.

sys@NIKUNJ> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => -
> 'C:\ORACLE\ORADATA\NIKUNJ\REDO03.LOG',OPTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

Here I had given my current online log file, you will get list of current log file through
sys@NIKUNJ> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
3 ONLINE
C:\ORACLE\ORADATA\NIKUNJ\REDO03.LOG
2 STALE ONLINE
C:\ORACLE\ORADATA\NIKUNJ\REDO02.LOG
1 STALE ONLINE
C:\ORACLE\ORADATA\NIKUNJ\REDO01.LOG

sys@NIKUNJ>


sys@NIKUNJ> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.

Above need for below reasons.

To fully translate the contents of redo logs, LogMiner requires access to a database
dictionary.

LogMiner uses the dictionary to translate internal object identifiers and datatypes to
object names and external data formats. Without a dictionary, LogMiner returns
internal object IDs and presents data as hex bytes.

For example, instead of the SQL statement:
INSERT INTO emp(name, salary) VALUES ('John Doe', 50000);
LogMiner will display:
insert into Object#2581(col#1, col#2) values (hextoraw('4a6f686e20446f65'),
hextoraw('c306'));"

LogMiner gives you three choices for your source dictionary:
1.Extracting the Dictionary to a Flat File
2.Extracting a Dictionary to the Redo Logs
3.Using the Online Catalog

sys@NIKUNJ> execute dbms_logmnr.start_logmnr;
PL/SQL procedure successfully completed.

sys@NIKUNJ> select sql_redo,sql_undo from v$logmnr_contents where username='SCOTT';


Then for time base you have to execute as below.

execute dbms_logmnr.start_logmnr(STARTTIME => TO_DATE('01-OCT-2005 10:00:00', 'DD-MON-YYYY HH:MI:SS'), -
ENDTIME => TO_DATE('01-OCT-2005 12:00:00', 'DD-MON-YYYY HH:MI:SS'));

might be you get error message like this which i get
ERROR at line 1:
ORA-01291: missing logfile
ORA-06512: at "SYS.DBMS_LOGMNR", line 53
ORA-06512: at line 1

then i add another log file
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => 'C:\ORACLE\ORADATA\NIKUNJ\REDO02.LOG', -
OPTIONS => DBMS_LOGMNR.ADDFILE);

and executed once again and i get sucess.
execute dbms_logmnr.start_logmnr(STARTTIME => TO_DATE('01-OCT-2005 10:00:00', 'DD-MON-YYYY HH:MI:SS'), -
ENDTIME => TO_DATE('01-OCT-2005 12:00:00', 'DD-MON-YYYY HH:MI:SS'));

EXECUTE DBMS_LOGMNR.END_LOGMNR;



LogMiner information is contained in the following views. You can use SQL to query them as you would any other view.
V$LOGMNR_CONTENTS
Shows changes made to user and table information.
V$LOGMNR_DICTIONARY
Shows information about the LogMiner dictionary file, provided the dictionary was created using the STORE_IN_FLAT_FILE option. The information show includes the database name and status information.
V$LOGMNR_LOGS
Shows information about specified redo logs. There is one row for each redo log.
V$LOGMNR_PARAMETERS
Shows information about optional LogMiner parameters, including starting and ending system change numbers (SCNs) and starting and ending times.