How to install SQLdeveloper on Macbook Pro Mojave

1. Download and install Java 11. You can choose SDK or JDK version. I opted for JDK
2. check your java version
   java -version
3. Download latest sqldeveloper from here
4. change to Applications folder
   cd /Applications
5. Copy the dowloaded zip to the Application folder. we will have backup zip file in downloads.
   We will delete the zip file when done.
   cp -p $HOME/Downloads/sqldeveloper-19.2.1.247.2212-macosx.app.zip .
6. unzip sqldeveloper-19.2.1.247.2212-macosx.app.zip .
7. Look for the SQLDevelopers.app file
   ls -alt SQLdeveloper.app
8. Open finder and look for SQLdeveloper.app ( below )
9. 
10. Click SQLDeveloper from launchpad
11. create database connections & voila done!
12. Delete the zip file from /Applications folder
    rm -f sqldeveloper-19.2.1.247.2212-macosx.app.zip

URL references : -
http://blog.mclaughlinsoftware.com/2015/06/12/mac-sql-developer-install/
https://github.com/oracle/oracle-db-examples/tree/master/sqldeveloper/extension/java/DependencyExample
https://www.thatjeffsmith.com/archive/2020/05/visualizing-oracle-object-dependencies-with-sql-developer/
https://www.igorkromin.net/index.php/2016/01/23/how-to-redirect-sqldeveloper-debug-log-to-a-file/?sp=1

LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

Problem:- While running opatch lsinventory, command fails with the below error:

Oracle Interim Patch Installer version 11.2.0.3.6

Copyright (c) 2013, Oracle Corporation.  All rights reserved.

Oracle Home       : /mnt/san01ch/vd001_v001/u01/app/oracle/product/112040/db_1

Central Inventory : /u01/app/oraInventory

   from           : /mnt/san01ch/vd001_v001/u01/app/oracle/product/112040/db_1/oraInst.loc

OPatch version    : 11.2.0.3.6

OUI version       : 11.2.0.4.0

Log file location : /mnt/san01ch/vd001_v001/u01/app/oracle/product/112040/db_1/cfgtoollogs/opatch/opatch2018-01-24_01-10-13AM_1.log

List of Homes on this system:

Inventory load failed... OPatch cannot load inventory for the given Oracle Home.

Possible causes are:

   Oracle Home dir. path does not exist in Central Inventory

   Oracle Home is a symbolic link

   Oracle Home inventory is corrupted

LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73

Solution:- 

cd $ORACLE_HOME/oui/bin

./attachHome.sh

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 21183 MB    Passed

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /mnt/san01ch/vd001_v001/u01/app/oraInventory

'AttachHome' was successful.

[oracle@amo02ch:/mnt/san01ch/vd001_v001/u01/app/oracle/product/112040/db_1/oui/bin ORA2CH] $ opatch lsinventory 

Oracle Interim Patch Installer version 11.2.0.3.6

Copyright (c) 2013, Oracle Corporation.  All rights reserved.

Oracle Home       : /mnt/san01ch/vd001_v001/u01/app/oracle/product/112040/db_1

Central Inventory : /mnt/san01ch/vd001_v001/u01/app/oraInventory

   from           : /mnt/san01ch/vd001_v001/u01/app/oracle/product/112040/db_1/oraInst.loc

OPatch version    : 11.2.0.3.6

OUI version       : 11.2.0.4.0

Log file location : /mnt/san01ch/vd001_v001/u01/app/oracle/product/112040/db_1/cfgtoollogs/opatch/opatch2018-01-24_01-12-40AM_1.log

Lsinventory Output file location : /mnt/san01ch/vd001_v001/u01/app/oracle/product/112040/db_1/cfgtoollogs/opatch/lsinv/lsinventory2018-01-24_01-12-40AM.txt

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

Installed Top-level Products (1): 

Oracle Database 11g                                                  11.2.0.4.0

There are 1 product(s) installed in this Oracle Home.

Interim patches (1) :

Patch  18522509     : applied on Wed Mar 11 16:34:12 PDT 2015

Unique Patch ID:  17604597

Patch description:  "Database Patch Set Update : 11.2.0.4.3 (18522509)"

   Created on 30 Jun 2014, 08:14:42 hrs PST8PDT

Sub-patch  18031668; "Database Patch Set Update : 11.2.0.4.2 (18031668)"

