cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle 8.1.6 Table

Raj_62
Occasional Visitor

Oracle 8.1.6 Table

Hi,

I use Oracle 8.1.6
I created a (simple )table couple of days back and inserted 16000+ records into it.

Now i'm unable to access this table.
For eg..

Say, If i try anyone of the follwing sqlplus client would freeze and i had to kill it.

1) Select * from TEMP_REPORT_2004
2) Truncate table TEMP_REPORT_2004
3) drop table TEMP_REPORT_2004
4) Select * from TEMP_REPORT_2004 where 1=0

What could be the issue. ANy help ??
Attaching script i used to create the table for ref.

Thanks a lot
Raj
10 REPLIES
Steven E. Protter
Exalted Contributor

Re: Oracle 8.1.6 Table

check space on the filesystem you tablespace is on. Maybe it took an extent and you are at 100%.

To correct, you'd need to relieve the space problem and then deal with whatever the database is unhappy with.

Any messages in alert file

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Jean-Luc Oudart
Honored Contributor

Re: Oracle 8.1.6 Table

Hi

Is client SQlplus on same machine and connect ok, this is just the query that hangs, please confirm

Can you run the following commands
1) desc TEMP_REPORT_2004
2) select * from TEMP_REPORT_2004 where rownum < 2

Regards
Jean-Luc

fiat lux
Volker Borowski
Honored Contributor

Re: Oracle 8.1.6 Table

Hi Raj,

probably you created the table as another user.

select owner from dba_tables where table_name='TEMP_REPORT_2004';

should reveal which user created this table.
Connect as this user and retry.

Volker

Raj_62
Occasional Visitor

Re: Oracle 8.1.6 Table

Hi Jean,

1) desc TEMP_REPORT_2004
2) select * from TEMP_REPORT_2004 where rownum < 2

1) Returns immdly with structure.
2) takes a while and coming back with results.

I'm using sqlplus client and server is in different location. Other queries runs fine.
Only this table gives me problem.

Steven,

i'm new to oracle.Can you tell me how to check whthr it reached last extent and no space left.

Thanks
Raj

Raj_62
Occasional Visitor

Re: Oracle 8.1.6 Table

Volker,

I only created this table. i again
double checked the owner and its me.

Thanks
Raj
Raj_62
Occasional Visitor

Re: Oracle 8.1.6 Table

Steven,

I found a way to check space on my table space .. its only 60.09% full.

I used script avbl in following link to check space details on my tablespace..

http://www.remote-dba.cc/oracle_tips_tablespace_full.htm

Thanks
Raj
Yogeeraj_1
Honored Contributor

Re: Oracle 8.1.6 Table

hi,

maybe you had locked the table in a previous session.

try to run the following SQL statement:
select oracle_username, process, OS_USER_NAME, locked_mode from v$locked_object;

to get an indication of the locks (if any).

there could be also foreign key association between your tables....

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Brian Crabtree
Honored Contributor

Re: Oracle 8.1.6 Table

Raj,

I am going with the assumption that you haven't bounced the database yet. If you have, and you are still getting this error, then it is something more serious. I expect from what you have said is that this is a library cache lock on the table. From what I can tell, this is a lock on the header of the table, but doesn't show up anywhere else.

This is what you have to do. Login to one session of sqlplus, run a 'select distinct sid from v$mystat' to get your sid, then run a query that hangs against that table.

Then login to another sqlplus session (leaving the last one running) and run the following sql statement:

select a.username,a.sid,a.serial#,last_call_et,spid from v$session a, v$process b where
a.paddr = b.addr and saddr in
(select kgllkses from x$kgllk where kgllkreq = 0 and kgllkhdl in
(select kgllkhdl from x$kgllk where kgllkreq > 0 and kgllkses in
(select saddr from v$session where sid = &locksid)));

You will need to enter the sid from your session in the first sqlplus session. This will most likely give you a list of sid's and serial#'s that you need to kill. This should free up the lock, although it might take a few times.

As to the next question, why. I have no idea. I have never been able to reproduce the problem, and Oracle has said that it shouldn't happen, however what you are posting is similar to my experiances with the problem.

Thanks,

Brian
Don Spare
Regular Advisor

Re: Oracle 8.1.6 Table

How did you create the table? With a name beginning with "TEMP" did you create it as a 'TEMPORARY' table? If you did then the data you put in it is only good for the current session. No other session can see that data even if your session is still active. To eliminate this behavior you must drop the table and re-create it without the 'TEMPORARY' adjective. Then the data will stick around across sessions like a normal table.

Jean-Luc Oudart
Honored Contributor

Re: Oracle 8.1.6 Table

Hi Raj,

You said that my second query gave results eventhough took very long to run.
How long ?
(in sqlplus : set timing on)
could you run a trace on the session ?

Also, queries on other tables are fine : Are you on same tablespace as other tables ?
If not, could check the physical file(s) behind the tablespace and compare with other tablespaces ?

Regards,
Jean-Luc
fiat lux