how to see which tables are in a datafile in oracle database?

I need to import a schema from one db to another. The destination db's data drive is full. I need to clean it. how do I find out which tables lives on that datafile?
superdbaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SurranoSystem EngineerCommented:
Hello superdba,

First find the tablespace containing that datafile.
select tablespace_name from dba_data_files where file_name='...';
Then the segments in that tbsp:
select segment_name, segment_type, partition_name, bytes/1024/1024 mb
from dba_segments
where tablespace_name ='...';

If the tbsp contains more than one file you cannot influence which segments inside the tbsp use that particular datafile but you can check file# in dba_extents.

S.
0
Gerwin Jansen, EE MVETopic Advisor Commented:
for tables:

select SEGMENT_NAME
from SYS.DBA_SEGMENTS
where SEGMENT_TYPE='TABLE'
and TABLESPACE_NAME =
(
  select TABLESPACE_NAME
  from SYS.DBA_DATA_FILES
  where file_name='/path/to/file.dbf'
);

change the where clause to INDEX if you want to find the indexes.
0
johnsoneSenior Oracle DBACommented:
DBA_SEGMENTS would tell you what tablespace the table resides in.  You can get that from DBA_TABLES.  DBA_SEGMENTS could tell you how much space is occupied by that table in the tablespace.

If you are truly looking for the file level, as the question implies to me, you would need to go to DBA_EXTENTS.
SELECT   a.owner, 
         a.segment_name, 
         b.file_name, 
         SUM(a.bytes)/1024/1024 size_in_mb 
FROM     dba_extents a 
join     dba_data_files b a.file_id = b.file_id 
WHERE    a.owner = 'table_owner' 
AND      a.segment_name = 'table_name' 
GROUP BY a.owner, 
         a.segment_name, 
         b.file_name;

Open in new window

If you are looking for which objects are in a specific file, then the query would be something like this:
SELECT DISTINCT a.owner, 
                a.segment_name, 
                a.segment_type 
FROM   dba_extents a 
       join dba_data_files b 
         ON a.file_id = b.file_id 
WHERE  b.file_name = 'full_path_to_file'; 

Open in new window

Gerwin's query would give you all objects in the tablespace and it isn't necessarily true that every object exists in every file of the tablespace.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve WalesSenior Database AdministratorCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Split:
-- johnsone (https:#a40968843)
-- Gerwin Jansen (https:#a40968721)
-- Surrano (https:#a40968578)


If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

sjwales
Experts-Exchange Cleanup Volunteer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.