Sub-patch  17478514; "Database Patch Set Update : 11.2.0.4.1 (17478514)"

   Bugs fixed:

     17752995, 17288409, 16392068, 17205719, 17811429, 17767676, 17614227

     17040764, 17381384, 17754782, 17726838, 13364795, 17311728, 17389192

     17006570, 17612828, 17284817, 17441661, 13853126, 17721717, 13645875

     18203837, 17390431, 16542886, 16992075, 16043574, 17446237, 16863422

     14565184, 17071721, 17610798, 17468141, 17786518, 17375354, 17397545

     18203838, 16956380, 17478145, 16360112, 17235750, 17394950, 13866822

     17478514, 17027426, 12905058, 14338435, 16268425, 13944971, 18247991

     14458214, 16929165, 17265217, 13498382, 17786278, 17227277, 17546973

     14054676, 17088068, 16314254, 17016369, 14602788, 17443671, 16228604

     16837842, 17332800, 17393683, 13951456, 16315398, 18744139, 17186905

     16850630, 17437634, 19049453, 17883081, 15861775, 17296856, 18277454

     16399083, 16855292, 18018515, 10136473, 16472716, 17050888, 17865671

     17325413, 14010183, 18554871, 17080436, 16613964, 17761775, 16721594

     17588480, 17551709, 17344412, 18681862, 15979965, 13609098, 18139690

     17501491, 17239687, 17752121, 17602269, 18203835, 17297939, 17313525

     16731148, 17811456, 14133975, 17600719, 17385178, 17571306, 16450169

     17655634, 18094246, 17892268, 17165204, 17011832, 17648596, 16785708

     17477958, 16180763, 16220077, 17465741, 17174582, 18522509, 16069901

     16285691, 17323222, 18180390, 17393915, 16875449, 18096714, 17238511

     17596908, 17811438, 17811447, 18031668, 16912439, 18061914, 17622427

     17545847, 16943711, 17082359, 17346671, 18996843, 14852021, 17783588

     16618694, 17672719, 17614134, 17341326, 17546761, 17716305

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

OPatch succeeded.

Database Design thoughts : A personal non-academic perspective.

The benefits of any database model, be it relational or non-relational is

1. Data storage is efficient.
2. Data retrival is efficient.
3. Reporting is efficient
4. Data transformation into Information is easy and self documenting the business / process. 

The first 3 benefits get a lot of focus while designing a database. In today's scenarios, implementations to achieve these benefits , are mainly managed by IT departments. The 4th benefit remains unspoken &  expected to be outcome of the first three.

4th benefit requires equal , if not more focus during the design phase. It should be the starting point of designing. Product managers and DBAs need to sit down and dream a little bit about current and future scenarios. After they have a rough draft of things to come, security & legal teams' input comes next in line. There are many academic papers and thoughts. 

         However after seeing difficult and messy designing of databases, The designing considerations boil down to :-

1. What legal rules apply to the data being stored.
2. What is data entry point.
3. What is data exit point.
4. At what stage or condition should the data be removed completely from the database.
5. How to categorize data segments for security and privacy.
6. What personnel and roles should have direct access to data.
7. What will be data policy of the company and who will define the policy.

Each of the above , including legal, requires workflow with clear cut process in place. 

These aspects of designing used to be DBA's responsibility but in changing times when the role of DBA's is diminishing to implementing data storage, retrieval and reporting efficiency , Product managers and Security teams' involvement increases considerably.

Implementation of what comes out of the 4th Benefit falls on the shoulders of IT departments.  IT departments are at their best when it comes to choice of correct technology , machines and storage. They would also be able to analyze what type of database technology is best for the organization. Database technologies dictate datatypes, tables, columns, rows, roles, privileges and so on. Modularly designed database are low-maintenance. They also increase efficiency of developers who create in-house applications. Examples of modularity are:-

1. Authentication module
2. Authorization module
3. Sales module 
4. Marketing module
5. Billing module 
6. Decision making module (data warehouse module)
7. and so on...

another module 'Link module' would be needed to to implement efficient storage and retrieval of data.  DFD should be also created and available as and when needed.

Preventive tasks : Avoid errors & issues while installing oracle 11gR2 on Red Hat Enterprise Linux 7 server(x86-64):-

Error/Issue No:-1 Packages "elfutils-libelf-devel-0.97" And "pdksh-5.2.14" Are Missing (PRVF-7532) (Doc ID 1454982.1)

