Tutoriais

Tutorial: Tablespace Management in Oracle Database What is a Tablespace? A tablespace is the logical storage unit in Oracle Database. It logically groups data segments (tables, indexes, etc.) and is physically composed of one or more datafiles on the operating system. Every Oracle database has at least the following default tablespaces: Tablespace Description SYSTEM Data dictionary and Oracle internal objects SYSAUX Auxiliary components (AWR, statspack, etc.) TEMP Temporary operations (sorts, joins, group by) UNDOTBS1 Undo data storage (rollback) USERS Default tablespace for user objects Listing Tablespaces List all tablespaces in the database SELECT tablespace_name, status, contents, logging, extent_management, segment_space_management FROM dba_tablespaces ORDER BY tablespace_name; List datafiles associated with each tablespace SELECT tablespace_name, file_name, bytes / 1024 / 1024 AS size_mb, autoextensible, maxbytes / 1024 / 1024 AS max_size_mb FROM dba_data_files ORDER BY tablespace_name, file_name; List tempfiles (temporary tablespaces) SELECT tablespace_name, file_name, bytes / 1024 / 1024 AS size_mb, autoextensible FROM dba_temp_files ORDER BY tablespace_name; Checking Usage and Free Space View free space per tablespace SELECT df.tablespace_name AS tablespace, ROUND(df.total_mb, 2) AS total_mb, ROUND(fs.free_mb, 2) AS free_mb, ROUND(df.total_mb - fs.free_mb, 2) AS used_mb, ROUND((df.total_mb - fs.free_mb) / df.total_mb * 100, 2) AS pct_used FROM (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS total_mb FROM dba_data_files GROUP BY tablespace_name) df 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; Warning: Tablespaces exceeding 85% usage require immediate attention to avoid errors such as ORA-01653: unable to extend table. ...