Ishwar - What Oracle version. Just off the top of my head, I think that 8i
only allows the subpartition to be hashed. What is your goal?
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Friday, June 20, 2003 12:00 PM
To: Multiple
Hey Dennis:
I created a range partition table with the last high_value being maxvalue.
The partition key is partner_id (an incremental sequence).
Now pmax (the last partition) has become very big and gives snapshot too
old.. while exporting.
Ultimate Plan
=
I realize that the
The version is 9i.
-Original Message-
DENNIS WILLIAMS
Sent: Friday, June 20, 2003 3:10 PM
To: Multiple recipients of list ORACLE-L
Ishwar - What Oracle version. Just off the top of my head, I think that 8i
only allows the subpartition to be hashed. What is your goal?
Dennis Williams
When you say 9i, do you mean 9.0.1 or 9.2.0? In 9.2.0 You can have list subpartitions.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_73a.htm#2153058
-Original Message-
From: Ishwar Tewari [mailto:[EMAIL PROTECTED]
I created a range partition table
Ishwar
I am fairly new to partitions myself and must relearn the commands each
time I need to work with the partitions. So I will speak more to the
philosophy of a DBA that doesn't like to end up in a jam.
I get a bad feeling about hashing to get around an immediate problem. I
also question
Jacques, the version is 9.0.1.1.1.
Dennis, Thanks for the confidence I totally agree with you about designing
and everything else.
Could there be a temporary solution to get successful exports until we
embark on this project.
we are in a 24x 7 environment.
Rgds
Ishwar.
-Original
Ishwar
One priority might be to get to 9.2. There are reasons Oracle has given
9.0.1 a short decommission date.
One idea would be to export with a SQL statement. This would reduce the
size of your exports. Also, if you are mostly just adding new data (few
updates), you may be mostly just
I agree with Dennis Williams. To know what is the right answer for you,
first you should determine why you want the table partitioned in the first
place. Is it so that you can easily archive old data by using the alter
table drop partition command? Is it so that your queries will benefit
from
Dennis,
Why not just drop the partition?
Arup
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, February 21, 2003 9:19 AM
I lost a data file that contained the tablespace for an empty partition. I
dropped the datafile from Oracle, and the
Thanks Arup
I have tried some experiments on our test system. It is that old problem
of Oracle having so much integrity that you can't do something simple like
that. When you try to just drop the partition, Oracle says woops I can't
read the file that partition is stored on.
Looks as if my
Hey Dennis,
You brought up an interesting situation so I had to go test it. I'll provide my test
case in a second, but the summary is that I would copy or export this table. You
should still be able to read from and write to this table, including the partition
which belongs in the deleted
I like that 40% OCP, 100% DBA.
--
Lyndon Tiu
Quoting DENNIS WILLIAMS [EMAIL PROTECTED]:
Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Lyndon Tiu
INET: [EMAIL PROTECTED]
Fat City
Dennis,
How about this strategy?
(1) Find out the tablespace the partition is in.
(2) ALTER TABLESPACE ... OFFLINE;
(3) ALTER TABLE ... DROP PARTITION ...
HTH.
Arup
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, February 21, 2003 1:55 PM
Thanks Darrell. I ended up moving the data, dropping and recreating the
table, and moving the data back.
At least I received more ideas from this list than from Oracle Support
(zero). Since I didn't have an active error, it got rated a priority 4. Oh
well, I'm just glad to have that over with,
Alter tablespace ... offline didn't work in testing I did in 8.1.6, as it wants to
checkpoint and update the datafiles in that tablespace when taking it to offline
status.
First alter database datafile '/missingdatafilename_goes_here' offline drop, then
offline the tablespace.
[EMAIL
No problem, thanks for the feedback.
I learned as well!
[EMAIL PROTECTED] 02/21/03 04:34PM
Thanks Darrell. I ended up moving the data, dropping and recreating the
table, and moving the data back.
At least I received more ideas from this list than from Oracle Support
(zero). Since I didn't
Hi Dennis...
Sorry for the late reply but I'm just reading this now... I've faced
similar issues in my environment... In the future, I think the following
will work for you:
Create a standalone table of the same definition as the partitioned table
Exchange the partition that belongs to the
Title: RE: Partition and primary key
Here a sample of my alert.log, SCOPE_MD_IDX is my index tablespace :
ORA-1652: unable to extend temp segment by 320 in tablespace SCOPE_MD_IDX
ORA-1652: unable to extend temp segment by 320 in tablespace TEMP
1. I've then created a primary key
Philippe
If you are using parallel clause in index creation/rebuilding you need more
space in target tablespace as well as temp tablespace.
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Fri, 07 Feb 2003 08:09:25 -0800
You
Title: RE: Partition and primary key
thank U Johnattan, which book are you talk about ? (isbn)
-Message d'origine-
De: Jonathan Lewis [mailto:[EMAIL PROTECTED]]
Date: mercredi 5 février 2003 19:20
À: Multiple recipients of list ORACLE-L
Objet: Re: Partition and primary key
First
Practical Oracle 8i
Addison Wesley; ISBN: 0201715848
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )
UK___March 19th
Title: RE: Partition and primary key
Jared,
UNABLE TO EXTENT TEMP SEGMENT BY ...
-Message d'origine-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Date: mercredi 5 février 2003 21:50
À: Multiple recipients of list ORACLE-L
Objet: Re: Partition and primary key
What is the exact
AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: Partition and primary key
Jared,
UNABLE TO EXTENT TEMP SEGMENT BY ...
-Message d'origine-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED
First create the index UNUSABLE - which will
create each index as a single extent at virtually
no cost.
Then for rebuild each partition.
Than add the PK using the index.
(At least, that's what it says in my book ;)
I think you'll find a similar piece of advice in
the PL/SQL packages manual
What is the exact error message, including text?
It could be failing on the temp segment initially created in
the index tablespace, and not necessarily running out
of sort space.
Jared
NGUYEN Philippe (Cetelem) [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
02/05/2003 06:52 AM
Please
Title: Partition and primary key
You need
tohave 15 to 20gig temp space to do this, assuming you are
equipartitioning the primary key index.
-Original Message-From: NGUYEN Philippe (Cetelem)
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, February 05,
2003 6:53 AMTo: Multiple
Sergei
Check HIGH_VALUE column in DBA_TAB_PARTITIONS and compare it to current
date.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, December 02, 2002 3:48 PM
Hello everybody.
I have a number of
DBA_TAB_PARTITIONS
-Original Message-
Sent: Monday, December 02, 2002 3:49 PM
To: Multiple recipients of list ORACLE-L
Hello everybody.
I have a number of tables with range by date partitions. I need to
write a script to monitor the latest partitions and send me an e-mail if
it's close
Got it
-Original Message-
Neyman
Sent: Monday, December 02, 2002 1:45 PM
To: Multiple recipients of list ORACLE-L
Sergei
Check HIGH_VALUE column in DBA_TAB_PARTITIONS and compare it to current
date.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: Multiple
You may use the following query will identify the 'highest' partition by date range.
select partition_name, partition_position , high_value, tablespace_name
from dba_tab_partitions
where table_name = 'table_name'
and table_owner = 'owner'
and partition_position = ( select max(partition_position)
05, 2002 4:09 PM
Para: Multiple recipients of list ORACLE-L
Asunto: Re: Partition Question
I agree...
What are you trying to accomplish with partitioning? Partitioning by year
/ month / day / whatever can make it easy to truncate / archive old data.
The only trick is to create new partitions
cc:
Sent by: Subject: Re: Partition Question
[EMAIL PROTECTED
It depends on your reason for partitioning -- if you mean to drop a partition in
the future (to roll off the 1999 data or whatever) then the ID range is
potentially a valid approach, as long as ID is serial. If you just want to put
chunks on different disk volumes, you could use the type or even
I think that there is another dimension to the problem that you may not be
aware of and that is even when you think that you succeeded to eliminate the
use of the index on the batch_date using a hint (no_index), Oracle still has
to go the table using the rowid (after conversion from bitmap) to
Larry,
Perhaps this is out of the question, but...
What about the possibility of creating a batch_month column and using that
to partition the table. Leave the bitmap index on batch_date for those who
need it and let the other users go into the table using batch_month
= 'blah'. Unfortunately
Hi Larry,
First I would suggest doing daily partitioning and dropping the index on the
batch_date.
Regarding your sql: partitions eliminations never substitutes the necessity
to validate any predicates on the partitioning key in the where clause.
If it's not feasible to partition by day, I
Hi Larry,
First I would suggest doing daily partitioning and dropping the
index on the
batch_date.
That's been kicked around. It's not a bad idea -- it would make sure the
index, since it wouldn't exist, doesn't get in the way. FWIW, it's
partitioned on a monthly basis to fall more in line
You can use insert select , export/import, create as
select to move data from a non-partitionned to a
partitionned table.
Partitionning helps in the management of large tables
more than in speeding the queries.
Will you delete data from that table one day ?
Choose the partition key carefully.
BigP - I agree with Dick that you will need to create your new partitioned
table and copy the rows from your current table into it. Given your
questions, before you charge into partitioning, carefully study the ways
partitioning can increase your performance. It isn't just some magic pixie
dust
-- paquette stephane [EMAIL PROTECTED]
You can use insert select , export/import, create as
select to move data from a non-partitionned to a
partitionned table.
Partitionning helps in the management of large tables
more than in speeding the queries.
Will you delete data from that table
Big (or should we just call you P?),
I have become somewhat experienced at manipulating
large partition tables since I have had to do it so
often (can you say poor initial design?). Export /
import is not the fastest way to go.
Here are some tips from the trenches:
1) You mention that each
BigP,
You stated that you would like to limit the number of rows in a
partition. The partitioning option uses a range function on a column
to determine what partition to place to data into. If you do not have a
column that is used in your where clause, you are going to have a
difficult time
I can confirm that this is true and a good idea. Testing is also done in
this fasion. Besides that, an Oracle salesperson would gladly have you
pay more without having to do anything on their part or yours. ;)
Regards,
Michael Sale
Author: Oracle9i for Windows(R) 2000 Tips Techniques
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
ahoo.comcc:
Sent by: Subject: RE: partition tables
http://docs.oracle.com/cd_a87860/doc/server.817/a76965/c09parti.htm#436972
-Original Message-From: BigP
[mailto:[EMAIL PROTECTED]]Sent: Thursday, May 30, 2002
8:59 PMTo: Multiple recipients of list ORACLE-LSubject:
partition tables
Hi All ,
We are thinking ofconverting
The alter table exchange partition lets you transfer
data from the partition of a partitioned table to a
non partitioned table. It changes the adress in the
data dictionnary, no data is moved, that's why it is
fast.
For example, I'm using it in a system to exchange old
data with new data. The
It means partitioning a non-partitioned table using exchange partition
mechanism, and creating non-partitioned table from partitions of a
partitioned table. You can find detail info in the Database Administrator's
Guide.
Regards,
- Kirti
-Original Message-
Sent: Tuesday, May 21, 2002
Hello Holly,
Make sure and pay attention to the clause that allows
you to skip validation. Don't remember it right off
the top, but it will save you beaucoup time if you are
sure about your data.
Jack
--- Deshpande, Kirti [EMAIL PROTECTED]
wrote:
It means partitioning a non-partitioned table
There is SKIP_UNUSABLE_INDEXES option in SQLldr
--- Rusnak, George A. [EMAIL PROTECTED]
wrote:
Group,
I have a partition that has 1 million records that
is joined to 4 other
tables. In order to get an acceptable response time
I added two additional
indexes. The response time is now
by default you can't change the rows part key.
do a alter table table_name enable row movement;
then the row can move between partitions.
joe
[EMAIL PROTECTED] 09/24/01 10:50AM
Hello,
I have a partitioned
table by range on a column like STATUS , where STATUS can be either say YES or
Alter
table enable row movement;
Regards
Ivo
-Original Message-From: Maya Kenner
[mailto:[EMAIL PROTECTED]]Sent: Monday, September 24, 2001
04:50 PMTo: Multiple recipients of list ORACLE-LSubject:
partition
Hello,
I have a
partitioned table by range on a
In
8.0.x it will NOT do the update. I think in 8i it will but not
sure.
Rick
-Original Message-From: Maya Kenner
[mailto:[EMAIL PROTECTED]]Sent: Monday, September 24, 2001
10:50 AMTo: Multiple recipients of list ORACLE-LSubject:
partition
Hello,
I have a
-LSubject:
Re: partition
by default you can't change the rows part key.
do a alter table table_name enable row movement;
then the row can move between partitions.
joe
[EMAIL PROTECTED] 09/24/01 10:50AM
Hello,
I have a partitioned
table by range on a column like
Hi All,
Once again sinking into the depths of Oracle code.
BUG INFO
Bug:1716968 / Bug:1273906
Base Bug:743019
Fixed In Ver: 9.0.2
Abstract: CANNOT DROP PARTITION IF ADDED VIA SYNONYM - ORA-2149
Still waiting for instructions on how to cleanup my
From: Jonathan Lewis [EMAIL PROTECTED]
Date: Tue, 7 Aug 2001 20:15:30 +0100
Subject: Re: PARTITION attache to SYNONYM
Which version of Oracle ?
I got a 600 error when trying to create a partition
when using a public synonym instead of the
table_name on 8.1.7.0
Have a look in dba_segments
Jonathan and others,
And some more information. I love my job, I love my job, ...
I have 15 million rows taking up 840 MB in a tablespace
that has no segments or extents (In a partition that sort of exists,
or sort of not exists)
The data is updatable and readable.
Did I mention that I
Clearly this hasn't happened ;)
But if the item shows up in dba_tab_partitions
then there is a data segment linked to the
partition.
Get the SQL from the view dba_tab_partitions,
you will see that the first section of the 3 unions
is for tabpart$, and it joins tabpart$ to seg$
on file#' and
Remind me,
Is it a locally managed tablespace ?
If so, get on to Oracle about the following idea.
a) Export the data from the extent
b) Use dbms_space_admin to make the non-existent
segment appear/disappear
If not, is there even an entry in UET$ for the
extent ?
Is this a standard
Which version of Oracle ?
I got a 600 error when trying to create a partition
when using a public synonym instead of the
table_name on 8.1.7.0
Have a look in dba_segments for segment_name = 'your table name'
and segment_name = 'your synonym', check especially the OWNER
in case something very
Many thanks for the replies on this.
I was indeed using autotrace instead of doing an explain plan with utlxpls.
I have also found the detail I was looking for in the documentation (can't
beleive I didn't seee it before, it must have been a long day!!).
Cheers,
Dave Leach
-Original
Dave:
Oracle does do partition elimination in this case...check out he
partition_start and partition_stop columns of you plan_table.
Kevin
-Original Message-
Sent: Thursday, May 31, 2001 12:01 PM
To: Multiple recipients of list ORACLE-L
Hi All,
Can anyone help me with this.
I have
Oracle could be doing partition elimination but the tools that you are using
to see the execution plan is not showing you the details you're looking for.
I usually do:
Truncate table plan_table;
explain plan for your-sql;
select * from plan_table;
There should be two columns that indicate the
Check optimizer_mode in v$parameter for your session. I don't think it will eliminate
partitions if it is RULE.
Here is an explain plan from a simple select on a partitioned table. An index exists:
SELECT STATEMENT Optimizer=CHOOSE (Cost=1.69535189333285 Card=32 Bytes=3488)
PARTITION RANGE
Yes it does.
The scan will be range of partitions and the execution plan will show
partition stop = key and partition start = key.
Regards,
Waleed
-Original Message-
Sent: Thursday, May 31, 2001 3:17 PM
To: Multiple recipients of list ORACLE-L
Can oracle do partition elimination
Can oracle do partition elimination when ysing bind variables?
Alex Hillman
-Original Message-
Sent: Thursday, May 31, 2001 1:16 PM
To: Multiple recipients of list ORACLE-L
Dave:
Oracle does do partition elimination in this case...check out he
partition_start and partition_stop
At parse time, the optimizer determines that partition elimination can be
done -- which partitions to use is determined after the values are bound to
the query.
-Original Message-
Sent: Thursday, May 31, 2001 3:17 PM
To: Multiple recipients of list ORACLE-L
Can oracle do partition
The docs actually do a good job of showing what the explain plan
will look like for a partition elimination.
Look at the 'Explan Plan' chapter of 'Designing and Tuning for Performance'
Are you using ?/rdbms/admin/utlxplp.sql to examine your explain plan?
Jared
On Thursday 31 May 2001 09:00,
67 matches
Mail list logo
