Tuesday 1 December 2015

Connecting to Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c


Connecting to the root of a container database is the same as that of any previous database instance. On the database server you can use OS Authentication.
 
[oracle@vmdb12c ~]$ sqlplus / as sysdba


SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 30 11:38:44 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 

SQL> show con_name

CON_NAME
——————————
CDB$ROOT  
The V$SERVICES views can be used to display available services from the database.

SQL> select name,pdb from v$services order by 2;

NAME PDB
——————– ———-
zwcXDB CDB$ROOT
SYS$BACKGROUND CDB$ROOT
SYS$USERS CDB$ROOT
ptest12c PTEST12C
ptest12c_1 PTEST12C_1
ptest12c_2 PTEST12C_2

6 rows selected.  

The lsnrctl utility allows you to display the available services from the command line.

[oracle@vmdb12c ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 01-DEC-2015 12:17:38

Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER_TEST12C
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 16-NOV-2015 18:02:53
Uptime 14 days 18 hr. 14 min. 45 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /data/amit/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File /data/amit/app/oracle/diag/tnslsnr/bigdata2/listener_test12c/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=localhost.localdomain)(PORT=5500))(Security=(my_wallet_directory=/data/amit/app/oracle/admin/test12c/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PTEST12C" has 2 instance(s).
Instance "PTEST12C", status UNKNOWN, has 1 handler(s) for this service...
Instance "test12c", status READY, has 1 handler(s) for this service...
Service "ptest12c_1" has 1 instance(s).
Instance "test12c", status READY, has 1 handler(s) for this service...
Service "ptest12c_2" has 1 instance(s).
Instance "test12c", status READY, has 1 handler(s) for this service...
Service "ptest12c_3" has 1 instance(s).
Instance "test12c", status READY, has 1 handler(s) for this service...
Service "test12c" has 2 instance(s).
Instance "test12c", status UNKNOWN, has 1 handler(s) for this service...
Instance "test12c", status READY, has 1 handler(s) for this service...
Service "test12cXDB" has 1 instance(s).
Instance "test12c", status READY, has 1 handler(s) for this service...

The command completed successfully

[oracle@vmdb12c ~]$ lsnrctl service 

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 01-DEC-2015 12:19:05

Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "PTEST12C" has 2 instance(s).
Instance "PTEST12C", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:71 refused:0
LOCAL SERVER
Instance "test12c", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
Service "ptest12c_1" has 1 instance(s).
Instance "test12c", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
Service "ptest12c_2" has 1 instance(s).
Instance "test12c", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
Service "ptest12c_3" has 1 instance(s).
Instance "test12c", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
Service "test12c" has 2 instance(s).
Instance "test12c", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:10 refused:0
LOCAL SERVER
Instance "test12c", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
Service "test12cXDB" has 1 instance(s).
Instance "test12c", status READY, has 1 handler(s) for this service...
Handler(s):
.......
The command completed successfully

Connections using services are unchanged from previous versions.
easy connect


[oracle@vmdb12c ~]$ sqlplus system/oracle123@202.137.235.171:1521/PTEST12C_2

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 1 15:24:39 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Tue Dec 01 2015 13:00:19 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME
------------------------------

PTEST12C_2

[oracle@vmdb12c ~]$ sqlplus system/oracle123@202.137.235.171:1521/PTEST12C_3

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 1 15:24:39 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Tue Dec 01 2015 13:00:19 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME
------------------------------

PTEST12C_3

tnsnames.ora


[oracle@vmdb12c ~]$sqlplus system/oracle@PTEST12C_3

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 1 15:47:25 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Tue Dec 01 2015 15:24:39 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


SQL> show con_name


CON_NAME
------------------------------

PTEST12C_3

[oracle@vmdb12c ~]$sqlplus system/oracle@PTEST12C_1

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 1 15:47:25 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Tue Dec 01 2015 15:24:39 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show con_name

CON_NAME
------------------------------
PTEST12C_1

tnsnames.ora


TEST12C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test12c)
)
)
PTEST12C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PTEST12C)
)
)
PTEST12C_1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PTEST12C_1)
)
)
PTEST12C_3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PTEST12C_3)
)
)
LISTENER_TEST12C =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))

When logged in to the CDB as an appropriately privileged user, the ALTER SESSION command can be used to switch between containers within the container database.

[oracle@vmdb12c ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 1 15:53:10 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter session set container=PTEST12C_1;

Session altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PTEST12C_1 READ WRITE NO

SQL> alter session set container=PTEST12C_2;

Session altered.

SQL> show pdbs


CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
5 PTEST12C_2 READ WRITE NO

SQL> alter session set container=cdb$root;

Session altered.

SQL> show pdbs


CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO 
4 PTEST12C_1 READ WRITE NO
5 PTEST12C_2 READ WRITE NO
6 PTEST12C_3 READ WRITE NO

SQL> select sys_context('USERENV','CON_NAME') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
——————————————————————————–
CDB$ROOT


SQL> alter session set container=PTEST12C_1

Session altered.

SQL> select sys_context('USERENV','CON_NAME') from dual;


SYS_CONTEXT('USERENV','CON_NAME')
——————————————————————————– 

PTEST12C_1




I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!





Wednesday 25 November 2015

Recovery scenarios involving loss of SPFILE

In this session we will see how to recover spfile from rman backup.

Considering we have RMAN backup available that has a copy of the spfile

Case 1) Autobackup in Flash (or now called Fast) Recovery Area

