Mir Sayeed Hassan – Oracle Blog » Blog Archive Checklist of ALTER Commands for Database Maintenance, Recovery and Optimization in Oracle database. - Mir Sayeed Hassan

Checklist of ALTER Commands for Database Maintenance, Recovery and Optimization in Oracle database.

Posted by Mir Sayeed Hassan on May 18th, 2026

Checklist of ALTER Commands for Database Maintenance, Recovery, and Optimization in Oracle database.

Brief about the alter cluster command: This is used to modify cluster settings and manage clustered database storage for improved performance and administration.

SQL>  ALTER CLUSTER pub_cluster SIZE 4K;
SQL> ALTER CLUSTER pub_cluster DEALLOCATE UNUSED KEEP 1M;

Altering DATAFILE command is used to modify Oracle data files, including offline, resizing, renaming, or managing database storage and recovery operations.

SQL>  ALTER DATABASE DATAFILE 3 OFFLINE;
SQL> ALTER DATABASE DATAFILE ‘/u01/app/oracle/mirdb/datafile/users01.dbf’ OFFLINE; 
SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/mirdb/datafile/users01.dbf'
RESIZE 100m;
SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/mirdb/datafile/users01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;
SQL> ALTER DATABASE DATAFILE 2 END BACKUP;

Alterting TEMPFILE command is used to manage Oracle tempfiles by resizing, renaming, adding, or modifying temporary database storage.

SQL> ALTER DATABASE TEMPFILE 4 RESIZE 100M;
SQL> ALTER DATABASE TEMPFILE 4 AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;
SQL> ALTER DATABASE TEMPFILE 4 DROP INCLUDING DATAFILES; 
SQL> ALTER DATABASE TEMPFILE 4 OFFLINE;

ARCHIVELOG Mode Commands: This is used to enable and manage archived redo logs for database backup, recovery, and point-in-time restoration operations.

SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE NOARCHIVELOG;
SQL> ALTER DATABASE FORCE LOGGING;
SQL> ALTER DATABASE CLEAR LOGFILE '/u01/app/oracle/mirdb/logfiles/redo01.rdo'; 
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE '/u01/app/oracle/mirdb/logfiles/redo01.rdo'; 
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE); 
SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

CONTROLFILE Operations is used to manage control files that store critical database structure, backup, and recovery information.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE as ‘/u01/app/oracle/mirdb/logfile_backup/backup_logfile.trc’ REUSE RESETLOGS;s
SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/u01/app/oracle/mirdb/logfile_backup/backup_logfile.ctl';

Create a Data File is used to add and manage data files for storing database objects and expanding database storage capacity.

SQL> ALTER DATABASE CREATE DATAFILE ‘/u01/app/oracle/mirdb/datafile/users01.dbf’ AS '/opt/oracle/datafile/users01.dbf'; 
SQL>ALTER DATABASE CREATE DATAFILE 4 AS '/u01/app/oracle/mirdb/datafile/users01.dbf'; 
SQL> ALTER DATABASE CREATE DATAFILE '/u01/app/oracle/mirdb/datafile/users01.dbf' AS NEW;

Datafile OFFLINE/ONLINE is used to disable or enable data files for maintenance, recovery, and database management operations.

Alter DATAFILE : logfile Commands

