Local Management of Tablespace - Help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Local Management of Tablespace - Help

  1. #1
    Join Date
    Dec 2001
    Posts
    10

    Unhappy Local Management of Tablespace - Help

    We are using oracle 8i std edition on solaris. We want to create a locally
    managed tablespace for a particular schema. The objects of this schema
    will be imported every day from the same schema on another same database server.
    The existing schema tablespace is locally managed with system allocation type.

    1.We want to know what are the points to be kept in mind while creating a locally
    managed tablespace in the new db.
    2. Should the tablespace have a single datafile or more than one(The existing one has
    3 datafiles)? Which one is better? (There will be no activity on this new db as it is
    for standby only. If the existing one fails, this db will be used.
    4. Since we are going to export from the existing one and import into the new db schema
    every day ( the schema in the new db will be dropped and recreated daily before import),
    will there be any problems that we will encounter by doing so?
    Please let me know all the information so that we do not face any problem after
    implementing this.

    Note: Currently the failover schema tablespace is dictionary managed and we plan to
    entirely drop this tablespace and create a new one by the same name but locally managed
    this time. So what steps to be taken while dropping the tablespace and creating a new one
    by the same name? Will there be any problems because of this? Is a db restart required
    after this? Kindly enlist the points and steps.

    We appreciate your immediate help.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    1. Rather a broad question, but you must lose some of your hangups from the DMT days. It's not bad to have a lot of extents per segment (up to 3000, say) for example.

    2. There are various reasons for having multiple datafiles ...

    a) load balancing -- placing a file on each i/o device allows the load to be spread
    b) avoiding too-large files -- for convenience in file-based operations

    If you have multiple arrays over which you want to spread the tablespace i/o, then use an LMT with a pretty small (eg. 1Mb) uniform extent size and one, two etc. files per array. This is a poor man's striping methodology that works pretty well.

    3. ?

    4. I wouldn't expect any problems with that.

    I don't think that a restart will be required.

    I'm inclined to wonder whether export/import is the best methodology for this operation. Have you considered taking a full backup to clone the database, or some other technique that would be faster, and probably more reliable? (I'm not a big fan of export/import)
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Dec 2001
    Posts
    10

    Thanks for that quick response

    I really appreciate that.
    Can you please tell me the steps to drop a tablespace.(it currently has 3 datafiles). What dependencies to be checked?

    I am well aware that exp/imp is not the solution, but we have to stick on to this due to some constraints. So how best can this be achieved? We do not want to face some storage problems everyday.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Dropping the TS is just a matter of running the "DROP TABLESPACE" command -- see the SQL Reference for syntax and options.

    I'm not sure what problems you might anticipate with exp/imp, other than performanceon the import. I would say you should just try the process and monitor the it for any issues, dealing with them as they turnup.

    What constraints prevent you from considering other methodologies?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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