Oracle DBA Day to Day Scripts

Friday, August 11, 2023

How to identify IMPDP status

SELECT

OPNAME,SID,SERIAL#,CONTEXT,SOFAR,TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "PERCENTAGE_COMPLETE"

FROM V$SESSION_LONGOPS

WHERE OPNAME in

(

select d.job_name

from v$session s, v$process p, dba_datapump_sessions d

where p.addr=s.paddr and s.saddr=d.saddr

)

AND OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK != 0

AND SOFAR <> TOTALWORK;

Index details for a specific table in oracle

select table_name, index_name,column_name,column_position from dba_ind_columns where table_name='EMPL_HIST' and table_owner='XXX';

Team,

orase1:SE1C@sapse1c-db /var/tmp/SE1C > tail -100f se1c_pitr.log

Recovery Manager: Release 10.2.0.5.0 - Production on Wed Dec 11 16:39:38 2013

Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN> connect target *
2> run
3> {
4> allocate channel c1 device type disk;
5> allocate channel c2 device type disk;
6> allocate channel c3 device type disk;
7> allocate channel c4 device type disk;
8> set until time "to_date('05-DEC-2013 13:00:00','dd-mm-yyyy hh24:mi:ss')";
9> restore database;
10> recover database;
11> }
12>
connected to target database: SE1 (DBID=305816433, not open)

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=1965 devtype=DISK

allocated channel: c2
channel c2: sid=1964 devtype=DISK

allocated channel: c3
channel c3: sid=1963 devtype=DISK

allocated channel: c4

This is what was done..

orase1:SE1C@sapse1c-db /var/tmp/SE1C > cat se1c_pitr.cmd
connect target /
run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
set until time "to_date('05-DEC-2013 13:00:00','dd-mm-yyyy hh24:mi:ss')";
restore database;
recover database;
}
orase1:SE1C@sapse1c-db /var/tmp/SE1C > rman checksyntax cmdfile=se1c_pitr.cmd

Recovery Manager: Release 10.2.0.5.0 - Production on Wed Dec 11 15:45:00 2013

Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN> connect target *
2> run
3> {
4> allocate channel c1 device type disk;
5> allocate channel c2 device type disk;
6> allocate channel c3 device type disk;
7> allocate channel c4 device type disk;
8> set until time "to_date('05-DEC-2013 13:00:00','dd-mm-yyyy hh24:mi:ss')";
9> restore database;
10> recover database;
11> }
12>
The cmdfile has no syntax errors

Recovery Manager complete.

orase1:SE1C@sapse1c-db /var/tmp/SE1C > crontab -l > /var/tmp/crontab_bkup_12112013.txt
orase1:SE1C@sapse1c-db /var/tmp/SE1C > ls -l /var/tmp/crontab_bkup_12112013.txt
-rw-r--r--   1 orase1   dba          298 Dec 11 16:17 /var/tmp/crontab_bkup_12112013.txt
orase1:SE1C@sapse1c-db /var/tmp/SE1C > crontab -r
orase1:SE1C@sapse1c-db /var/tmp/SE1C > sqldba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 11 16:17:17 2013

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 5368709120 bytes
Fixed Size                  2059680 bytes
Variable Size            2046825056 bytes
Database Buffers         3305111552 bytes
Redo Buffers               14712832 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
orase1:SE1C@sapse1c-db /var/tmp/SE1C > rman cmdfile=se1c_pitr.cmd log=se1c_pitr.log
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12>

Friday, January 22, 2021

adding disk

Steps to add a raw disk to the asm diskgroup

/dev/oracleasm/disks > sudo oracleasm querydisk /dev/mapper/bolora1a_cooraprod06_datafile_4

/dev/oracleasm/disks > sudo oracleasm querydisk /dev/mapper/bolora1a_log_6
/dev/oracleasm/disks > sudo oracleasm querydisk /dev/mapper/bolora1a_log_5

sudo /etc/init.d/oracleasm querydisk /dev/mapper/bolora1a_cooraprod06_datafile_4

dmzora:+ASM@bolora1a /dev/mapper > sudo /etc/init.d/oracleasm querydisk /dev/mapper/bolora1a_cooraprod06_datafile_4
Device "/dev/mapper/bolora1a_cooraprod06_datafile_4" is not marked as an ASM disk
dmzora:+ASM@bolora1a /dev/mapper > sudo /etc/init.d/oracleasm querydisk /dev/mapper/bolora1a_cooraprod06_datafile_3
Device "/dev/mapper/bolora1a_cooraprod06_datafile_3" is not marked as an ASM disk
dmzora:+ASM@bolora1a /dev/mapper > date
Fri Jun 13 02:49:41 MDT 2014

