Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    3

    Question Unanswered: System Tablespace issue

    I'm a recently-hired dba working for a company that has an existing Oracle aplication up and running where all of the implementation work was done by a vendor. They installed their application tables in the system tablespace. I want to correct this error. What kind of a mess do I have on my hands?

    I need to keep the application available while I am repairing this problem. The server this is installed on has limited available disk space.
    Any help you can provide will be greatly appreciated.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    Is the application schema different than the system schema?

    Ie: Application schema is 'scott', but all of it's objects are located on the system tablespace.

    OR, did they create everything as the SYSTEM user?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jul 2003
    Posts
    3
    Originally posted by The_Duck
    Is the application schema different than the system schema?

    Ie: Application schema is 'scott', but all of it's objects are located on the system tablespace.

    OR, did they create everything as the SYSTEM user?
    answer - Application schema is 'scott'

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    wow! I was right on the money!
    2 points for me, but -500 points for the idiots that set that up.

    You actually have a few options:
    1. rebuild all scott objects and assign the appropriate tablespace as you do this.
    2. create a new user/schema with the correct defaults and then export scott-objects, import into new-user, re-align application to new-user, then remove everything from scott (drop user scott cascade).
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jul 2003
    Posts
    3
    The_Duck wrote -

    You actually have a few options:
    1. rebuild all scott objects and assign the appropriate tablespace as you do this.
    2. create a new user/schema with the correct defaults and then export scott-objects, import into new-user, re-align application to new-user, then remove everything from scott (drop user scott cascade).

    My question still remains about the disk space issue and availability of the application.

    1. If I choose option 1, won't I need to allocate disk space for the new-user tablespace? I probably don't have enough disk space to hold two copies of the objects with data on the server.

    2. If I choose option 2, could this be done with very little down time of the application as opposed to option 1?

    Thanks for the help as I am still learning Oracle after working with MS-SQL Server. And yes, the vendor should be shot for what they did!!

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    I would determine how much space the scott objects take up.

    Attempt a test. Hopefully you have a test oracle server.

    Export scott objects with rows=Y.
    create a new schema and tablespace on your test server.
    Attempt to import all objects onto the test schema (make sure your import doesn't try to load all the objects onto your test-server SYSTEM tablespace!).
    Then take a look at the tablespace size.
    That at least would give you some clue.

    You could also import with no indexes to save space and then re-create all the indexes once you delete the old objects after the tables are loaded.


    Option 1 might be better for you, but you might have some downtime.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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