Skip to Main Content

Cloud Platform

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Wrong database name is displayed in SQL worksheet on OCI console

ChakJNov 29 2022

I created an Autonomous transaction database in the cloud free tier account yesterday. I used "Database Actions" to connect to this database ocilabsdb1. But when I query the database "select * from v$database" with the "admin" user in the SQL worksheet icon, I geta different database name "fefypod" created in 2020.
Could you please help?
Kind regards
Chak Jagannathan

Comments

Maran Viswarayar

GOOGLE!!!!!!!

Salman Qureshi

Hello,

Before moving forward, you would need to note following to clear your concepts

1. If you have a request to create a tablespace, you don't need to check existing free space in the database because somehow, you have to create a new tablespace.

2. You can get a query to check free space in the database, but not for all databases in your environment, You would need to run that query in each database individually to find out free space in it.

See following scripts from my blog, these would give you highlight of allocated and free space in your database and some more information as well

Salman - Oracle DBA: Tablespace Growth History and Forecast

http://salmandba.blogspot.sg/2015/01/database-growth-history-and-forecast.html

Salman

salmandba.blogspot.com

trajon

Hi, you need to check your freespace on level tablspaces:


select  a.tablespace_name,

       round(a.bytes_alloc / 1024 / 1024) megs_alloc,

       round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,

       round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,

       round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,

       100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,

       round(maxbytes/1048576) Max,

      c.status, c.contents

from  ( select  f.tablespace_name,

               sum(f.bytes) bytes_alloc,

               sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes

        from dba_data_files f

        group by tablespace_name) a,

      ( select  f.tablespace_name,

               sum(f.bytes)  bytes_free

        from dba_free_space f

        group by tablespace_name) b,

      dba_tablespaces c

where a.tablespace_name = b.tablespace_name(+)

and a.tablespace_name = c.tablespace_name

union all

select h.tablespace_name,

       round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,

       round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576) megs_free,

       round(sum(nvl(p.bytes_used, 0))/ 1048576) megs_used,

       round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,

       100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) pct_used,

       round(sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes) / 1048576)) max,

      c.status, c.contents

from   sys.v_$TEMP_SPACE_HEADER h,

       sys.v_$Temp_extent_pool p,

       dba_temp_files f,

      dba_tablespaces c

where  p.file_id(+) = h.file_id

and    p.tablespace_name(+) = h.tablespace_name

and    f.file_id = h.file_id

and    f.tablespace_name = h.tablespace_name

and f.tablespace_name = c.tablespace_name

group by h.tablespace_name, c.status, c.contents

ORDER BY 1

good luck...

kt1

HI

Are using ASM??, I use the following at database level.

SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,

fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,

(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,

fs.free_space_mb FREE_SPACE_MB,

ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE

FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,

      ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB

      FROM dba_data_files

      GROUP BY tablespace_name) df,

     (SELECT tablespace_name, SUM(bytes) FREE_SPACE,

       ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB

       FROM dba_free_space

       GROUP BY tablespace_name) fs

WHERE df.tablespace_name = fs.tablespace_name(+)

ORDER BY fs.tablespace_name;

1 - 4

Post Details

Added on Nov 29 2022
4 comments
173 views