sudo /etc/init.d/oracleasm createdisk LOG5 /dev/mapper/bolora1a_log_5

sudo /etc/init.d/oracleasm createdisk LOG6 /dev/mapper/bolora1a_log_6

sudo /etc/init.d/oracleasm createdisk LOG5 /dev/mapper/bolora1a_log_5

sudo /etc/init.d/oracleasm createdisk LOG5 /dev/mapper/bolora1b_log_5
sudo /etc/init.d/oracleasm createdisk LOG6 /dev/mapper/bolora1b_log_6

/dev/mapper/bolora1b_log_6

sudo /etc/init.d/oracleasm createdisk LOG5 /dev/mapper/bolora1a_log_5
sudo /etc/init.d/oracleasm createdisk LOG6 /dev/mapper/bolora1a_log_6

sudo chown dmzora:dba LOG5
sudo chown dmzora:dba LOG6

sudo chown dmzora:dba LOG5

sudo chmod 660 LOG5
sudo chmod 660 LOG6

sudo chmod 660 DATA_ORPROD06_02

CREATE DISKGROUP LOG5 EXTERNAL REDUNDANCY DISK '/dev/oracleasm/disks/LOG5' attribute 'compatible.asm' = '11.2','au_size' = '4M';

CREATE DISKGROUP LOG6 EXTERNAL REDUNDANCY DISK '/dev/oracleasm/disks/LOG6' attribute 'compatible.asm' = '11.2','au_size' = '4M';

/dev/oracleasm/disks/DATA_ORPROD06_01 0 PROVISIONED
0 /dev/oracleasm/disks/DATA_ORPROD06_02

CREATE DISKGROUP DATAFILE_COORAPROD06
EXTERNAL REDUNDANCY DISK
'/dev/oracleasm/disks/DATA_ORPROD06_01',
'/dev/oracleasm/disks/DATA_ORPROD06_02'
attribute 'compatible.asm' = '11.2','au_size' = '4M';

CREATE DISKGROUP DATAFILE_COORAPROD06
EXTERNAL REDUNDANCY DISK
'/dev/oracleasm/disks/DATA_ORPROD06_01',
'/dev/oracleasm/disks/DATA_ORPROD06_02'
attribute 'compatible.asm' = '11.2','au_size' = '4M';

DATA_ORPROD06_02

Sarada,

Here are the steps for adding of disks and rebalancing.

Check for the disk groups:

SQL>
set lines 250
SELECT NAME, TOTAL_MB, FREE_MB, (TOTAL_MB - FREE_MB) USED_MB,STATE, TYPE FROM V$ASM_DISKGROUP ;

NAME TOTAL_MB FREE_MB USED_MB STATE TYPE
------------------------------ ---------- ---------- ---------- ----------- ------
ARCHLOG 307260 296404 10856 MOUNTED EXTERN
DATAFILE_COORAPROD46 716940 32724 684216 MOUNTED EXTERN
DATAFILE_COORAPROD68 102420 29896 72524 MOUNTED EXTERN
DATAFILE_COORAPROD75 204840 200788 4052 MOUNTED EXTERN
DATAFILE_CTORAPROD36 2048400 221516 1826884 MOUNTED EXTERN
LOG1 20484 11148 9336 MOUNTED EXTERN
LOG2 20484 11180 9304 MOUNTED EXTERN

7 rows selected.

SQL> select NAME,GROUP_NUMBER from v$asm_diskgroup;

NAME GROUP_NUMBER
------------------------------ ------------
ARCHLOG 1
DATAFILE_COORAPROD46 2
DATAFILE_COORAPROD68 3
DATAFILE_COORAPROD75 4
DATAFILE_CTORAPROD36 5
LOG1 6
LOG2 7

7 rows selected.

SQL>
set linesize 150
set pagesize 9999
column path format a70
select GROUP_NUMBER,NAME,PATH,TOTAL_MB/1024,HEADER_STATUS from v$asm_disk order by total_mb;