The SPFILE has accidently been deleted and now the database is not starting up after a shutdown has been 
executed.

FRA has been configured.

If FRA has been configured, the backup of the SPFILE is located in the autobackup sub-directory.

For example:

amit/app/oracle/fast_recovery_area/TEST12C/autobackup/2015_11_25/o1_mf_s_896703524_c5bpgjs5_.bkp'

Note that it is stored in OMF format in this example. The ‘s’ in the string identifies the OMF as a backup 
related to the SPFILE

To recover from loss of SPFILE if you are NOT using an RMAN Catalog, we need to do two things first :

1) Set the DBID
2) Issue the STARTUP NOMOUNT FORCE command from an RMAN prompt (note – not SQL*PLUS)
RMAN> SET DBID=1500410030;

executing command: SET DBID

RMAN> startup nomount force;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/data/amit/app/oracle/product/12.1.0/db_1/dbs/inittest12c.ora'

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started
Total System Global Area    1073741824 bytes

Fixed Size                     2932632 bytes
Variable Size                293601384 bytes
Database Buffers             771751936 bytes
Redo Buffers                   5455872 bytes

This is a typical error we will face when either restoring the SPFILE or control file from an autobackup.
RMAN> restore spfile from autobackup;

Starting restore at 25-NOV-15
using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151125
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151124
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151123
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151122
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151121
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151120
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151119
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/25/2015 16:01:20
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece
The reason in this case is that since the spfile is missing and we have mounted the instance using a dummy 
spfile, the database needs to know where to look to find the autobackup of the spfile .

So now we include the db_file_recovery_dest and db_name parameters in the RESTORE SPFILE command.
RMAN> restore spfile from '/data/amit/app/oracle/fast_recovery_area/TEST12C/autobackup/2015_11_25/o1_mf_s_896703524_c5bpgjs5_.bkp';

Starting restore at 25-NOV-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /data/amit/app/oracle/fast_recovery_area/TEST12C/autobackup/2015_11_25/o1_mf_s_896703524_c5bpgjs5_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 25-NOV-15

Case 2) Autobackup in non-FRA location – non OMF
RMAN> startup nomount force

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/inittest12c.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     158662656 bytes

Fixed Size                     2226456 bytes
Variable Size                104859368 bytes
Database Buffers              46137344 bytes
Redo Buffers                   5439488 bytes

RMAN> set DBID=2968723077

executing command: SET DBID

