DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: ReOrg database

  1. #1
    Join Date
    Apr 2001
    Posts
    151
    Hi All,

    I have been assigned to reorg our production database and so far I have no idea how to get it done. Any input will be useful.

    Oracle 8.06.1/NT-sp6, DB about 75G.

    Tablespace
    NAME
    ------------------------------
    SYSTEM
    RBS
    USR
    TEMPORARY
    INDX
    ERPDATA

    Right now, INDX is about 35G and ERPDATA is about same size.
    My task here is to break INDX and ERPDATA tablespace into several smaller tablespace based on each companyid in our ERP system.
    Someting like,
    INDX105, INDX206, INDX100, INDX...
    DATA105, DATA206, DATA100, DATA...
    (105,206,100 are represented each companyID in ERP)

    Currently, I have only one user, ERP_USR in Oracle database and this user owns everything (beside system owned objects).

    When users connect to database, they are using ERP_USR, one and the only one user in Oracle. How do I make company 100 users to work on INDX100 and DATA100 tablespaces. In other words, user login to database as ERP_USR and based on different companyid they are working on, data need to be stored in companyid-related tablespace. How can I do this? Please help.

    Elin@trend

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Sounds like you will have to partition your existing tables based on company_id.
    Jeff Hunter

  3. #3
    Join Date
    Apr 2001
    Posts
    151
    Partition sound right but here is the problem.
    companyid only shows on table name but not field in tables.

    say, I have table tttyyy105, tttyee105, ttrrurk206, ttslddd206
    and I know tttyyy105 and tttyee105 are belong to companid=105. How can I store those 105 data to INDX105 and DATA105.

    Any suggestion?
    Elin@trend

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Oh, that's easy. I was assuming you had a relational design.

    0. take cold backup
    1. export the *105 tables
    2. drop *105 tables
    3. imp with indexfile=yyy.sql
    4. edit yyy.sql to change the tablespace name
    5. pre-create your tables
    6. import with ignore=y
    Jeff Hunter

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    hello

    if you are on 8i you could simply do a alter table table-name move tablespace..................

    you can then rebuild your indexes online to a different tablespace to.

    hope this helps

    regards
    hrishy

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    But not on 8.0.6, the version you reported.
    Jeff Hunter

  7. #7
    Join Date
    Nov 2000
    Posts
    245
    ex.
    drop all primary key index for 105tab then:

    ALTER TABLE 105tab
    ADD CONSTRAINT 105_pk PRIMARY KEY (105_id)
    USING INDEX
    TABLESPACE 105_INDEX
    STORAGE ( INITIAL 128K NEXT 64K MAXEXTENTS UNLIMITED PCTINCREASE 0 );


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