Advertisement

04.02.2007 at 09:52AM PDT, ID: 22487268
[x]
Attachment Details

Oracle - Add partition (Alter table)

Asked by mayankeagle in Oracle 9.x, Databases Miscellaneous, Oracle Database

Tags: partition, oracle, add, table, alter

Hi folks,

I have a table already created on which I want to add a partition by range on a date column. I checked for for the syntax on the Net and most of them:

1. Either explain the syntax for partitions specified while creating the table: (e.g., http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10739/partiti.htm)

2. Or specify it without the column-name while partitioning (e.g., http://www.databasedesign-resource.com/oracle-partitions.html) - does that assume by default that partitioning will be done on the primary key?

I know I might not have looked enough but am just looking for a sample Alter table command which adds a partition by range on a column which is not the primary key. Maybe something like the correct syntax for:

ALTER TABLE MY_TABLE ADD PARTITION BY RANGE (DATE_CREATED) DATA_98 VALUES LESS THAN (TO_DATE ('01/01/1999', 'DD/MM/YYYY'))

Thanks,
Mayank.
Start Free Trial
[+][-]04.02.2007 at 10:01AM PDT, ID: 18837822

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.02.2007 at 10:51AM PDT, ID: 18838210

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 7-day free trial to view this Assisted Solution or ask the Experts your question.

 
[+][-]04.02.2007 at 10:54AM PDT, ID: 18838245

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.02.2007 at 10:58AM PDT, ID: 18838268

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]04.02.2007 at 11:17AM PDT, ID: 18838407

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.02.2007 at 11:18AM PDT, ID: 18838413

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]04.02.2007 at 11:22AM PDT, ID: 18838447

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Oracle 9.x, Databases Miscellaneous, Oracle Database
Tags: partition, oracle, add, table, alter
Sign Up Now!
Solution Provided By: kimarti3
Participating Experts: 2
Solution Grade: A
 
 
 
Loading Advertisement...
Microsoft
  • Internet Protocols
  • Applications
  • Development
  • OS
  • Hardware
  • Windows Security
Apple
  • Operating Systems
  • Hardware
  • Programming
  • Networking
  • Software
Internet
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Spy / Ad Blockers
  • Web Browsers
  • New Net Users
  • Web Development
  • Chat / IM
  • Anti Spam
  • Web Servers
  • Anti-Virus
  • Email Clients
Gamers
  • Tips
  • Online / MMORPG
  • Puzzle
  • Emulators
  • Action / Adventure
  • Role Playing
  • Consoles
  • Game Programming
  • Strategy
  • Sports
  • Misc
  • Computer Games
Digital Living
  • Hardware
  • Automotive
  • New Net Users
  • New Users
  • Software
  • Digital Music
  • Gaming World
  • Home Security
  • Apple
  • Networking Hardware
Virus & Spyware
  • Vulnerabilities
  • IDS
  • Encryption
  • Anti-Virus
  • Operating Systems Security
  • Software Firewalls
  • WebApplications
  • Cell Phones
  • Operating Systems
  • Internet
  • Hardware Firewalls
Hardware
  • Displays / Monitors
  • Handhelds / PDAs
  • Components
  • Peripherals
  • Laptops/Notebooks
  • Servers
  • Misc
  • Apple
  • Embedded Hardware
  • Networking Hardware
  • Storage
  • Desktops
  • New Users
Software
  • System Utilities
  • Industry Specific
  • Network Management
  • Photos / Graphics
  • Page Layout
  • VMware
  • Misc
  • Web Development
  • OS
  • CYGWIN
  • Voice Recognition
  • Virtualization
  • Message Queue
  • Quality Assurance
  • Security
  • Firewalls
  • MultiMedia Applications
  • Development
  • Database
  • Office / Productivity
  • Business Management
  • OS/2 Apps
  • Server Software
  • Internet / Email
ITPro
  • OS
  • Storage
  • Encryption
  • Operating Systems Security
  • Apple Hardware
  • Laptops & Notebooks
  • Servers
  • Networking Hardware
  • Peripherals
  • Devices
  • Displays / Monitors
  • WebTrends / Stats
  • Search Engines
  • Firewalls
  • Web Computing
  • WebApplications
  • IDS
  • Vulnerabilities
  • Email Clients
  • File Sharing
  • Spy / Ad Blockers
  • Web Browsers
  • Web Servers
  • Networking
  • Anti-Virus
  • Consulting
  • Chat / IM
  • Anti Spam
Developer
  • Web Servers
  • Web Browsers
  • Game Programming
  • Dev Tools
  • Industry Specific
  • Office / Productivity
  • Database
  • CYGWIN
  • Web Development
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Programming
  • Content Management
  • Application Servers
  • Protocols
Storage
  • Removable Backup Media
  • Storage Technology
  • Servers
  • Grid
  • Remote Access
  • Backup / Restore
  • Misc
  • Hard Drives