Workaround:-
Once the software is copied/extracted under  <path>/database, do the following:
1	Change directory to <path>/database/stage/cvu/cv/admin
2	Backup cvu_config
	% cp cvu_config backup_cvu_config
3	Edit cvu_config and change the following line:
	CV_ASSUME_DISTID=OEL4 TO CV_ASSUME_DISTID=OEL6
4	Save the updated cvu_config file
5	Install the 11.2.0.3 or 11.2.0.4 software using <path>/database/runInstaller
	cd <path>/database
6	./runInstaller

Error/Issue No:-2 error in invoking target 'agent nmhs' of make file ins_emagent.mk while installing Oracle 11.2.0.4 on Linux (Doc ID 2299494.1)

workaround:-
1	vi $ORACLE_HOME/sysman/lib/ins_emagent.mk
2	search for the line
	$(MK_EMAGENT_NMECTL)
3	Then replace the line with
	$(MK_EMAGENT_NMECTL) -lnnz11 and save the file
4	Then click “Retry” button to continue.

Oracle and BASH : How to calculate how many minutes have elapsed between two timestamps

There are many ways to calculate minutes passed between two timestamps. The script below is to calculate how many minutes passed between two time stamps using oracle database.

Script : test.sh
====================
#!/bin/bash

# Create a function to accept two timestamps. The order of the timestapms (FROM_TIME, TO_TIME) does not affect the result.
fn_howmany_mins()
{
FROM_TIME=$1
TO_TIME=$2
MINS=`sqlplus -S "/ as sysdba " <<EOF
set echo off
set feedback off
set linesize 10;
set pagesize 0
set trimspool on
set space 0
set truncate on
select abs(round((to_date('$TO_TIME','YYYYMMDD_HH24MISS')-to_date('$FROM_TIME','YYYYMMDD_HH24MISS'))*1440)) from dual;
exit;
EOF`
MINS=`echo $MINS`
echo $MINS
}

#SET up test time stapms as below.
FROM_TIME=20150811_110601
TO_TIME=`date +"%Y%m%d_%H%M%S"`
echo "FROM_TIME=$FROM_TIME TO_TIME=$TO_TIME"

#Call the function to calculate the minutes.
fn_howmany_mins $FROM_TIME $TO_TIME

Sample output sh ./test.sh

==================

FROM_TIME=20150811_111458 TO_TIME=20150811_110601

9

In an actual usage remove the echo statement to get pure number . e.g. number 9 above

Bash : How to get last character in a string

Last character in a string can be extracted using sed. example:-

$cat test.sh

#!/bin/bash

STRING="Humpty Dumpty"

STRING=`echo "$STRING"|sed -e 's/\(^.*\)\(.$\)/\2/'`

echo "STRING=$STRING"

exit 0

Output:-

STRING=y

How to send select rows into a while loop (bash)

Sample File:-  test.txt

2015-08-01  WHY=Beacuse of testing

DATE=2015-08-01  Beacuse of testing

DATE=2015-08-01  WHY=Beacuse of testing

2015-08-01  Beacuse of testing

Sample script:script.sh

head -3 test.txt|while read LINE

do

echo  "LINE=$LINE"

STR1=`echo $LINE|awk '{print $1}'`

STR2=`echo $LINE|awk '{first=$1; $1=""; print $0}'`

VALU1=`echo $STR1|awk -F\= '{print $2}'`

VALU1="${VALU1:-$STR1}"

LABL1=`echo $STR1|awk -F\= '{print $1}'` 

if [ "$VALU1" == "$STR1" ]

   then

   LABL1=''

   export STR1

fi

VALU2=`echo $STR2|awk -F\= '{first=$1; $1="";print $0}'`

VALU2="${VALU2:-$STR2}"

LABL2=`echo $STR2|awk -F\= '{print $1}'`

if [ "$VALU2" == "$STR2" ]

   then

   LABL2=''

   export STR1

fi

echo "STR1=$STR1 "

echo "LABL1=$LABL1"

echo "VALU1=$VALU1"

echo -e "\t\tSTR2=$STR2 "

echo -e "\t\tLABL2=$LABL2"

echo -e "\t\tVALU2=$VALU2"

echo -e "\n\n"

done

Sample Output:-

LINE=2015-08-01  WHY=Beacuse of testing

STR1=2015-08-01 

LABL1=

VALU1=2015-08-01