GROUP_NUMBER NAME PATH TOTAL_MB/1024 HEADER_STATU
------------ ------------------------------ ---------------------------------------------------------------------- ------------- ------------
0 /dev/oracleasm/disks/VMX_DATA38 0 FORMER
0 /dev/oracleasm/disks/VMX_DATA25 0 FORMER
0 /dev/oracleasm/disks/VMX_R10_LOG4 0 FORMER
0 /dev/oracleasm/disks/VMX_DATA26 0 FORMER
0 /dev/oracleasm/disks/VMX_R10_LOG3 0 FORMER
0 /dev/oracleasm/disks/VMX_DATA40 0 FORMER
0 /dev/oracleasm/disks/VMX_DATA34 0 FORMER
0 /dev/oracleasm/disks/VMX_DATA39 0 FORMER
0 /dev/oracleasm/disks/VMX_DATA35 0 FORMER
6 LOG1_0000 /dev/oracleasm/disks/VMX_R10_LOG1 20.0039063 MEMBER
7 LOG2_0000 /dev/oracleasm/disks/VMX_R10_LOG2 20.0039063 MEMBER
5 DATAFILE_CTORAPROD36_0008 /dev/oracleasm/disks/VMX_DATA31 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0007 /dev/oracleasm/disks/VMX_DATA32 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0006 /dev/oracleasm/disks/VMX_DATA33 100.019531 MEMBER
4 DATAFILE_COORAPROD75_0001 /dev/oracleasm/disks/VMX_DATA2 100.019531 MEMBER
4 DATAFILE_COORAPROD75_0000 /dev/oracleasm/disks/VMX_DATA1 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0010 /dev/oracleasm/disks/VMX_DATA3 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0005 /dev/oracleasm/disks/VMX_DATA4 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0004 /dev/oracleasm/disks/VMX_DATA5 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0003 /dev/oracleasm/disks/VMX_DATA6 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0002 /dev/oracleasm/disks/VMX_DATA7 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0001 /dev/oracleasm/disks/VMX_DATA8 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0000 /dev/oracleasm/disks/VMX_DATA9 100.019531 MEMBER
3 DATAFILE_COORAPROD68_0000 /dev/oracleasm/disks/VMX_DATA10 100.019531 MEMBER
2 DATAFILE_COORAPROD46_0000 /dev/oracleasm/disks/VMX_DATA11 100.019531 MEMBER
2 DATAFILE_COORAPROD46_0001 /dev/oracleasm/disks/VMX_DATA12 100.019531 MEMBER
2 DATAFILE_COORAPROD46_0002 /dev/oracleasm/disks/VMX_DATA13 100.019531 MEMBER
2 DATAFILE_COORAPROD46_0004 /dev/oracleasm/disks/VMX_DATA15 100.019531 MEMBER
2 DATAFILE_COORAPROD46_0003 /dev/oracleasm/disks/VMX_DATA14 100.019531 MEMBER
2 DATAFILE_COORAPROD46_0005 /dev/oracleasm/disks/VMX_DATA16 100.019531 MEMBER
1 ARCHLOG_0001 /dev/oracleasm/disks/VMX_DATA17 100.019531 MEMBER
1 ARCHLOG_0002 /dev/oracleasm/disks/VMX_DATA18 100.019531 MEMBER
2 DATAFILE_COORAPROD46_0006 /dev/oracleasm/disks/VMX_DATA19 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0011 /dev/oracleasm/disks/VMX_DATA29 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0012 /dev/oracleasm/disks/VMX_DATA28 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0013 /dev/oracleasm/disks/VMX_DATA27 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0017 /dev/oracleasm/disks/VMX_DATA20 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0019 /dev/oracleasm/disks/VMX_DATA24 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0018 /dev/oracleasm/disks/VMX_DATA23 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0014 /dev/oracleasm/disks/VMX_DATA22 100.019531 MEMBER
1 ARCHLOG_0000 /dev/oracleasm/disks/VMX_DATA21 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0016 /dev/oracleasm/disks/VMX_DATA37 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0015 /dev/oracleasm/disks/VMX_DATA36 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0009 /dev/oracleasm/disks/VMX_DATA30 100.019531 MEMBER

44 rows selected.

Given devices:

oracle:+ASM@lnxora14 /oracle/admin/scripts > sudo /etc/init.d/oracleasm querydisk /dev/emcpowerbf
Device "/dev/emcpowerbf" is not marked as an ASM disk
oracle:+ASM@lnxora14 /oracle/admin/scripts > sudo /etc/init.d/oracleasm querydisk /dev/emcpowerbg
Device "/dev/emcpowerbg" is not marked as an ASM disk
oracle:+ASM@lnxora14 /oracle/admin/scripts > sudo /etc/init.d/oracleasm querydisk /dev/emcpowerbh
Device "/dev/emcpowerbh" is not marked as an ASM disk

or

-----------------------------------------------------------------------------------------------------------
[{{{{{ sudo oracleasm querydisk /dev/mapper/test23b_asm_data_3_new

oracle:+ASM@lnxora17 /dev/oracleasm/disks > sudo oracleasm querydisk /dev/mapper/test23b_asm_data_3_new
[sudo] password for oracle:
Device "/dev/mapper/test23b_asm_data_3_new" is not marked as an ASM disk

cd /dev/oracleasm/disks

DATA_ORTEST23_03

sudo /usr/sbin/asmtool -C -l /dev/oracleasm/disks -n DATA_ORTEST23_03 -s /dev/mapper/test23b_asm_data_3_new -a force=yes

sudo /etc/init.d/oracleasm createdisk DATA_ORTEST23_03 /dev/mapper/test23b_asm_data_3_new

sudo /etc/init.d/oracleasm listdisks ]}}}}}}}}}

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

sudo /usr/sbin/asmtool -C -l /dev/oracleasm -n VMX_DATA41 -s /dev/emcpowerbf -a force=yes
sudo /usr/sbin/asmtool -C -l /dev/oracleasm -n VMX_DATA42 -s /dev/emcpowerbg -a force=yes
sudo /usr/sbin/asmtool -C -l /dev/oracleasm -n VMX_DATA43 -s /dev/emcpowerbh -a force=yes

oracle:+ASM@lnxora14 /dev/oracleasm/disks > sudo /usr/sbin/asmtool -C -l /dev/oracleasm -n VMX_DATA41 -s /dev/emcpowerbf -a force=yes
[sudo] password for oracle:
asmtool: Device "/dev/emcpowerbf" is not a partition
asmtool: Continuing anyway

oracle:+ASM@lnxora14 /dev/oracleasm/disks > sudo /usr/sbin/asmtool -C -l /dev/oracleasm -n VMX_DATA42 -s /dev/emcpowerbg -a force=yes
asmtool: Device "/dev/emcpowerbg" is not a partition
asmtool: Continuing anyway

oracle:+ASM@lnxora14 /dev/oracleasm/disks > sudo /usr/sbin/asmtool -C -l /dev/oracleasm -n VMX_DATA43 -s /dev/emcpowerbh -a force=yes
asmtool: Device "/dev/emcpowerbh" is not a partition
asmtool: Continuing anyway

sudo /etc/init.d/oracleasm listdisks

oracle:+ASM@lnxora14 /dev/oracleasm/disks > ls -la

brw-r----- 1 root root 120, 912 Jun 6 17:36 VMX_DATA41
brw-r----- 1 root root 120, 928 Jun 6 17:37 VMX_DATA42
brw-r----- 1 root root 120, 944 Jun 6 17:37 VMX_DATA43

oracle:+ASM@lnxora14 /dev/oracleasm/disks > sudo chown oracle:dba VMX_DATA41
oracle:+ASM@lnxora14 /dev/oracleasm/disks > sudo chown oracle:dba VMX_DATA42
oracle:+ASM@lnxora14 /dev/oracleasm/disks > sudo chown oracle:dba VMX_DATA43

oracle:+ASM@lnxora14 /dev/oracleasm/disks > ls -la
brw-r----- 1 oracle dba 120, 912 Jun 6 17:36 VMX_DATA41
brw-r----- 1 oracle dba 120, 928 Jun 6 17:37 VMX_DATA42
brw-r----- 1 oracle dba 120, 944 Jun 6 17:37 VMX_DATA43

r - 4
w - 2
x - 1

oracle:+ASM@lnxora14 /dev/oracleasm/disks > sudo chmod 660 VMX_DATA41
oracle:+ASM@lnxora14 /dev/oracleasm/disks > sudo chmod 660 VMX_DATA42
oracle:+ASM@lnxora14 /dev/oracleasm/disks > sudo chmod 660 VMX_DATA43
oracle:+ASM@lnxora14 /dev/oracleasm/disks > ls -la

brw-rw---- 1 oracle dba 120, 912 Jun 6 17:36 VMX_DATA41
brw-rw---- 1 oracle dba 120, 928 Jun 6 17:37 VMX_DATA42
brw-rw---- 1 oracle dba 120, 944 Jun 6 17:37 VMX_DATA43

oracle:+ASM@lnxora14 /dev/oracleasm/disks > sqlasm

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 6 17:40:31 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option

SQL>
set lines 250
col path format a70
select GROUP_NUMBER,NAME,PATH,TOTAL_MB/1024,HEADER_STATUS from v$asm_disk where HEADER_STATUS='PROVISIONED';

GROUP_NUMBER NAME PATH TOTAL_MB/1024 HEADER_STATU
------------ ------------------------------ ---------------------------------------------------------------------- ------------- ------------
0 /dev/oracleasm/disks/VMX_DATA43 0 PROVISIONED
0 /dev/oracleasm/disks/VMX_DATA41 0 PROVISIONED
0 /dev/oracleasm/disks/VMX_DATA42 0 PROVISIONED