OS
  • Miscellaneous
  • Security
  • Development
  • Linux
  • VMware
  • MainFrame OS
  • Unix
  • Apple
  • OS / 2
  • AS / 400
  • BeOS
  • Microsoft
  • VMS / OpenVMS
Database
  • Oracle
  • Miscellaneous
  • MySQL
  • Software
  • Sybase
  • Contact Management
  • PostgreSQL
  • Data Manipulation
  • Clarion
  • InterSystems Cache
  • Siebel
  • MUMPS
  • OLAP
  • SQLBase
  • SAS
  • GIS & GPS
  • 4GL
  • Berkeley DB
  • DB2
  • Informix
  • Interbase / Firebird
  • FoxPro
  • Reporting
  • LDAP
  • Filemaker Pro
  • MS SQL Server
  • dBase
  • MS Access
Security
  • Misc
  • Web Browsers
  • Software Firewalls
  • Operating Systems Security
  • File Sharing
  • Spy / Ad Blockers
  • Vulnerabilities
  • WebApplications
  • IDS
  • Anti-Virus
  • Encryption
  • Anti Spam
  • Email Clients
  • VPN
  • Chat / IM
Programming
  • Editors IDEs
  • Installation
  • Handhelds / PDAs
  • Multimedia Programming
  • System / Kernel
  • Automation
  • Algorithms
  • Game
  • Signal Processing
  • Project Management
  • Open Source
  • Database
  • Misc
  • Languages
  • Processor Platforms
  • Theory
Web Development
  • Scripting
  • Blogs
  • Web Servers
  • Software
  • Search Engines
  • Web Graphics
  • Web Services
  • Images
  • Internet Marketing
  • Images and Photos
  • Components
  • Document Imaging
  • Web Languages/Standards
  • Illustration
  • WebApplications
  • Fonts
  • WebTrends / Stats
  • Authoring
  • Digital Camera Software
  • Miscellaneous
Networking
  • Protocols
  • Apple Networking
  • Network Management
  • Message Queue
  • Application Servers
  • Content Management
  • File Servers
  • Email Servers
  • Misc
  • Java Editors & IDEs
  • Wireless
  • Networking Hardware
  • Backup / Restore
  • System Utilities
  • ISPs & Hosting
  • Web Servers
  • Storage Technology
  • Removable Backup Media
  • Servers
  • Web Computing
  • Broadband
  • Grid
  • OS / 2
  • Novell Netware
  • Unix Networking
  • Windows Networking
  • Security
  • Telecommunications
  • Operating Systems
  • Linux Networking
Other
  • Lounge
  • Business Travel
  • Community Support
  • New Net Users
  • Philosophy / Religion
  • Math / Science
  • Miscellaneous
  • URLs
  • Expert Lounge
  • Politics
  • Puzzles / Riddles
  • Automotive
Community Support
  • Suggestions
  • New to EE
  • New Topics
  • CleanUp
  • Announcements
  • General
  • Feedback
  • Input
  • EE Bugs
 
04.02.2007 at 10:01AM PDT, ID: 18837822
Here is a good article that explains partitions and how to add them.  http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/partiti.htm#6536
 
04.02.2007 at 10:51AM PDT, ID: 18838210
I believe your partition name must follow the add partition statement. Try this -
ALTER TABLE MY_TABLE ADD PARTITION DATA_98 BY RANGE (DATE_CREATED) VALUES LESS THAN (TO_DATE ('01/01/1999', 'DD/MM/YYYY'))
Assisted Solution
 
04.02.2007 at 10:54AM PDT, ID: 18838245
As another note, it depends on if the column in question is indexed.  If its indexed, you have to use different syntax, which is explained in the link I added before.
 
04.02.2007 at 10:58AM PDT, ID: 18838268
>> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/partiti.htm#6536

That link is almost the same as the 1st one which I posted (they look like 2 different versions of the same tutorial) - doesn't explain how to add a partition on a specific column by altering the table. Though it explains how to create and alter (without specifying a column).

>> ALTER TABLE MY_TABLE ADD PARTITION DATA_98 BY RANGE (DATE_CREATED)
>> VALUES LESS THAN (TO_DATE ('01/01/1999', 'DD/MM/YYYY'))

I'd hope that will work :( but it gave an error "ORA-14020: this physical attribute may not be specified for a table partition"
 
04.02.2007 at 11:17AM PDT, ID: 18838407
Are you inserting a partition between partitions?  or are you adding a partition after the last partition?  If you are adding a partition between existing partitions, you have to use the SPLIT PARTITION clause.

I know we have partitions on our database...but when we wanted to add more partitions to the table...we found out the best and easiest way to do this is to export the data, recreate the table with more partitions and then import the data.
 
04.02.2007 at 11:18AM PDT, ID: 18838413
Let me clarify - the table is not partitioned. Can I partition it and add partitions to it? Or do I need to recreate the table?
 
04.02.2007 at 11:22AM PDT, ID: 18838447
Ohhh...if the table is not already partitioned, you need to recreate it as a partitioned table.
Accepted Solution
 
 
20080716-EE-VQP-32