STR2= WHY=Beacuse of testing 

LABL2=WHY

VALU2= Beacuse of testing

LINE=DATE=2015-08-01  Beacuse of testing

STR1=DATE=2015-08-01 

LABL1=DATE

VALU1=2015-08-01

STR2= Beacuse of testing 

LABL2=

VALU2= Beacuse of testing

LINE=DATE=2015-08-01  WHY=Beacuse of testing

STR1=DATE=2015-08-01 

LABL1=DATE

VALU1=2015-08-01

STR2= WHY=Beacuse of testing 

LABL2=WHY

VALU2= Beacuse of testing

Blogs & URL to Reference

http://www.shutdownabort.com/

Oracle : Installations' URLs

10gR2 Installation : installation of Oracle Database 10g Release 2 (10.2.0.1) on Red Hat Enterprise Linux 5 (RHEL5).
The link is from "oracle-base". The site is very practicle and conscise; especially useful when a DBA wants to refer or refresh something "real quick" without going into concepts & theory surrounding implementation.
http://www.oracle-base.com/articles/10g/OracleDB10gR2InstallationOnRHEL5.php

Below is a post about switching from primary database to standby database. It a step by step instruction set.
Before begining to follow the instruction switch the logs

step 0) alter system switch logfile;
http://www.visi.com/~mseberg/Data_Guard_switchover.html

Oracle : Types of Locks

The locks are classified in many ways. The most common classifications are as below:-
Classification based on functionality

1. DML locks (data locks)
2. DDL locks (dictionary locks) 
3. Oracle Internal Locks/Latches
4. Oracle Distributed Locks
5. Oracle Parallel Cache Management Locks

...to be continued

Oracle Latches :Protect data structures in SGA. These are serialization mechanisms.

1. Latches prevent more than one process from executing the same  piece of code at the same time
2. Latch has a cleanup process associated with it.
3. Cleanup process of a latch kicks in if a process dies while holding a latch
4. Levels are associated with latches.
5. Once a process acquires a latch at a certain level, it can only acquire higher level latches.


......to be continued

Oracle (From Metalink) : Summary Of Bugs Which Could Cause Deadlock

Summary Of Bugs Which Could Cause Deadlock [Metalink Note : ID 554616.1]
--------------------------------------------------------------------------------
This summary is from metalink https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=REFERENCE&id=554616.1 .

Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.4 to 11.1.0.6
Information in this document applies to any platform.

Purpose
The purpose of this Note is to explain various bugs filed specifically for the Dead lock errors against specific Oracle database versions (This Note covers bugs reported versions  above 9.2.0.4), and explain the symptoms of each bug, workarounds if any and references the patch availability at the time this article was written.

