DBA Hub

📋Steps in this guide1/5

List Files in a Directory From PL/SQL and SQL : External Table

This article shows how to list files in a directory on the database server using an external table.

oracle miscconfigurationintermediate
by OracleDba
20 views
1

Setup

We connect to a privileged user, and create a new test user. We create three directories to handle the processing of our directory listing functionality. - "/u01/fs_list/logs" : A directory used by the external table to write logs. This needs read-write permissions. - "/u01/fs_list/script" : A directory to hold a pre-processor script used to list the files in a directory. This needs read-execute permissions. - "/u01/fs_list/control" : A directory to hold files to control which directories can be listed. This needs read permissions. We create the directories using the following commands. We create the Oracle directory objects associated with these physical directories, granting the relevant permissions to our test user. We create the pre-processor script with the following commands. The script lists the files in the directory provided by the external table LOCATION clause. It specifies the date format in a more useful form than the default format. The second command shows us the contents of the file once it's been written. For each directory we want to list files in, we create a separate file control file that contains the path of interest. We'll just create two files. One for the location and one for the trace file location.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
CONN sys/SysPassword1@//localhost:1521/pdb1 AS SYSDBA

DROP USER testuser1 CASCADE;
CREATE USER testuser1 IDENTIFIED BY testuser1 QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO testuser1;

mkdir -p /u01/fs_list/logs
mkdir -p /u01/fs_list/script
mkdir -p /u01/fs_list/control

CREATE OR REPLACE DIRECTORY fs_list_logs_dir AS '/u01/fs_list/logs/';
GRANT READ, WRITE ON DIRECTORY fs_list_logs_dir TO testuser1;

CREATE OR REPLACE DIRECTORY fs_list_script_dir AS '/u01/fs_list/script/';
GRANT READ, EXECUTE ON DIRECTORY fs_list_script_dir TO testuser1;

CREATE OR REPLACE DIRECTORY fs_list_control_dir AS '/u01/fs_list/control/';
GRANT READ ON DIRECTORY fs_list_control_dir TO testuser1;

cat > /u01/fs_list/script/list_directory.sh <<EOF
#!/bin/bash
/usr/bin/ls -l --time-style=+"%Y-%m-%d:%H:%M:%S" "\$(/usr/bin/cat \$1)"
EOF

chmod u+x /u01/fs_list/script/list_directory.sh


cat /u01/fs_list/script/list_directory.sh
#!/bin/bash
/usr/bin/ls -l --time-style=+"%Y-%m-%d:%H:%M:%S" "$(/usr/bin/cat $1)"
$

cat > /u01/fs_list/control/oraclebase.txt <<EOF
/u01/app/oracle
EOF

cat > /u01/fs_list/control/trace.txt <<EOF
/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace
EOF
2

Create External Table

We connect to our test user and create an external table. Notice the use of the directory objects in bold.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
CONN testuser1/testuser1@//localhost:1521/pdb1

DROP TABLE list_directory_ext;

CREATE TABLE list_directory_ext (
  file_name        VARCHAR2(200),
  file_permissions VARCHAR2(11),
  file_hardlinks   NUMBER,
  file_owner       VARCHAR2(32),
  file_group       VARCHAR2(32),
  file_size        NUMBER,
  file_datetime    DATE
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
DEFAULT DIRECTORY fs_list_logs_dir
ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
PREPROCESSOR fs_list_script_dir:'list_directory.sh'
FIELDS TERMINATED BY WHITESPACE
    (
      file_permissions,
      file_hardlinks,
      file_owner,
      file_group,
      file_size,
      file_datetime DATE 'YYYY-MM-DD:HH24:MI:SS',
      file_name
    )
  )
LOCATION (fs_list_control_dir:'oraclebase.txt')
)
REJECT LIMIT UNLIMITED;
3

Test It

We can query the external table to get the list of files and directories in default location, which is pointed to by the "oraclebase.txt" file. We can list the files and directories in the trace location by using the "trace.txt" file. We can do this by altering the external table clause. We set it back to the default location once we are done. Alternatively, from 18c onward we can modify the external table location directly in the SQL.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
ALTER SESSION SET nls_date_format='DD-MON-YYYY:HH24:MI:SS';

SET LINESIZE 200

COLUMN file_name FORMAT A30
COLUMN file_owner FORMAT A10
COLUMN file_group FORMAT A10

SELECT file_name,
       file_permissions,
       file_hardlinks,
       file_owner,
       file_group,
       file_size,
       file_datetime
FROM   list_directory_ext;

