Not able to create table on Database / Oracle not shutting down

[KVS Raju]

Hi Friends.

We are facing a problem on Oracle8.0.6 on HP Unix 11.0.
We are not able to create tables on any users, create table command is hanging
We are able to create views,Sequences and users...
While shutting down the database also it is hanging. (even with shutdown abort)
Database is running on NoArchive mode. We have enough of space in tablespcaes. Oracle is not writing any alert logs.
Could you please suggest me what may be the problem.

[T G Manikandan]

Just check up your status of the log files

select * from v$logfile

If the status is inactive,

Just do a

alter system switch logfile

The alert log should have the problem specified check for the file.

Revert

[twang]

Have you try to recreate another user and take a try in table creation:
SQL> connect sys/******
SQL> create user scott identified by tiger
default tablespace users temporary tablespace temp quota 100m on users;
SQL> grant connect, resource to scott;
SQL> connect scott/tiger
SQL> create table xyz ( a number);
SQL> create table abc ( d number);

Could you successfully create the tables?

[twang]

Have you try to recreate another user and take a try in table creation:
SQL> connect sys/******
SQL> create user scott identified by tiger
default tablespace users temporary tablespace temp quota 100m on users;
SQL> grant connect, resource to scott;
SQL> connect scott/tiger
SQL> create table xyz ( a number);
SQL> create table abc ( d number);

Could you successfully create the tables?

[Massimo Bianchi]

Hi,

In order to see why shutdown immediate is hanging we need to do the following:

A. While shutdown immediate is hanging

Start Server Manager (or SQL*Plus for 8i or higher)
SVRMGRL> connect internal
SVRMGRL> select * from x$ktuxe where ktuxecfl = 'DEAD';

This shows dead transactions that smon is looking to rollback.

B. Plan to shutdown again and gather some infomation. Before issuing the
shutdown immediate command set some events as follows:

SVRMGRL> connect internal
SVRMGRL> alter session set events '10046 trace name context forever,level
12';
SVRMGRL> alter session set events '10400 trace name context forever, level
1';
SVRMGRL> shutdown immediate;

10046 turns on extended SQL_TRACE for the shutdown process.
10400 dumps a systemstate every 5 minutes.

Let the shutdown go for around 15 minutes and then send the traces to Oracle
Support. The trace files should show where the time is going.

Note :
Shut down immediate may hang because of various reasons.
- processes still continue to be connected to the database and do not terminate.
- SMON is cleaning temp segments or performing delayed block cleanouts.
- Uncommitted transasactions are being rolled back.




HTH,
Massimo

[Brian Crabtree]

You might also want to verify that automatic archiving is active as well.

"alter system log archive start" will kick it off. The init.ora parameter log_archive_start=true should work as well.

Thanks,

Brian

[Yogeeraj_1]

hi,

For users to be able to create tables, you should grant them required privileges.

E.g.
create user user1 identified by user1 default tablespace users temporary tablespace temp;

grant create session, create table to user1;

In the worst case, you would do a:
grant connect, resource to user1;

As for the shutdown problem, if even with a shutdown abort, the database does not shutdown, you should contact Oracle Support.

Also, unless there is a specific reason, i would suggest that you enable archive log mode!

Regards
Yogeeraj

[KVS Raju]

Hi Friends.

We have solved the problem. Problem in the desk paging size. After rectifying the problem we are able to do all the operations.

Thanks a lot for you valubale info.