Home » SQL & PL/SQL » SQL & PL/SQL » Partition Table (Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production)
Partition Table [message #340505] Wed, 13 August 2008 02:34 Go to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi
I have a partion table :

CREATE TABLE PAS_SIG_SCRIPT
(
  FILL_DT             DATE                      NOT NULL,
  SIG_SCRIPT_SEQ_NBR  NUMBER(12)                NOT NULL,
  STORE_NBR           NUMBER(5)                 NOT NULL,
  RX_NBR              NUMBER(7)                 NOT NULL,
  SIG_SEQ_NBR         NUMBER(9)                 NOT NULL,
  INSERT_DT_TME       DATE                      NOT NULL,
  CUR_IND             VARCHAR2(1 BYTE)          NOT NULL
)

PARTITION BY RANGE (FILL_DT) 
(  
  PARTITION PQ20041 VALUES LESS THAN 
(TO_DATE(' 2004-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PARTITION PQ20042 VALUES LESS THAN 
(TO_DATE(' 2004-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PARTITION PQ20043 VALUES LESS THAN 
(TO_DATE(' 2004-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
....and so on


Now my requirment is to write a query which take a particular date and tell me whether partition is exist or not.I know that it can be done with all_tab_partitions table bupLease write exact code.

Thanks and regards.

[Updated on: Wed, 13 August 2008 02:43] by Moderator

Report message to a moderator

Re: Partition Table [message #340512 is a reply to message #340505] Wed, 13 August 2008 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you tried.

Regards
Michel
Re: Partition Table [message #340516 is a reply to message #340512] Wed, 13 August 2008 02:59 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi
I tried:

SELECT MAX(high_value),MIN(high_value)
FROM all_tab_partitions WHERE table_name LIKE 'PAS_SIG_SCRIPT'

error:
ORA-00997: illegal use of LONG datatype

Re: Partition Table [message #340518 is a reply to message #340516] Wed, 13 August 2008 03:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Google is your friend.
First hit has a link to AskTom
Re: Partition Table [message #340530 is a reply to message #340518] Wed, 13 August 2008 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which is a link to old Tom's site and no more work, but OP should be able to find the new URL. Wink

Regards
Michel
Re: Partition Table [message #340546 is a reply to message #340530] Wed, 13 August 2008 04:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Wed, 13 August 2008 10:42
Which is a link to old Tom's site and no more work, but OP should be able to find the new URL. Wink

Regards
Michel


It does work. I get linked to a valid AskTom page.
Re: Partition Table [message #340558 is a reply to message #340546] Wed, 13 August 2008 05:08 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hey! It didn't when I tried it a couple of hours ago and now there is an automatic redirection to the current page! Shocked

Regards
Michel
Previous Topic: problem identifying a symbol in the content of a column
Next Topic: syntax of update stmt with literals in subquery
Goto Forum:
  


Current Time: Sun Dec 04 00:19:42 CST 2016

Total time taken to generate the page: 0.18919 seconds