Bugs Fixed in Version  9.2.0.5
==============================
Note 2796282.8  Bug 2796282    False deadlock possible using shared servers
Note 3001270.8  Bug 3001270    Deadlock between SMON and foreground process for dc_suers 
Note 3030298.8 Bug 3030298    OERI:2103 from concurrent 'drop tablespace including datafiles
Note 3080929.8 Bug 3080929    ORA-4021 hang  SMON self deadlock  UNDO$ row cache lock
Note 3093080.8 Bug 3093080    ALTER TABLE ENABLE TABLE LOCK can cause a deadlock
Note 3271271.8 Bug 3271271    QMON can deadlock with job queue processes
Note 3009268.8 Bug 3009268    Recovery of DEAD prepared TX may deadlock with SMON
Note 2995746.8 Bug 2995746   Deadlock between session doing a GRANT 
Note 2918838.8 Bug 2918838   Undetected deadlock for dc_tablespace_quotas
Bugs Fixed in Version 9.2.0.6
=============================
Note 3398485.8 Bug.3398485   Deadlock during on demand materialized view refresh (ORA-4020)
Note 2615271.8 Bug.2615271   Deadlock from concurrent GRANT and logon
Note 2014833.8 Bug.2014833   Deadlock possible from concurrent SELECT and TRUNCATE
Note 3320292.8 Bug.3320292   Parallel recompilation hangs when recompiling type generated for pipeline function
Note 3424721.8 Bug 3424721   deadlock ALTER INDEX REBUILD on partition with concurrent SQL
Note.3166756.8 Bug.3166756   Self deadlock (ORA-60) / OERI possible on LOB index update
Note 3605165.8 Bug.3605165   Hang/deadlock between sessions concurrently loading a cursor with INVALID trigger
Note 3717619.8 Bug.3717619   Deadlock/hang possible due to concurrent cursor loads referencing same INVALID trigger
Note 3562032.8 Bug.3562032   Cancelled ONLINE index rebuild can deadlock with DML session
Note 3381218.8 Bug.3381218   Deadlock involving 'library cache lock' X mode request
Bugs Fixed in Version 9.2.0.7
=============================
Note 3314850.8 Bug.3314850     Can deadlock with query rewrite sessions
Note 3261205.8 Bug.3261205     Hang / OERI[kxttdropobj-1] on parallel direct load to temporary table
Note 2883771.8 Bug.2883771    "WAITED TOO LONG FOR ROWCACHE ENQUEUE" when using Resource Manager in PLSQL
Note 3896974.8 Bug.3896974     creating DIMENSIONs from schemas simultaneously
Bugs Fixed in 9.2.0.8 10.1.0.5
==============================
Note 4114238.8  Bug 4114238  Deadlock between dc_users and dc_usernames row cache lock enabling FK
Note 4416907.8 Bug 4416907   ORA-4020 DO_DEFERRED_REPCAT_ADMIN  concurrent SQL
Note 4329748.8 Bug 4329748   ORA-4020 / deadlock quiescing a replication group
Note 4029101.8 Bug 4029101  Concurrent CREATE TABLE / VIEW can deadlock (ORA-60)
Note 4275733.8 Bug 4275733  Deadlock between library cache lock and row cache lock from concurrent rename partition
Note 4313246.8  Bug 4313246  PLSQL execution can hold dc_users row cache lock leading to hang / deadlocks
Note 4185270.8 Bug.4185270   PMON "failed to acquire row cache enqueue" cleaning a dead process
Note 4446011.8 Bug.4446011   Hang with row cache lock deadlock from concurrent ALTER USER / TRUNCATE
Note 3987280.8 Bug.3987280    Concurrent GRANT / SET ROLE can hang / deadlock
Bugs Fixed in 10.1.0.4 and 10.2.0.1
===================================
Note 3540821.8 Bug.3540821  ORA-4020 deadlock from concurrent ANALYZE index / query compilation against a cluster
Note 3990235.8 Bug.3990235  Deadlock in disk drop and instance recovery in ASM
Note 3975268.8 Bug.3975268  Deadlock possible after gathering statistics for certain SYS objects
Note 3756949.8 Bug.3756949  Sequences can deadlock on space allocation failure
Note 4137000.8 Bug.4137000  Concurrent SPLIT PARTITION can deadlock / hang
Note 4008775.8 Bug.4008775  Self deadlock calling DBMS_SPACE / DBMS_STATS in same user call
Bugs Fixed in  10.2.0.2
=======================
Note 4153150.8 Bug.4153150   Deadlock on dc_rollback_segments from concurrent parallel load and undo segment creation
Note 4382653.8 Bug.4382653   Deadlock / ORA-4020 gathering statistics on indices
Note 4375798.8 Bug.4375798   ORA-60 deadlock from AQ enqueue
Note 4552067.8 Bug.4552067   Deadlock / ORA-4020 using TRUNCATE SQL against global temporary tables
Bugs Fixed in 10.2.0.3
======================
Note 4627237.8 Bug.4627237  autonomous_transaction with DB link to shared server can self deadlock on DX
Note 4732503.8 Bug.4732503  Self-deadlock on TT enqueue

Oracle : report roles assigned to users (SQL)

select
  username,
  default_tablespace    dts,
  temporary_tablespace  tts,
  profile prof,
  granted_role || ' ' ||
  decode(admin_option,'YES','- A',' ') ||
  decode(granted_role,'YES','- G',' ') role
from
  dba_users,
  dba_role_privs
where
  dba_users.username = dba_role_privs.grantee and
  username not in ('PUBLIC')
order by
  1,2,3,4;

Linux : 1-liners needed by a DBA

1. Convert variable data from upper/lowercase to lower/uppercase:

   STRING=ExaMple
   echo $STRING | tr '[:lower:]' '[:upper:]'  ==> EXAMPLE
   echo $STRING | tr '[:upper:]' '[:lower:]'  ==> example

   Another example (shell script : test.sh)  :-
   #!/bin/bash
   ############################################################
   #      Example : Convert case of a string                  #
   ############################################################
   HOSTNAME=`hostname|awk -F\. '{print $1}'|tr '[:lower:]' '[:upper:]'`
   echo "Upper case HOSTNAME : ${HOSTNAME}"
   HOSTNAME=`hostname|awk -F\. '{print $1}'|tr '[:upper:]' '[:lower:]'`
   echo "Lower case HOSTNAME : ${HOSTNAME}"

