Database Hangs when Creating Objects
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Database Hangs when Creating Objects

  1. #1
    Join Date
    Jan 2001
    Posts
    515

    Database Hangs when Creating Objects

    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.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,966
    What is the SQL that you are using to create tables?
    Is the tablespace growing while you are creating tables and by how much?
    this space intentionally left blank

  3. #3
    Join Date
    Jan 2001
    Posts
    515

    SQL

    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.

  4. #4
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    Re: Database Hangs when Creating Objects

    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?
    I'm stmontgo and I approve of this message

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,966
    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.
    this space intentionally left blank

  6. #6
    Join Date
    Jan 2001
    Posts
    515

    I think it is fixed

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width