How to query the table space where the table is located in Oracle

In Oracle, you can use the select statement to query the table space where the table is located. The syntax is "select TABLESPACE_NAME from tabs where TABLE_NAME = 'uppercase table name'"; "TABLESPACE_NAME" represents the table space name.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How oracle queries the table space where the table is located

The basic format of the SELECT statement is:

SELECT 要查询的列名 FROM 表名 WHERE 限制条件

ps: If you want to query all the contents of the table, put the columns to be queried Names are represented by an asterisk *.

The syntax for querying the table space where a table is located is:

select TABLESPACE_NAME from tabs where TABLE_NAME = 表名;

It should be noted that the table name needs to be in uppercase letters.

Extended knowledge:

Oracle tablespaces (tablespaces) are a logical concept. Data files are what actually store data. . An Oracle database can have one or more table spaces, and a table space corresponds to one or more physical database files.

Table space is the smallest unit for Oracle database recovery, housing many database entities, such as tables, views, indexes, clusters, rollback segments, temporary segments, etc.

oracle query all table spaces

Method 1: dba_tablespaces

select * from dba_tablespaces;

Method 2: v$tablespace

select * from v$tablespace;

Delete table space

Delete an empty table space, which does not contain physical files

DROP TABLESPACE tablespace_name;

Delete an empty table space, which contains physical files

DROP TABLESPACE tablespace_name INCLUDING DATAFILES;

Delete a non-empty table space, which does not contain physical files

DROP TABLESPACE tablespace_name INCLUDING DATAFILES;

Delete Non-empty table space, containing physical files

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of How to query the table space where the table is located in Oracle. For more information, please follow other related articles on the PHP Chinese website!