Posts Tagged ‘data files’

Renaming Data Files in 11g database

September 22nd, 2019, posted in Oracle Queries
Share

Renaming datafile (.dbf) rename in 11g

 

Rename Datafiles in Single Tablespace (Database Open Mode) :

Caution : Backup your tablespace Before you will do some change in your tablespace.

Please find below steps to rename datafile.

We can use the alter tablespace rename datafile command,
but the tablespace most be offline and you must re-name at OS level command the data file while the tablespace is offline:

Step: 1

[ora@oracle ~]$ sqlplus / as sysdba

SQL> ALTER TABLESPACE SYSAUX OFFLINE NORMAL;
Tablespace altered.

SQL> !



Step: 2

Rename undo07.dbf to sysaux08.dbf

[ora@oracle ~]$ cd /u01/db/apps_st/data/

[ora@oracle data]$ ls -lrt|grep undo07.dbf
-rw-r----- 1 oraqa dba 3221233664 Aug 9 11:32 undo07.dbf

[ora@oracle data]$ chmod 755 undo07.dbf

[ora@oracle data]$ mv undo07.dbf sysaux08.dbf

[ora@oracle data]$ ls -lrt|grep undo07.dbf

[ora@oracle data]$ ls -lrt|grep sysaux08.dbf
-rwxr-xr-x 1 oraqa dba 3221233664 Aug 9 11:32 sysaux08.dbf


Step: 3

[ora@oracle data]$ sqlplus / as sysdba

SQL> ALTER TABLESPACE SYSAUX RENAME DATAFILE '/u01/db/apps_st/data/undo07.dbf' to '/u01/db/apps_st/data/sysaux08.dbf';

Tablespace altered.



Step: 4

SQL> ALTER TABLESPACE SYSAUX ONLINE;

Tablespace altered.


Steps: 5

Verify tablespace status.

SQL> SELECT status
FROM dba_tablespaces
WHERE tablespace_name = 'SYSAUX';

STATUS
---------
ONLINE

Verify datafile name.

SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME = 'SYSAUX';

TABLESPACE_NAME-------------FILE_NAME
--------------------------------------------------------------------------------
SYSAUX /u01/db/apps_st/data/sysaux08.dbf

 

Rename datafile undo07.dbf to sysaux08.dbf completed.

Share
read more no comments

Oracle : Size Of Database

March 31st, 2015, posted in Oracle Queries
Share

An Oracle Database consists of data files, redo log files, control files, temporary files.Oracle : Size Of Database,Oracle,Size Of Database,Database,data files, redo log files, control files, temporary files,Oracle data files,Oracle redo log files,Oracle control files,Oracle temporary files
Whenever you say the size of the database this actually means the summation of these files.

 

 

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from 
( select sum(bytes)/1024/1024 data_size from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024 temp_size from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size from v$controlfile) d;

-------------------------------------------------------------
For Round Figure For MB :

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from 
( select round( sum(bytes)/1024/1024 ) data_size from dba_data_files ) a,
( select round( nvl(sum(bytes),0)/1024/1024 ) temp_size from dba_temp_files ) b,
( select round( sum(bytes)/1024/1024 ) redo_size from sys.v_$log ) c,
( select round( sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 ) controlfile_size from v$controlfile) d;

-------------------------------------------------------------

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in GB"
from 
( select sum(bytes/1024/1024/1024) data_size from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) b,
( select sum(bytes/1024/1024/1024) redo_size from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) d;

-------------------------------------------------------------
For Round Figure GB :


select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in GB"
from 
( select round( sum(bytes/1024/1024/1024) ) data_size from dba_data_files ) a,
( select round( nvl(sum(bytes),0)/1024/1024/1024 ) temp_size from dba_temp_files ) b,
( select round( sum(bytes/1024/1024/1024) ) redo_size from sys.v_$log ) c,
( select round( sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 ) controlfile_size from v$controlfile) d;



----------------------------------------------------------

Get database size from v$datafile:

select round((sum(bytes)/1048576/1024),2) from v$datafile;

------------------------------------------------------------
Get Oracle Database size from dba_data_files:

select "Reserved_Space(MB)", "Reserved_Space(MB)" - "Free_Space(MB)" "Used_Space(MB)","Free_Space(MB)"
from(
select 
(select sum(bytes/(1014*1024)) from dba_data_files) "Reserved_Space(MB)",
(select sum(bytes/(1024*1024)) from dba_free_space) "Free_Space(MB)"
from dual
);

-----------------------------------------------------------------
-

 

 

here
a is megabytes allocated to ALL datafiles
b is megabytes allocated to ALL TEMP files
c is megabytes allocated to ALL redo-logs
d is megabytes allocated to ALL control files

Share
read more no comments