Open alert log to check any errors found

Always give complete path(shown in above query) of disk and should be in provisioned header status while creating diskgroup :
==============================================================================================================================

CREATE DISKGROUP MYTEST EXTERNAL REDUNDANCY DISK '/dev/oracleasm/disks/VMX_DATA41','/dev/oracleasm/disks/VMX_DATA42','/dev/oracleasm/disks/VMX_DATA43';

Open alert log to check any errors found
---------------------------------------

SQL> ALTER DISKGROUP MYTEST CHECK ALL;

Diskgroup altered.

SQL> ALTER DISKGROUP MYTEST MOUNT ;

Diskgroup altered.

SQL> DROP DISKGROUP MYTEST ;

Diskgroup dropped.

SQL>
set lines 250
col path format a70
select GROUP_NUMBER,NAME,PATH,TOTAL_MB/1024,HEADER_STATUS from v$asm_disk where HEADER_STATUS='PROVISIONED';

it shOULD SHOW NO ROWS

SQL>ALTER DISKGROUP DATAFILE_COORAPROD46 ADD DISK '/dev/oracleasm/disks/VMX_DATA41' NAME DATAFILE_COORAPROD46_0007 ,'/dev/oracleasm/disks/VMX_DATA42' NAME DATAFILE_COORAPROD46_0008 REBALANCE POWER 4;

Diskgroup altered.

SQL> set lines 250
col path format a70
select GROUP_NUMBER,NAME,PATH,TOTAL_MB/1024,HEADER_STATUS from v$asm_disk;

SQL> Select operation, state, power, est_rate, est_minutes, round((sofar/EST_WORK)*100,2) pct_comp From v$asm_operation;

OPERA STAT POWER EST_RATE EST_MINUTES PCT_COMP
----- ---- ---------- ---------- ----------- ----------
REBAL RUN 4 2443 14 5.4

SQL>
set lines 250
SELECT NAME, TOTAL_MB, FREE_MB, (TOTAL_MB - FREE_MB) USED_MB,STATE, TYPE FROM V$ASM_DISKGROUP ;

Drop old disks if any to recliam the space( note: after adding new disks only) [mostly while storage migration] :
----------------------------------------------------------------------------------------------------------------
ALTER DISKGROUP DATAFILE_COORAPROD46 DROP DISK 'DATAFILE_COORAPROD46_0007' REBALANCE POWER 5;

How to tar a file in linux

Let say u have a directory under /rajesh/logs/ and logs has multiple logs and trace files, and you would like to create or tar the logs files means execute the command below as

tar -zcvf tar_filename.tar.gz /rajesh/logs

In your current directory the file will be created

-c : Create a tar ball.
-v : Verbose output (show progress).
-f : Output tar ball archive file name.
-x : Extract all files from archive.tar.
-t : Display the contents (file list) of an archive.

Create a tar ball called /tmp/data.tar for /home/vivek/data directory, enter:

tar -cvf /tmp/data.tar /home/vivek/data

To View a Tar Ball Contains (list file inside a tar ball)

Type the following command:

tar -tvf /tmp/data.ta

To Extract a Tar Ball

Type the following command to extract /tmp/data.tar in a current directory, enter:

tar -xvf /tmp/data.ta

Type the following command to extract resume.pdf file from an archive called data-backup.tar in a current directory, enter:

tar -xvf data-backup.tar resume.pdf

Type the following command to extract pic1.jpg, file1.doc, and foo.mov files from an archive called data-backup.tar in a current directory, enter:

tar -xvf data-backup.tar pic1.jpg file1.doc foo.movf

Type the following command to extract /tmp/data.tar in a directory called /home/sales/data, enter:

tar -xvf /tmp/data.tar -C /home/sales/data

Backup hangs

Some times , rman sessions can hang due to so many reasons, or some time we may have to stop

the backup jobs, during those times we can use the below query to grab oracle rman session details to

kill from the database level

COLUMN CLIENT_INFO FORMAT a30

COLUMN SID FORMAT 999

COLUMN SPID FORMAT 9999

SELECT s.SID, p.SPID, s.CLIENT_INFO
FROM   V$PROCESS p, V$SESSION s
WHERE  p.ADDR = s.PADDR
AND    CLIENT_INFO LIKE 'rman%'
/

Oracle Database size

To Find an oracle database size

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

The above dba_data_files view will calculate size of the (actual) all datafiles created with the

current size.