Search Results bis_rs_all_tablespaces_v

Overview

BIS_RS_ALL_TABLESPACES_V is a lightweight reporting view shipped within the Applications BIS (Business Intelligence System / Oracle EBS reporting infrastructure) product family. Its purpose is to expose a simple key/value list of the permanent tablespaces defined in the database, suitable for use as a value-set source or LOV (list of values) backing query in Oracle E-Business Suite reporting, concurrent programs, and integration components.

The ETRM documentation for this object records the description as "Implementation/DBA Data" and notes that it is "Not implemented in this database" — meaning the view is delivered as a seed/reference object rather than a populated business entity. It carries no transactional or business semantics; it is a pure metadata projection over the Oracle data dictionary. In Oracle EBS 12.1.1 and 12.2.2, such views are typically referenced by BIS-related concurrent programs, Oracle Discoverer / Oracle Business Intelligence workbooks, or parameter collection screens that require a selection from database structural attributes rather than application tables.

Because the view filters the dictionary to permanent tablespaces only, it aligns with the common DBA search term dba_tablespaces: it is effectively a constrained projection of DBA_TABLESPACES.

Underlying Base Objects

The ETRM view text documents a single base object: DBA_TABLESPACES. The view definition is:

• SELECT TABLESPACE_NAME ID, TABLESPACE_NAME VALUE FROM DBA_TABLESPACES WHERE CONTENTS = 'PERMANENT'

DBA_TABLESPACES is a standard Oracle data dictionary view owned by SYS, listing all tablespaces in the database plus their attributes (CONTENTS, STATUS, EXTENT_MANAGEMENT, BLOCK_SIZE, LOGGING, etc.). BIS_RS_ALL_TABLESPACES_V restricts that row set to the PERMANENT value of the CONTENTS column, excluding TEMPORARY and UNDO tablespaces. The ETRM metadata records no additional referenced base objects, and no owner is documented for the view itself. In a standard EBS installation, this style of object is commonly created under the APPS schema or an equivalent reporting schema and granted SELECT to the reporting/BI user, since the underlying dictionary view requires elevated privileges that ordinary EBS application users do not hold.

Key Columns

Only two columns are exposed, deliberately aliased for use as a generic key/value pair:

• ID — the tablespace name, returned from DBA_TABLESPACES.TABLESPACE_NAME. This is the technical identifier and is unique within the database.
• VALUE — a duplicate of the tablespace name, provided so the view can plug directly into Oracle Forms / OAF value-set conventions and generic LOV frameworks that expect an ID/VALUE pair.

No status, contents, extent-management, or sizing attributes are surfaced. Any query requiring block size, default storage, or tablespace status must join back to DBA_TABLESPACES on TABLESPACE_NAME.

Common Use Cases and Queries

Typical scenarios include populating a DBA/reporting parameter LOV with valid permanent tablespaces, validating a user-supplied tablespace name before running a DDL or space-usage report, and feeding comparison queries in capacity-planning workbooks.

• List all permanent tablespaces available for selection:

  SELECT id, value FROM bis_rs_all_tablespaces_v ORDER BY value;

• Restrict a selection to a specific set:

  SELECT value FROM bis_rs_all_tablespaces_v WHERE value IN ('APPS_TS_TX_DATA','APPS_TS_QUEUES');

• Join to the dictionary for additional attributes such as size or status:

  SELECT v.value, t.status, t.block_size, t.logging
FROM bis_rs_all_tablespaces_v v, dba_tablespaces t
WHERE v.value = t.tablespace_name;

• Case-insensitive lookup driven by a form field:

  SELECT id, value FROM bis_rs_all_tablespaces_v WHERE UPPER(value) = UPPER(:p_tablespace);

Because the view filters on CONTENTS = 'PERMANENT', TEMP and UNDO tablespaces are never returned; use DBA_TABLESPACES directly when those are required. Access to the view depends on the underlying dictionary privileges, so deployments that report "not implemented" may need the object created in the target instance before referencing it.