SQL> ALTER DATABASE ADD LOGFILE GROUP 2 (‘/u01/app/oracle/mirdb/logfiles/redo02a.redo’, ‘/u01/app/oracle/mirdb/logfiles/redo02b.redo’) SIZE 500M REUSE;
SQL>ALTER DATABASE ADD LOGFILE MEMBER ‘/u01/app/oracle/mirdb/logfiles/redo02c.rdo’
to GROUP 2;
SQL> ALTER DATABASE ADD LOGFILE thread 3 GROUP 2 ('/u01/app/oracle/mirdb/logfiles/redo02a.rdo', ‘u01/app/oracle/mirdb/logfiles/redo02b.rdo') SIZE 500M REUSE;
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
SQL> ALTER DATABASE DROP LOGFILE MEMBER ‘u01/app/oracle/mirdblogfiles/redo02b.redo’;

Mount and Open the Database

SQL> ALTER DATABASE MOUNT;
SQL> ALTER DATABASE OPEN;

Move or Rename a Database File or Online Redo Log

NOTE
The database must be mounted to rename or move online redo logs.
The database must be mounted or the data files taken offline to move.

SQL> ALTER DATABASE RENAME FILE ‘/u01/app/oracle/mirdb/datafile/<oldfile.dbf>’ TO ‘/u01/app/oracle/mirdb/datafile/<newfile.dbf>’;

Open the Database Read-Only

SQL> ALTER DATABASE OPEN READ ONLY;

Open the Database with resetlogs

SQL> ALTER DATABASE OPEN RESETLOGS;

Recover the Database recommend the use of the recover command.

alter function: Recompile a Function is used to recompile invalid functions and restore their execution validity and performance.

SQL> ALTER FUNCTION my_function1 COMPILE;

alter index: Allocate and Deallocate Extents is used to allocate or deallocate extents for efficient index storage management and performance optimization.

SQL> ALTER INDEX inx_my_tab ALLOCATE EXTENT;
SQL> ALTER INDEX inx_my_tab ALLOCATE EXTENT DATAFILE ‘/u01/app/oracle/mirdb/datafile/<newidx.dbf>’;
SQL> ALTER INDEX inx_my_tab DEALLOCATE UNUSED;
SQL> ALTER INDEX inx_my_tab DEALLOCATE UNUSED KEEP 100M;

alter index: Miscellaneous Maintenance is used for index maintenance tasks such as rebuilding, monitoring, renaming, and optimizing index performance.

SQL> ALTER INDEX inx_my_tab PARALLEL 3;
SQL> ALTER INDEX inx_my_tab NOPARALLEL;
SQL> ALTER INDEX inx_my_tab NOCOMPRESS;
SQL> ALTER INDEX inx_my_tab COMPRESS;

alter index: Modify Logging Attributes is used to modify index logging attributes for controlling redo generation and optimizing database performance.

SQL> ALTER INDEX inx_my_tab LOGGING;
SQL> ALTER INDEX inx_my_tab NOLOGGING;

alter index: Modify Storage and Physical Attributes is used to modify index storage and physical attributes for better space management and performance optimization.

SQL> ALTER INDEX inx_my_tab PCTFREE 10 PCTUSED 40 INITRANS 5 STORAGE (NEXT 100k MAXEXTENTS UNLIMITED FREELISTS 10 BUFFER_POOL KEEP);

alter index: Partition – Add Hash Index Partition is used to add hash index partitions for improved partition management and query performance optimization.

SQL> ALTER INDEX inx_my_tab ADD PARTITION TABLESPACE NEWIDXTBS;

alter index: Partition – Coalesce Partition is used to merge index partition space for improved storage utilization and index performance.

SQL> ALTER INDEX inx_my_tab COALESCE PARTITION;

alter index: Partition – Drop Partition is used to remove index partitions for efficient partition management and storage optimization.

SQL> ALTER INDEX inx_my_tab DROP PARTITION inx_my_tab_mar_04;

alter index: Partition – Modify Default Attributes is used to change default storage and partition settings for better index management and performance optimization.

SQL> ALTER INDEX inx_my_tab MODIFY DEFAULT ATTRIBUTES FOR PARTITION inx_my_tab_mar_04 PCTFREE 10 PCTUSED 40 TABLESPACE newidxtbs NOLOGGING COMPRESS;

alter index: Partition – Modify Partition is used to manage and update index partition properties for improved partition maintenance and performance.

SQL> ALTER INDEX inx_my_tab MODIFY PARTITION inx_my_tab_mar_04 DEALLOCATE UNUSED KEEP 100M;
SQL> ALTER INDEX inx_my_tab MODIFY PARTITION inx_my_tab_mar_04 ALLOCATE EXTENT SIZE 100m; 
SQL> ALTER INDEX inx_my_tab MODIFY PARTITION inx_my_tab_mar_04 PCTUSED 40 STORAGE(NEXT 50m) NOLOGGING;

alter index: Partition – Modify Subpartition is used to manage and update index subpartition settings for efficient partition maintenance and performance optimization.

SQL> ALTER INDEX inx_my_tab MODIFY SUBPARTITION inx_my_tab_mar_04 DEALLOCATE UNUSED KEEP 100M;
SQL> SALTER INDEX inx_my_tab MODIFY SUBPARTITION inx_my_tab_mar_04 ALLOCATE EXTENT SIZE 100m; 
SQL> ALTER INDEX inx_my_tab MODIFY SUBPARTITION inx_my_tab_mar_04 PCTUSED 40 STORAGE(NEXT 50m) NOLOGGING;

alter index: Partition – Rename is used to rename index partitions for better partition organization and database management.

SQL> ALTER INDEX inx_my_tab RENAME PARTITION inx_my_tab_mar_04 TO inx_my_tab_mar_05;
SQL> ALTER INDEX inx_my_tab RENAME SUBPARTITION inx_my_tab_mar_04 TO inx_my_tab_mar_05;

alter index: Partition – Split is used to divide index partitions for improved partition management and query performance optimization.

SQL> ALTER INDEX inx__my_tab SPLIT PARTITION inx__my_tab_jan_05 AT ('15-JAN-05') INTO PARTITION inx__my_tab_jan_05a TABLESPACE myidxtbs STORAGE (INITIAL 100m NEXT 50M FREELISTS 5);

alter index: Rebuild Non partitioned Indexes is used to rebuild nonpartitioned indexes for improved index performance and storage optimization.

SQL> ALTER INDEX inx__my_tab REBUILD ONLINE; ALTER INDEX inx__my_tab REBUILD ONLINE TABLESPACE idx_tbs_new PCTFREE 1 STORAGE (INITIAL 50M NEXT 50m FREELISTS 5) COMPUTE STATISTICS PARALLEL 0;

alter index: Rebuild Partitions is used to rebuild index partitions for better performance, maintenance, and storage efficiency.

SQL> ALTER INDEX inx__my_tab REBUILD PARTITION inx__my_tab_mar_04 ONLINE;  
SQL> ALTER INDEX inx__my_tab REBUILD SUBPARTITION inx__my_tab_mar_04 ONLINE PCTFREE 1 STORAGE (INITIAL 50M NEXT 50m FREELISTS 5) COMPUTE STATISTICS PARALLEL 0;

alter index: Rename is used to change the name of an index for better database organization and management.

SQL> ALTER INDEX inx__my_tab RENAME TO 'inx__my_tab_01';

alter index: Shrink is used to reclaim unused index space and optimize storage utilization and performance.

SQL> ALTER INDEX inx__my_tab SHRINK SPACE;
SQL> ALTER INDEX inx__my_tab SHRINK SPACE COMPACT CASCADE;

alter materialized view: Allocate and Deallocate Extents is used to allocate or deallocate extents for efficient storage management and materialized view performance optimization.

SQL> ALTER MATERIALIZED VIEW mview_my_tab ALLOCATE EXTENT; ALTER MATERIALIZED VIEW mview_my_tab DEALLOCATE UNUSED;

alter materialized view: Miscellaneous is used to manage and maintain materialized view properties for improved performance and refresh operations.

SQL> ALTER MATERIALIZED VIEW mview_my_tab COMPRESS;
SQL> ALTER MATERIALIZED VIEW mview_my_tab PARALLEL 3;
SQL> ALTER MATERIALIZED VIEW mview_my_tab NOLOGGING;
SQL> ALTER MATERIALIZED VIEW mview_my_tab LOGGING;
SQL> ALTER MATERIALIZED VIEW mview_my_tab CONSIDER FRESH;
SQL> ALTER MATERIALIZED VIEW mview_my_tab ENABLE QUERY REWRITE;

alter materialized view: Physical Attributes and Storage

SQL> ALTER MATERIALIZED VIEW mview_my_tab PCTFREE 5 PCTUSED 60 STORAGE (NEXT 100m FREELISTS 5);

alter materialized view: Refresh

SQL> ALTER MATERIALIZED VIEW mview_my_tab ALTER MATERIALIZED VIEW mview_my_tab ALTER MATERIALIZED VIEW mview_my_tab ALTER MATERIALIZED VIEW mview_my_tab ALTER MATERIALIZED VIEW mview_my_tab START WITH sysdate;

SQL> ALTER MATERIALIZED VIEW mview_my_tab START WITH sysdate NEXT sysdate+1/24;

alter materialized view: Shrink Space

SQL> ALTER MATERIALIZED VIEW mview_my_tab SHRINK SPACE; ALTER MATERIALIZED VIEW mview_my_tab
SHRINK SPACE COMPACT CASCADE;

alter materialized view log: Add Components

SQL> ALTER MATERIALIZED VIEW LOG ON my_tab ALTER MATERIALIZED VIEW LOG ON my_tab INCLUDING NEW VALUES;
SQL> ALTER MATERIALIZED VIEW LOG ON my_tab ROWID, SEQUENCE INCLUDING NEW VALUES;

alter materialized view log: Allocate and Deallocate Extents

SQL> ALTER MATERIALIZED VIEW LOG ON my_tab ALLOCATE EXTENT; ALTER MATERIALIZED VIEW LOG ON my_tab DEALLOCATE UNUSED;

alter materialized view log: Miscellaneous

SQL> ALTER MATERIALIZED VIEW LOG ON my_tab PARALLEL 3;

SQL> ALTER MATERIALIZED VIEW LOG ON my_tab NOLOGGING; ALTER MATERIALIZED VIEW LOG ON my_tab SHRINK SPACE;

alter materialized view log: Physical Attributes and Storage

SQL> ALTER MATERIALIZED VIEW LOG ON my_tab 
REFRESH FAST;
REFRESH COMPLETE;
REFRESH FAST ON DEMAND;
REFRESH FAST ON COMMIT;
REFRESH COMPLETE
REFRESH COMPLETE
PCTFREE 5 PCTUSED 60
STORAGE (NEXT 100m FREELISTS 5);

alter package: Compile

SQL> ALTER PACKAGE pk_my_package COMPILE;
SQL> ALTER PACKAGE pk_my_package COMPILE SPECIFICATION; ALTER PACKAGE pk_my_package COMPILE BODY;

alter procedure: Compile

SQL> ALTER PROCEDURE pk_my_package COMPILE;

alter profile: Miscellaneous

SQL> ALTER ROLE my_role IDENTIFIED BY password; ALTER ROLE my_role NOT IDENTIFIED;

alter profile: Modify Limits (Password)

SQL> ALTER PROFILE my_profile LIMIT FAILED_LOGIN_ATTEMPTS=3; ALTER PROFILE my_profile LIMIT PASSWORD_LOCK_TIME=2/24; ALTER PROFILE my_profile LIMIT PASSWORD_GRACE_TIME=5; ALTER PROFILE my_profile LIMIT PASSWORD_LIFETIME=60; ALTER PROFILE my_profile LIMIT PASSWORD_REUSE_TIME=365 PASSWORD_REUSE_MAX=3;

alter profile: Modify Limits (Resource)

SQL> ALTER PROFILE my_profile LIMIT SESSIONS_PER_CPU=10; ALTER PROFILE my_profile LIMIT CONNECT_TIME=1000; ALTER PROFILE my_profile LIMIT IDLE_TIME=60;

SQL> ALTER PROFILE my_profile LIMIT PRIVATE_SGA=1000000;

alter rollback segment: Online/Offline

SQL> ALTER ROLLBACK SEGMENT rbs1;
SQL> ALTER ROLLBACK SEGMENT rbs1;

alter rollback segment: Shrink

SQL> ALTER ROLLBACK SEGMENT rbs1;
SQL> ALTER ROLLBACK SEGMENT rbs1;

alter rollback segment: storage Clause

SQL> ALTER ROLLBACK SEGMENT rbs1 STORAGE(NEXT 50M OPTIMAL 100M);

alter sequence: Miscellaneous

SQL> ALTER SEQUENCE my_seq INCREMENT BY –5;
SQL> ALTER SEQUENCE my_seq INCREMENT BY 1 MAXVALUE 50000 CYCLE; ALTER SEQUENCE my_seq NOMAXVALUE;
SQL> ALTER SEQUENCE my_seq CACHE ORDER;
SQL> ALTER SEQUENCE my_seq INCREMENT BY 1 MINVALUE 1 MAXVALUE 500 CYCLE;

alter session: Enable and Disable Parallel Operations

SQL> ALTER SESSION ENABLE PARALLEL DML PARALLEL 3; ALTER SESSION ENABLE PARALLEL DDL;
SQL> ALTER SESSION DISABLE PARALLEL QUERY;

alter session: Resumable Space Management

SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600; 
SQL> ALTER SESSION DISABLE RESUMABLE;

alter session: Set Session Parameters

SQL> ALTER SESSION SET nls_date_format='MM/DD/YYYY HH24:MI:SS'; ALTER SESSION SET sort_area_size=10000000;
SQL> ALTER SESSION SET query_rewrite_enabled=TRUE; 
SQL> ALTER SESSION SET resumable_timeout=3600; ALTER SESSION SET skip_unusable_indexes=TRUE; ALTER SESSION SET SQL_TRACE=TRUE;

alter system: Logfile and Archive Logfile Management

SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM ARCHIVE LOG START;
SQL> ALTER SYSTEM ARCHIVE LOG STOP;
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
SQL> ALTER SYSTEM ARCHIVE LOG THREAD 1 ALL;
SQL> ALTER SYSTEM ARCHIVE LOG ALL TO 'C:\oracle\allarch';

alter system: Set System Parameters

SQL> ALTER SYSTEM SET db_cache_size=325M COMMENT='This change is to add more memory to the system' SCOPE=BOTH;
SQL> ALTER SYSTEM SET COMPATIBLE=10.0.0 COMMENT='GOING TO 10G!' SCOPE=SPFILE;

alter system: System Management

SQL> ALTER SYSTEM CHECKPOINT GLOBAL;
SQL> ALTER SYSTEM KILL SESSION '145,334'; ALTER SYSTEM ENABLE RESTRICTED SESSION; ALTER SYSTEM DISABLE RESTRICTED SESSION; ALTER SYSTEM SUSPEND;
SQL> ALTER SYSTEM QUIESCE RESTRICTED;
SQL> ALTER SYSTEM UNQUIESCE;
SQL> ALTER SYSTEM RESUME;
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

alter table: External Table Operations

SQL> ALTER TABLE ext_parts REJECT LIMIT 500;
SQL> ALTER TABLE ext_parts DEFUALT DIRECTORY ext_employee_dir; ALTER TABLE ext_parts ACCESS PARAMETERS (FIELDS TERMINATED BY ’,’);
SQL> ALTER TABLE ext_parts LOCATION (’PARTS01.TXT’,’PARTS02.TXT’); ALTER TABLE ext_parts ADD COLUMN (SSN NUMBER);

alter table: Move Table

SQL> ALTER TABLE parts move TABLESPACE parts_new_tbs PCTFREE 10 PCTUSED 60;

alter table: Table Column – Add

SQL> ALTER TABLE PARTS ADD (part_location ALTER TABLE PARTS ADD (part_location );
SQL> ALTER TABLE parts ADD (photo BLOB) LOB (photo) STORE AS lob_parts_photo (TABLESPACE parts_lob_tbs);

alter table: Table Column – Modify

VARCHAR2(20) );
VARCHAR2(20), part_bin VARCHAR2(30) 

SQL> ALTER TABLE PARTS MODIFY (part_location VARCHAR2(30) ); ALTER TABLE PARTS MODIFY
part_location VARCHAR2(30), part_bin VARCHAR2(20) ); ALTER TABLE parts modify (name NOT NULL); 

