Database Hangs when Creating Objects

[lesstjm]

We are having a problem with our development database. It seems to take forever when we create tables or indexes. I thought that it might be that the default tablespace for the user next extent parmeter was to small. We have autoextensible data files and we had a next extent of 32k. I made it 5m but it did not make a difference. Can anyone help me out with this? It is Oracle 8.1.7, dictionary manage tablespaces.

[gandolf989]

What is the SQL that you are using to create tables?
Is the tablespace growing while you are creating tables and by how much?

[lesstjm]

Ususally when creating tables in development we are doing something like

create table abc as
select * from def;

The create index

create index ind_abc on abc(column1);

Sometimes the files are extending but usually they are not.

[stmontgo]

Originally posted by lesstjm
We are having a problem with our development database. It seems to take forever when we create tables or indexes. I thought that it might be that the default tablespace for the user next extent parmeter was to small. We have autoextensible data files and we had a next extent of 32k. I made it 5m but it did not make a difference. Can anyone help me out with this? It is Oracle 8.1.7, dictionary manage tablespaces.

did you check the alert log? is the archiver stuck?

[gandolf989]

You should set SQL_TRACE = TRUE and TIMED_STATISTICS=TRUE in your session before creating your table. Create your table and then do a tkprof on the trace file.

Of course when you create a table from a query the amount of time it takes is dependent on the amount of data you are tying to put into the table. You could easily create the table first, preallocate the space and then do the insert based on a query.

[lesstjm]

I restarted the database and increased the db_block_buffers. There seemed to be a lot of back and forth to disk. This seems to have fixed the problem. Thanks for all your help.