oracle query all table data

In Oracle database, querying all table data can be achieved in the following ways.

1. Use system tables to query all table names

There are some system tables in the Oracle database that can query all table names. Through these table names, you can further query all table names. data. The most commonly used system tables are USER_TABLES, ALL_TABLES and DBA_TABLES.

• Query all table names under the current user

You can use the USER_TABLES table to query all table names under the current user, as shown below:

SELECT TABLE_NAME FROM USER_TABLES;

This SQL statement will query all table names under the current user (login user).

• Query all table names for all users

If you need to query all table names for all users, you need to use the ALL_TABLES table. The ALL_TABLES table contains all table names of all users in the database, as shown below:

SELECT OWNER, TABLE_NAME FROM ALL_TABLES;

This SQL statement will query all table names of all users, and will return the user (OWNER) to which each table belongs. .

• Query all table names in the entire database

Use the DBA_TABLES table to query all table names in the entire Oracle database, as follows:

SELECT OWNER, TABLE_NAME FROM DBA_TABLES;

Note: Querying the DBA_TABLES table requires administrator privileges.

Through these queries, you can get the name list of all tables, and then you can use loop statements to query table data one by one.

2. Use a loop statement to query all table data

Another way to query all table data is to use a loop statement to query table data one by one. You can use any of the above methods to get the name list of all tables, and then use a loop statement to query the table data one by one. The example is as follows:

DECLARE
    v_table_name USER_TABLES.TABLE_NAME%TYPE;
BEGIN
    FOR c IN (SELECT TABLE_NAME FROM USER_TABLES)
    LOOP
        v_table_name := c.TABLE_NAME;
        EXECUTE IMMEDIATE 'SELECT * FROM ' || v_table_name;
    END LOOP;
END;

This code will use a cursor loop statement to get all the tables under the current user one by one. table names and then use dynamic SQL to query all data in each table.

Summary:

In Oracle database, you can query all table names through system tables, or you can use loop statements to query the data of each table one by one. Which method to use depends on the situation. If you only need to obtain a list of table names, then using system table query is enough; if you need to query table data, you can use dynamic SQL and loop statements.

The above is the detailed content of oracle query all table data. For more information, please follow other related articles on the PHP Chinese website!