SQL> ALTER TABLE parts modify (name NULL);
SQL> ALTER TABLE parts MODIFY LOB (photo) (STORAGE(FREELISTS 2)); ALTER TABLE parts MODIFY LOB (photo) (PCTVERSION 50);

alter table: Table Column – Remove

SQL> ALTER TABLE TEST1 DROP (part_location);
SQL> ALTER TABLE TEST1 DROP (part_location, part_bin);

alter table: Table Column – Rename

SQL> ALTER TABLE TEST1 RENAME COLUMN part_location TO part_loc;

alter table: Table Constraints – Add Check Constraint

SQL> ALTER TABLE parts ADD (CONSTRAINT ck_parts_01 CHECK (id > 0) );

alter table: Table Constraints – Add Default Value

SQL> ALTER TABLE PARTS MODIFY (name DEFAULT 'Not Available'); ALTER TABLE PARTS ADD (vendor_code NUMBER DEFAULT 0); ALTER TABLE PARTS MODIFY (part_description DEFAULT NULL);

alter table: Table Constraints – Add Foreign Key

SQL> ALTER TABLE parts ADD CONSTRAINT fk_part_bin FOREIGN KEY (bin_code) REFERENCES part_bin;

alter table: Table Constraints – Add Primary and Unique Key

SQL> ALTER TABLE parts ADD CONSTRAINT pk_parts_part_id PRIMARY KEY (id) USING INDEX TABLESPACE parts_index STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0); ALTER TABLE parts ADD CONSTRAINT uk_parts_part_bin UNIQUE (part_bin)USING INDEX TABLESPACE parts_index STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

