In case you only have SQL*Plus as your editor, you can use the following codes to show the execution plan of your query.
First Step : create plan
EXPLAIN PLAN FOR
SELECT field1, field2
FROM table;
Second Step: show results
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
You will need the necessary privileges to execute this. You’ll also need to have the PLAN table. If not, you’ll have to create it as that’s the placeholder where execution plan is written.
This technique is important especially when you don’t have a GUI-based SQL editor like PL/SQL Developer or TOAD.
In case you are maintaining a pl/sql program developed by someone else and that program writes into a file, then basically that file is written to a folder/directory. And since the directories are saved in codes, you have to find the meaning (or location) of those codes. The code below will help you display the information required.
SELECT * FROM ALL_DIRECTORIES;
The query above will display all the directories accessible to the current user. They are folders in the server where you can write your output files. For some reason, I forgot about this twice already and had to scour the web to find it. The problem is, I also forgot the keywords. So, I’m writing it now.
To register new directories, you can follow the example below.
Example
create or replace directory mydir as 'c:\mydirectory';
To use, run the code below.
declare
f utl_file.file_type;
begin
f := utl_file.fopen('MYDIR', 'myfilename.txt', 'w');
utl_file.put_line(f, 'just another line text 1');
utl_file.put_line(f, 'just another line text 2');
utl_file.fclose(f);
end;