How to know tablespace name and datafilename associated with a table in oracle11g-CodePudding

I have a user in my sysdba, and created a table in this user named as 'sampletable1', now I want that sql query to select the tablespace name , database file associated with table “sampletable1”.

CodePudding user response：

I guess you read comments.

Anyway: for a simple case - an "ordinary" table created in my schema:

SQL> connect scott/tiger@pdb1
Connected.
SQL> create table stack_test (id number);

Table created.

you might run such a query:

SQL> connect sys@pdb1 as sysdba
Enter password:
Connected.
SQL> select f.file_name,
  2         f.tablespace_name,
  3         t.table_name
  4  from dba_data_files f join dba_tables t on t.tablespace_name = f.tablespace_name
  5  where t.owner = 'SCOTT'
  6    and t.table_name = 'STACK_TEST';

FILE_NAME                                           TABLESPACE_NAME TABLE_NAME
--------------------------------------------------- --------------- ---------------
C:\ORACLE\ORACLE_21XE\ORADATA\XE\XEPDB1\USERS01.DBF USERS           STACK_TEST

SQL>

CodePudding user response：

Bear in mind that a table's data could be spread across many files, or it might not be associated with any storage if it is empty. Also, it's not clear what you mean by in my sysdba - hopefully you didn't create or change anything in the SYS schema. Anyway, you can try something like the following:

select x.segment_name, f.file_name
from   dba_extents x
       join dba_data_files f on f.file_id = x.file_id 
where  x.owner = user  -- or whatever schema owns the table
and    x.segment_name = 'YOURTABLENAME';

A bit of history: many years ago there used to be a technique for spreading i/o across multiple disk drives, whereby you would add files from different disk groups to a tablespace and use multiple alter table xyz allocate extent (datafile path/to/file.dat) or some such (it's been a while) commands, such that the table's physical data was spread across multiple storage devices, the hope being that if the table was accessed by multiple sessions concurrently this would spread the load across the available hardware. Nowadays data files tend to be allocated automatically, or else they are on a SAN which is itself a logical volume and not one physical filesystem anyway, not to mention heavily cached, and this kind of micromanagement is pointless.