I have recently started learning oracle. I have created few Tables. The DB is in some server. Is there any possibility that i can find the table location in the server. Similar to scratch directory in case of hive.
Oracle doesn't work like
MySQL engines like
InnoDB). There is no concept of storing tables as stand alone files in filesystem. Your data is stored physically in (big)
datafiles, which contain many logical objects, like
Datafiles are also grouped in another logical objects, called
tablespaces. While this doesn't cover whole concept of Oracle's storage system, it should show You, in short, how it is designed.
It's bit more complicated with database on
Oracle ASM, which is Oracle's volume manager and file system (and a variation/implementation of
LVM), or, in older versions, mechanism called
If You have administrative rights in database You can check this query. It will show You data files and their location on filesystem:
SELECT NAME, FILE#, STATUS FROM V$DATAFILE;
This one, on the other hand, will show You default setting where datafiles are created:
SELECT value FROM V$PARAMETER WHERE name = 'db_create_file_dest'
You can find more on database storage in Oracle documentation:
Some basic information about
Oracle ASM and
Oracle tables are stored in tablespaces, which are collections of datafiles. Use these queries in SQLPlus to demonstrate the principle:
- Enter one of your table names after this:
select owner,table_name, tablespace_name
where table_name = '&table';
- Enter the tablespace_name from Query 1 after this:
select tablespace_name, file_id, file_name, round(bytes/1024/1024) as "Size(MB)", autoextensible, maxbytes/1024/1024 as "MaxSize(MB)"
where tablespace_name = '&tablespace';
Your table can be stored in only one tablespace, but the data may be spread across multiple datafiles. It may also be in the same datafile as many other tables (and other database objects such as indexes and procedures).