Oracle SQL Statement Examples: alter database

alter database

·         backup controlfile as a text file

alter database backup controlfile to trace as '';

·         resize a data file: increase size or decrease size

ALTER DATABASE DATAFILE '' RESIZE 1200M;

·         take a datafile out of AUTOEXTEND Mode

alter database datafile '' AUTOEXTEND off;

·         turn on the autoextend feature on a datafile

alter database datafile '' autoextend on next 100m maxsize 2000m;

·         enable block change tracking

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '';

·         add logfile group

ALTER DATABASE ADD LOGFILE GROUP 1 ('') SIZE 200M;

ALTER DATABASE ADD LOGFILE GROUP 2 ('', '') SIZE 200M;

ALTER DATABASE ADD LOGFILE MEMBER '' TO GROUP 1;

·         change GLOBAL_NAME

ALTER DATABASE RENAME GLOBAL_NAME TO mydb;

·         create a new database incarnation of the database

alter database open resetlogs;

·         change archivelog mode

alter database archivelog

alter database archivelog manual

alter database noarchivelog

·         READ ONLY / READ WRITE: The following statement opens the database in read-only mode:

startup mount;

ALTER DATABASE OPEN READ ONLY;

·         The following statement opens the database in read/write mode and clears the online redo logs:

startup mount;

ALTER DATABASE OPEN READ WRITE RESETLOGS;

·         Using Parallel Recovery Processes: The following statement performs tablespace recovery using parallel recovery processes:

ALTER DATABASE RECOVER TABLESPACE tbs_03  PARALLEL;

·         Adding Redo Log File Groups: Examples The following statement adds a redo log file group with two members and identifies it with a GROUP parameter value of 3:

ALTER DATABASE

ADD LOGFILE GROUP 3

('diska:log3.log', 'diskb:log3.log') SIZE 50K;

·         The following statement adds a redo log file group containing two members to thread 5 (in a Real Application Clusters environment) and assigns it a GROUP parameter value of 4:

ALTER DATABASE 

ADD LOGFILE THREAD 5 GROUP 4 

('diska:log4.log', 'diskb:log4:log');

·         Adding Redo Log File Group Members: The following statement adds a member to the redo log file group added in the previous example:

ALTER DATABASE ADD LOGFILE MEMBER 'diskc:log3.log' TO GROUP 3;

·         Dropping Log File Members: The following statement drops one redo log file member added in the previous example:

ALTER DATABASE DROP LOGFILE MEMBER 'diskb:log3.log';

·         The following statement drops all members of the redo log file group 3:

ALTER DATABASE DROP LOGFILE GROUP 3;

·         Renaming a Log File Member: The following statement renames a redo log file member:

ALTER DATABASE RENAME FILE 'diskc:log3.log' TO 'diskb:log3.log';

·         Setting the Default Type of Tablespaces: The following statement specifies that subsequently created tablespaces be created as bigfile tablespaces by default:

ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;

·         Changing the Default Temporary Tablespace: The following statement makes the tbs_5 tablespace the default temporary tablespace of the database. This statement either establishes a default temporary tablespace if none was specified at create time, or replaces an existing default temporary tablespace with tbs_05:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tbs_05;

·         A group of tablespaces can be defined as the default temporary tablespace by using a tablespace group. The following statement makes the tablespaces in the tablespace group tbs_group_01 the default temporary tablespaces of the database:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tbs_grp_01;

·         Creating a New Data File: The following statement creates a new data file tbs_f04.dbf based on the file tbs_f03.dbf. Before creating the new data file, you must take the existing data file (or the tablespace in which it resides) offline.

ALTER DATABASE CREATE DATAFILE 'tbs_f03.dbf' AS 'tbs_f04.dbf';

·         Manipulating Temp Files: The following takes offline the temp file temp02.dbf and then renames the temp file:

ALTER DATABASE TEMPFILE 'temp02.dbf' OFFLINE;

ALTER DATABASE RENAME FILE 'temp02.dbf' TO 'temp03.dbf';

·         Changing the Global Database Name: The following statement changes the global name of the database and includes both the database name and domain:

ALTER DATABASE RENAME GLOBAL_NAME TO demo.world.example.com;

·         Enabling and Disabling Block Change Tracking: The following statement enables block change tracking and causes Oracle Database to create a block change tracking file named tracking_file and overwrite the file if it already exists:

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING

USING FILE 'tracking_file' REUSE;

·         The following statement disables block change tracking and deletes the existing block change tracking file:

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

·         Resizing a Data File: The following statement attempts to change the size of data file diskb:tbs_f5.dbf:

ALTER DATABASE DATAFILE 'diskb:tbs_f5.dbf' RESIZE 10 M;

·         Clearing a Log File: The following statement clears a log file:

ALTER DATABASE CLEAR LOGFILE 'diskc:log3.log';

·         Database Recovery: The following statement performs complete recovery of the entire database, letting Oracle Database generate the name of the next archived redo log file needed:

ALTER DATABASE RECOVER AUTOMATIC DATABASE;

·         The following statement explicitly names a redo log file for Oracle Database to apply:

ALTER DATABASE RECOVER LOGFILE 'diskc:log3.log';

·         The following statement performs time-based recovery of the database:

ALTER DATABASE

RECOVER AUTOMATIC UNTIL TIME '2001-10-27:14:00:00'; 

·         Making a Consistent Database Backup: Assume that the database is open and you want to make a consistent backup of the whole database. This example shuts down the database consistently, mounts the database, makes a consistent whole database backup, and then opens the database.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

BACKUP DATABASE PLUS ARCHIVELOG;

# Now that the backup is complete, open the database.

ALTER DATABASE OPEN;

·         Mounting the Database After Restoring the Control File: This example restores the control file, mounts it, and performs recovery. Finally, the example resets the online redo log.

STARTUP FORCE NOMOUNT;

RESTORE CONTROLFILE FROM AUTOBACKUP;

ALTER DATABASE MOUNT;

# You must run the RECOVER command after restoring a control file even if no

# datafiles require recovery.

RECOVER DEVICE TYPE DISK DATABASE;

ALTER DATABASE OPEN RESETLOGS;