| Replace External Table Files [message #74371] |
Tue, 30 November 2004 04:57  |
Jono
Messages: 3
Registered: June 2004
|
Junior Member |
|
|
How does one delete, move, or replace the external table source files once finished importing data from them?
Dropping the external table and the directory does not seem to be enough. I get a nice little "sharing violation" message from windows.
So far I have found that the only way to do this is to shutdown oracle. I'm hoping there is a more straight forward approach as I need to upload new data files on a daily basis.
|
|
|
|
| Re: Replace External Table Files [message #74372 is a reply to message #74371] |
Tue, 30 November 2004 12:11  |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
There are various ways that you can handle this. I have demonstrated a couple of them below. If you are going to be re-loading data of the same structure on a daily basis, a simple method would be to alter the table to use the new data file, without dropping anything. However, you should be able to drop the table and delete the data file, if you like, without dropping the directory. If you are unable to do these things, then the problem may be a lack of permissions, rather than a problem in your code. If the example below does not help, then please post a complete run of your code, including table creation, attempt to drop table and/or delete data file, error messages received, and any other releveant data, such as operating system and Oracle version.
scott@ORA92> CREATE DIRECTORY my_dir AS 'c:oracle'
2 /
Directory created.
scott@ORA92> DECLARE
2 v_file BFILE;
3 BEGIN
4 v_file := BFILENAME ('MY_DIR', 'test.dat');
5 IF DBMS_LOB.FILEEXISTS (v_file) = 1 THEN
6 DBMS_OUTPUT.PUT_LINE ('test.dat file exists');
7 ELSE
8 DBMS_OUTPUT.PUT_LINE ('test.dat file does not exist');
9 END IF;
10 v_file := BFILENAME ('MY_DIR', 'test2.dat');
11 IF DBMS_LOB.FILEEXISTS (v_file) = 1 THEN
12 DBMS_OUTPUT.PUT_LINE ('test2.dat file exists');
13 ELSE
14 DBMS_OUTPUT.PUT_LINE ('test2.dat file does not exist');
15 END IF;
16 END;
17 /
test.dat file exists
test2.dat file exists
PL/SQL procedure successfully completed.
scott@ORA92> CREATE TABLE test_table
2 (col1 NUMBER,
3 col2 VARCHAR2(1))
4 ORGANIZATION external
5 (TYPE ORACLE_LOADER
6 DEFAULT DIRECTORY my_dir
7 ACCESS PARAMETERS
8 (RECORDS DELIMITED BY NEWLINE
9 FIELDS TERMINATED BY ","
10 (col1,
11 col2))
12 LOCATION ('test.dat'))
13 /
Table created.
scott@ORA92> SELECT * FROM test_table
2 /
COL1 C
---------- -
1 A
2 B
3 C
scott@ORA92> ALTER TABLE test_table LOCATION ('test2.dat')
2 /
Table altered.
scott@ORA92> SELECT * FROM test_table
2 /
COL1 C
---------- -
4 D
5 E
6 F
scott@ORA92> DROP TABLE test_table
2 /
Table dropped.
scott@ORA92> HOST DEL c:oracletest.dat
scott@ORA92> HOST DEL c:oracletest2.dat
scott@ORA92> DECLARE
2 v_file BFILE;
3 BEGIN
4 v_file := BFILENAME ('MY_DIR', 'test.dat');
5 IF DBMS_LOB.FILEEXISTS (v_file) = 1 THEN
6 DBMS_OUTPUT.PUT_LINE ('test.dat file exists');
7 ELSE
8 DBMS_OUTPUT.PUT_LINE ('test.dat file does not exist');
9 END IF;
10 v_file := BFILENAME ('MY_DIR', 'test2.dat');
11 IF DBMS_LOB.FILEEXISTS (v_file) = 1 THEN
12 DBMS_OUTPUT.PUT_LINE ('test2.dat file exists');
13 ELSE
14 DBMS_OUTPUT.PUT_LINE ('test2.dat file does not exist');
15 END IF;
16 END;
17 /
test.dat file does not exist
test2.dat file does not exist
PL/SQL procedure successfully completed.
scott@ORA92> DROP DIRECTORY my_dir
2 /
Directory dropped.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
