Space Management
How to list Oracle directory objects
List every Oracle DIRECTORY object with owner and physical filesystem path from DBA_DIRECTORIES.
How to list all datafiles in Oracle
List every Oracle datafile with file id, path, size, max size, autoextend, and status from DBA_DATA_FILES.
How to get the DDL for all tablespaces in Oracle
Extract the CREATE TABLESPACE DDL for every tablespace using DBMS_METADATA and spool it to a file for reuse.
How to project Oracle tablespace growth from AWR history
PL/SQL report that reads DBA_HIST_TBSPC_SPACE_USAGE and prints per-tablespace and database-wide growth history plus 30/60/90/120/150/180 day projections.
How to check total datafile size created per month in Oracle
Summarize Oracle datafile creation by year and month with total size in GB from V$DATAFILE.
How to check daily growth of a specific tablespace with variance in Oracle
Show day-over-day allocated and used size for a specific Oracle tablespace from AWR, with variance labels for growth, no change, or purged data.
How to shrink Oracle datafiles to their highwatermark
Generate ALTER DATABASE DATAFILE ... RESIZE statements to shrink each Oracle datafile down to just above its actual highwatermark.
How to get the DDL for a table in Oracle
Extract the CREATE TABLE DDL for a specific table (or all tables in a schema) using DBMS_METADATA.GET_DDL.
How to list Oracle tempfiles
Show file id, path, size, max size, and autoextend for every Oracle tempfile from DBA_TEMP_FILES.
How to check TEMP tablespace free space per datafile in Oracle
Show Oracle TEMP tablespace per-datafile size, free, max size accounting for autoextend, and a used-percentage bar.
How to check partition sizes for a table in Oracle
Show size in GB per partition for a given Oracle table from DBA_SEGMENTS.
How to check tablespace free space with a visual bar in Oracle
Show Oracle tablespace size, free, max free, free percentage, and a simple ASCII bar of used percentage.
How to check tablespace usage with autoextend max size in Oracle
Show Oracle tablespace current size, used and free, plus max size and percent-used accounting for datafile autoextend — for data and temp tablespaces.
How to list datafiles of a specific tablespace in Oracle
Show file id, path, size, max size, and autoextend for every datafile of a given Oracle tablespace.
How to calculate the recommended UNDO tablespace size in Oracle
Estimate the ideal Oracle UNDO tablespace size from UNDO_RETENTION and the peak undo block generation rate in V$UNDOSTAT.
How to clean up trace (.trc / .trm) files in Oracle
Reclaim disk space by finding and deleting old Oracle trace files (.trc) and trace metadata files (.trm) under the diagnostic destination.
How to clean up Oracle logs, traces, and archivelogs with find (cheatsheet)
find-based housekeeping recipes for Oracle trace files, audit files, listener logs, archivelogs, and RMAN backups by age.
How to clean up core dump (cdump) files in Oracle
Reclaim disk space in the Oracle diagnostic destination by finding and deleting old core dump files under the cdump directory.
How to find the largest segments in Oracle
Find the largest segments (tables, indexes, LOBs) by size in Oracle from DBA_SEGMENTS.
How to create a tablespace in Oracle
Create an Oracle tablespace with autoextend and local extent management, then verify.
How to enable autoextend on a datafile in Oracle
Enable autoextend on an Oracle datafile with a growth step and a max size, then verify.
How to resize a datafile in Oracle
Resize an Oracle datafile with ALTER DATABASE DATAFILE RESIZE, then verify the new size.
How to add a datafile to a tablespace in Oracle
Add a datafile to an Oracle tablespace with ALTER TABLESPACE ADD DATAFILE, then verify.
How to list tablespaces and their datafiles in Oracle
List Oracle tablespaces and their datafiles with size and autoextend.
How to find tablespaces running out of space in Oracle
Find Oracle tablespaces running out of space, accounting for datafile autoextend max size.
How to check undo tablespace usage in Oracle
Check Oracle undo tablespace usage by status and undo health from V$UNDOSTAT.
How to check temp tablespace usage in Oracle
Check Oracle temp tablespace usage and which sessions are using temp right now.
How to check datafiles and autoextend in Oracle
Check Oracle datafiles: size, autoextend, max size, and increment, from DBA_DATA_FILES.
How to check space used per schema in Oracle
Check space used per schema (owner) in Oracle, ranked by size, from DBA_SEGMENTS.
How to check the size of a table in Oracle (table, indexes, LOBs)
Check the total size of an Oracle table including its indexes and LOB segments.
How to check tablespace usage and free space in Oracle
Check per-tablespace size, used, free, and percent used in Oracle, including TEMP and UNDO.
How to check the total database size in Oracle
Check the total size of an Oracle database in GB: used, free, and allocated, from the datafiles, tempfiles, redo logs, and free space.