In the operation of Oracle Database, there are frequent situations where you want to prevent accidental updates to master data or need to move a table to a different location due to disk capacity issues.
This article explains how to change an Oracle table to Read Only mode and the procedure for Moving an Oracle table between tablespaces, in a way that is easy to understand even for beginners. In particular, dealing with “Index Invalidation” that occurs when moving a table is essential knowledge, so please be sure to master it.
What you will learn in this article:
- How to protect data by making specific tables “Read Only”
- How to move a table to another tablespace using the
ALTER TABLE MOVEcommand - The “Index Rebuild” procedure required after moving (Essential)
Conclusion: Quick Command List by Objective
Here is a “To-Do List” for those in a hurry.
Make a table read-only
ALTER TABLE table_name READ ONLY;
Return a table to writable status
ALTER TABLE table_name READ WRITE;
Move a table to another tablespace
ALTER TABLE table_name MOVE TABLESPACE new_tablespace_name;
Fix indexes after moving (Essential)
ALTER INDEX index_name REBUILD;
1. Basics: What are Read Only and Table Move?
Before diving into the procedures, here is a quick note explaining how each function works.
Read Only Mode
When this attribute is set for a table, all DML (Data Manipulation Language) operations such as INSERT, UPDATE, and DELETE are prohibited.
- Merit: You can prevent data loss or modification due to application bugs or operational mistakes at the database level.
- Note: Some DDL (Data Definition Language) operations, such as
DROP TABLE, are still executable.
Table Move (Move Tablespace)
This reallocates the actual data of the table to a physically different “Tablespace” (storage location).
- Merit: Allows for storage optimization, such as moving to a faster disk to improve performance or evacuating from a full tablespace.
- Side Effect: Since the physical location (ROWID) of the data changes, all indexes pointing to that table become “UNUSABLE“.
2. Practical Steps: Setting Read Only and Moving Tables
From here, we will go through steps you can try on an actual machine (SQL*Plus, etc.).
As a prerequisite, we will proceed with the flow where the operation target table EMPLOYEES exists, and we create a destination tablespace NEW_TABLESPACE.
2.1 Preparation (Create Test Data)
First, create a table and index for the experiment, and insert data.
-- Prerequisite: Execute with a user having appropriate privileges
-- Create test table
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
name VARCHAR2(50),
salary NUMBER
);
-- Insert data
INSERT INTO employees VALUES (1, 'Alice', 5000);
INSERT INTO employees VALUES (2, 'Bob', 6000);
COMMIT;
2.2 Set the Table to Read Only
Change the table to read-only to protect important data.
1. Execute the setting command
ALTER TABLE employees READ ONLY;
2. Verify operation
Confirm that an error occurs when trying to delete data.
DELETE FROM employees WHERE employee_id = 1;
-- Result: ORA-12081: update operation not allowed on table "SCHEMA"."EMPLOYEES"
3. Check status SQL
SELECT table_name, read_only
FROM user_tables
WHERE table_name = 'EMPLOYEES';
Execution Log:
SQL> SELECT * FROM employees;
EMPLOYEE_ID NAME SALARY
----------- -------------------- ----------
1 Alice 5000
2 Bob 6000
SQL> ALTER TABLE employees READ ONLY;
Table altered.
SQL> DELETE FROM employees WHERE employee_id = 1;
DELETE FROM employees WHERE employee_id = 1
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."EMPLOYEES"
SQL> SELECT table_name, read_only FROM user_tables
2 WHERE table_name = 'EMPLOYEES';
TABLE_NAME REA
-------------------- ---
EMPLOYEES YES ★READ ONLY
How to release:
To revert, execute ALTER TABLE employees READ WRITE;.
2.3 Move the Table to a New Tablespace
Next, physically move this table to another tablespace.
1. Create the destination tablespace
Note: Change the path according to your environment (the example is for Linux). For Windows, it would be C:\app… etc.
SQL
CREATE TABLESPACE new_tablespace
DATAFILE '/u01/app/oracle/oradata/ORCL/new_tablespace.dbf'
SIZE 50M
AUTOEXTEND ON;
2. Execute table move command
Moving (DDL) is possible even in read-only mode.
ALTER TABLE employees MOVE TABLESPACE new_tablespace;
3. Verify move
SELECT table_name, tablespace_name
FROM user_tables
WHERE table_name = 'EMPLOYEES';
Execution Log:
SQL> CREATE TABLESPACE new_tablespace
2 DATAFILE '/u01/app/oracle/oradata/ORCL/new_tablespace.dbf'
3 SIZE 50M
4 AUTOEXTEND ON;
Tablespace created.
SQL> ALTER TABLE employees MOVE TABLESPACE new_tablespace;
Table altered.
SQL> SELECT table_name, tablespace_name FROM user_tables
2 WHERE table_name = 'EMPLOYEES';
TABLE_NAME TABLESPACE_NAME
-------------------- ------------------------------
EMPLOYEES NEW_TABLESPACE
2.4 [Important] Rebuild Indexes
When a table is moved (MOVE), the ROWIDs change, so indexes such as primary keys become “Invalid”. Rebuilding (REBUILD) is absolutely necessary.
1. Check index status
SELECT index_name, status
FROM user_indexes
WHERE table_name = 'EMPLOYEES';
2. Rebuild the index
It is common practice to move the index to the new tablespace as well.
-- Execute by specifying the index name automatically created by the Primary Key constraint
-- (Check the actual name in the SELECT result above. Here we assume employees_pk as an example)
ALTER INDEX <index_name> REBUILD TABLESPACE new_tablespace;
Execution Log:
SQL> SELECT index_name, status FROM user_indexes
2 WHERE table_name = 'EMPLOYEES';
INDEX_NAME STATUS
-------------------- --------
SYS_C007538 UNUSABLE
SQL> ALTER INDEX SYS_C007538 REBUILD TABLESPACE new_tablespace;
Index altered.
SQL> SELECT index_name, status FROM user_indexes
2 WHERE table_name = 'EMPLOYEES';
INDEX_NAME STATUS
-------------------- --------
SYS_C007538 VALID
3. Diagram: Operation Flow and State Changes
The following diagram illustrates the change in table state and the impact on indexes throughout the sequence of operations.
+-----------------------+ +-----------------------+
| (1) Initial State | | Index |
| Tablespace: USERS | <---- | State: VALID |
| Attr: READ WRITE | | |
+-----------------------+ +-----------------------+
|
v (2) ALTER TABLE ... READ ONLY
|
+-----------------------+
| (3) Read Only | DML (Insert/Update) Not Allowed
| Tablespace: USERS | SELECT is Allowed
| Attr: READ ONLY |
+-----------------------+
|
v (4) ALTER TABLE ... MOVE TABLESPACE ...
|
+-----------------------+ +-----------------------+
| (5) Move Complete | | Index |
| Tablespace: NEW_TBS | <x--- | State: UNUSABLE |
| Attr: READ ONLY | | *Access becomes impossible|
+-----------------------+ +-----------------------+
|
v (6) ALTER INDEX ... REBUILD
|
+-----------------------+ +-----------------------+
| (7) Fully Restored | | Index |
| Tablespace: NEW_TBS | <---- | State: VALID |
| Attr: READ ONLY | | |
+-----------------------+ +-----------------------+
4. Troubleshooting
Here are common errors encountered during operation and how to deal with them.
| Error Code | Error Message (Overview) | Cause and Solution |
| ORA-12081 | update operation not allowed… | Cause: DML was executed on a read-only table. Solution: This is expected behavior. If an update is necessary, revert to READ WRITE. |
| ORA-01502 | index ‘…’ or partition of such index is in unusable state | Cause: Forgot to rebuild the index after MOVE.Solution: REBUILD the target index. |
| ORA-01950 | no privileges on tablespace | Cause: No quota (usage rights) on the destination tablespace. Solution: Grant privileges using ALTER USER username QUOTA UNLIMITED ON tablespace_name; etc. |
5. Operational, Monitoring, and Security Notes
Recommended to execute outside of business hours
During the execution of ALTER TABLE MOVE, the table is exclusively locked. Other users cannot search or update the table until it is completed.
Note: This can be avoided using the ONLINE option in Oracle Enterprise Edition, but Standard Edition requires scheduled downtime.
Regathering Statistics
After moving a table or rebuilding an index, the optimizer statistics may become outdated. To maintain performance, it is recommended to gather statistics at the end.
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'<schema_name>', TABNAME=>'<table_name>');
6. Frequently Asked Questions (FAQ)
Q1. Can I DROP (delete) a read-only table?
A. Yes, you can.
READ ONLY strictly prevents changes to the data content (rows); it does not prevent the deletion of the table definition itself (DROP). If you want to prevent accidental deletion, you need to restrict DDL using triggers or similar methods.
Q2. What happens if the power fails while moving the table?
A. It will be rolled back.
DDL either completes or fails. Data will not be moved halfway, but re-execution is required.
Q3. Is the procedure the same for partitioned tables?
A. The basics are the same, but moving by partition unit is possible.
Use ALTER TABLE … MOVE PARTITION …. Note that in this case as well, global indexes may become invalid.
7. Summary
We explained the protection and movement of tables in Oracle Database.
READ ONLYis a powerful feature that reliably prevents accidental data updates and deletions.MOVE TABLESPACEallows for storage optimization.- Note: After moving, always perform index status checks and reconstruction (
REBUILD) as a set.
By combining these basic operations, robust and efficient database operation becomes possible. First, please verify the behavior, including error occurrences, in a test environment.
This article explains based on Oracle Database 19c (screens and default values may differ for other versions).
[reference]
Oracle Database SQL Language Reference, 19c
コメント