alter table: Table Constraints – Modify

SQL> ALTER TABLE parts DISABLE UNIQUE (part_bin);
SQL> ALTER TABLE parts DISABLE CONSTRAINT uk_parts_part_bin;
SQL> ALTER TABLE parts DISABLE CONSTRAINT uk_parts_part_bin KEEP INDEX; ALTER TABLE parts DISABLE CONSTRAINT fk_part_bin;
SQL> ALTER TABLE parts DISABLE CONSTRAINT fk_part_bin
DISABLE PRIMARY KEY KEEP INDEX;
SQL> ALTER TABLE parts ENABLE CONSTRAINT fk_part_bin;
SQL> ALTER TABLE parts ENABLE PRIMARY KEY;
SQL> ALTER TABLE parts ENABLE UNIQUE (part_bin);
SQL> ALTER TABLE parts ENABLE NOVALIDATE CONSTRAINT fk_part_bin;
SQL> ALTER TABLE parts ENABLE NOVALIDATE PRIMARY KEY;
SQL> ALTER TABLE parts ENABLE NOVALIDATE UNIQUE (part_bin);
SQL> ALTER TABLE parts ENABLE NOVALIDATE PRIMARY KEY ENABLE NOVALIDATE CONSTRAINT fk_part_bin;

alter table: Table Constraints – Remove

SQL> ALTER TABLE parts DROP CONSTRAINT fk_part_bin; 
SQL> ALTER TABLE parts DROP PRIMARY KEY;
SQL> ALTER TABLE parts DROP PRIMARY KEY CASCADE;
SQL> ALTER TABLE parts DROP UNIQUE (uk_parts_part_bin);

alter table: Table Partition – Add

SQL> ALTER TABLE store_sales ADD PARTITION sales_q1_04 VALUES LESS THAN (TO_DATE('01-APR-2004','DD-MON-YYYY')) TABLESPACE data_0104_tbs UPDATE GLOBAL INDEXES;
SQL> ALTER TABLE daily_transactions ADD PARTITION;

ALTER TABLE daily_transactions

SQL> ADD PARTITION Alaska VALUES ('AK');
SQL> ALTER TABLE daily_transactions add PARTITION SALES_2004_Q1 VALUES LESS THAN (TO_DATE('01-APR-2004','DD-MON-YYYY')) SUBPARTITIONS 4;

alter table: Table Partition – Merge

SQL> ALTER TABLE store_sales MERGE PARTITIONS Oklahoma, texas INTO PARTITION oktx;

alter table: Table Partition – Move

SQL> ALTER TABLE store_sales MOVE PARTITION sales_overflow TABLESPACE new_sales_overflow STORAGE (INITIAL 100m NEXT 100m PCTINCREASE 0) UPDATE GLOBAL INDEXES;