RMAN>  run {
2> set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/backup/%F';
3> restore spfile from autobackup;
4> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 13-JUN-13
using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151119
channel ORA_DISK_1: AUTOBACKUP found: '/u01/backup/c-2968723077-20151119-01
channel ORA_DISK_1: restoring spfile from AUTOBACKUP '/u01/backup/c-2968723077-20130613-01
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 13-JUN-13





I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!



Friday 20 November 2015

Installing Oracle Multimedia Component on 11g


Installing Oracle Multimedia, which is required for Spatial and/or Locator is quite simple.
All of the following must be carried out while logged in as a SYSDBA user.

1.Pre-Requisite to install Oracle Multimedia component.

Decide which tablespace to use for the Oracle Multimedia users, and which tablespace to use for the Oracle

Spatial/Oracle Multimedia Location Services user. Oracle recommends using the SYSAUX tablespace for all

of these users.

2.Create the users and grant the appropriate privileges, as follows:

Call the script ordinst.sql, with two parameters for the Oracle Multimedia tablespace and the Oracle

Spatial/Oracle Multimedia Location Services tablespace:

On Linux and UNIX:

SQL> @<ORACLE_HOME>/ord/admin/ordinst.sql SYSAUX SYSAUX

On Windows:

SQL> @<ORACLE_HOME>\ord\admin\ordinst.sql SYSAUX SYSAUX

3.Install the Oracle Multimedia types and packages, as follows:

Call the script catim.sql, while you are connected as SYSDBA:

On Linux and UNIX:

SQL> @<ORACLE_HOME>/ord/im/admin/catim.sql

On Windows:

SQL> @<ORACLE_HOME>\ord\im\admin\catim.sql


Now Oracle Multimedia is ready for use.

4.Verifying an Installed Version of Oracle Multimedia

set lines 140
col COMP_NAME format a40
select comp_name, version, status from dba_registry;


COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ -----------
Oracle Database Vault 12.1.0.2.0 VALID
Oracle Application Express 4.2.5.00.08 VALID
Oracle Label Security 12.1.0.2.0 VALID
Spatial 12.1.0.2.0 VALID
Oracle Multimedia 12.1.0.2.0 VALID
Oracle Text 12.1.0.2.0 VALID
Oracle Workspace Manager 12.1.0.2.0 VALID
Oracle XML Database 12.1.0.2.0 VALID
Oracle Database Catalog Views 12.1.0.2.0 VALID
Oracle Database Packages and Types 12.1.0.2.0 VALID
JServer JAVA Virtual Machine 12.1.0.2.0 VALID
Oracle XDK 12.1.0.2.0 VALID
Oracle Database Java Packages 12.1.0.2.0 VALID
OLAP Analytic Workspace 12.1.0.2.0 VALID
Oracle OLAP API 12.1.0.2.0 VALID
Oracle Real Application Clusters 12.1.0.2.0 OPTION OFF

16 rows selected.





I hope this article helped you. Y
our suggestions/feedback are most welcome.

Keep learning... Have a great day!!!



Friday 23 October 2015

How to find the last RMAN backup’s log?

set pagesize 2000
set linesize 2000
select
output
from
GV$RMAN_OUTPUT
where
session_recid =
(
select
session_recid
from
V$RMAN_BACKUP_JOB_DETAILS
where
session_key=(select max(session_key) from v$RMAN_BACKUP_JOB_DETAILS)
);


OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
connected to target database: TEST (DBID=3221280546)
connected to recovery catalog database


allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=2817 devtype=DISK
validation succeeded for archived log
archive log filename=/data/TEST/arch/TEST1_1243_891780004.arc recid=2465 stamp=893720321
validation succeeded for archived log
archive log filename=/backup/data/TEST/arch/TEST1_1243_891780004.arc recid=2466 stamp=893720321
validation succeeded for archived log
archive log filename=/data/TEST/arch/TEST1_1244_891780004.arc recid=2467 stamp=893720321
validation succeeded for archived log
archive log filename=/backup/data/TEST/arch/TEST1_1244_891780004.arc recid=2468 stamp=893720321
validation succeeded for archived log
archive log filename=/data/TEST/arch/TEST1_1245_891780004.arc recid=2469 stamp=893720326
validation succeeded for archived log
archive log filename=/backup/data/TEST/arch/TEST1_1245_891780004.arc recid=2470 stamp=893720326
validation succeeded for archived log
archive log filename=/data/TEST/arch/TEST1_1246_891780004.arc recid=2471 stamp=893720326
validation succeeded for archived log
archive log filename=/backup/data/TEST/arch/TEST1_1246_891780004.arc recid=2472 stamp=893720326
validation succeeded for archived log
archive log filename=/data/TEST/arch/TEST1_1247_891780004.arc recid=2475 stamp=893720329
validation succeeded for archived log
archive log filename=/backup/data/TEST/arch/TEST1_1247_891780004.arc recid=2476 stamp=893720329
validation succeeded for archived log
archive log filename=/data/TEST/arch/TEST1_1248_891780004.arc recid=2473 stamp=893720329
validation succeeded for archived log
archive log filename=/backup/data/TEST/arch/TEST1_1248_891780004.arc recid=2474 stamp=893720329
archive log filename=/backup/data/TEST/arch/TEST1_1251_891780004.arc recid=2482 stamp=893722157
validation succeeded for archived log
archive log filename=/data/TEST/arch/TEST1_1252_891780004.arc recid=2483 stamp=893723957
validation succeeded for archived log
archive log filename=/backup/data/TEST/arch/TEST1_1252_891780004.arc recid=2484 stamp=893723957
validation succeeded for archived log
archive log filename=/data/TEST/arch/TEST1_1253_891780004.arc recid=2485 stamp=893725758
validation succeeded for archived log
archive log filename=/backup/data/TEST/arch/TEST1_1253_891780004.arc recid=2486 stamp=893725758
validation succeeded for archived log
archive log filename=/data/TEST/arch/TEST1_1254_891780004.arc recid=2487 stamp=893727558
validation succeeded for archived log
archive log filename=/backup/data/TEST/arch/TEST1_1254_891780004.arc recid=2488 stamp=893727558
.
.
.
input archive log thread=1 sequence=1305 recid=2589 stamp=893806765
channel CH1: starting piece 1 at 22-OCT-15
channel CH1: finished piece 1 at 22-OCT-15
piece handle=/backup/data/TEST/rman_backup/arch/FULL_TEST_1_5nqkcq6k_1_1 tag=TAG20151022T234001 comment=NONE
channel CH1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-OCT-15
released channel: CH1
released channel: CH2
released channel: CH3



967 rows selected.




I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Wednesday 14 October 2015

oracle script to check the database growth

SET LINESIZE 200
SET PAGESIZE 200
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a11
COL "Used in %" FORMAT a11
COL "Free in %" FORMAT a11
COL "Database Name" FORMAT a13
COL "Free Space" FORMAT a12
COL "Growth DAY" FORMAT a11
COL "Growth WEEK" FORMAT a12
COL "Growth DAY in %" FORMAT a16
COL "Growth WEEK in %" FORMAT a16
SELECT
(select min(creation_time) from v$datafile) "Create Time",
(select name from v$database) "Database Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %"
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;

Output: