Narender hasn't replied yet. But the sample I sent only uses local indexes since there is only one partition. I did see that Jim Conboy pointed out that I missed the 'including indexes' option.
Tony
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 24, 2001 12:35 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: providing 24*7 database ---
Tony,
If the partitioned indexes have a locality of global (not local), then they
become invalid after activity on the underlying table partitions.
Does Narender say whether his indexes are global or local?
Cherie Machler
"Aponte, Tony"
<[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
et> cc:
Sent by: Subject: RE: providing 24*7 database ---
[EMAIL PROTECTED]
om
10/24/01 10:25
AM
Please respond
to ORACLE-L
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?
Thanks.
Tony Aponte
-----Original Message-----
From: Narender Akula [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 23, 2001 10:30 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: providing 24*7 database ---
Thanks all for the input.
hi tony ,
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...
naren
-----Original Message-----
From: Aponte, Tony [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, 23 October 2001 05:06
To: Multiple recipients of list ORACLE-L
Subject: 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
********************** pseudo-attachment
******************************
SQL> create table x(x1 number,x2 varchar2(50));
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)
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 original data from regular table
SQL> select * from y;
X1 X2
---------- --------------------------------------------------
2 original data from partitioned table
SQL> alter table y exchange partition y with table x;
Table altered.
SQL> select * from x;
X1 X2
---------- --------------------------------------------------
2 original data from partitioned table
SQL> select * from y;
X1 X2
---------- --------------------------------------------------
1 original data from regular table
SQL> select * from user_indexes;
output snipped
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 original data from regular table
SQL> select * from y;
X1 X2
---------- --------------------------------------------------
2 original data from partitioned 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 [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 18, 2001 5:30 PM
To: Multiple recipients of list ORACLE-L
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 .................
Possible Solutions
Replication -
* not possible until Oracle 9i spatial (because of the
restriction on
replicating objects).
* Even then expense of additional licensing/machinery may
be too great
Duplicate instances - have 2 instances and users switch from one
to other
after production load.-- how to implement ?
Duplicate schema within current database. Have 3 schemas inside
a single
TIPSPROD instance. The schema with the current data remains in
production
until the second schema is loaded with the new data. This is can
only work
if we introduce a third schema that holds the views. At certain
time, we
redefine all the views in the DATAVIEWS schema. --- its kind of
duplication
--- certainly not my option
Can some body direct me where to look or any ideas ?
TIA
> narender.akula
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Narender Akula
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858)
538-5051
San Diego, California -- Public Internet access / Mailing
Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You
may
also send the HELP command for other information (like
subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
