Alter Database
1-Startup
desc v$database;
select name,open_mode,log_mode from v$database;
startup nomount
alter database mount
alter database open read only;
____________________________________________________________________
____________________________________________________________________
____________________________________________________________________
2-Temp Files
desc dba_users;
select username,DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users;
desc v$tempfile;
select * from v$tempfile;
create temporary tablespace temp2 tempfile
'E:\oracle\oradata\ana\temp02_DB01.DBF' size 10M;
or
create temporary tablespace temp2 tempfile
'E:\oracle\oradata\ana\temp02_DB01.DBF' size 10M
extent management local uniform size 1M;
alter user system temporary tablespace temp2;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
alter database tempfile 'E:\oracle\oradata\ana\temp02_DB01.DBF' offline;
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
alter database tempfile 'E:\oracle\oradata\ana\temp02_DB01.DBF' resize 20M;
CREATE USER scott DEFAULT TABLESPACE data TEMPORARY TABLESPACE temp;
ALTER USER scott TEMPORARY TABLESPACE temp;
select d.tablespace_name,d.bytes/1024/1024 "Total Space",f.bytes/1024/1024 "Used Space" from
dba_data_files d,dba_free_space f where d.tablespace_name=f.tablespace_name
____________________________________________________________________
____________________________________________________________________
____________________________________________________________________
3-Data Files
desc v$datafile;
select file#,status,enabled,name from v$datafile;
create tablespace joe datafile
'E:\ORACLE\ORADATA\ANA\joe01.dbf' size 10M;
select file#,status,enabled,name from v$datafile;
shutdown;
startup nomunt;
ORA-01145: offline immediate disallowed unless media recovery enabled
Cause: ALTER TABLESPACE ... OFFLINE IMMEDIATE or ALTER DATABASE DATAFILE ... OFFLINE is
only allowed if database is in ARCHIVELOG mode.
modify initana.ora file , include following parameters
log_archive_start=TRUE
log_archive_dest=E:\oracle\oradata\ana\archive\
shutdown;
startup
select name,value from v$parameter where name like '%log%';
select file#,status,enabled,name from v$datafile;
shutdown
startup nomunt
alter database rename file 'E:\ORACLE\ORADATA\ANA\JOE01.DBF' to 'E:\ORACLE\ORADATA\ANA\
JOE02.DBF'
recover datafile 'E:\ORACLE\ORADATA\ANA\JOE02.DBF';
select d.tablespace_name,d.bytes/1024/1024,d.autoextensible,f.bytes/1024/1024 from dba_data_files d,
dba_free_space f where f.tablespace_name=d.tablespace_name;
select TABLESPACE_NAME, BYTES/1024/1024,BLOCKS,
STATUS,MAXBYTES,MAXBLOCKS,INCREMENT_BY,AUTOEXTENSIBLE from dba_data_files
TABLESPACE_NAME BYTES/1024/1024 BLOCKS STATUS MAXBYTES MAXBLOCKS
INCREMENT_BY AUT
------------------------------ --------------- ---------- --------- ---------- ---------- ----------
SYSTEM 200 51200 AVAILABLE 0 0 0 NO
UNDOTBS 40 10240 AVAILABLE 0 0 0 NO
RBS 10 2560 AVAILABLE 1.7180E+10 4194302 1 YES
JOE 10 2560 AVAILABLE 0 0 0 NO
SQL> alter database datafile 'E:\ORACLE\ORADATA\ANA\JOE02.DBF' autoextend on
2 next 1M
3 maxsize 1024M /////maxsize unlimited equal to 4b db-16 GB 8b db-32 GB
4 ;
Database altered.
SQL>
SQL>
SQL> select TABLESPACE_NAME, BYTES/1024/1024,BLOCKS,
STATUS,MAXBYTES/1024/1024,MAXBLOCKS,INCREMENT_BY,
AUTOEXTENSIBLE from dba_data_files;
TABLESPACE_NAME BYTES/1024/1024 BLOCKS STATUS MAXBYTES/1024/1024
MAXBLOCKS INCREMENT_B
------------------------------ --------------- ---------- --------- ------------------ ---------- --
SYSTEM 200 51200 AVAILABLE 0 0 0 NO
UNDOTBS 40 10240 AVAILABLE 0 0 0 NO
RBS 10 2560 AVAILABLE 16383.9922 4194302 1 YES
JOE 10 2560 AVAILABLE 1024 262144 256 YES
alter database datafile 'E:\ORACLE\ORADATA\ANA\JOE02.DBF' resize 20M;
SQL> select name from v$tempfile
2 union
3 select name from v$datafile;
____________________________________________________________________
____________________________________________________________________
____________________________________________________________________
4-control files
alter database backup controlfile to 'E:\oracle\oradata\ana\archive\controlbk.ctrl';
alter database backup controlfile to 'E:\oracle\oradata\ana\archive\controlbk.ctrl' reuse;
select * from v$controlfile;
shutdown;
modify parameter file,
control_files=("E:\oracle\oradata\ana\CONTROL01.CTL"
,"E:\oracle\oradata\ana\CONTROL02.CTL"
,"E:\oracle\oradata\ana\CONTROL03.CTL"
,"E:\oracle\oradata\ana\CONTROL04.CTL"
)
create spfile from pfile;
startup;
____________________________________________________________________
____________________________________________________________________
____________________________________________________________________
5-Logs / Archives
alter database add logfile 'E:\ORACLE\ORADATA\ANA\LOG_05_DB01.RDO' size 5M
alter system switch logfile;
alter database drop logfile group 3;
alter database add logfile 'E:\ORACLE\ORADATA\ANA\LOG_01_DB01.RDO' size 10M reuse;
select name from V$ARCHIVED_LOG;
