Oracle教程 2023-02-24

Oracle 视图 DBA_TABLESPACES 官方解释，作用，如何使用详细说明

本站中文解释

Oracle视图DBA_TABLESPACES用于查询数据库中表空间的属性信息，可以使 DBA 更便捷地查询表空间的信息和状态，用于计划数据库的表空间的管理、布局和容量扩展。其中包括对表空间的创建、状态以及大小等信息，其中，表空间的状态包含：ONLINE，内存中的表空间；OFFLINE，外存中的表空间。

使用方法：

在 Oracle 数据库中，可以通过查询 DBA_TABLESPACES 视图来查看表空间的属性信息，格式如下：

SELECT *
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = ‘your_tablespace’;

upd：
可以过滤出满足一定表空间大小需求的表空间，其查询语句如下：

SELECT *
FROM DBA_TABLESPACES
WHERE BLOCK_SIZE > 1024;

官方英文解释

DBA_TABLESPACES describes all tablespaces in the database.

Related View

USER_TABLESPACES describes the tablespaces accessible to the current user. This view does not display the PLUGGED_IN column.

Column	Datatype	NULL	Description
`TABLESPACE_NAME`	`VARCHAR2(30)`	`NOT NULL`	Name of the tablespace
`BLOCK_SIZE`	`NUMBER`	`NOT NULL`	Tablespace block size (in bytes)
`INITIAL_EXTENT`	`NUMBER`		Default initial extent size (in bytes)
`NEXT_EXTENT`	`NUMBER`		Default incremental extent size (in bytes)
`MIN_EXTENTS`	`NUMBER`	`NOT NULL`	Default minimum number of extents
`MAX_EXTENTS`	`NUMBER`		Default maximum number of extents
`MAX_SIZE`	`NUMBER`		Default maximum size of segments (in Oracle blocks)
`PCT_INCREASE`	`NUMBER`		Default percent increase for extent size
`MIN_EXTLEN`	`NUMBER`		Minimum extent size for this tablespace (in bytes)
`STATUS`	`VARCHAR2(9)`		Tablespace status: `ONLINE` `OFFLINE` `READ ONLY`
`CONTENTS`	`VARCHAR2(9)`		Tablespace contents: `UNDO` `LOST WRITE PROTECTION` `PERMANENT` `TEMPORARY`
`LOGGING`	`VARCHAR2(9)`		Default logging attribute: `LOGGING` `NOLOGGING`
`FORCE_LOGGING`	`VARCHAR2(3)`		Indicates whether the tablespace is under force logging mode (`YES`) or not (`NO`)
`EXTENT_MANAGEMENT`	`VARCHAR2(10)`		Indicates whether the extents in the tablespace are dictionary managed (`DICTIONARY`) or locally managed (`LOCAL`)
`ALLOCATION_TYPE`	`VARCHAR2(9)`		Type of extent allocation in effect for the tablespace: `SYSTEM` `UNIFORM` `USER`
`PLUGGED_IN`	`VARCHAR2(3)`		Indicates whether the tablespace is plugged in (`YES`) or not (`NO`)
`SEGMENT_SPACE_MANAGEMENT`	`VARCHAR2(6)`		Indicates whether the free and used segment space in the tablespace is managed using free lists (`MANUAL`) or bitmaps (`AUTO`)
`DEF_TAB_COMPRESSION`	`VARCHAR2(8)`		Indicates whether default table compression is enabled (`ENABLED`) or not (`DISABLED`) Note: Enabling default table compression indicates that all tables in the tablespace will be created with table compression enabled unless otherwise specified.
`RETENTION`	`VARCHAR2(11)`		Undo tablespace retention: `GUARANTEE` – Tablespace is an undo tablespace with `RETENTION` specified as `GUARANTEE` A `RETENTION` value of `GUARANTEE` indicates that unexpired undo in all undo segments in the undo tablespace should be retained even if it means that forward going operations that need to generate undo in those segments fail. `NOGUARANTEE` – Tablespace is an undo tablespace with `RETENTION` specified as `NOGUARANTEE` `NOT APPLY` – Tablespace is not an undo tablespace
`BIGFILE`	`VARCHAR2(3)`		Indicates whether the tablespace is a bigfile tablespace (`YES`) or a smallfile tablespace (`NO`)
`PREDICATE_EVALUATION`	`VARCHAR2(7)`		Indicates whether predicates are evaluated by host (`HOST`) or by storage (`STORAGE`)
`ENCRYPTED`	`VARCHAR2(3)`		Indicates whether the tablespace is encrypted (`YES`) or not (`NO`)
`COMPRESS_FOR`	`VARCHAR2(30)`		Default compression for what kind of operations: `BASIC` `ADVANCED` `QUERY LOW` `QUERY HIGH` `ARCHIVE LOW` `ARCHIVE HIGH` `NULL` The `QUERY LOW`, `QUERY HIGH`, `ARCHIVE LOW`, and `ARCHIVE HIGH` values are associated with Hybrid Columnar Compression, a feature of the Enterprise Edition of Oracle Database that is dependent on the underlying storage system. See Oracle Database Concepts for more information.
`DEF_INMEMORY`	`VARCHAR2(8)`		Indicates whether the In-Memory Column Store (IM column store) is by default enabled (`ENABLED`) or disabled (`DISABLED`) for tables in this tablespace
`DEF_INMEMORY_PRIORITY`	`VARCHAR2(8)`		Indicates the default priority for In-Memory Column Store (IM column store) population for this tablespace. Possible values: `LOW` `MEDIUM` `HIGH` `CRITICAL` `NONE` `NULL`
`DEF_INMEMORY_DISTRIBUTE`	`VARCHAR2(15)`		Indicates how the IM column store is distributed by default for this tablespace in an Oracle Real Application Clusters (Oracle RACE) environment: `AUTO` `BY ROWID RANGE` `BY PARTITION` `BY SUBPARTITION`
`DEF_INMEMORY_COMPRESSION`	`VARCHAR2(17)`		Indicates the default compression level for the IM column store for this tablespace: `NO MEMCOMPRESS` `FOR DML` `FOR QUERY [ LOW \| HIGH ]` `FOR CAPACITY [ LOW \| HIGH ]` `NULL`
`DEF_INMEMORY_DUPLICATE`	`VARCHAR2(13)`		Indicates the duplicate setting for the IM column store in an Oracle RAC environment: `NO DUPLICATE` `DUPLICATE` `DUPLICATE ALL`
`SHARED`	`VARCHAR2(12)`		Tablespace type: `SHARED`: For shared tablespace `LOCAL_ON_LEAF`: For local temporary tablespace for leaf (read-only) instances `LOCAL_ON_ALL`: For local temporary tablespace for all instance types
`DEF_INDEX_COMPRESSION`	`VARCHAR2(8)`		Indicates whether default index compression is enabled (`ENABLED`) or not (`DISABLED`) Note: Enabling default index compression indicates that all indexes in the tablespace will be created with index compression enabled unless otherwise specified.
`INDEX_COMPRESS_FOR`	`VARCHAR2(13)`		Valid values are: `ADVANCED LOW` `ADVANCED HIGH` `NULL` No other values are allowed.
`DEF_CELLMEMORY`	`VARCHAR2(14)`		This specifies the default value for the `CELLMEMORY` attribute that tables created in the tablespace will inherit unless the behavior is overridden explicitly This column is intended for use with Oracle Exadata.
`DEF_INMEMORY_SERVICE`	`VARCHAR2(12)`		Indicates how the IM column store is populated on various instances by default for this tablespace. The possible values are: `DEFAULT`: Data is populated on all instances specified with the `PARALLEL_INSTANCE_GROUP` initialization parameter. If that parameter is not set, then the data is populated on all instances. This is the default. `NONE`: Data is not populated on any instance. `ALL`: Data is populated on all instances, regardless of the value of the `PARALLEL_INSTANCE_GROUP` initialization parameter. `USER_DEFINED`: Data is populated only on the instances on which the user-specified service is active. The service name corresponding to this is stored in the `DEF_INMEMORY_SERVICE_NAME` column.
`DEF_INMEMORY_SERVICE_NAME`	`VARCHAR2(1000)`		Indicates the service name for the service on which the IM column store should be populated by default for this tablespace. This column has a value only when the corresponding `DEF_INMEMORY_SERVICE` is `USER_DEFINED`. In all other cases, this column is null.
`LOST_WRITE_PROTECT`	`VARCHAR2(7)`		The lost write protection setting for the tablespace. Possible values: `ENABLED`: Indicates that lost write data is being collected `OFF`: Indicates that lost write data is not being collected `SUSPEND`: Indicates that lost write data is not currently being collected, but it can be enabled at a later date. The lost write data collected when the file was `ENABLED` remains in the lost write database, but it is not being checked or updated. If lost write protection is enabled for a tablespace, it is enabled for all data files for that tablespace, including data files added later. If lost write protection is enabled for a single data file, it does not have to be enabled for another data file in the same tablespace. You can check the lost write protection status for a data file by querying the `LOST_WRITE_PROTECT` column in the `DBA_DATA_FILES` view.
`CHUNK_TABLESPACE`	`VARCHAR2(1)`		Indicates whether this is a chunk tablespace (`Y`) or not (`N`)

本站中文解释

官方英文解释

相关推荐