alter table: Table Partition – Remove

SQL> ALTER TABLE store_sales DROP PARTITION sales_q1_04 UPDATE GLOBAL INDEXES;

alter table: Table Partition – Rename

SQL> ALTER TABLE store_sales RENAME PARTITION sales_q1 TO sales_first_quarter;

alter table: Table Partition – Split

SQL> ALTER TABLE store_sales
SPLIT PARTITION sales_overflow AT (TO_DATE('01-FEB-2004','DD-MON-YYYY') )
INTO (PARTITION sales_q4_2026,
PARTITION sales_overflow)
UPDATE GLOBAL INDEXES;
ALTER TABLE composite_sales SPLIT PARTITION sales_q1
AT (TO_DATE('15-FEB-2026','DD-MON-YYYY'))
INTO (PARTITION sales_q1_01 SUBPARTITIONS 4
STORE IN (q1_01_tab1, q1_01_tab2, q1_01_tab3, q1_01_tab4), PARTITION sales_q1_02 SUBPARTITIONS 4
STORE IN (q1_02_tab1, q1_02_tab2, q1_02_tab3, q1_02_tab4) ) UPDATE GLOBAL INDEXES;

alter table: Table Partition – Truncate

SQL> ALTER TABLE store_sales TRUNCATE PARTITION sales_overflow UPDATE GLOBAL INDEXES;

