0
SELECT * FROM DBA_DIRECTORIES; --gives all list of directory in database.
select * from DBMS_CLOUD.LIST_FILES('tmp'); --gives detail about files in tmp directory.

Can we find out directory name, path and other details related to files in oracle?
If yes, what is the query for that?

Arulkumar
  • 12,966
  • 14
  • 47
  • 68
PRIYA GOUR
  • 11
  • 1
  • Which files specifically are you looking for? If you are asking about data files, `dba_data_files`. If you are asking about something else, please be more specific. There are lots of files you might be looking for-- the alert log, trace files, data files, etc. – Justin Cave May 20 '21 at 05:49
  • @JustinCave , Thank You for the reply Sir. I am looking for the files which are present in my database and I want to know from which directory it is coming and what is the name of the directory, directory path , bytes , created on , last_modified. – PRIYA GOUR May 20 '21 at 06:04
  • Define "the files which are present in my database". Are you talking the data files? Control files? Log files? Trace files? Something else? If you are talking about data files, for example, `last_modified` makes no sense-- the database is constantly checkpointing the data files so they'll all constantly be modified. – Justin Cave May 20 '21 at 06:12
  • @JustinCave All kind of files are present in my database. It is fine if i just get the name of the files along with its directory name and directory path. – PRIYA GOUR May 20 '21 at 08:05
  • If you want answers for every conceivable file that has some relationship to your database then you're asking dozens of separate questions and your question will probably be closed. If you can narrow down the question and be specific about which files you are asking about, it is likely narrow enough to be answerable. – Justin Cave May 20 '21 at 08:18
  • @JustinCave, I want to write a query to get a lists of directory names along with the names and path of the log files and text files present in that directory. – PRIYA GOUR May 20 '21 at 09:48
  • Unfortunately, I'm afraid I still don't understand what you are trying to accomplish. You're interested in log files. Which log files? The alert log? Listener log? Something else? What text files are you talking about? Maybe you're talking about text files that you load via an external table and the log files that are generated when those files are loaded? If so, I'm a bit confused because then you'd just use `dbms_cloud.list_files` assuming that you're using Oracle in the cloud (otherwise I assume you'd mention something about the `dbms_cloud` command throwing an error). – Justin Cave May 20 '21 at 10:45
  • Use a loop? I'm not sure what the problem is. – Justin Cave May 20 '21 at 11:39
  • @JustinCave Thank You Sir for the help, but I got what i was looking for. – PRIYA GOUR May 20 '21 at 11:46

1 Answers1

1

I could be wrong, but most probably you are looking for this SQL:

SELECT * FROM DBA_DATA_FILES;

Please see this post for more details: How to find the default location in which Oracle DBF files are created?

Aleksandr Chehrov
  • 11
  • 1