Datafile usage in tablespace

select substr(a.file_name,1,30),
substr(a.tablespace_name,1,20),
a.bytes allocated,
nvl(b.free,0) free,
a.bytes-nvl(b.free,0) used,
(nvl((a.bytes-nvl(b.free,0)),0)*100)/a.bytes usage_perc
from dba_data_files a,
( select file_id, sum(bytes) free
from dba_free_space
group by file_id ) b
where a.file_id = b.file_id (+)
AND a.tablespace_name='&tablespace'
/

Mount USB Pen Drive in to Linux.

Before plug in USB pen see /var/log/messages kernel through which you get device name and path. Here I get sdc as device name (or sda0, sda1 like that.).

Now mount it.

Mount for FAT file system
mount –t vfat /dev/sdc /mnt/mounttemp/

Mount for NTFS file system
mount –t ntfs /dev/sdc /mnt/mounttemp/

access your device

Configuration for allow public user to use service on server i.e. port 25, 110.

I have Red Hat Linux 9.0 on which I have 2 NIC.

NIC 1 is used for internal network connection.
NIC 2 is used for Broad band internet connection which has static IP.

Now in this server my sendmail on 25 , POP3 on 110, HTTPD on 90 Is running now I want to allow user to access this services from outside my network or on internet.

In this box I have my firewall and for that I have to reconfigure and opened port 25, 110, 90 so outside user can access it.

After opening port and checking my settings I am only able to access POP3 service.

Why ?

Because my sendmail & HTTPD is configure on my local/internal IP.

So which ever request comes inside for port 25 and port 90 I need to forward to my local/internal ip port 25 and 90.

So need to add below in my rc.local file for route the request.

Iptables –t nat –A PREROUTING –i eth1 –p tcp – -dport 25 –j DNAT - -to :25
Iptables –t nat –A PREROUTING –i eth1 –p tcp – -dport 90 –j DNAT - -to :90

After doing routing I am able to access.

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;