![]() ![]() ![]() All constraints, indexes, and statistics defined on the column are also removed. However, a column that is marked as unused is not displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. This statement marks one or more columns as unused, but does not actually remove the target column data or restore the disk space occupied by these columns. If you are concerned about the length of time it could take to drop column data from all of the rows in a large table, you can use the ALTER TABLE.SET UNUSED statement. If not specified, the default is to not move the LOB data and LOB index segments. If the table includes LOB column(s), this statement can be used to move the table along with LOB data and LOB index segments (associated with this table) which the user explicitly specifies. Likewise, any statistics for the table become invalid and new statistics should be collected after moving the table. The indexes on the table must be dropped or rebuilt. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. Moving a table changes the rowids of the rows in the table. The following statement moves the hr.admin_emp table to a new segment, specifying new storage parameters: ALTER TABLE hr.admin_emp MOVE If you want to leave the table available for DML while moving it, see "Redefining Tables Online". The ALTER TABLE.MOVE statement does not permit DML against the table while the statement is executing. This is especially important with columns that you intend to modify by adding transparent data encryption. ![]() One important reason to move a table to a new tablespace (with a new datafile) is to eliminate the possibility that old versions of column data-versions left on now unused portions of the disk due to segment shrink, reorganization, or previous table moves-could be viewed by bypassing the access controls of the database (for example with an operating system utility). ![]() You can also use the ALTER TABLE.MOVE statement with a COMPRESS clause to store the new segment using table compression. This statement also lets you modify any of the storage attributes of the table or partition, including those which cannot be modified using ALTER TABLE. The ALTER TABLE.MOVE statement enables you to relocate data of a non-partitioned table or of a partition of a partitioned table into a new segment, and optionally into a different tablespace for which you have quota. Moving a Table to a New Segment or Tablespace Many of these operations are discussed in succeeding sections. Put a table in read-only mode and return it to read/write modeĪdd or modify index-organized table characteristicsĪlter the characteristics of an external tableĪdd or modify object type, nested table, or varray columns Modify the degree of parallelism for the table Modify the logging attributes of the tableĪdd, modify or drop integrity constraints associated with the tableĮnable or disable integrity constraints or triggers associated with the table Move the table to a new segment or tablespaceĮxplicitly allocate an extent or deallocate unused spaceĪdd, drop, or rename columns, or modify an existing column definition (datatype, length, default value, NOT NULL integrity constraint, column expression (for virtual columns), and encryption properties.) Modify physical characteristics ( INITRANS or storage parameters) You can use the ALTER TABLE statement to perform any of the following actions that affect a table: Reasons for Using the ALTER TABLE Statement ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |