Yes. Use the srvctl relocate scan command with the ordinal number of the SCAN IP and the target node name:

srvctl relocate scan -i <ordinal_number> -n <node_name>
# Example: relocate SCAN IP #1 to node1
srvctl relocate scan -i 1 -n node1

# View help
srvctl relocate scan -h

The -i flag specifies which of the three SCAN IPs to move (1, 2, or 3). The -n flag is optional — omit it to let CRS pick the target node automatically.

Yes. Each instance’s LREG process registers database services with both the local listener and all SCAN listeners (specified via the REMOTE_LISTENER parameter). When a client connects to a SCAN listener, that listener identifies the least-loaded node and redirects the client to the local listener on that node — the actual session is then established by the local listener.

DNS resolves the SCAN name to up to three IP addresses. The client tries them in sequence; if one fails, it automatically tries the next — providing built-in connection failover as well as load balancing.

No. Oracle recommends the SCAN resolve to three VIP addresses for high availability and scalability — and three SCAN VIPs means three SCAN listeners. The number three is the standard; it is not chosen because it is odd.

If three SCAN listeners are genuinely insufficient for your connection volume, you can add more — but in GNS-based dynamic IP environments, changing the SCAN listener count is not straightforward.

The Oracle Listener is a server-side network process that accepts incoming client connection requests and routes them to the appropriate database instance. It reads its configuration from listener.ora and matches incoming requests against registered services or static SID definitions.

Without the listener, clients cannot initiate new database sessions. However, once a session is established, the listener is no longer involved — existing connections persist even if the listener is stopped.

LOCAL_LISTENER points each instance to the listener running on its own node. The instance registers its services directly with this listener for local connections.

REMOTE_LISTENER points to listeners on other nodes — typically the SCAN listeners in a RAC environment. This enables server-side load balancing: when a client connects via a SCAN listener, that listener uses workload information from all instances to redirect the connection to the least-loaded node.

Static registration is manually defined in listener.ora using a SID_LIST_LISTENER entry. The listener knows about the instance even if it is not running. Required for connecting to instances in NOMOUNT or MOUNT state (e.g. during startup or RMAN operations).

Dynamic registration is handled automatically by the LREG process (Oracle 12c+) or PMON (earlier releases). The instance registers itself — along with its services and load — when it starts. No manual listener.ora changes are needed. This is the recommended approach for normal operations.

TNS Listener Poisoning is a man-in-the-middle attack first documented in 2015. An attacker exploits dynamic registration to inject a rogue service entry into the listener, hijacking legitimate client sessions and silently routing them to an attacker-controlled server.

Common mitigations include:

• Disable dynamic registration: set DYNAMIC_REGISTRATION_LISTENER=OFF in listener.ora. Note: not feasible if using RAC, Data Guard, or APEX.
• Class of Secure Transport (COST): restricts instance registration to local or SSL-authenticated sources (MOS Notes 1453883.1 and 1340831.1).
• Valid node checking: set TCP.VALIDNODE_CHECKING=YES with an explicit IP allowlist in sqlnet.ora.
• Network segmentation: restrict listener access at the firewall or network layer.

Use lsnrctl to set an admin password:

lsnrctl
LSNRCTL> change_password
Old password:
New password:
Reenter new password:
LSNRCTL> save_config

When a firewall sits between clients and the database server, ensure the following ports are open:

• Listener port (default 1521, or your custom port) — for initial connection handoff.
• The port range used for dedicated server connections if the firewall does not support stateful inspection of TNS traffic.

In addition, configure SQLNET.EXPIRE_TIME in sqlnet.ora to send keepalive probes — this prevents the firewall from silently dropping idle connections that appear dead to the firewall but are still active at the database level.

# sqlnet.ora
SQLNET.EXPIRE_TIME = 10   # in minutes

Yes. Each node runs its own local listener bound to that node’s public VIP. Separately, there are three SCAN listeners that can float across any nodes in the cluster — CRS places them automatically.

On a two-node cluster, one node might host one SCAN listener and the other hosts two. On a four-node cluster, the three SCAN listeners may all reside on two of the four nodes. Their placement is dynamic and managed by CRS for HA.

Each instance’s LREG process continuously pushes workload metrics to all listeners — including SCAN listeners. This information includes service names, instance names, and current load indicators such as active sessions and CPU utilisation.

When a new connection request arrives at a SCAN listener, the listener consults the most recent workload data and redirects the client to the local listener on whichever node hosts the least-loaded instance for the requested service.

No. Disabling the firewall is not required — and is not recommended. The correct approach is to open only the specific ports the listener uses (typically 1521) and configure the database to work within the firewall’s constraints. See Q9 for the full configuration guidance.

Yes. Dynamic registration is the attack vector exploited by the TNS Poison attack (Q7). Because any process on the network can attempt to register a service with the listener, an attacker could inject a rogue service entry. This can be mitigated using COST, valid node checking, or disabling dynamic registration where architecturally feasible.

There is no universal best practice, but the considerations are:

Default port 1521: Easier to configure; well-known so tools and drivers connect without extra config. However, it is a well-known attack target, so automated scanners will probe it.

Non-default port: Provides security through obscurity — reduces noise from automated scans. However, all clients, monitoring tools, and connection strings must be updated, increasing operational complexity.

Most security standards consider port changes a minor hardening step, not a substitute for proper authentication, COST, and network controls.

Query the listener log or use lsnrctl status and lsnrctl services for a snapshot. For historical statistics, parse the listener log file:

# Count refused connections in the listener log
grep "TNS-" $ORACLE_BASE/diag/tnslsnr/$(hostname)/listener/trace/listener.log | wc -l

# View live listener stats
lsnrctl status listener

From Oracle 11g onward, the listener log is in XML format under the ADR (Automatic Diagnostic Repository) and can be queried with ADRCI for more structured reporting.

A large listener log file can degrade listener performance, as Oracle appends to it for every connection attempt. In extreme cases it can cause connection timeouts. Management steps:

• Rotate the log by temporarily disabling logging, renaming the file, then re-enabling: lsnrctl set log_status off, rename the file, then lsnrctl set log_status on.
• In 11g+, the ADR automatically manages log segmentation. Use adrci to purge old segments.
• Schedule regular log rotation via a cron job or operating system log management tool.

PMON (Process Monitor) is responsible for cleaning up failed processes and registering the instance with the listener. It starts early in the startup sequence so it can contact the listener as soon as the instance reaches NOMOUNT — making the instance available for privileged connections immediately. In Oracle 12c+, the dedicated LREG process handles registration, but it still starts very early for the same reason.

Three SCAN IPs — regardless of the number of nodes. Oracle’s recommendation is always three SCAN VIPs for any size cluster. SCAN is designed to scale: a small number of SCAN listeners can handle hundreds of new connections per second across a large cluster. Adding more than three is only warranted in exceptionally high-concurrency environments.

RATE_LIMIT caps the number of new connections the listener accepts per second. When the limit is reached, additional connection attempts are queued or rejected.

Advantages: Protects against denial-of-service connection floods; limits damage from application bugs that open connections in a tight loop (“logon storms”).

Disadvantages: Legitimate traffic spikes — such as application server restarts where many connections are initiated simultaneously — will be throttled, causing elevated client connect times and slow TNS ping response.

RATE_LIMIT is most useful in environments where uncontrolled connection surges are a known risk — for example, large application server farms, public-facing APIs connecting to Oracle, or systems prone to connection storm bugs.

When to use: High-traffic production systems; databases that have experienced logon storms or suspected DoS activity.

Trade-offs: Any cap that is too low will impede legitimate bursts (cold starts, failovers). Tuning requires baseline measurement of your peak legitimate connection rate. Start conservatively and adjust upward based on observed patterns.

LREG (Listener Registration, introduced in Oracle 12c, replacing PMON for this task) automatically registers the database instance — its services, dispatcher processes, and current workload — with all configured listeners. Key benefits:

• No manual listener.ora SID_LIST entries required for normal operation.
• Workload metrics are continuously updated, enabling accurate server-side load balancing.
• If the listener is not yet running at startup, LREG retries periodically until registration succeeds.
• Services created dynamically (e.g. via DBMS_SERVICE) are registered automatically without listener restart.

No. Node eviction is driven exclusively by cluster heartbeat mechanisms — the private interconnect UDP/RDS heartbeat and the voting disk (quorum disk) heartbeat. The listener runs on the public network and has no role in the cluster membership protocol.

A listener failure prevents new connections from being established to instances on that node. Existing connections are unaffected. CRS will attempt to restart a failed listener automatically.

The recommended approach is one listener per Oracle home, each on a different port. This avoids cross-home version compatibility issues and simplifies troubleshooting. Alternatively, a single listener from the highest Oracle home can be used for all instances — but this requires careful validation of TNS compatibility across the homes.

In practice, use a dedicated Grid Infrastructure listener if one is present — it manages listeners for all database homes on the host centrally.

Reload (lsnrctl reload) re-reads listener.ora without stopping the process. Existing sessions are untouched — users notice nothing. Use this for configuration changes like adding static services.

Restart terminates and restarts the listener process. Already-established sessions (which the listener handed off to server processes) are not dropped. However, any connections mid-handshake at the moment of shutdown will fail and must reconnect.

A connection is the physical communication channel between the client and the database server — the network pipe established via TCP/IP.

A session is the logical entity created when a user authenticates against the database. A single connection typically has one session, but in shared server (MTS) configurations a session can exist without a persistent connection — it is suspended between calls and resumed when needed.

Sessions are tracked in V$SESSION; connections in V$PROCESS.

This is typically caused by a firewall or network ACL blocking port 1521 (or the SCAN listener port) on Node 2, or the instances not having Node 2’s SCAN VIP in their REMOTE_LISTENER parameter.

Troubleshooting steps:

• Verify REMOTE_LISTENER is set to the SCAN name, not hardcoded IPs: SHOW PARAMETER REMOTE_LISTENER.
• Confirm DNS resolves the SCAN name to all three IPs from all nodes.
• Check firewall rules on Node 2 are identical to other nodes.
• Run lsnrctl status on Node 2 to confirm the SCAN listener is running and accepting registrations.
• Force re-registration from each instance: ALTER SYSTEM REGISTER;

COST is an Oracle security feature that restricts which network transports an instance can use to register with a listener. By configuring COST, you can require that dynamic registration only occurs over trusted transport types — such as IPC (local system) or TCPS (SSL/TLS) — blocking registration attempts from remote, unauthenticated sources.

This is the primary recommended mitigation for the TNS Poison attack in environments where disabling dynamic registration entirely is not feasible (RAC, Data Guard). Configured via VALID_NODE_CHECKING_REGISTRATION_LISTENER or COST parameters in sqlnet.ora.

Standalone: Register the listener with Oracle Restart using srvctl add listener. Oracle Restart (part of the 11g+ Grid Infrastructure for single-instance) will start the listener automatically after a system reboot without any OS-level init scripts.

RAC: CRS (Cluster Ready Services) owns the complete lifecycle of all Grid Infrastructure resources, including listeners. Manual startup scripts (dbstart, dbshut) are not supported for CRS-managed components from 11.2 onward. CRS handles automatic restart on node reboot. (Ref: MOS Notes 105957.1 and 1397813.1)

Three primary methods:

• Manual editing of listener.ora: Direct text file configuration. Most flexible; required for advanced static registration.
• Oracle Net Manager (netmgr): GUI tool for creating and modifying listener configuration.
• Oracle Net Configuration Assistant (netca): Wizard-based tool for initial setup and guided configuration changes.

For RAC and Grid Infrastructure environments, srvctl commands are the preferred method as they update CRS resources alongside the configuration files.

In Oracle 12c+, PDBs are registered with the listener automatically via dynamic registration — no special listener configuration is needed. The CDB’s LREG process registers each PDB as a separate service. Clients connect using the PDB’s service name:

# tnsnames.ora entry for a PDB
MYPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = mypdb.mydomain.com)
    )
  )

You can also create dedicated services per PDB using DBMS_SERVICE or srvctl for more granular control and to support connection pool segregation.

Several options are available:

• lsnrctl via SSH to the target host — the most common approach for DBAs.
• Oracle Enterprise Manager (OEM / Cloud Control) — provides a centralized web-based interface to manage listeners across all monitored targets.
• srvctl — for listeners managed by Grid Infrastructure, srvctl commands can be run from any node with Grid home access.

A layered approach is recommended:

• IP allowlisting: TCP.VALIDNODE_CHECKING=YES with TCP.INVITED_NODES in sqlnet.ora.
• Connection timeout: Set INBOUND_CONNECT_TIMEOUT_LISTENER in listener.ora to drop clients that connect but do not send a valid TNS packet promptly.
• Class of Secure Transport (COST): Restrict dynamic registration to trusted transport types.
• Network encryption: Use Oracle Native Network Encryption (NNE) or TLS/TCPS to protect data in transit.
• Non-default port: Reduces automated scanner noise (minor hardening).

A BLOCKED service handler means the instance is in NOMOUNT, MOUNT, or RESTRICTED mode. The listener blocks non-privileged connection attempts to protect the database during startup or maintenance.

To connect to a BLOCKED instance (e.g. for DBA work during startup), append (UR=A) to the connect descriptor in tnsnames.ora:

MYDB_STARTUP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL=TCP)(HOST=myhost)(PORT=1521))
    (CONNECT_DATA =
      (SERVICE_NAME = mydb)
      (UR = A)
    )
  )

Use SERVICE_NAME rather than SID with the (UR=A) clause for dynamically registered handlers.

To add more SCAN listeners (beyond the default three), you need additional SCAN VIP addresses registered in DNS. The number of SCAN listeners always equals the number of SCAN VIPs.

In non-GNS environments:

# Add a new SCAN address to DNS, then update CRS
srvctl modify scan -scanname mycluster-scan.domain.com
# CRS will detect the additional IPs and create corresponding SCAN VIPs and listeners

In GNS-based environments, the SCAN listener count is managed by GNS and cannot be changed directly.

Dedicated server: The listener receives the connection request, spawns (or hands off to) a dedicated server process for that client, then steps out. The client communicates directly with its dedicated server process for the lifetime of the session.

Shared server (MTS): The listener redirects the client to a dispatcher process. The dispatcher queues requests in the SGA’s request queue. Shared server processes pick up work from the queue, execute it, and place results in a response queue for the dispatcher to return to the client. The listener’s role ends at the redirect; the dispatcher manages the connection thereafter.

In Oracle 12c and later, the LREG (Listener Registration) background process handles all service registration. In earlier releases, PMON performed this function.

At instance startup, LREG polls the local listener (and SCAN listeners via REMOTE_LISTENER). If a listener is running, LREG sends it the instance name, database name, service names, and current workload metrics. LREG continues to send updates periodically (approximately every 60 seconds) to keep the listener’s service data current.

Use srvctl to modify the SCAN listener port after installation:

# Stop the SCAN listener
srvctl stop scan_listener

# Modify the port
srvctl modify scan_listener -p 1522

# Start it again
srvctl start scan_listener

# Verify
srvctl config scan_listener

Also update tnsnames.ora on all clients and any connection pool configurations to reflect the new port.

Yes. SCAN (Single Client Access Name) is a required component of Oracle Grid Infrastructure 11g R2 and later. It provides a stable single hostname for client connections regardless of cluster topology changes — nodes can be added or removed without updating client connection strings. SCAN also enables server-side load balancing and connection failover transparently. You cannot install Oracle Grid Infrastructure 11g R2+ without configuring SCAN.

A tablespace is a logical storage container within an Oracle database that groups related segments together. It maps to one or more physical datafiles on disk. Every database object (table, index, LOB, etc.) resides in exactly one tablespace.

This abstraction layer allows DBAs to manage storage at a meaningful level — setting quotas, taking tablespaces offline, or moving them — without directly manipulating OS files.

A materialized view pre-computes and physically stores query results — aggregations, joins, transformations — so repeated reporting queries read the cached result rather than re-scanning large base tables. This dramatically reduces execution time for complex analytical queries.

Unlike a regular table, a materialized view can be configured to refresh automatically (on commit, on a schedule, or on demand), keeping the cached data reasonably current. Oracle’s query rewrite feature can also transparently redirect queries against base tables to an equivalent materialized view, accelerating ad hoc workloads without any query changes.

ALTER TABLESPACE undo_ts RETENTION GUARANTEE;

With GUARANTEE, Oracle will never overwrite unexpired undo data — even if the tablespace is full. DML statements that require undo space will fail rather than overwriting retained undo. Without this setting, Oracle may recycle undo space prematurely under pressure, causing ORA-01555 (snapshot too old) errors on long-running queries.

