List tables for table space

[pedrovlf]

Good morning,

I need to list the existing tables by tablespace, what is the correct syntax? Thanks.

[stohlirck]

You can list all tables:

Select all

Select tablespace_name, table_name From dba_tables Order By tablespace_name;

or then filter by the tablespace or table name:

Select all

Select tablespace_name, table_name From dba_tables Where tablespace_name = 'EGC_DATA'; 
Select tablespace_name, table_name From dba_tables Where table_name = 'PESSOAS';

If you do not You have access to DBA_Tables, you can use User_Tables (which will list the user tables) or all_tables (which will list all the tables that the logged in user has access).

[marquesjr]

Select all

SELECT 
    OWNER, 
    TABLE_NAME, 
    TABLESPACE_NAME 
FROM 
    ALL_TABLES

[pedrovlf]

Thanks for the help, but I performed these querys and none returned anything, I did not understand !!

[marquesjr]

What program are you running the Select? Is the bank oracle?

What I posted is to work.

[pedrovlf]

I am using the PL / SQL Developer and the bank is Oracle. Thanks!!!

[marquesjr]

So pedrovlf, is to function,

attempts:

Select all

SELECT * FROM ALL_TABLES;

[pedrovlf]

Dude I list all the tables my bank will burst the memory is too much table hehe so I need to list using the filter by the tablespace, thank you.

[marquesjr]

Select all

SELECT * FROM ALL_TABLES WHERE TABLESPACE_NAME = 'TABLESPACE_NAME_QUE_VOCÊ_QUER'

[pedrovlf]

Big, with this command it shows me all the fields of select but blank does not return result, and I am using the user system

[marquesjr]

Face with the user System you were to have access (I believe).

Do you have any access table of the tablespace_name you want?

Here, for example, when using the Select below I have the following result:

Select all

SELECT 
    OWNER, 
    TABLE_NAME, 
    TABLESPACE_NAME 
FROM 
    ALL_TABLES 
WHERE 
    OWNER IN ('SYS', 'ALBA')

Select all

OWNER     TABLE_NAME  TABLESPACE_NAME   
--------  ----------  ---------------   
ALBA  ACESSO      DADOSALB          
ALBA  AGENCIA     DADOSALB 
SYS    ACCESS_LICENSE$  SYSTEM            
SYS    AUDIT_ACTIONS    SYSTEM 

You can also search for the Table Owner.

If it does not work out and why you do not have permission to access tablespace_name you want.

[pedrovlf]

It is really he listed me only to Tablespace System and did not list the ones I need, strange because I thought User System's access was FULL.

[pedrovlf]

I made the command below with the User Owner that has access to tablespace that I need, however I listed some tables that the user I was logged in is the owner and in the tablespace_name field was empty? why? Thank you so much.

Select all

 
SELECT 
    OWNER, 
    TABLE_NAME, 
    TABLESPACE_NAME 
FROM 
    ALL_TABLES

[marquesjr]

Here there are also Table_Name that have Tablespace_Name NULL. I honestly do not know why some are empty. It's my question too, let's wait for someone to comment on something.

[pedrovlf]

????

[dr_gori]

From what I know, when Table_Space is null, it's temporary tables, so it does not have tablespace. (or IoT - Index Organized Tables)

Let's move this topic to DBA, because I believe that there we will have more answers.

Moved !!! :-O

[gilbertoca]

From what I know, when the table_space is null, it's temporary tables, so it does not have tablespace. (or IoT - Index Organized Tables)

Let's move this topic to DBA, because I believe that there we will have more answers.

Moved !!! : -The

Viewing Abespace Information

Viewing Normation About Tables

Gilberto

[dbacesar]

Good morning,

You can use the command below to list the tables and their respective tablespaces.

Select all

select segment_name, tablespace_name from dba_segments where segment_type='TABLE' ;