CODEWITHSUNDEEP
oracle-databaseplsqldatabase-administration
Data2explore
Data2explore

Reputation: 452

Database Size in GB with use space and free space Oracle DB

Hi all i am trying to find database size in GB with used space and free space

Query

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
/

error

ORA-00911: invalid character
00911. 00000 -  "invalid character"
*Cause:    identifiers may not start with any ASCII character other than
           letters and numbers.  $#_ are also allowed after the first
           character.  Identifiers enclosed by doublequotes may contain
           any character other than a doublequote.  Alternative quotes
           (q'#...#') cannot use spaces, tabs, or carriage returns as
           delimiters.  For all other contexts, consult the SQL Language
           Reference Manual.
*Action:
Error at Line: 2 Column: 48

upon trying still not able to rectify the error , any suggestions ?

Upvotes: 0

Views: 1497

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

The error message refers to the 48th character on line 2. The 'minus' character is an en-dash, not a hyphen/minus. You can see the difference by dumping the characters.

You may have types that accidentally, or maybe copied and pasted from, say, a Word document. That can also cause problems with 'smart' quotes and sometimes even non-breaking spaces.

Retype the character and it should work.

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"
...

Upvotes: 1

Related Questions