AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Alter table add column oracle11/19/2023 ![]() ![]() To specify an edition in the evaluation_edition_clause or the unusable_editions_clause, you must have the USE privilege on the edition. To use the flashback_archive_clause to disable historical tracking for the table, you must have the FLASHBACK ARCHIVE ADMINSTER system privilege or you must be logged in as SYSDBA.Īdditional Prerequisite for Referring to Editioned Objects To use the flashback_archive_clause to enable historical tracking for the table, you must have the FLASHBACK ARCHIVE object privilege on the flashback data archive that will contain the historical data. ![]() To use an object type in a column definition when modifying a table, either that object must belong to the same schema as the table being altered, or you must have either the EXECUTE ANY TYPE system privilege or the EXECUTE object privilege for the object type.Īdditional Prerequisites for Flashback Data Archive Operations To enable or disable triggers, the triggers must be in your schema or you must have the ALTER ANY TRIGGER system privilege.ĬREATE INDEX for information on the privileges needed to create indexesĪdditional Prerequisites When Using Object Types You need these privileges because Oracle Database creates an index on the columns of the unique or primary key in the schema containing the table. To enable a unique or primary key constraint, you must have the privileges necessary to create an index on the table. You cannot partition a non-partitioned table that has an object type.Īdditional Prerequisites for Constraints and Triggers When using the exchange_partition_subpart clause, if the table data being exchanged contains an identity column and you are not the owner of both tables involved in the exchange, then you must have the ALTER ANY SEQUENCE system privilege. When a partitioning operation cascades to reference-partitioned child tables, privileges are not required on the reference-partitioned child tables. You must also have space quota in the tablespace in which space is to be acquired in order to use the add_table_partition, modify_table_partition, move_table_partition, and split_table_partition clauses. If you are not the owner of the table, then you need the DROP ANY TABLE privilege in order to use the drop_table_partition or truncate_table_partition clause. If I can get that recursive UPDATE to go PARALLEL, I know then the ALTER will be much quicker, because as a separate test, I ran that UPDATE standalone in PARALLEL, and it did go PARALLEL and completed very quickly.The table must be in your own schema, or you must have ALTER object privilege on the table, or you must have ALTER ANY TABLE system privilege.Īdditional Prerequisites for Partitioning Operations However, applying the same approach I had used for enabling FK constraint in PARALLEL to adding NULLABLE columns with DEFAULT values - it's not working, the recursive UPDATE above is refusing to go PARALLEL (even though the execution PLAN is showing PARALLEL).Īm I missing a "trick" ? Or has this improved in a later version of Oracle, 12c onwards,etc ? The above works and forces the recursive SQL that Oracle executes when validating the FK constraint to be executed in PARALLEL. now do my foreign key constraints => first enable novalidate, then validate Update big_table set col1 = '0', col2 = '0'įrom experience of enabling foreign key constraint in PARALLEL, I did this It's taking a long time to do because Oracle is executing a bit of recursive SQL like this behind the scenes when the ALTER TABLE is submitted ![]() I'm adding a number of columns with DEFAULT values that are NULLABLE to a large tableĪlter table big_table add (col1 varchar2(1) default 0, col2 varchar2(1) default 0) ![]()
0 Comments
Read More
Leave a Reply. |