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,[Link]/1024/1024 "Total Space",[Link]/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\[Link]' 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 [Link] 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\[Link]' to 'E:\ORACLE\ORADATA\ANA\
[Link]'
recover datafile 'E:\ORACLE\ORADATA\ANA\[Link]';
select d.tablespace_name,[Link]/1024/1024,[Link],[Link]/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\[Link]' 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\[Link]' 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\[Link]';
alter database backup controlfile to 'E:\oracle\oradata\ana\archive\[Link]' reuse;
select * from v$controlfile;
shutdown;
modify parameter file,
control_files=("E:\oracle\oradata\ana\[Link]"
,"E:\oracle\oradata\ana\[Link]"
,"E:\oracle\oradata\ana\[Link]"
,"E:\oracle\oradata\ana\[Link]"
)
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;