| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: providing 24*7 database ---
Seemed fine when I tried it (and thanks for the idea!). The trick
with the indexes is that the ones on the partitioned table have to be local,
which was in the scripts provided, and the 'exchange partition' had to say
'including indexes', which was not. Adding 'including indexes' made this
work like a charm for me.
Jim>>> AponteT_at_hsn.net 10/24/01 11:25AM
>>>
I
couldn't verify that the non-partitioned indexes become unusable after
exchanging the partition for the normal table. In the sample I posted
I snipped the output of the queries on USER_INDEXES and USER_PART_INDEXES,
but my tests showed that they remain valid. I'm curious to see why
your results are different. Can you post the spooled output of your
test?
<SPAN
class=638130914-24102001>
<SPAN
class=638130914-24102001>Thanks.
Tony
Aponte
<FONT face="Times New Roman"
size=2>-----Original Message-----From: Narender Akula
[mailto:narender.akula_at_terralink.co.nz]Sent: Tuesday, October 23,
2001 10:30 PMTo: Multiple recipients of list
ORACLE-LSubject: RE: providing 24*7 database
---
<SPAN
class=152063301-24102001>Thanks all for the input.
hi
tony ,
<SPAN
class=152063301-24102001>Quick question ... when you exchange partititons with
non partitioned table data , all indexes on non partitioned tables become
unusable status right.
do
have to rebuild them after every exchnage...
<SPAN
class=152063301-24102001>
<SPAN
class=152063301-24102001>naren
<FONT face=Tahoma
size=2>-----Original Message-----From: Aponte, Tony
[mailto:AponteT_at_hsn.net]Sent: Tuesday, 23 October 2001
05:06To: Multiple recipients of list ORACLE-LSubject:
RE: providing 24*7 database ---
We use a modified version of your duplicate schema
idea. But we don't have the objects in different schemas. We use
partitioned objects so that we can exchange the partitions with the
production tables at a scheduled time. The voodoo is that we use a
single range partition of MAXVALUE and all indexes are LOCAL
PARTITIONED. The partitioning key doesn't really matter in this setup
since we aren't using the features for its advantages, just to be able to
swap data and index segments on the fly. I've attached a transcript
showing the actual sequence but I'll give you a short explanation
first:
There are production tables/indexes that are used by the
application, whether directly or via synonyms. There is a second set
of tables with a _TEMP suffix that have duplicate structural definitions
(constraints, column names and data types, etc.) The
indexes also end with a _TEMP but are identical to the production
ones. The only difference is that they are partitioned
tables/indexes. All partitioned objects have a single range partition
by a bogus column. The single partition is bounded by the MAXVALUE
keyword, so all of the data is contained in one partition.
Now you can manipulate the _TEMP tables at your convenience
without interrupting the access tot he "published" objects. Once you
have refreshed your _TEMP objects and are ready to publish the new data your
would execute a series of ALTER TABLE <tablename>_TEMP EXCHANGE
PARTITION TABLE <tablename>. That's it. No re-pointing of
synonyms, revalidating of views/stored procs./etc. The application
keeps chugging along. The next execution of SQL will use the published
tables.
HTH Tony Aponte
Table created.
SQL> create index xi1 on x(x1);
Index created.
SQL> create table y(x1 number,x2 varchar2(50))
2 partition by range (x1)
(partition y values less than (maxvalue));
Table created.
SQL> create index yi1 on y(x1) <FONT
size=2> 2 local (partition yi1 );
Index created.
SQL> insert into x values (1,'original data from regular
table');
1 row created.
SQL> insert into y values (2,'original data from
partitioned table');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from x;
X1
X2
--------------------------------------------------
1
table
SQL> select * from y;
X1
X2
--------------------------------------------------
2
table
SQL> alter table y exchange partition y with table x;
Table altered.
SQL> select * from x;
X1
X2
--------------------------------------------------
2
table
SQL> select * from y;
X1
X2
--------------------------------------------------
1
table
SQL> select * from user_indexes;
output snipped <FONT
size=2>
SQL> select * from user_part_indexes; output
snipped
SQL> alter table y exchange partition y with table x;
Table altered.
SQL> select * from x;
X1
X2
--------------------------------------------------
1
table
SQL> select * from y;
X1
X2
--------------------------------------------------
2
table
SQL> select * from user_indexes;
output snipped
SQL> select * from user_part_indexes;
output
snipped
SQL> drop table x;
Table dropped.
SQL> drop table y;
Table dropped.
SQL> spool off
-----Original Message----- From:
Narender Akula [<A
href="">mailto:narender.akula_at_terralink.co.nz]
Sent: Thursday, October 18, 2001 5:30 PM <FONT
size=2>To: Multiple recipients of list ORACLE-L <FONT
size=2>Subject: providing 24*7 database ---
hi gurus,
Our shop ( GIS oracle spatials ) attempting to provide a
production database (7x 24 hours) , currently we
have to offline database for users while loading of
data. we donot what users to access data while
loading. We are thinking of provide 24* 7 services
to customers with out going offline.
What are the best possible solutions ? I had
few but I donot know its right direction
.................
 |
 |