2.  Top 10 memory consuming processes
     ps uax --sort=-rss|head -10

3.  Top 10 CPU intensive processes
     ps uax --sort=-pcpu|head -10

Oracle : one liners needed by a DBA

Below is the reposirory of 1-liners (commands and some formatting) that we keep requiring most of the the time. I'll try to keep growing this repository

1. List datafile belonging to a tablespace
    set linesize 150
    set pagesize 200
    set echo off
    set verify off
    col file_name format a80
    select file_name , bytes/1024/1024 size_in_mb from dba_data_files where tablespace_name like upper('&1');

2. List all the data files.
    select name from v$datafile;

3. Versions:
     select * from v$version;
     select * from v$database;
     select * from product_component_version;
     select version from v$instance;
     select * from sys.gv_$version;
     select * from sys.product_component_version;
     select * from sys.sm_$version;
     select * from sys.gv_$instance;

4.  unzip Opatch to ORACLE_HOME
     unzip p6880880_112000_Linux-x86-64.zip -d $ORACLE_HOME
5.  Opatch Prereq Check
     opatch prereq CheckConflictAgainstOHWithDetail -ph ./

6. Apply patch

    opatch apply

Redhat : How to add swap space

I needed to temporarily increase the swap space by 16GB. Simplest and the quickest method is to add a swap file for the duration and then remove it after the task is over.

First of all identify a large enough partition on the internal disk and then go about the task as below..

1. Locate the partition where the file can be created, say /u06/swap
2. make a directory : /u06/swap
3. create the swap file using the command "dd" : dd if=/dev/zero of=/u06/swap/swapfile bs=1024 count=16384000
4. make the file as swapfile : mkswap /u06/swap/swapfile
5. To make the file available after reboot , add it to the /etc/fstab file: /u06/swap/swapfile  swap  swap  defaults  0 0
6. Activate the swap space : swapon -a /tmp/swapfile
7. check the swap space : swapon -s

I followed redhat documentation , but the link below explains methods precisely the way I created the swap space. The link also shows how to drop the swap space.

http://www.technofunction.com/2010/08/adding-swap-space-in-redhatfedora-linux-manually-using-file-and-partition/


How to move swapfile from one location to another

1. Locate the file where the created swapfile exists

2. Turnoff swap from swapfile

   swapoff /mnt/dallas_prod_old/swapfile_dir/swapfile

3. 

Oracle : tablespace used , ordered by %used_space (SQL)

A simple script to see permanent / temporary tablespace usage in Oracle
=================================================
set linesize 250
set pagesize 200
set echo off
select b.tablespace_name,b.total_space_in_mb,(b.total_space_in_mb - nvl(a.free_space_in_mb,0)) used_space_in_mb,
nvl(a.free_space_in_mb,0) free_space_MB, round(((b.total_space_in_mb - nvl(a.free_space_in_mb,0)) / b.total_space_in_mb) * 100,2) as "%_USED_SPACE"
from
(select sum(bytes)/1024/1024 free_space_in_mb, tablespace_name from dba_free_space group by tablespace_name) a,
(select sum(bytes)/1024/1024 total_space_in_mb,tablespace_name from dba_data_files group by tablespace_name) b
where a.tablespace_name(+) = b.tablespace_name order by "%_USED_SPACE" desc
;
select b.tablespace_name,b.total_space_in_mb,(b.total_space_in_mb - nvl(a.free_space_in_mb,0)) used_space_in_mb,
nvl(a.free_space_in_mb,0) free_space_MB, round(((b.total_space_in_mb - nvl(a.free_space_in_mb,0)) / b.total_space_in_mb) * 100,2)
as "%_USED_SPACE" from
(select sum(bytes_free)/1024/1024 free_space_in_mb, tablespace_name from v$temp_space_header group by tablespace_name) a,
(select sum(bytes)/1024/1024 total_space_in_mb,tablespace_name from dba_temp_files group by tablespace_name) b
where a.tablespace_name(+) = b.tablespace_name order by "%_USED_SPACE" desc ;