Home » SQL & PL/SQL » SQL & PL/SQL » Partitioning on table
Partitioning on table [message #296272] Fri, 25 January 2008 08:15 Go to next message
AdeleSwart
Messages: 3
Registered: January 2008
Junior Member
Good day, we have a lot of partitioned tables and most of it is range partitions on a date field.

We have a couple of new tables and it is partitioned on a number field, also range partitioning.

The thing is, we have a script that does maintenance on a monthly basis and it caters for both version. However, it doesn't like the date message that is build in my stored proc as this is based on a date.

Table example:

Create table mis.mytable
(
sk_mytable_no number(Cool not null,
sk_receive_buy_date_no number(Cool not null,
)
Partition by range (sk_receive_buy_date_no)
(
Partition mytable_20041030
values less than (20041031)

My question: is there any place in Oracle where the column_name that the table is partitioned on, is stored? In this example is there a place where I can find the table mytable is partitioned by range on column sk_receive_buy_date_no.

Else I have to do some more coding to see whether a specific table is partitioned on a date or a number field. Sad

Hope someone can help me.

Re: Partitioning on table [message #296280 is a reply to message #296272] Fri, 25 January 2008 09:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ALL_PART_KEY_COLUMNS
ALL_TAB_COLS

Next time, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Fri, 25 January 2008 09:13]

Report message to a moderator

Re: Partitioning on table [message #296524 is a reply to message #296280] Mon, 28 January 2008 01:22 Go to previous messageGo to next message
AdeleSwart
Messages: 3
Registered: January 2008
Junior Member
Michel, thank you for all the info. I'm new to Oracle and appreciate any help/assistance.

Do you perhaps know if there is a table where I can see what the datatype of the column in ALL_PART_KEY_COLUMNS is?

Example: myTable is partitioned on column sk_receive_buy_date_no - I need to know whether the partition key is a date or a number field.

Thanks
Adele

Re: Partitioning on table [message #296525 is a reply to message #296524] Mon, 28 January 2008 01:22 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Join it with ALL_TAB_COLS.

Regards
Michel
Previous Topic: Chptr 9 more PL/SQL tools the oracle PL/SQL 101 book
Next Topic: how to write a query for my necessity
Goto Forum:
  


Current Time: Mon Dec 05 14:58:12 CST 2016

Total time taken to generate the page: 0.08851 seconds