alter table: Table Properties

SQL> ALTER TABLE parts PCTFREE 10 PCTUSED 60; ALTER TABLE parts STORAGE (NEXT 1M);

SQL> ALTER TABLE parts PARALLEL 4;

alter table: Triggers – Modify Status

SQL> ALTER TABLE test1 DISABLE ALL TRIGGERS;

SQL> ALTER TABLE test1 ENABLE ALL TRIGGERS;

alter tablespace: Backups

SQL> ALTER TABLESPACE test_data_tbs BEGIN BACKUP; ALTER TABLESPACE test_data_tbs END BACKUP;

alter tablespace: Data Files and Tempfiles

SQL> ALTER TABLESPACE test_tbs ADD DATAFILE '/u01/app/oracle/mirdb/mirdb_mirdb_01.dbf' SIZE 100M; ALTER TABLESPACE mytemp ADD TEMPFILE ‘/u01/app/oracle/mirdb/mydb_mytemp_01.dbf' SIZE 100M;
SQL> ALTER TABLESPACE mytemp AUTOEXTEND OFF;
SQL> ALTER TABLESPACE mytemp AUTOEXTEND ON NEXT 100m MAXSIZE 1G;

alter tablespace: Rename

SQL> ALTER TABLESPACE test_data_tbs RENAME TO my_newdata_tbs; 
SQL> alter tablespace: Tablespace Management 
SQL> ALTER TABLESPACE test_data_tbs DEFAULT
STORAGE (INITIAL 100m NEXT 100m FREELISTS 3);  
SQL> ALTER TABLESPACE test_data_tbs MINIMUM EXTENT 500k; ALTER TABLESPACE test_data_tbs RESIZE 100m;
SQL> ALTER TABLESPACE test_data_tbs COALESCE;
SQL> ALTER TABLESPACE test_data_tbs OFFLINE; 

SQL> ALTER TABLESPACE test_data_tbs ONLINE;
SQL> ALTER TABLESPACE test_tbs READ ONLY;
SQL> ALTER TABLESPACE test_tbs READ WRITE;
SQL> ALTER TABLESPACE test_tbs FORCE LOGGING;
SQL> ALTER TABLESPACE test_tbs NOLOGGING;
SQL> ALTER TABLESPACE test_tbs FLASHBACK ON;
SQL> ALTER TABLESPACE test_tbs FLASHBACK OFF;
SQL> ALTER TABLESPACE test_tbs RETENTION GUARANTEE; 
SQL> ALTER TABLESPACE test_tbs RETENTION NOGUARANTEE;

alter trigger

SQL> ALTER TRIGGER trg_my_trigger DISABLE;
SQL> ALTER TRIGGER trg_my_trigger ENABLE;
SQL> ALTER TRIGGER trg_my_trigger RENAME TO tr_new_my_trigger; ALTER TRIGGER trg_my_trigger COMPILE;

alter user: Change Password

SQL> ALTER USER olduser IDENTIFIED BY newpassword; ALTER USER olduser IDENTIFIED EXTERNALLY;

alter user: Password and Account Management

SQL> ALTER USER olduser PASSWORD EXPIRE; ALTER USER olduser ACCOUNT LOCK; ALTER USER olduser ACCOUNT UNLOCK;

alter user: Profile

SQL> ALTER USER olduser PROFILE admin_profile;

alter user: Quotas

SQL> ALTER USER olduser QUOTA UNLIMITED ON users; ALTER USER olduser QUOTA 10000M ON USERS;

alter user: Roles

SQL> ALTER USER olduser DEFAULT ROLE admin_role;
SQL> ALTER USER olduser DEFAULT ROLE NONE;
SQL> ALTER USER olduser DEFAULT ROLE ALL EXCEPT admin_role;

alter user: Tablespace Assignments

SQL> ALTER USER olduser DEFAULT TABLESPACE users; 
SQL> ALTER USER olduser TEMPORARY TABLESPACE temp;

alter view: Constraints

SQL> ALTER VIEW my_view ADD CONSTRAINT u_my_view_01 UNIQUE (empno) RELY DISABLE NOVALIDATE;
SQL> ALTER VIEW my_view DROP CONSTRAINT u_my_view_01;
SQL> ALTER VIEW my_view DROP PRIMARY KEY;
SQL> ALTER VIEW my_view MODIFY CONSTRAINT u_my_view_01 NORELY; ALTER VIEW my_view MODIFY CONSTRAINT u_my_view_01 RELY;

alter view: Recompile

SQL> ALTER VIEW my_view RECOMPILE;

====Hence this will be useful for all our dba daily task in work environment====

This entry was posted on Monday, May 18th, 2026 at 7:10 am and is filed under DBA Administration. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

« Optimizing RMAN Backups to Minimize Database Performance Impact in Oracle Database

Complete DDL CREATE Commands for Operations and Database Administration »

Mir Sayeed Hassan – Oracle Blog

Oracle DBA – Tips & Techniques | Learn with real-time examples

Categories

Archives

Translate

It’s Me

My Certificates

Links

Achievements

VISITORS COUNT

Verified International Academic Qualification from World Education Service (WES)

Jobs