Size your undo tablespace appropriately before enabling GUARANTEE to avoid blocking legitimate transactions.

Temporary tablespaces hold transient data generated during SQL execution that cannot fit in the PGA — primarily sort results, hash join partitions, and bitmap merge operations. They also store global temporary table data. No redo is generated for temp writes, making operations faster.

Sizing guidance: Monitor V$TEMPSEG_USAGE during peak load to find the high-water mark. Size the temp tablespace to at least 120% of the observed peak usage. For data warehouses or systems running large analytical queries, several GB to hundreds of GB may be required.

Dictionary Managed Tablespace (DMT): Extent allocation and free-space tracking are stored in SYS data dictionary tables (UET$, FET$). Every extent allocation or deallocation generates recursive SQL, produces undo, and creates contention on shared dictionary structures.

Locally Managed Tablespace (LMT): Extent bitmaps are stored directly in each datafile’s header. Space management requires no dictionary I/O, generates no undo, and requires no coalescing. Oracle recommends LMT for all tablespaces in all modern databases.

DMT is obsolete — it existed before LMT was introduced in Oracle 8i and should not be used in any new deployment.

No. Segment space management (MANUAL vs AUTOMATIC) is fixed at tablespace creation time and cannot be altered. To change it, create a new tablespace with the desired setting and migrate all objects using ALTER TABLE ... MOVE TABLESPACE, DBMS_DATAPUMP, or online redefinition.

Taking a datafile offline with IMMEDIATE forces it offline without a checkpoint — dirty buffers for that file are not written to disk. Consequences:

• The datafile will require media recovery before it can be brought back online.
• This mode is only available in ARCHIVELOG mode (Oracle needs archived logs to recover).
• Any active transactions writing to the affected segments will receive errors immediately.

Use NORMAL offline whenever possible to checkpoint cleanly. IMMEDIATE is a last resort when a disk failure makes normal offline impossible.

Query the data dictionary for last modification timestamps:

SELECT name, to_char(last_time, 'YYYY-MM-DD HH24:MI:SS') AS last_modified
FROM v$datafile
WHERE trunc(last_time) = trunc(SYSDATE)
ORDER BY last_time DESC;

For OS-level verification you can also check file modification timestamps, but the data dictionary view is more reliable as it reflects Oracle’s last checkpoint time rather than OS write time.

Notable 11g tablespace features include:

• Read-only datafiles in RMAN backups: Read-only tablespace datafiles do not need to be backed up repeatedly.
• Tablespace encryption (TDE): Encrypt entire tablespaces transparently without application changes.
• Online datafile move: In 12c this became online; in 11g it was extended to allow some moves without full downtime.
• SYSAUX management improvements: Better monitoring and component management for the SYSAUX tablespace.

A Bigfile Tablespace (BFT) contains exactly one datafile that can be up to 128 TB (with 32 KB blocks). For very large databases, this reduces the number of datafiles from potentially thousands to a manageable count, simplifying backup, recovery, and space management.

Best suited for: Data warehouse fact tables, archive tablespaces, and any very large single-segment object.

Considerations: RMAN backups of a single very large file take longer to complete than the same data spread across multiple smaller files. ASM mitigates I/O bottlenecks by striping internally. Not ideal for OLTP systems that benefit from datafile-level parallelism.

Capacity planning frequency should reflect the rate of data growth:

• Critical / fast-growing databases: Review weekly. Monitor segment growth trends and alert when tablespaces exceed 80% utilisation.
• Standard production databases: Review quarterly. Track month-over-month growth rates.

Use Automated Workload Repository (AWR) and the DBA_SEGMENTS, DBA_DATA_FILES, and DBA_FREE_SPACE views to build growth trend reports. Projecting forward 6–12 months gives adequate lead time for storage procurement.

Oracle Managed Files (OMF) lets Oracle automatically create, name, and delete datafiles, redo logs, and control files. You configure a destination directory via DB_CREATE_FILE_DEST; Oracle handles all file naming.

Advantages: Eliminates stale orphaned files after a DROP TABLESPACE (Oracle deletes the OS file automatically); no need to specify filenames in DDL.

Disadvantages: System-generated names (e.g. o1_mf_users_k7h2j4bq_.dbf) are opaque — identifying a file’s purpose from the OS is difficult without querying the data dictionary.

-- Database timezone
SELECT DBTIMEZONE FROM DUAL;

-- Session timezone
SELECT SESSIONTIMEZONE FROM DUAL;

-- NLS timestamp format with timezone
SELECT value FROM v$nls_parameters
WHERE parameter = 'NLS_TIMESTAMP_TZ_FORMAT';

Database and session timezones can differ. For applications that store TIMESTAMP WITH TIME ZONE data, ensure both are consistently configured to avoid unexpected offset calculations.

SELECT s.username, s.sid, s.serial#,
       t.tablespace, t.segtype,
       t.blocks * 8192 / 1024 / 1024 AS mb_used
FROM v$tempseg_usage t
JOIN v$session s ON s.saddr = t.session_addr
ORDER BY mb_used DESC;

This identifies which sessions are using temp space, what type of operation is generating it (SORT, HASH, etc.), and how much space each is consuming — useful for diagnosing temp tablespace exhaustion.

A datafile with allocated extents cannot be dropped. First ensure the datafile is empty (move or drop all segments within it), then:

-- For an empty datafile in Oracle 12c+
ALTER TABLESPACE users DROP DATAFILE '/u01/oradata/orcl/users03.dbf';

-- Legacy workaround (NOARCHIVELOG mode only — use with caution)
ALTER DATABASE DATAFILE '/path/to/file.dbf' OFFLINE DROP;

The 12c+ ALTER TABLESPACE ... DROP DATAFILE syntax is the clean approach — it removes the datafile from the tablespace and deletes the OS file (if OMF) in one step.

SELECT current_scn FROM v$database;

The System Change Number (SCN) is Oracle’s internal logical clock — a monotonically increasing integer that uniquely identifies every committed state of the database. It underpins consistent reads (multi-version concurrency), point-in-time recovery, Data Guard synchronization, and Flashback features. Understanding the current SCN is useful for calculating undo retention requirements and planning Flashback operations.

Both cap the total number of datafiles, but at different layers:

MAXDATAFILES: Set at database creation (or control file recreation). Hard limit on the number of datafile entries in the control file. Increasing it requires recreating the control file.

DB_FILES: Initialization parameter that controls how many datafile entries are pre-allocated in the SGA. Can be changed without recreation but requires a restart. Must be ≤ MAXDATAFILES to have effect.

The effective limit is whichever is lower. Best practice is to set both generously at creation time to avoid later control file recreation.

ALTER TABLESPACE users
  ADD DATAFILE '/u01/oradata/orcl/users02.dbf'
  SIZE 1G
  AUTOEXTEND ON NEXT 256M MAXSIZE 10G;

Always include AUTOEXTEND ON with a MAXSIZE — this prevents unexpected space errors when the datafile fills, while the MAXSIZE cap prevents unbounded disk consumption. Proactive monitoring of tablespace usage trends is still essential alongside autoextend.

LMT addresses all the major weaknesses of Dictionary Managed Tablespaces:

• No dictionary contention: Extent bitmaps live in each datafile header, not in shared SYS tables. No I/O or locking on UET$ / FET$.
• No undo generated: Space allocation is not a transactional operation — no rollback segments are consumed.
• No coalescing required: The bitmap inherently tracks free contiguous blocks; fragmentation is a non-issue.
• Reduced recursive SQL: Oracle does not need to execute internal SQL to allocate or free extents.

-- Free space by tablespace
SELECT tablespace_name,
       ROUND(SUM(bytes)/1024/1024, 1) AS free_mb
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY free_mb;

-- Full picture: used, free, and autoextend headroom
SELECT df.tablespace_name,
       ROUND(df.total_mb, 1)                          AS total_mb,
       ROUND(df.total_mb - NVL(fs.free_mb, 0), 1)    AS used_mb,
       ROUND(NVL(fs.free_mb, 0), 1)                   AS free_mb,
       ROUND((1 - NVL(fs.free_mb,0)/df.total_mb)*100, 1) AS pct_used
FROM (SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb
      FROM dba_data_files GROUP BY tablespace_name) df
LEFT JOIN (SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
           FROM dba_free_space GROUP BY tablespace_name) fs
  ON df.tablespace_name = fs.tablespace_name
ORDER BY pct_used DESC NULLS LAST;