-1

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.

2

Oracle doesn't work like Hive (or MySQL engines like MyISAM or 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 tables or indexes. 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 RAW partitions.

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:

https://docs.oracle.com/cd/E11882_01/server.112/e40540/physical.htm#CNCPT403

https://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm#CNCPT88801

https://docs.oracle.com/cd/B28359_01/server.111/b28310/dfiles010.htm

Some basic information about Oracle ASM and RAW partitions:

http://ora-x2.blogspot.com/2011/05/whats-asm-and-how-it-works.html

https://docs.oracle.com/cd/B28359_01/win.111/b32010/ap_raw.htm

https://community.oracle.com/message/11075301

  • What I really wanted to do was build a purging mechanism by deleting partition older than few months using shell script. Any suggestions? – Prateek Choukse Aug 11 '16 at 5:06
1

Oracle tables are stored in tablespaces, which are collections of datafiles. Use these queries in SQLPlus to demonstrate the principle:

  1. Enter one of your table names after this:

select owner,table_name, tablespace_name from dba_tables where table_name = '&table';

  1. 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)" from dba_data_files 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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.