FILE_NAME                      FILE_PERMIS FILE_HARDLINKS FILE_OWNER FILE_GROUP  FILE_SIZE FILE_DATETIME
------------------------------ ----------- -------------- ---------- ---------- ---------- --------------------
admin                          drwxr-x---.              3 oracle     oinstall           18 30-JUN-2020:11:23:07
audit                          drwxr-x---.              3 oracle     oinstall           18 30-JUN-2020:11:25:41
cfgtoollogs                    drwxr-x---.              4 oracle     oinstall           34 30-JUN-2020:11:25:42
checkpoints                    drwxr-xr-x.              2 oracle     oinstall            6 30-JUN-2020:11:22:43
diag                           drwxrwxr-x.             23 oracle     oinstall          280 30-JUN-2020:11:22:44
product                        drwxr-xr-x.              3 oracle     oinstall           20 30-JUN-2020:11:19:02

6 rows selected.

SQL>

ALTER TABLE list_directory_ext LOCATION (fs_list_control_dir:'trace.txt');

SELECT file_name,
       file_permissions,
       file_hardlinks,
       file_owner,
       file_group,
       file_size,
       file_datetime
FROM   list_directory_ext;

FILE_NAME                      FILE_PERMIS FILE_HARDLINKS FILE_OWNER FILE_GROUP  FILE_SIZE FILE_DATETIME
------------------------------ ----------- -------------- ---------- ---------- ---------- --------------------
alert_cdb1.log                 -rw-r-----.              1 oracle     oinstall       172573 30-JUN-2020:17:48:43
cdb1_dbrm_15137.trc            -rw-r-----.              1 oracle     oinstall         1061 30-JUN-2020:11:23:16
cdb1_dbrm_15137.trm            -rw-r-----.              1 oracle     oinstall          912 30-JUN-2020:11:23:16

... deleted for brevity ...

cdb1_vktm_17026.trm            -rw-r-----.              1 oracle     oinstall          952 30-JUN-2020:11:56:17
cdb1_vktm_17896.trc            -rw-r-----.              1 oracle     oinstall         1230 30-JUN-2020:11:56:37
cdb1_vktm_17896.trm            -rw-r-----.              1 oracle     oinstall          930 30-JUN-2020:11:56:37

423 rows selected.

SQL>

ALTER TABLE list_directory_ext LOCATION (fs_list_control_dir:'oraclebase.txt');

SELECT file_name,
       file_permissions,
       file_hardlinks,
       file_owner,
       file_group,
       file_size,
       file_datetime
FROM   list_directory_ext EXTERNAL MODIFY (LOCATION (fs_list_control_dir:'trace.txt'));

FILE_NAME                      FILE_PERMIS FILE_HARDLINKS FILE_OWNER FILE_GROUP  FILE_SIZE FILE_DATETIME
------------------------------ ----------- -------------- ---------- ---------- ---------- --------------------
alert_cdb1.log                 -rw-r-----.              1 oracle     oinstall       172573 30-JUN-2020:17:48:43
cdb1_dbrm_15137.trc            -rw-r-----.              1 oracle     oinstall         1061 30-JUN-2020:11:23:16
cdb1_dbrm_15137.trm            -rw-r-----.              1 oracle     oinstall          912 30-JUN-2020:11:23:16

... deleted for brevity ...

cdb1_vktm_17026.trm            -rw-r-----.              1 oracle     oinstall          952 30-JUN-2020:11:56:17
cdb1_vktm_17896.trc            -rw-r-----.              1 oracle     oinstall         1230 30-JUN-2020:11:56:37
cdb1_vktm_17896.trm            -rw-r-----.              1 oracle     oinstall          930 30-JUN-2020:11:56:37

423 rows selected.

SQL>
4

Using Views

You can hide the internal workings by creating views over the external table. The following views sit in front of the external table, setting the appropriate location file and filtering out directories, so we only see files listed.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE VIEW oraclebase_files_v AS
SELECT *
FROM   list_directory_ext EXTERNAL MODIFY (LOCATION (fs_list_control_dir:'oraclebase.txt'))
WHERE  file_permissions NOT LIKE 'd%';

CREATE OR REPLACE VIEW trace_files_v AS
SELECT *
FROM   list_directory_ext EXTERNAL MODIFY (LOCATION (fs_list_control_dir:'trace.txt'))
WHERE  file_permissions NOT LIKE 'd%';
5

Pros and Cons

Pros of this method are. - Doesn't require too much initial setup. - Doesn't give additional access to the files. It just lists them. - Has a control mechanism for what directories can be accessed. - Can list files from any directory on the DB server that is accessible to the "oracle" OS user. - Gives addition details about the files, not just the file names. We can tailor this further by altering the pre-processor script if we want. Cons of this method are. - Does require some initial setup on the file system. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!