How to view the data in a specific partition using Oracle?

2 years ago
William Carter
2 minutes

To view data in a partition of an Oracle database, you can use one of the following methods:

  1. Retrieve data from a specific partition using the SELECT statement.
SELECT * FROM table_name PARTITION(partition_name);

table_name is the name of the table to be queried, and partition_name is the name of the partition to be queried.

  1. Set the partition for the current session using the ALTER SESSION statement.
ALTER SESSION SET CURRENT_SCHEMA = schema_name;

Next, use the SELECT statement to query the table data.

SELECT * FROM table_name;

schema_name is the name of the schema containing the table to be queried, while table_name is the name of the table to be queried.

  1. Usage of space
DECLARE
  used_space NUMBER;
  total_space NUMBER;
BEGIN
  DBMS_SPACE_ADMIN.SPACE_USAGE('schema_name', 'table_name', 'partition_name', used_space, total_space);
  DBMS_OUTPUT.PUT_LINE('Used Space: ' || used_space);
  DBMS_OUTPUT.PUT_LINE('Total Space: ' || total_space);
END;

The schema_name represents the schema name of the table to be queried, the table_name is the name of the table to be queried, and the partition_name is the name of the partition to be queried.

The methods above can assist you in viewing data in a specific partition of an Oracle database.

bannerAds