-
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.
-
Sounds like you will have to partition your existing tables based on company_id.
-
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?
-
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
-
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
-
But not on 8.0.6, the version you reported.
-
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 );
