Size need to check as DBAs


How to find database size?

SQL> select sum(bytes)/1024/1024 from dba_data_files;


                              (OR)
SQL>select sum(result) from (
select ‘dba_data_files’,sum(bytes)/1024/1024/1024 result from dba_data_files
union
select ‘dba_temp_files’,sum(bytes)/1024/1024/1024 result from dba_temp_files);


(OR)


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;

How to find Schema size?

SQL> select sum(bytes)/1024/1024 from dba_segments where OWNER=’SCOTT’;


How to find tablespace size?
SQL> select tablespace_name,file_name,(bytes/1024/1024) from dba_data_files where tablespace_name=’USERS’

How to find free space of tablespace?

SQL> select sum(bytes/1024/1024/1024) from dba_free_space where TABLESPACE_NAME= ‘USERS’;

How to find datafile size?

SQL>select file#,status,enabled,checkpoint_change#,bytes,create_bytes,name from v$datafile;

How to find table size?

SQL>select segment_name,segment_type,bytes/1024/1024 MB
 from dba_segments
 where segment_type=’TABLE’ and segment_name= <’table_name’>;

How to find used size in database?

SQL> select sum(bytes)/1024/1024 from dba_segments;

How to find free space in database?

select sum(bytes)/1024/1024/1024 from dba_free_space;

How to find redo log size?

SELECT sum(bytes)/1024/1024 redo_size FROM sys.v_$log;

How to find temp file size?

SELECT sum(bytes)/1024/1024 temp_size FROM dba_temp_files;

How to find default tablespace in user?

SQL> select default_tablespace from dba_users where username='<User_name>’;

How find default tablespace, user in particular table?

SQL> select OWNER,TABLESPACE_NAME from dba_tables where TABLE_NAME='<Table_name>’;

  • December 31, 2018 | 15 views
  • Comments