total size of oracle database

How to check the total size of oracle database

We know  oracle database consists of data files, redo log files, control files, temporary files and temporary files.

The size of the database actually means the total size of all these files.


col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/

I found this on a blog and very use full. It will show you the total size and the used size. Total size includes size of  all files.

output will be like :-



Database Size        Used space           Free space
-------------------- -------------------- --------------------
78 GB                59 GB                19 GB

Keep sharing :)

ORA-65016: FILE_NAME_CONVERT must be specified

ORA-65016: FILE_NAME_CONVERT must be specified

I just started working on 12C version of oracle database.It's new to me and facing many problems.


Error code: ORA-65016: FILE_NAME_CONVERT must be specified


Description:"ORA-65016: FILE_NAME_CONVERT must be specified" normally occurs when you create a PDB.I will explain later what is a PDB.


Cause and solution :

 ORA-65016: FILE_NAME_CONVERT must be specified caused when Data files, and possibly other files, needed to be copied as a part of creating a pluggable database.Enable OMF or define PDB_FILE_NAME_CONVERT system parameter before issuing CREATE PLUGGABLE DATABASE statement, or specify FILE_NAME_CONVERT clause as a part of the statement and make sure the path you are giving to convert the file exists.

I think if you are creating the PDB's using GUI then you will not face this error "ORA-65016: FILE_NAME_CONVERT must be specified". If you creating ODB using script and you have gave a wrong path then may you face "ORA-65016: FILE_NAME_CONVERT must be specified".


Syntax to create PDB:-

create pluggable database TEST admin user TEST identified by TEST  file_name_convert = ('lcoation/', '/lcoation/');


Please write in comment box if you think there is better explanation on this error "ORA-65016: FILE_NAME_CONVERT must be specified".