This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.
COURSE of the MONTH
9 days, 7 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Partitioning Indexes Question
Posted on 2012-12-22
Trying to create this index partition
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
(
PARTITION aa_2010_04 TABLESPACE BB_BB60_STATS_INDX,
PARTITION aa_2010_05 TABLESPACE BB_BB60_STATS_INDX,
PARTITION aa_2010_06 TABLESPACE BB_BB60_STATS_INDX,
PARTITION aa_2010_07 TABLESPACE BB_BB60_STATS_INDX,
Getting the message
"Index created.
PARTITION AA_2010_04 TABLESPACE BB_BB60_STATS_DATA
*
ERROR at line 5:
ORA-14010: this physical attribute may not be specified for an index partition
"
What attribute am I specifying that I should not?
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
(
PARTITION aa_2010_04 TABLESPACE BB_BB60_STATS_INDX,
PARTITION aa_2010_05 TABLESPACE BB_BB60_STATS_INDX,
PARTITION aa_2010_06 TABLESPACE BB_BB60_STATS_INDX,
PARTITION aa_2010_07 TABLESPACE BB_BB60_STATS_INDX,
Getting the message
"Index created.
PARTITION AA_2010_04 TABLESPACE BB_BB60_STATS_DATA
*
ERROR at line 5:
ORA-14010: this physical attribute may not be specified for an index partition
"
What attribute am I specifying that I should not?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
10
2
13 Comments
Active 1 day ago
I don't claim to be an expert on partitioning, but it looks like you are creating the index twice with the same name ?
Oracle in general doesn't like two things of the same type in the database with the same name.
(Unless something about partitioning requires you to do it this way, but nothing I could find in the documentation suggested that)
Oracle in general doesn't like two things of the same type in the database with the same name.
(Unless something about partitioning requires you to do it this way, but nothing I could find in the documentation suggested that)
Thanks
That's what I thought also but when i removed the first SQL command and just ran the second, I got some kind of error. I'll run over that again now and come back with the error message. Did a search on it in mySupport, came back with basically 'returned no rows' on the Knowledge thing :-)
That's what I thought also but when i removed the first SQL command and just ran the second, I got some kind of error. I'll run over that again now and come back with the error message. Did a search on it in mySupport, came back with basically 'returned no rows' on the Knowledge thing :-)
I'll run this now:
DROP INDEX bb_bb60_stats.activity_acc
DROP INDEX bb_bb60_stats.activity_acc
DROP INDEX bb_bb60_stats.activity_acc
DROP INDEX bb_bb60_stats.activity_acc
DROP INDEX bb_bb60_stats.activity_acc
DROP INDEX bb_bb60_stats.activity_acc
DROP INDEX bb_bb60_stats.activity_acc
DROP INDEX bb_bb60_stats.activity_acc
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
..........................
DROP INDEX bb_bb60_stats.activity_acc
DROP INDEX bb_bb60_stats.activity_acc
DROP INDEX bb_bb60_stats.activity_acc
DROP INDEX bb_bb60_stats.activity_acc
DROP INDEX bb_bb60_stats.activity_acc
DROP INDEX bb_bb60_stats.activity_acc
DROP INDEX bb_bb60_stats.activity_acc
DROP INDEX bb_bb60_stats.activity_acc
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
COMMIT;
CREATE INDEX bb_bb60_stats.activity_acc
..........................
It's finished dropping whatever is there now starting the CREATE. That will run about fifty minutes, make a significant amount of noise on OEM's 'Performance' screen, the error out.
ON bb_bb60_stats.activity_acc
*
ERROR at line 2:
ORA-01408: such column list already indexed.
When I issue the drop, it says index does not exist
When i issue the CREATE it says index does exist.
*
ERROR at line 2:
ORA-01408: such column list already indexed.
When I issue the drop, it says index does not exist
When i issue the CREATE it says index does exist.
SQL query show only one non system generated index.
SQL> select owner, index_name from dba_indexes where table_owner = 'BB_BB60_STATS'
2 and table_name = 'ACTIVITY_ACCUMULATOR';
OWNER INDEX_NAME
--------------------------
BB_BB60_STATS ACTIVITY_ACCUMULATOR_IECON
BB_BB60_STATS SYS_IL0000122326C00013$$
SQL>
SQL> select owner, index_name from dba_indexes where table_owner = 'BB_BB60_STATS'
2 and table_name = 'ACTIVITY_ACCUMULATOR';
OWNER INDEX_NAME
--------------------------
BB_BB60_STATS ACTIVITY_ACCUMULATOR_IECON
BB_BB60_STATS SYS_IL0000122326C00013$$
SQL>
Active 1 day ago
It doesn't say that there is an index of that name, it says that those columns already exist in another index somewhere.
Does that table have a primary key ? If you add a primary key constraint it automatically builds an index.
Check the values in dba_ind_columns for that table as well as dba_constraints. Do you have a Primary Key (P) constraint using those columns ?
Does that table have a primary key ? If you add a primary key constraint it automatically builds an index.
Check the values in dba_ind_columns for that table as well as dba_constraints. Do you have a Primary Key (P) constraint using those columns ?
Yes
ACTIVITY_ACCUMULATOR_IECON
For the moment I would leave that be and try creating one or more of the other indexes.
ACTIVITY_ACCUMULATOR_IECON
For the moment I would leave that be and try creating one or more of the other indexes.
SQL> select index_name, column_name from dba_ind_columns
2 where table_owner = 'BB_BB60_STATS'
3 and table_name = 'ACTIVITY_ACCUMULATOR';
INDEX_NAME
--------------------------
COLUMN_NAME
--------------------------
ACTIVITY_ACCUMULATOR_IECON
CONTENT_PK1
2 where table_owner = 'BB_BB60_STATS'
3 and table_name = 'ACTIVITY_ACCUMULATOR';
INDEX_NAME
--------------------------
COLUMN_NAME
--------------------------
ACTIVITY_ACCUMULATOR_IECON
CONTENT_PK1
SQL> select constraint_name from dba_constraints
2 where table_name = 'ACTIVITY+ACCUMULATOR'
3 and owner = 'BB_BB60_STATS';
no rows selected
SQL>
2 where table_name = 'ACTIVITY+ACCUMULATOR'
3 and owner = 'BB_BB60_STATS';
no rows selected
SQL>
All above can be explained in the following:
1) PK constraint is a rule that prohibits multiple rows from having the same value in the same column or combination of columns.
2) Oracle Database enforces a UNIQUE key or PRIMARY KEY integrity constraint on a table by creating a unique index on the unique key or primary key.
3) Thus, when your table ACTIVITY_ACCUMULATOR was created with primary key as CONTENT_PK1, a unique index is created by Oracle with same name as primary key constraint name
4) Your very first statement in your very first post:
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
This runs through since this is a duplicate index unlike the unique index created by Oracle to enforce uniqueness. Index with (same name in the same schema) OR (same type on the same set of coulmn(s) with matching sort specifiers i.e ASC or DESC) are not allowed.
Now you are trying to create an index partition but the top main part i.e. CREATE INDEX is exactly same as above. The error you are getting is not about index already exists but syntax error in creating index partition described as below:
ORA-14010:
this physical attribute may not be specified for an index partition
Cause: unexpected option was encountered while parsing physical attributes of an index partition; valid options for Range or Composite Range partitions are INITRANS, MAXTRANS, TABLESPACE, STORAGE, PCTFREE; only TABLESPACE may be specified for Hash partitions
Action: remove invalid option(s) from the list of physical attributes of an index partition
Please go through the following link on how to create table partitions and extending on that as how to create index partitions:
Partitioned Tables and Indexes
http://docs.oracle.com/cd/B10501_01/server.920/a96524/c12parti.htm
1) PK constraint is a rule that prohibits multiple rows from having the same value in the same column or combination of columns.
2) Oracle Database enforces a UNIQUE key or PRIMARY KEY integrity constraint on a table by creating a unique index on the unique key or primary key.
3) Thus, when your table ACTIVITY_ACCUMULATOR was created with primary key as CONTENT_PK1, a unique index is created by Oracle with same name as primary key constraint name
4) Your very first statement in your very first post:
CREATE INDEX bb_bb60_stats.activity_acc
ON bb_bb60_stats.activity_acc
This runs through since this is a duplicate index unlike the unique index created by Oracle to enforce uniqueness. Index with (same name in the same schema) OR (same type on the same set of coulmn(s) with matching sort specifiers i.e ASC or DESC) are not allowed.
Now you are trying to create an index partition but the top main part i.e. CREATE INDEX is exactly same as above. The error you are getting is not about index already exists but syntax error in creating index partition described as below:
ORA-14010:
this physical attribute may not be specified for an index partition
Cause: unexpected option was encountered while parsing physical attributes of an index partition; valid options for Range or Composite Range partitions are INITRANS, MAXTRANS, TABLESPACE, STORAGE, PCTFREE; only TABLESPACE may be specified for Hash partitions
Action: remove invalid option(s) from the list of physical attributes of an index partition
Please go through the following link on how to create table partitions and extending on that as how to create index partitions:
Partitioned Tables and Indexes
http://docs.oracle.com/cd/B10501_01/server.920/a96524/c12parti.htm
Featured Post
Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This post first appeared at Oracleinaction (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself).
I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
- Acronis
- Databases
- Oracle Database
- MongoDB
- Oracle
- *Backup Software
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month9 days, 7 hours left to enroll
762 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.