Oracle內建包DBMS_LOB使用說明

摘自：網路

Oracle DBMS_LOB

Version 11.1

General Information Source {ORACLE_HOME}/rdbms/admin/dbmslob.sql First Available 8.0 Constants Name Data Type Value call PLS_INTEGER 12 default_csid INTEGER 0 default_lang_ctx INTEGER 0 file_readonly BINARY_INTEGER 0 lob_readonly BINARY_INTEGER 0 lob_readwrite BINARY_INTEGER 1 lobmaxsize INTEGER 18446744073709551615 no_warning INTEGER 0 session PLS_INTEGER 10 transaction PLS_INTEGER 11 warn_inconvertible_char INTEGER 1 Option Types opt_compress PLS_INTEGER 1 opt_encrypt PLS_INTEGER 2 opt_deduplicate PLS_INTEGER 4 Option Values compress_off PLS_INTEGER 0 compress_on PLS_INTEGER 1 encrypt_off PLS_INTEGER 0 encrypt_on PLS_INTEGER 2 deduplicate_off PLS_INTEGER 0 deduplicate_on PLS_INTEGER 4 Data Types TYPE blob_deduplicate_region IS RECORD ( lob_offset INTEGER, len INTEGER, primary_lob BLOB, primary_lob_offset NUMBER, mime_type VARCHAR2(80)); TYPE blob_deduplicate_region_tab IS TABLE OF blob_deduplicate_region INDEX BY PLS_INTEGER; TYPE clob_deduplicate_region IS RECORD ( lob_offset INTEGER, len INTEGER, primary_lob CLOB, primary_lob_offset NUMBER, mime_type VARCHAR2(80)); TYPE clob_deduplicate_region_tab IS TABLE OF clob_deduplicate_region INDEX BY PLS_INTEGER; Dependencies SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_LOB' UNION SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_LOB'; Exceptions Error Code Reason ORA-21560 The argument is expecting a non-null, valid value but the argument value passed in is null, invalid, or out of range ORA-22285 The directory leading to the file does not exist ORA-22286 user does not have the necessary access privileges on the directory alias and/or file ORA-22287 directory alias is not valid ORA-22288 file operation failed ORA-22288 The file is not open for the required operation ORA-22290 open files has reached the maximum limit ORA-22925 operation exceeds maximum lob size Object Privileges Execute is granted to PUBLIC APPEND Appends the contents of a source internal LOB to a destination LOB Overload 1 dbms_lob.append( dest_lob IN OUT NOCOPY BLOB, src_lob IN BLOB); CREATE OR REPLACE PROCEDURE Example_1a IS dest_lob BLOB; src_lob BLOB; BEGIN -- get the LOB locators -- note that the FOR UPDATE clause locks the row SELECT b_lob INTO dest_lob FROM lob_table WHERE key_value = 12 FOR UPDATE; SELECT b_lob INTO src_lob FROM lob_table WHERE key_value = 21; dbms_lob.append(dest_lob, src_lob); COMMIT; END; Overload 2 dbms_lob.append( dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, src_lob IN CLOB CHARACTER SET dest_lob%CHARSET); CREATE OR REPLACE PROCEDURE Example_1b IS dest_lob, src_lob BLOB; BEGIN -- get the LOB locators SELECT b_lob INTO dest_lob FROM lob_table WHERE key_value = 12 FOR UPDATE; SELECT b_lob INTO src_lob FROM lob_table WHERE key_value = 12; dbms_lob.append(dest_lob, src_lob); COMMIT; END; / CLOSE Closes a previously opened internal or external LOB Overload 1 dbms_lob.close(lob_loc IN OUT NOCOPY BLOB); TBD Overload 2 dbms_lob.close(lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS); See CREATETEMPORARY demo Overload 3 dbms_lob.close(file_loc IN OUT NOCOPY BFILE); TBD COMPARE Compares two entire LOBs or parts of two LOBs Overload 1 dbms_lob.compare( lob_1 IN BLOB, lob_2 IN BLOB, amount IN INTEGER := 18446744073709551615, offset_1 IN INTEGER := 1, offset_2 IN INTEGER := 1) RETURN INTEGER; TBD Overload 2 dbms_lob.compare( lob_1 IN CLOB CHARACTER SET ANY_CS, lob_2 IN CLOB CHARACTER SET lob_1%CHARSET, amount IN INTEGER := 18446744073709551615, offset_1 IN INTEGER := 1, offset_2 IN INTEGER := 1) RETURN INTEGER; TBD Overload 3 dbms_lob.compare( file_1 IN BFILE, file_2 IN BFILE, amount IN INTEGER, offset_1 IN INTEGER := 1, offset_2 IN INTEGER := 1) RETURN INTEGER; TBD CONVERTOBLOB Reads character data from a source CLOB or NCLOB instance, converts the character data to the specified character, writes the converted data to a destination BLOB instance in binary format, and returns the new offsets dbms_lob.convertToBlob( dest_lob IN OUT NOCOPY BLOB, src_clob IN CLOB CHARACTER SET ANY_CS, amount IN INTEGER, dest_offset IN OUT INTEGER, src_offset IN OUT INTEGER, blob_csid IN NUMBER, lang_context IN OUT INTEGER, warning OUT INTEGER); TBD CONVERTOCLOB Takes a source BLOB instance, converts the binary data in the source instance to character data using the specified character, writes the character data to a destination CLOB or NCLOB instance, and returns the new offsets dbms_lob.convertToClob( dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, src_blob IN BLOB, amount IN INTEGER, dest_offset IN OUT INTEGER, src_offset IN OUT INTEGER, blob_csid IN NUMBER, lang_context IN OUT INTEGER, warning OUT INTEGER); TBD COPY Copies all, or part, of the source LOB to the destination LOB Overload 1 dbms_lob.copy( dest_lob IN OUT NOCOPY BLOB, src_lob IN BLOB, amount IN INTEGER, dest_offset IN INTEGER := 1, src_offset IN INTEGER := 1); TBD Overload 2 dbms_lob.copy( dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, src_lob IN CLOB CHARACTER SET dest_lob%CHARSET, amount IN INTEGER, dest_offset IN INTEGER := 1, src_offset IN INTEGER := 1); TBD CREATETEMPORARY Creates a temporary BLOB or CLOB and its corresponding index in the user's default temporary tablespace Overload 1 dbms_lob.createtemporary( lob_loc IN OUT NOCOPY BLOB, cache IN BOOLEAN, dur IN PLS_INTEGER := 10); DECLARE clobvar CLOB := EMPTY_CLOB; len BINARY_INTEGER; x VARCHAR2(80); BEGIN dbms_lob.createtemporary(clobvar, TRUE); dbms_lob.open(clobvar, dbms_lob.lob_readwrite); x := 'before line break' || CHR(10) || 'after line break'; len := length(x); dbms_lob.writeappend(clobvar, len, x); dbms_lob.close(clobvar); END; / Overload 2 dbms_lob.createtemporary( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, cache IN BOOLEAN, dur IN PLS_INTEGER := 10); TBD EMPTY_BLOB Null BLOB dbms_lob.empty_blob(); CREATE TABLE ebdemo ( fid NUMBER(3), iclob BLOB); INSERT INTO ebdemo (fid, iblob) VALUES (1, EMPTY_BLOB()); EMPTY_CLOB Null CLOB dbms_lob.empty_clob(); CREATE TABLE ecdemo ( fid NUMBER(3), iclob CLOB); INSERT INTO ecdemo (fid, iclob) VALUES (1, EMPTY_CLOB()); ERASE Erases all or part of a LOB Overload 1 dbms_lob.erase( lob_loc IN OUT NOCOPY BLOB, amount IN OUT NOCOPY INTEGER, offset IN INTEGER := 1); TBD Overload 2 dbms_lob.erase( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, amount IN OUT NOCOPY INTEGER, offset IN INTEGER := 1); TBD FILECLOSE Closes a file opened with dbms_lob.file_open dbms_lob.fileclose(file_loc IN OUT NOCOPY BFILE); exec dbms_lob.fileclose(src_file); FILECLOSEALL Closes all files opened with dbms_lob.file_open dbms_lob.filecloseall; exec dbms_lob.fileclose; FILEEXISTS Determine whether a file exists dbms_lob.fileexists(file_loc IN BFILE) RETURN INTEGER; TBD FILEGETNAME Returns the source filename and directory given a BFILE dbms_lob.filegetname( file_loc IN BFILE, dir_alias OUT VARCHAR2, filename OUT VARCHAR2); TBD FILEISOPEN Checks if the file was opened using the input BFILE locators dbms_lob.fileisopen(file_loc IN BFILE) RETURN INTEGER; TBD FILEOPEN Open a file for reading dbms_lob.fileopen( file_loc IN OUT NOCOPY BFILE, open_mode IN BINARY_INTEGER := file_readonly); exec dbms_lob.fileopen(src_file, dbms_lob.file_readonly); FRAGMENT_DELETE Deletes the data at the given offset for the given length from the LOB Overload 1 dbms_lob.fragment_delete( lob_loc IN OUT NOCOPY BLOB, amount IN INTEGER, offset IN INTEGER); TBD Overload 2 dbms_lob.fragment_delete( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, amount IN INTEGER, offset IN INTEGER); TBD FRAGMENT_INSERT Inserts the given data (limited to 32K) into the LOB at the given offset Overload 1 dbms_lob.fragment_insert( lob_loc IN OUT NOCOPY BLOB, amount IN INTEGER, offset IN INTEGER, buffer IN RAW); TBD Overload 2 dbms_lob.fragment_insert( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, amount IN INTEGER, offset IN INTEGER, buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET); TBD FRAGMENT_MOVE Moves the amount of bytes (BLOB) or characters (CLOB/NCLOB) from the given offset to the new offset specified Overload 1 dbms_lob.fragment_move( lob_loc IN OUT NOCOPY BLOB, amount IN INTEGER, src_offset IN INTEGER, dest_offset IN INTEGER); TBD Overload 2 dbms_lob.fragment_move( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, amount IN INTEGER, src_offset IN INTEGER, dest_offset IN INTEGER); TBD FRAGMENT_REPLACE Replaces the data at the given offset with the given data (not to exceed 32k) Overload 1 dbms_lob.fragment_replace( lob_loc IN OUT NOCOPY BLOB, old_amount IN INTEGER, new_amount IN INTEGER, offset IN INTEGER, buffer IN RAW); TBD Overload 2 dbms_lob.fragment_replace( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, old_amount IN INTEGER, new_amount IN INTEGER, offset IN INTEGER, buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET); TBD FREETEMPORARY Frees the temporary BLOB or CLOB in the default temporary tablespace Overload 1 dbms_lob.freetemporary(lob_loc IN OUT NOCOPY BLOB); conn pm/pm desc print_media SELECT ad_sourcetext FROM print_media WHERE product_id = 2056; set long 100000 SELECT ad_sourcetext FROM print_media WHERE product_id = 2056; set serveroutput on DECLARE clobvar CLOB; BEGIN SELECT ad_sourcetext INTO clobvar FROM print_media WHERE product_id = 2056; dbms_output.put_line('1: ' || clobvar); dbms_lob.freetemporary(clobvar); dbms_output.put_line('2: ' || clobvar); END; / Overload 2 dbm_lob.freetemporary( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS); TBD GETCHUNKSIZE Returns the amount of space used in the LOB chunk to store the LOB value Overload 1 dbms_lob.getchunksize(lob_loc IN BLOB) RETURN INTEGER; TBD Overload 2 dbms_lob.getchunksize(lob_loc IN CLOB CHARACTER SET ANY_CS) RETURN INTEGER; TBD GETLENGTH Gets the length of the LOB value Overload 1 dbms_lob.getlength(lob_loc IN BLOB) RETURN INTEGER; conn pm/pm desc print_media SELECT dbms_lob.getlength(ad_photo) FROM print_media; Overload 2 dbms_lob.getlength(lob_loc IN CLOB CHARACTER SET ANY_CS) RETURN INTEGER; conn pm/pm desc print_media SELECT dbms_lob.getlength(ad_sourcetext) FROM print_media; Overload 3 dbms_lob.getlength(file_loc IN BFILE) RETURN INTEGER; DECLARE src_file BFILE; dst_file BLOB; lgh_file BINARY_INTEGER; BEGIN src_file := bfilename('CTEMP', 'myfile.txt'); lgh_file := dbms_lob.getlength(src_file); END; / GET_DEDUPLICATE_REGIONS (new in 11g) Undocumented Overload 1 dbms_lob.get_deduplicate_regions( lob_loc IN BLOB, region_table IN OUT NOCOPY BLOB_DEDUPLICATE_REGION_TAB); TBD Overload 2 dbms_lob.get_deduplicate_regions( lob_loc IN CLOB CHARACTER SET ANY_CS, region_table IN OUT NOCOPY CLOB_DEDUPLICATE_REGION_TAB); TBD GETOPTIONS Obtains settings corresponding to the option_types field for a particular LOB Overload 1 dbms_lob.getoptions( lob_loc IN BLOB, option_types IN PLS_INTEGER) RETURN PLS_INTEGER; See SECUREFILES demo Overload 2 dbms_lob.getoptions( lob_loc IN CLOB CHARACTER SET ANY_CS, option_types IN PLS_INTEGER) RETURN PLS_INTEGER; TBD GET_STORAGE_LIMIT Returns the storage limit for LOBs in your database configuration Overload 1 dbms_lob.get_storage_limit( lob_loc IN CLOB CHARACTER SET ANY_CS) RETURN INTEGER; conn pm/pm desc print_media SELECT dbms_lob.get_storage_limit(ad_sourcetext) FROM print_media; Overload 2 dbms_lob.get_storage_limit(lob_loc IN BLOB) RETURN INTEGER; conn pm/pm desc print_media SELECT dbms_lob.get_storage_limit(ad_photo) FROM print_media; INSTR Returns the matching position of the nth occurrence of the pattern in the LOB Overload 1 dbms_lob.instr( lob_loc IN BLOB, pattern IN RAW, offset IN INTEGER := 1, nth IN INTEGER := 1) RETURN INTEGER; TBD Overload 2 dbms_lob.instr( lob_loc IN CLOB CHARACTER SET ANY_CS, pattern IN VARCHAR2 CHARACTER SET lob_loc%CHARSET, offset IN INTEGER := 1, nth IN INTEGER := 1) RETURN INTEGER; conn pm/pm SELECT dbms_lob.getlength(ad_sourcetext), dbms_lob.instr(ad_sourcetext, 'A') FROM print_media; SELECT dbms_lob.getlength(ad_sourcetext), dbms_lob.instr(ad_sourcetext, 'E') FROM print_media; Overload 3 dbms_lob.instr( file_loc IN BFILE, pattern IN RAW, offset IN INTEGER := 1, nth IN INTEGER := 1) RETURN INTEGER; TBD ISOPEN Checks to see if the LOB was already opened using the input locator Overload 1 dbms_lob.isopen(lob_loc IN BLOB) RETURN INTEGER; TBD Overload 2 dbms_lob.isopen(lob_loc IN CLOB CHARACTER SET ANY_CS) RETURN INTEGER; TBD Overload 3 dbms_lob.isopen(file_loc IN BFILE) RETURN INTEGER; TBD ISSECUREFILE (new in 11g) Returns TRUE is a LOB has been stored in an encrypted SECUREFILE Overload 1 dbms_lob.issecurefile(lob_loc IN BLOB) RETURN BOOLEAN; See SECUREFILES demo Overload 2 dbms_lob.issecurefile(lob_loc IN CLOB CHARACTER SET ANY_CS) RETURN BOOLEAN; TBD ISTEMPORARY Checks if the locator is pointing to a temporary LOB Overload 1 dbms_lob.istemporary(lob_loc IN BLOB) RETURN INTEGER; TBD Overload 2 dbms_lob.istemporary(lob_loc IN CLOB CHARACTER SET ANY_CS) RETURN INTEGER; TBD LOADBLOBFROMFILE Loads BFILE data into an internal BLOB dbm_lob.loadblobfromfile( dest_lob IN OUT NOCOPY BLOB, src_bfile IN BFILE, amount IN INTEGER, dest_offset IN OUT INTEGER, src_offset IN OUT INTEGER); TBD LOADCLOBFROMFILE Loads BFILE data into an internal CLOB dbm_lob.loadclobfromfile( dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, src_bfile IN BFILE, amount IN INTEGER, dest_offset IN OUT INTEGER, src_offset IN OUT INTEGER, bfile_csid IN NUMBER, lang_context IN OUT INTEGER, warning OUT INTEGER); TBD LOADFROMFILE Loads BFILE data into an internal LOB Overload 1 dbms_lob.loadfromfile( dest_lob IN OUT NOCOPY BLOB, src_lob IN BFILE, amount IN INTEGER, dest_offset IN INTEGER := 1, src_offset IN INTEGER := 1); exec dbms_lob.loadfromfile(dst_file, src_file, lgh_file); Overload 2 dbms_lob.loadfromfile( dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, src_lob IN BFILE, amount IN INTEGER, dest_offset IN INTEGER := 1, src_offset IN INTEGER := 1); TBD OPEN Opens a LOB (internal, external, or temporary) in the indicated mode Overload 1 dbms_lob.open( lob_loc IN OUT NOCOPY BLOB, open_mode IN BINARY_INTEGER); TBD Overload 2 dbms_lob.open( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, open_mode IN BINARY_INTEGER); See CREATETEMPORARY demo Overload 3 dbms_lob.open( file_loc IN OUT NOCOPY BFILE, open_mode IN BINARY_INTEGER := file_readonly); TBD READ Reads data from the LOB starting at the specified offset Overload 1 dbms_lob.read( lob_loc IN BLOB, amount IN OUT NOCOPY INTEGER, offset IN INTEGER, buffer OUT RAW); TBD Overload 2 dbms_lob.read( lob_loc IN CLOB CHARACTER SET ANY_CS, amount IN OUT NOCOPY INTEGER, offset IN INTEGER, buffer OUT VARCHAR2 CHARACTER SET lob_loc%CHARSET); TBD Overload 3 dbms_lob.read( file_loc IN BFILE, amount IN OUT NOCOPY INTEGER, offset IN INTEGER, buffer OUT RAW); TBD SETOPTIONS Enables CSCE features on a per-LOB basis, overriding the default LOB column settings Overload 1 dbms_lob.setoptions( lob_loc IN OUT NOCOPY BLOB, option_types IN PLS_INTEGER, options IN PLS_INTEGER); Option Types opt_compress 1 opt_encrypt 2 opt_deduplicate 4 Options compress_off 0 compress_on 1 encrypt_off 0 encrypt on 2 deduplicate_off 0 deduplicate_on 4 TBD Overload 2 dbms_lob.setoptions( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, option_types IN PLS_INTEGER, options IN PLS_INTEGER); TBD SUBSTR Returns part of the LOB value starting at the specified offset Overload 1 dbms_lob.substr( lob_loc IN BLOB, amount IN INTEGER := 32767, offset IN INTEGER := 1) RETURN RAW; TBD Overload 2 dbms_lob.substr( lob_loc IN CLOB CHARACTER SET ANY_CS, amount IN INTEGER := 32767, offset IN INTEGER := 1) RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET; TBD Overload 3 dbms_lob.substr( file_loc IN BFILE, amount IN INTEGER := 32767, offset IN INTEGER := 1) RETURN RAW; TBD TRIM Trims the LOB value to the specified shorter length Overload 1 dbms_lob.trim(lob_loc IN OUT NOCOPY BLOB, newlen IN INTEGER); TBD Overload 2 dbms_lob.trim( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, newlen IN INTEGER); TBD WRITE Writes data to the LOB from a specified offset Overload 1 dbm_lob.write( lob_loc IN OUT NOCOPY BLOB, amount IN INTEGER, offset IN INTEGER, buffer IN RAW); TBD Overload 2 dbm_lob.write( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, amount IN INTEGER, offset IN INTEGER, buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET); TBD WRITEAPPEND Writes a buffer to the end of a LOB Overload 1 dbm_lob.writeappend( lob_loc IN OUT NOCOPY BLOB, amount IN INTEGER, buffer IN RAW); TBD Overload 2 dbm_lob.writeappend( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, amount IN INTEGER, buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET); CREATE TABLE book ( bookid NUMBER(5), title VARCHAR2(50), description VARCHAR2(100)); INSERT INTO book VALUES (1, '11g Inovations', 'New Features in Oracle 11g'); CREATE TABLE author ( authorid NUMBER(5), author_name VARCHAR2(60)); INSERT INTO author VALUES (1, 'Daniel Morgan'); CREATE TABLE book_author_ie ( bookid NUMBER(5), authorid NUMBER(5)); INSERT INTO book_author_ie SELECT bookid, authorid FROM book, author; CREATE OR REPLACE PROCEDURE xml_gen(cvar IN OUT NOCOPY CLOB) AS CURSOR c IS SELECT b.title, b.description, a.author_name FROM book b, author a, book_author_ie ie WHERE b.bookid = ie.bookid AND a.authorid = ie.authorid; BEGIN FOR r IN c LOOP dbms_lob.writeappend(cvar, 19, ''); dbms_lob.writeappend(cvar, length(r.description), r.description); dbms_lob.writeappend(cvar, 27, ''); dbms_lob.writeappend(cvar, length(r.author_name), r.author_name); dbms_lob.writeappend(cvar, 21, ''); END LOOP; END xml_gen; / set serveroutput on DECLARE cvar CLOB := ' '; BEGIN xml_gen(cvar); dbms_output.put_line(cvar); END; / DBMS_LOB Demos Blob Load Demo /* define the directory inside Oracle when logged on as SYS create or replace directory ctemp as 'c:\temp\'; grant read on the directory to the Staging schema grant read on directory ctemp to staging; */ -- the storage table for the image file CREATE TABLE pdm ( dname VARCHAR2(30), -- directory name sname VARCHAR2(30), -- subdirectory name fname VARCHAR2(30), -- file name iblob BLOB); -- image file -- create the procedure to load the file CREATE OR REPLACE PROCEDURE load_file ( pdname VARCHAR2, psname VARCHAR2, pfname VARCHAR2) IS src_file BFILE; dst_file BLOB; lgh_file BINARY_INTEGER; BEGIN src_file := bfilename('CTEMP', pfname); -- insert a NULL record to lock INSERT INTO pdm (dname, sname, fname, iblob) VALUES (pdname, psname, pfname, EMPTY_BLOB()) RETURNING iblob INTO dst_file; -- lock record SELECT iblob INTO dst_file FROM pdm WHERE dname = pdname AND sname = psname AND fname = pfname FOR UPDATE; -- open the file dbms_lob.fileopen(src_file, dbms_lob.file_readonly); -- determine length lgh_file := dbms_lob.getlength(src_file); -- read the file dbms_lob.loadfromfile(dst_file, src_file, lgh_file); -- update the blob field UPDATE pdm SET iblob = dst_file WHERE dname = pdname AND sname = psname AND fname = pfname; -- close file dbms_lob.fileclose(src_file); END load_file; / Save BLOB to File Demo How to save a BLOB to a file on disk in PL/SQL From: Thomas Kyte Use DBMS_LOB to read from the BLOB You will need to create an external procedure to take binary data and write it to the operating system, the external procedure can be written in C. If it was CLOB data, you can use UTL_FILE to write it to the OS but UTL_FILE does not support the binary in a BLOB. There are articles on MetaLink explaining how to do and it has a C program ready for compiling and the External Procedure stuff, i'd advise a visit. Especially, look for Note:70110.1, Subject: WRITING BLOB/CLOB/BFILE CONTENTS TO A FILE USING EXTERNAL PROCEDURES Here is the Oracle code cut and pasted from it. The outputstring procedure is the oracle procedure interface to the External procedure. ------------------------------------- DECLARE i1 BLOB; len NUMBER; my_vr RAW(10000); i2 NUMBER; i3 NUMBER := 10000; BEGIN -- get the blob locator SELECT c2 INTO i1 FROM lob_tab WHERE c1 = 2; -- find the length of the blob column len := dbms_lob.getlength(i1); dbms_output.put_line('Column Length: ' || TO_CHAR(len)); -- Read 10000 bytes at a time i2 := 1; IF len < 10000 THEN -- If the col length is <> dbms_lob.read(i1,len,i2,my_vr); outputstring('p:\bfiles\ravi.bmp', rawtohex(my_vr),'wb',2*len); -- You have to convert the data to rawtohex format. -- Directly sending the buffer -- data will not work -- That is the reason why we are sending the length as -- the double the size of the data read dbms_output.put_line('Read ' || to_char(len) || 'Bytes'); ELSE -- If the col length is > 10000 dbms_lob.read(i1,i3,i2,my_vr); outputstring('p:\bfiles\ravi.bmp', rawtohex(my_vr),'wb',2*i3); dbms_output.put_line('Read ' || TO_CHAR(i3) || ' Bytes '); END IF; i2 := i2 + 10000; WHILE (i2 < len ) LOOP -- loop till entire data is fetched dbms_lob.read(i1,i3,i2,my_vr); dbms_output.put_line('Read ' || TO_CHAR(i3+i2-1) || ' Bytes '); outputstring('p:\bfiles\ravi.bmp', rawtohex(my_vr),'ab',2*i3); i2 := i2 + 10000 ; END LOOP; END; / Load from file demo CREATE OR REPLACE PROCEDURE read_file IS src_file BFILE := bfilename('DOCUMENT_DIR', 'image.gif'); dst_file BLOB; lgh_file BINARY_INTEGER; BEGIN -- lock record SELECT bin_data INTO dst_file FROM db_image FOR update; -- open the file dbms_lob.fileopen(src_file, dbms_lob.file_readonly); -- determine length lgh_file := dbms_lob.getlength(src_file); -- read the file dbms_lob.loadfromfile(dst_file, src_file, lgh_file); -- update the blob field UPDATE db_image SET bin_data = dst_file; COMMIT; -- close file dbms_lob.fileclose(src_file); EXCEPTION WHEN access_error THEN WHEN invalid_argval THEN WHEN invalid_directory THEN WHEN no_data_found THEN WHEN noexist_directory THEN WHEN nopriv_directory THEN WHEN open_toomany THEN WHEN operation_failed THEN WHEN unopened_file THEN WHEN others THEN END read_file; / LOB Demo by Alberto Dell'Era >> Actually, I have already done my own tests and it doesn't. >> I can only retrieve 4000 as you already mentioned as >> opposed to the 64000 we're used to, but I think that this >> is a good trade off considering that we were doing almost >> 5000 queries at a time. Perhaps you could consider tuning the temp tablespace extent size to retain the ability to fetch 64000 bytes. Consider this test case (9.2.0.5, 8k block size): CREATE TABLE don (x clob); DECLARE l_clob clob; BEGIN FOR i IN 1..10 LOOP INSERT INTO don (x) VALUES (empty_clob()) RETURNING x INTO l_clob; -- create a 400,000 bytes clob FOR i IN 1..100 LOOP dbms_lob.append(l_clob, rpad ('*',4000,'*')); END LOOP; END LOOP; END; / CREATE TEMPORARY TABLESPACE don_1024 TEMPFILE 'c:\temp\don_1024.dbf' SIZE 10M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024k; CREATE TEMPORARY TABLESPACE don_512 TEMPFILE 'c:\temp\don_512.dbf' SIZE 10M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512k; CREATE TEMPORARY TABLESPACE don_64 TEMPFILE 'c:\temp\don_64.dbf' SIZE 10M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64k; SELECT tablespace_name, initial_extent FROM dba_tablespaces WHERE tablespace_name LIKE ('DON%'); TABLESPACE_NAME INITIAL_EXTENT --------------- -------------- DON_1024 1048576 DON_512 524288 DON_64 65536 ALTER USER uwclass TEMPORARY TABLESPACE don_1024; (You must exit and relog in to use the new temp tablespace) SELECT SUBSTR (x, 1, 64000) PIECE FROM don; SELECT COUNT(*) FROM gv$temporary_lobs WHERE sid = ( SELECT sid FROM gv$mystat WHERE rownum = 1); COUNT(*) ---------- 1 (Note: Even if we fetched 10 rows, we have only 1 temp clob at the end). SELECT tablespace, segtype, blocks*8*1024 USED_BYTES FROM gv$tempseg_usage WHERE username = user; TABLESPACE SEGTYPE USED_BYTES ---------- ----------- ---------- DON_1024 LOB_DATA 1048576 DON_1024 LOB_INDEX 1048576 ALTER USER dellera TEMPORARY TABLESPACE don_512; (logout then in again) TABLESPACE SEGTYPE USED_BYTES ---------- ----------- ---------- DON_512 LOB_DATA 524288 DON_512 LOB_INDEX 524288 ALTER USER dellera TEMPORARY TABLESPACE don_64; (logout then in again) TABLESPACE SEGTYPE USED_BYTES ---------- ----------- ---------- DON_64 LOB_DATA 327680 DON_64 LOB_INDEX 65536 So by reducing the extent size we greatly reduce the space allocated to the temp lob_index. I don't know why the lob_data that should contain 64000 bytes stays to 327,680 for an extent size of 64K. Interestingly, if we select only 1 row: SELECT SUBSTR(x, 1, 64000) PIECE FROM don WHERE rownum = 1; TABLESPACE SEGTYPE USED_BYTES ---------- ----------- ---------- DON_64 LOB_DATA 196608 DON_64 LOB_INDEX 65536 I don't know the reason for this. Perhaps temporary LOBs have a different (bigger) CHUNKSIZE and/or PCTVERSION or perhaps they are updated versus being 'truncated' and then inserted for each row fetched? Obviously, changing the extent size may adversely affect sort-to-disk and hash-join-to-disk, etc, operations - even if, by using an LMT temp tablespace, the impact may (stress on *may*) be immaterial. Replaces All Code Occurrences Of A String With Another Within A CLOB -- 1) clob src - the CLOB source to be replaced. -- 2) replace str - the string to be replaced. -- 3) replace with - the replacement string. FUNCTION replaceClob ( srcClob IN CLOB, replaceStr IN VARCHAR2, replaceWith IN VARCHAR2) RETURN CLOB IS vBuffer VARCHAR2 (32767); l_amount BINARY_INTEGER := 32767; l_pos PLS_INTEGER := 1; l_clob_len PLS_INTEGER; newClob CLOB := EMPTY_CLOB; BEGIN -- initalize the new clob dbms_lob.createtemporary(newClob,TRUE); l_clob_len := dbms_lob.getlength(srcClob); WHILE l_pos < l_clob_len LOOP dbms_lob.read(srcClob, l_amount, l_pos, vBuffer); IF vBuffer IS NOT NULL THEN -- replace the text vBuffer := replace(vBuffer, replaceStr, replaceWith); -- write it to the new clob dbms_lob.writeappend(newClob, LENGTH(vBuffer), vBuffer); END IF; l_pos := l_pos + l_amount; END LOOP; RETURN newClob; EXCEPTION WHEN OTHERS THEN RAISE; END; /

Oracle内建包UTL_FILE使用说明

摘自：網路

最近用到了Oracle的包UTL_FILE，网上却没找到关于它的函数，过程使用说明，虽然都不是很难的东西，但简单列出来，也能提高些效率。
于是有了这篇文。
以下翻译来自《Oracle Built-in Packages》的第六章，只翻译了部分，想了解的更详细，请参考原文。http://www.oreilly.com/catalog/oraclebip/chapter/ch06.html

FOPEN
IS_OPEN
GET_LINE
PUT
NEW_LINE
PUT_LINE
PUTF
FFLUSH
FCLOSE
FCLOSE_ALL

UTL_FILE.FOPEN 用法
FOPEN会打开指定文件并返回一个文件句柄用于操作文件。
所有PL/SQL版本: Oracle 8.0版及以上:
FUNCTION UTL_FILE.FOPEN ( FUNCTION UTL_FILE.FOPEN (
location IN VARCHAR2, location IN VARCHAR2,
filename IN VARCHAR2, filename IN VARCHAR2,
open_mode IN VARCHAR2) open_mode IN VARCHAR2,
RETURN file_type; max_linesize IN BINARY_INTEGER)
RETURN file_type;

参数

location
文件地址

filename
文件名

openmode
打开文件的模式（参见下面说明）

max_linesize
文件每行最大的字符数，包括换行符。最小为1，最大为32767

3种文件打开模式：
R 只读模式。一般配合UTL_FILE的GET_LINE来读文件。
W 写（替换）模式。文件的所有行会被删除。PUT, PUT_LINE, NEW_LINE, PUTF和FFLUSH都可使用
A 写（附加）模式。原文件的所有行会被保留。在最末尾行附加新行。PUT, PUT_LINE, NEW_LINE, PUTF和FFLUSH都可使用

打开文件时注意以下几点：
文件路径和文件名合起来必须表示操作系统中一个合法的文件。
文件路径必须存在并可访问；FOPEN并不会新建一个文件夹。
如果你想打开文件进行读操作，文件必须存在；如果你想打开文件进行写操作，文件不存在时，会新建一个文件。
如果你想打开文件进行附加操作，文件必须存在。A模式不同于W模式。文件不存在时，会抛出INVALID_OPERATION异常。

FOPEN 会抛出以下异常
UTL_FILE.INVALID_MODE
UTL_FILE.INVALID_OPERATION
UTL_FILE.INVALID_PATH
UTL_FILE.INVALID_MAXLINESIZE

UTL_FILE.IS_OPEN用法
如果文件句柄指定的文件已打开，返回TRUE，否则FALSE

FUNCTION UTL_FILE.IS_OPEN (file IN UTL_FILE.FILE_TYPE) RETURN BOOLEAN;

UTL_FILE只提供一个方法去读取数据：GET_LINE

UTL_FILE.GET_LINE用法
读取指定文件的一行到提供的缓存。
PROCEDURE UTL_FILE.GET_LINE
(file IN UTL_FILE.FILE_TYPE,
buffer OUT VARCHAR2);

file
由FOPEN返回的文件句柄

buffer
读取的一行数据的存放缓存

buffer必须足够大。否则，会抛出VALUE_ERROR 异常。行终止符不会被传进buffer。

异常
NO_DATA_FOUND
VALUE_ERROR
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.READ_ERROR

UTL_FILE.PUT用法
在当前行输出数据
PROCEDURE UTL_FILE.PUT
(file IN UTL_FILE.FILE_TYPE,
buffer OUT VARCHAR2);
file
由FOPEN返回的文件句柄
buffer
包含要写入文件的数据缓存；Oracle8.0.3及以上最大允许32kB，早期版本只有1023B

UTL_FILE.PUT输出数据时不会附加行终止符。

UTL_FILE.PUT会产生以下异常
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.WRITE_ERROR

UTL_FILE.NEW_LINE
在当前位置输出新行或行终止符，必须使用NEW_LINE来结束当前行，或者使用PUT_LINE输出带有行终止符的完整行数据。

PROCEDURE UTL_FILE.NEW_LINE
(file IN UTL_FILE.FILE_TYPE,
lines IN NATURAL := 1);
file
由FOPEN返回的文件句柄
lines
要插入的行数

如果不指定lines参数，NEW_LINE会使用默认值1，在当前行尾换行。如果要插入一个空白行，可以使用以下语句：
UTL_FILE.NEW_LINE (my_file, 2);
如果lines参数为0或负数，什么都不会写入文件。

NEW_LINE会产生以下异常
VALUE_ERROR
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.WRITE_ERROR
例子
如果要在UTL_FILE.PUT后立刻换行，可以如下例所示：
PROCEDURE add_line (file_in IN UTL_FILE.FILE_TYPE, line_in IN VARCHAR2)
IS
BEGIN
UTL_FILE.PUT (file_in, line_in);
UTL_FILE.NEW_LINE (file_in);
END;

UTL_FILE.PUT_LINE
输出一个字符串以及一个与系统有关的行终止符
PROCEDURE UTL_FILE.PUT_LINE
(file IN UTL_FILE.FILE_TYPE,
buffer IN VARCHAR2);
file
由FOPEN返回的文件句柄
buffer
包含要写入文件的数据缓存；Oracle8.0.3及以上最大允许32kB，早期版本只有1023B
在调用UTL_FILE.PUT_LINE前，必须先打开文件。
UTL_FILE.PUT_LINE会产生以下异常
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.WRITE_ERROR

例子
这里利用UTL_FILE.PUT_LINE从表emp读取数据到文件：
PROCEDURE emp2file
IS
fileID UTL_FILE.FILE_TYPE;
BEGIN
fileID := UTL_FILE.FOPEN ('/tmp', 'emp.dat', 'W');

/* Quick and dirty construction here! */
FOR emprec IN (SELECT * FROM emp)
LOOP
UTL_FILE.PUT_LINE
(TO_CHAR (emprec.empno) || ',' ||
emprec.ename || ',' ||
...
TO_CHAR (emprec.deptno));
END LOOP;

UTL_FILE.FCLOSE (fileID);
END;
PUT_LINE相当于PUT后加上NEW_LINE；也相当于PUTF的格式串"%s\n"。

UTL_FILE.PUTF
以一个模版样式输出至多5个字符串，类似C中的printf

PROCEDURE UTL_FILE.PUTF
(file IN FILE_TYPE
,format IN VARCHAR2
,arg1 IN VARCHAR2 DEFAULT NULL
,arg2 IN VARCHAR2 DEFAULT NULL
,arg3 IN VARCHAR2 DEFAULT NULL
,arg4 IN VARCHAR2 DEFAULT NULL
,arg5 IN VARCHAR2 DEFAULT NULL);
file
由FOPEN返回的文件句柄
format
决定格式的格式串
argN
可选的5个参数，最多5个

格式串可使用以下样式
%s
在格式串中可以使用最多5个%s，与后面的5个参数一一对应
\n
换行符。在格式串中没有个数限制
%s会被后面的参数依次填充，如果没有足够的参数，%s会被忽视，不被写入文件

UTL_FILE.PUTF会产生以下异常
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.WRITE_ERROR

UTL_FILE.FFLUSH
确保所有数据写入文件。
PROCEDURE UTL_FILE.FFLUSH (file IN UTL_FILE.FILE_TYPE);
file
由FOPEN返回的文件句柄

操作系统可能会缓存数据来提高性能。因此可能调用put后，打开文件却看不到写入的数据。在关闭文件前要读取数据的话可以使用UTL_FILE.FFLUSH。
典型的使用方法包括分析执行进度和调试纪录。
UTL_FILE.FFLUSH会产生以下异常
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.WRITE_ERROR

UTL_FILE.FCLOSE
关闭文件
PROCEDURE UTL_FILE.FCLOSE (file IN OUT FILE_TYPE);
file
由FOPEN返回的文件句柄

注意file是一个IN OUT参数，因为在关闭文件后会设置为NULL
当试图关闭文件时有缓存数据未写入文件，会抛出WRITE_ERROR异常

UTL_FILE.FCLOSE会产生以下异常
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.WRITE_ERROR

UTL_FILE.FCLOSE_ALL
关闭所有已打开的文件
PROCEDURE UTL_FILE.FCLOSE_ALL;

在结束程序时要确保所有打开的文件已关闭，可使用FCLOSE_ALL
也可以在EXCEPTION使用，当异常退出时，文件也会被关闭。
EXCEPTION
WHEN OTHERS

THEN
UTL_FILE.FCLOSE_ALL;
... other clean up activities ...
END;

注意：当使用FCLOSE_ALL关闭所有文件时，文件句柄并不会标记为NULL，使用IS_OPEN会返回TRUE。但是，那些关闭的文件不能执行读写操作（除非你再次打开文件）。
UTL_FILE.FCLOSE_ALL会产生以下异常
UTL_FILE.WRITE_ERROR

時間管理、知識管理、金錢管理、健康管理

時間管理、知識管理、金錢管理、健康管理

如何取得Concurrent Request ID?!

Oracle Report:
1. 宣告Number型態的User Parameter : P_CONC_REQUEST_ID
2. 在Before Report Trigger中加上: srw.user_exit( 'FND SRWINIT' )
3. 在After Report Trigger中加上 : srw.user_exit( 'FND SRWEXIT' )
4. P_CONC_REQUEST_ID中就放著目前的Concurrent request id

PL/SQL:
fnd_profile.value('CONC_REQUEST_ID')

SELECT fnd_global.conc_request_id  FROM dual ;

快速檢查 DB alert log 的語法

摘自：網路

不知道你有沒有遇過需要去 DB alert log 裡頭
去抓取 ORA- 開頭發生的狀況

如果你用 vi 去搜尋，只能一筆一筆依序判讀，很沒效率

其實可以透過 "sed" 的語法，篩選特定字元，顯示上一行的「時間」

例如：
sed '/ORA-/H;x;/\n/!d' alert_PROD.log

顯示的結果： 這樣可以快速列出 db alert log 發生的問題跟時間
Wed May 21 20:17:35 2008
ORA-1652: unable to extend temp segment by 16 in tablespace TEMP
Wed May 21 20:17:36 2008
ORA-1652: unable to extend temp segment by 16 in tablespace TEMP
Thu May 22 01:14:53 2008
ORA-01555 caused by SQL statement below (Query Duration=39068 sec, SCN: 0x0007.69a1780f):


另外呢，如果我們想要找..上次開關機之間的時間？
這樣就很有效率去顯示從 alert log 記錄的DB開關 時間囉

### 顯示 alter database open 的上一行 系統時間

sed '/ALTER DATABASE OPEN/H;x;/\n/!d' alert_PROD.log

輸出片段：
Thu May 1 09:10:09 2008
ALTER DATABASE OPEN
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN
Thu May 1 11:44:23 2008
ALTER DATABASE OPEN
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN
Thu May 15 18:20:29 2008
ALTER DATABASE OPEN
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN

Oracle Partition Table

摘自：網路

1.1 分区表PARTITION table

在ORACLE里如果遇到特别大的表，可以使用分区的表来改变其应用程序的性能。

1.1.1 分区表的建立：

某公司的每年产生巨大的销售记录，DBA向公司建议每季度的数据放在一个分区内，以下示范的是该公司1999年的数据(假设每月产生30M的数据)，操作如下：

范围分区表：

CREATE TABLE sales

(invoice_no NUMBER,

...

sale_date DATE NOT NULL )

PARTITION BY RANGE (sale_date)

(PARTITION sales1999_q1

VALUES LESS THAN (TO_DATE(‘1999-04-01’,’YYYY-MM-DD’)

TABLESPACE ts_sale1999q1,

PARTITION sales1999_q2

VALUES LESS THAN (TO_DATE(‘1999-07-01’,’YYYY-MM-DD’)

TABLESPACE ts_sale1999q2,

PARTITION sales1999_q3

VALUES LESS THAN (TO_DATE(‘1999-10-01’,’YYYY-MM-DD’)

TABLESPACE ts_sale1999q3,

PARTITION sales1999_q4

VALUES LESS THAN (TO_DATE(‘2000-01-01’,’YYYY-MM-DD’)

TABLESPACE ts_sale1999q4 );

--values less than (maxvalue)

列表分区表：

create table emp (

empno number(4),

ename varchar2(30),

location varchar2(30))

partition by list (location)

(partition p1 values ('北京'),

partition p2 values ('上海','天津','重庆'),

partition p3 values ('广东','福建')

partition p0 values (default)

);

哈希分区：

create table emp (

empno number(4),

ename varchar2(30),

sal number)

partition by hash (empno)

partitions 8

store in (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);

组合分区：

范围哈希组合分区：

create table emp (

empno number(4),

ename varchar2(30),

hiredate date)

partition by range (hiredate)

subpartition by hash (empno)

subpartitions 2

(partition e1 values less than (to_date('20020501','YYYYMMDD')),

partition e2 values less than (to_date('20021001','YYYYMMDD')),

partition e3 values less than (maxvalue));

范围列表组合分区：

CREATE TABLE customers_part (

customer_id NUMBER(6),

cust_first_name VARCHAR2(20),

cust_last_name VARCHAR2(20),

nls_territory VARCHAR2(30),

credit_limit NUMBER(9,2))

PARTITION BY RANGE (credit_limit)

SUBPARTITION BY LIST (nls_territory)

SUBPARTITION TEMPLATE

(SUBPARTITION east VALUES ('CHINA', 'JAPAN', 'INDIA', 'THAILAND'),

SUBPARTITION west VALUES ('AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'),

SUBPARTITION other VALUES (DEFAULT))

(PARTITION p1 VALUES LESS THAN (1000),

PARTITION p2 VALUES LESS THAN (2500),

PARTITION p3 VALUES LESS THAN (MAXVALUE));

create table t1 (id1 number,id2 number)

partition by range (id1) subpartition by list (id2)

(partition p11 values less than (11)

(subpartition subp1 values (1))

);

索引分区：

CREATE INDEX month_ix ON sales(sales_month)
GLOBAL PARTITION BY RANGE(sales_month)
(PARTITION pm1_ix VALUES LESS THAN (2)
PARTITION pm12_ix VALUES LESS THAN (MAXVALUE));

1.1.2 分区表的维护：

增加分区：

ALTER TABLE sales ADD PARTITION sales2000_q1

VALUES LESS THAN (TO_DATE(‘2000-04-01’,’YYYY-MM-DD’)

TABLESPACE ts_sale2000q1;

如果已有maxvalue分区，不能增加分区，可以采取分裂分区的办法增加分区！

删除分区：

ALTER TABLE sales DROP PARTION sales1999_q1;

截短分区:

alter table sales truncate partiton sales1999_q2;

合并分区：

alter table sales merge partitons sales1999_q2, sales1999_q3 into sales1999_q23;

alter index ind_t2 rebuild partition p123 parallel 2;

分裂分区：

ALTER TABLE sales

SPLIT PARTITON sales1999_q4

AT TO_DATE (‘1999-11-01’,’YYYY-MM-DD’)

INTO (partition sales1999_q4_p1, partition sales1999_q4_p2) ;

alter table t2 split partition p123 values (1,2) into (partition p12,partition p3);

交换分区:

alter table x exchange partition p0 with table bsvcbusrundatald ;

访问指定分区：

select * from sales partition(sales1999_q2)

EXPORT指定分区：

exp sales/sales_password tables=sales:sales1999_q1

file=sales1999_q1.dmp

IMPORT指定分区：

imp sales/sales_password FILE =sales1999_q1.dmp

TABLES = (sales:sales1999_q1) IGNORE=y

查看分区信息：

user_tab_partitions, user_segments

注：若分区表跨不同表空间，做导出、导入时目标数据库必须预建这些表空间。分表区各区所在表空间在做导入时目标数据库一定要预建这些表空间！这些表空间不一定是用户的默认表空间，只要存在即可。如果有一个不存在，就会报错！

默 认时，对分区表的许多表维护操作会使全局索引不可用，标记成UNUSABLE。 那么就必须重建整个全局索引或其全部分区。如果已被分区，Oracle 允许在用于维护操作的ALTER TABLE 语句中指定UPDATE GLOBAL INDEXES 来重载这个默认特性，指定这个子句也就告诉Oracle 当它执行维护操作的DDL 语句时更新全局索引，这提供了如下好处：
1.在操作基础表的同时更新全局索引这就不需要后来单独地重建全局索引；
2.因为没有被标记成UNUSABLE， 所以全局索引的可用性更高了，甚至正在执行分区的DDL 语句时仍然可用索引来访问表中的其他分区,避免了查询所有失效的全局索引的名字以便重建它们；
另外在指定UPDATE GLOBAL INDEXES 之前还要考虑如下性能因素:
1.因为要更新事先被标记成UNUSABLE 的索引，所以分区的DDL 语句要执行更长时间，当然这要与先不更新索引而执行DDL 然后再重建索引所花的时间做个比较，一个适用的规则是如果分区的大小小于表的大小的5% ，则更新索引更快一点；
2.DROP TRUNCATE 和EXCHANGE 操作也不那么快了，同样这必须与先执行DDL 然后再重建所有全局索引所花的时间做个比较；
3.要登记对索引的更新并产生重做记录和撤消记录，重建整个索引时可选择NOLOGGING；
4.重建整个索引产生一个更有效的索引，因为这更利于使用空间，再者重建索引时允许修改存储选项。
注意分区索引结构表不支持UPDATE GLOBAL INDEXES 子句。

1.1.3 普通表变为分区表

将已存在数据的普通表转变为分区表，没有办法通过修改属性的方式直接转化为分区表，必须通过重建的方式进行转变，一般可以有三种方法，视不同场景使用：

用例：

方法一：利用原表重建分区表。

CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
INSERT INTO T
SELECT ROWNUM, SYSDATE - ROWNUM FROM DBA_OBJECTS WHERE ROWNUM <= 5000;
COMMIT;

CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)
(PARTITION P1 VALUES LESS THAN (TO_DATE('2000-1-1', 'YYYY-MM-DD')),
PARTITION P2 VALUES LESS THAN (TO_DATE('2002-1-1', 'YYYY-MM-DD')),
PARTITION P3 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),
PARTITION P4 VALUES LESS THAN (MAXVALUE))
AS SELECT ID, TIME FROM T;

RENAME T TO T_OLD;

RENAME T_NEW TO T;

SELECT COUNT(*) FROM T;

COUNT(*)
----------
5000

SELECT COUNT(*) FROM T PARTITION (P1);

COUNT(*)
----------
2946

SELECT COUNT(*) FROM T PARTITION (P2);

COUNT(*)
----------
731

SELECT COUNT(*) FROM T PARTITION (P3);

COUNT(*)
----------
1096

优点：方法简单易用，由于采用DDL语句，不会产生UNDO，且只产生少量REDO，效率相对较高，而且建表完成后数据已经在分布到各个分区中了。

不足：对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性，在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失，如果要保证一致性，需要在执行完语句后对数据进行检查，而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。

适用于修改不频繁的表，在闲时进行操作，表的数据量不宜太大。

方法二：使用交换分区的方法。

Drop table t;
CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
INSERT INTO T
SELECT ROWNUM, SYSDATE - ROWNUM FROM DBA_OBJECTS WHERE ROWNUM <= 5000;
COMMIT;

CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
(PARTITION P1 VALUES LESS THAN (TO_DATE('2005-9-1', 'YYYY-MM-DD')),
PARTITION P2 VALUES LESS THAN (MAXVALUE));

ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T;

RENAME T TO T_OLD;

RENAME T_NEW TO T;

优点：只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。如果对数据在分区中的分布没有进一步要求的话，实现比较简单。在执行完RENAME操作后，可以检查T_OLD中是否存在数据，如果存在的话，直接将这些数据插入到T中，可以保证对T插入的操作不会丢失。

不足：仍然存在一致性问题，交换分区之后RENAME T_NEW TO T之前，查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中，则需要进行分区的SPLIT操作，会增加操作的复杂度，效率也会降低。

适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。

方法三：Oracle9i以上版本，利用在线重定义功能

Drop table t;
CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
INSERT INTO T
SELECT ROWNUM, SYSDATE - ROWNUM FROM DBA_OBJECTS WHERE ROWNUM <= 5000;
COMMIT;

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T');

PL/SQL 过程已成功完成。

CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
(PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')),
PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),
PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),
PARTITION P4 VALUES LESS THAN (MAXVALUE));

表已创建。

EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T', 'T_NEW');

PL/SQL 过程已成功完成。

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'T', 'T_NEW');

PL/SQL 过程已成功完成。

SELECT COUNT(*) FROM T;

COUNT(*)
----------
5000

SELECT COUNT(*) FROM T PARTITION (P3);

COUNT(*)
----------
1096

优点：保证数据的一致性，在大部分时间内，表T都可以正常进行DML操作。只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。

不足：实现上比上面两种略显复杂。

适用于各种情况。

这里只给出了在线重定义表的一个最简单的例子，详细的描述和例子可以参考下面两篇文章。

Oracle的在线重定义表功能：http://blog.itpub.net/post/468/12855

Oracle的在线重定义表功能（二）：http://blog.itpub.net/post/468/12962

XSB:把一个已存在数据的大表改成分区表：

第一种（表不是太大）：

1.把原表改名：
rename xsb1 to xsb2;
2.创建分区表：
CREATE TABLE xsb1
PARTITION BY LIST (c_test)
(PARTITION xsb1_p1 VALUES (1),
PARTITION xsb1_p2 VALUES (2),
PARTITION xsb1_p0 VALUES (default))
nologging AS SELECT * FROM xsb2;
3.将原表上的触发器、主键、索引等应用到分区表上；
4.删除原表：
drop table xsb2;

第二种(表很大)：

1. 创建分区表：
CREATE TABLE x PARTITION BY LIST (c_test) [range ()]
(PARTITION p0 VALUES [less than ](1) tablespace tbs1,
PARTITION p2 VALUES (2) tablespace tbs1,
PARTITION xsb1_p0 VALUES ([maxvalue]default))
AS SELECT * FROM xsb2 [where 1=2];

2. 交换分区 alter table x exchange partition p0 with table bsvcbusrundatald ;

3. 原表改名alter table bsvcbusrundatald rename to x0;

4. 新表改名alter table x rename to bsvcbusrundatald ;

5. 删除原表drop table x0;

6. 创建新表触发器和索引create index ind_busrundata_lp on bsvcbusrundatald(。。。) local tablespace tbs_brd_ind ;

或者：

1. 规划原大表中数据分区的界限，原则上将原表中近期少量数据复制至另一表；

2. 暂停原大表中的相关触发器；

3. 删除原大表中近期数据；

4. 改名原大表名称；

5. 创建分区表；

6. 交换分区；

7. 重建相关索引及触发器（先删除之再重建）.

参考脚本：

select count(*) from t1 where recdate>sysdate-2

create table x2 nologging as select * from t1 where recdate>trunc(sysdate-2)

alter triger trg_t1 disable

delete t1 where recdate>sysdate-2

commit

rename t1 to x1

create table t1 [nologging] partition by range(recdate)

(partition pbefore values less than (trunc(sysdate-2)),

partition pmax values less than (maxvalue))

as select * from x1 where 1=2

alter table t1 exchange partition pbefore with table x1

alter table t1 exchange partition pmax with table x2

drop table x2

[重建触发器]

drop table x1

1.1.4 参考材料：
如果表中预期的数据量较大，通常都需要考虑使用分区表，确定使用分区表后，还要确定什么类型的分区 （range partition、hash partition、list partition等）、分区区间大小等。分区的创建最好与程序有某种默契，偶曾经创建分区表，按自然月份定义分区的，但程序却在查询时默认的开始时间与 结束时间是：当前日期-30至当前日期，比如当天是9.18号，那查询条件被产生为8.18-9.18，结果分区后并不没有大幅提高性能，后来对程序的查 询日期做了调整，按自然月查询，系统的负载小了很多。

从Oracle8.0开始支持表分区（MSSQL2005开始支持表分区）。

Oracle9i 分区能够提高许多应用程序的可管理性、性能与可用性。分区可以将表、索引及索引编排表进一步划分，从而可以更精细地对这些数据库对象进行管理和访问。 Oracle 提供了种类繁多的分区方案以满足所有的业务需要。另外，由于在 SQL 语句中是完全透明的，所以分区可以用于几乎所有的应用程序。

分区表允许将数据分成被称为分区甚至子分区的更小的更好管理的块。索引也可以这么分区。每个分区可以被单独管理，可以不依赖于其他分区而单独发挥作用，因此提供了一个更有利于可用性和性能的结构。

分区可以提高可管理性、性能与可用性，从而给各种各样的应用程序带来极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外，分区 还能够在很大程度上简化日常管理任务。分区还使数据库设计人员和管理员能够解决尖端应用程序带来的最难的问题。分区是建立上亿万字节数据系统或需要极高可 用性系统的关键工具。

在多CPU配置环境下，如果打算使用并行执行，则分区提供了另一种并行的方法。通过给表或索引的不同分区分配不同的并行执行服务器，就可以并行执行对分区表和分区索引的操作。

表或索引的分区和子分区都共享相同的逻辑属性。例如表的所有分区或子分区共享相同的列和约束定义，一个索引的分区或子分区共享相同的索引选项。然而它们可以具有不同的物理属性如表空间。

尽管不需要将表或索引的每个分区或子分区放在不同的表空间，但这样做更好。将分区存储到不同的表空间能够

l 减少数据在多个分区中冲突的可能性

l 可以单独备份和恢复每个分区

l 控制分区与磁盘驱动器之间的映射对平衡I/O 负载是重要的

l 改善可管理性可用性和性能

分区操作对现存的应用和运行在分区表上的标准DML 语句来说是透明的。但是可以通过在DML 中使用分区扩展表或索引的名字来对应用编程，使其利用分区的优点。

可以使用SQL*Loader、Import 和Export 工具来装载或卸载分区表中的数据。这些工具都是支持分区和子分区的。

分区的方法Oracle9i 提供了如下5种分区方法：

l 范围分区Range

l 散列分区Hash

l 列表分区List

l 组合范围-散列分区Range-Hash

l 组合范围-列表分区Range-List

可对索引和表分区。全局索引只能按范围分区，但可以将其定义在任何类型的分区或非分区表上。通常全局索引比局部索引需要更多的维护。

一般组建局部索引，以便反映其基础表的结构。它与基础表是等同分区的，即它与基础

表在同样的列上分区，创建同样数量的分区或子分区，设置与基础表相对应的同样的分区边界。对局部索引而言，当维护活动影响分区时，会自动维护索引分区。这保证了索引与基础表之间的等同分区。

关于范围分区Range：

要想将行映射到基于列值范围的分区，就使用范围分区方法。当数据可以被划分成逻辑范围时如年度中的月份，这种类型的分区就有用了。当数据在整个范围中能被均等地划分时性能最好。如果靠范围的分区会由于不均等的划分而导致分区在大小上明显不同时，就需要考虑其他的分区方法。

关于散列分区Hash：

如果数据不那么容易进行范围分区，但为了性能和管理的原因又想分区时，就使用散列分区方法。散列分区提供了一种在指定数量的分区中均等地划分数据的 方法。基于分区键的散列值将行映射到分区中。创建和使用散列分区会给你提供了一种很灵活的放置数据的方法，因为你可以通过在I/O 驱动器之间播撒(摘掉)这些均等定量的分区，来影响可用性和性能。

关于列表分区List：

当你需要明确地控制如何将行映射到分区时，就使用列表分区方法。可以在每个分区的描述中为该分区列指定一列离散值，这不同于范围分区，在那里一个范 围与一个分区相关，这也不同于散列分区，在那里用户不能控制如何将行映射到分区。列表分区方法是特意为遵从离散值的模块化数据划分而设计的。范围分区或散 列分区不那么容易做到这一点。进一步说列表分区可以非常自然地将无序的和不相关的数据集进行分组和组织到一起。

与范围分区和散列分区所不同，列表分区不支持多列分区。如果要将表按列分区，那么分区键就只能由表的一个单独的列组成，然而可以用范围分区或散列分区方法进行分区的所有的列,都可以用列表分区方法进行分区。

关于组合范围-散列分区：

范围和散列技术的组合，首先对表进行范围分区，然后用散列技术对每个范围分区再次分区。给定的范围分区的所有子分区加在一起表示数据的逻辑子集。

关于组合范围-列表分区：

范围和列表技术的组合，首先对表进行范围分区，然后用列表技术对每个范围分区再次分区。与组合范围-散列分区不同的是，每个子分区的所有内容表示数据的逻辑子集，由适当的范围和列表分区设置来描述。

创建或更改分区表时可以指定行移动子句，即ENABLE ROW MOVEMENT 或者DISABLE ROW MOVEMENT ，当其键被更改时，该子句启用或停用将行迁移到一个新的分区。默认值为DISABLE ROW MOVEMENT。本产品(项目)使用ENABLE ROW MOVEMENT子句。

分区技术能够提高数据库的可管理性：

使用分区技术，维护操作可集中于表的特定部分。例如，数据库管理员可以只对表的一部分做备份，而不必对整个表做备份。对整个数据库对象的维护操作，可以在每个分区的基础上进行，从而将维护工作分解成更容易管理的小块。

分区技术提高可管理性的一个典型用法是支持数据仓库中的‘滚动视窗’加载进程。假设数据库管理员每周向表中加载新数据。该表可以是范围分区，以便每 个分区包含一周的数据。加载进程只是简单地添加新的分区。添加一个新分区的操作比修改整个表效率高很多，因为数据库管理员不需要修改任何其他分区。从分区 后的表中去除数据也是一样。你只要用一个很简便快捷的数据字典操作删掉一个分区，而不必发出使用大量资源和调动所有要删除的数据的 ‘DELETE’ 命令。

分区技术能够提高数据库的性能:

由于减少了所检查或操作的数据数量，同时允许并行执行，Oracle9i 的分区功能提供了性能上的优势。这些性能包括：

l 分区修整：分区修整是用分区技术提高性能的最简单最有价值的手段。分区修整常常能够将查询性能提高几个数量级。例如，假定应用程序中有包含定单历史记录的 定单表，该表用周进行了分区。查询一周的定单只需访问该定单表的一个分区。如果该定单表包含两年的历史记录，这个查询只需要访问一个而不是一百零四个分 区。该查询的执行速度因为分区修整而有可能快一百倍。分区修整能与所有其他 Oracle 性能特性协作。Oracle 公司将把分区修整技术与索引技术、连结技术和并行访问方法一起联合使用。

l 分区智能联接：分区功能可以通过称为分区智能联接的技术提高多表联接的性能。当两个表要联接在一起，而且每个表都用联接关键字来分区时，就可以使用分区智 能联接。分区智能联接将大型联接分解成较小的发生在各个分区间的联接，从而用较少的时间完成全部联接。这就给串行和并行的执行都能带来显著的性能改善。

l 更新和删除的并行执行：分区功能能够无限地并行执行 UPDATE、DELETE 与 MERGE 语句。当访问分区或未分区的数据库对象时Oracle 将并行处理 SELECT 与 INSERT 语句。当不使用位图索引时，也可以对分区或未分区的数据库对象并行处理 UPDATE、DELETE 和 MERGE 语句。为了对有位图索引的对象并行处理那些操作，目标表必须先分区。这些 SQL 语句的并行执行可以大大提高性能，特别是提高 UPDATE 与 DELETE 或 MERGE 操作涉及大量数据时的性能。

分区技术提高可用性：

分区的数据库对象具有分区独立性。该分区独立性特点可能是高可用性战略的一个重要部分，例如，如果分区表的分区不能用，但该表的所有其他分区仍然保 持在线并可用。那么这个应用程序可以继续针对该分区表执行查询和事务处理，只要不是访问那个不可用的分区，数据库操作仍然能够成功运行。 数据库管理员可以指定各分区存放在不同的表空间里，从而让管理员独立于其它表分区针对每个分区进行备份与恢复操作。 还有，分区功能可以减少计划停机时间。性能由于分区功能得到了改善，使数据库管理员在相对较小的批处理窗口完成大型数据库对象的维护工作。

Partition table 實作筆記 (INV的 MTL_MATERIAL_TRANSACTIONS)

摘自：網路

在 Oracle ERP 裡頭，我們發現 Inventory 的 table 有幾個很大，
以我們的環境，MTL_MATERIAL_TRANSACTIONS 大約是 14GB

因此，有沒有可能透過 partition 來檢驗這樣可以對 performance 有幫助？
如果有幫助，那這樣可以不用花費多餘的成本就能對 Database performance 作有效的控制獲改善。


在作之前，
先跟 Application Team 同事索取一段測試的 SQL statement
作為 performance 檢驗，
為了避免誤差，
我將這段 SQL 寫成 script ，放在 crontab 連續排程多次測試共 80多次，取得平均值後

整理如下：

1. explain plan 的 cost 有些微改變，但 index 的使用對象(MTL_MATERIAL_TRANSACTIONS_N8)仍不變，
這也顯示不影響原本的執行計畫

2.在 index 跟 table partition size 與分割前有滿大的空間不同
以 index MTL_MATERIAL_TRANSACTIONS_N8為例，
分割前空間為 1593MB，
經過分割跟 compress 後，各 partition 空間總和為原先的一半
1 select owner,segment_name,segment_type,extents ,bytes/1024/1024 "MB"from dba_segments
2* where segment_name like 'MTL_MATERIAL_TRANSACTIONS_N8'
SQL> /
e
OWNER SEGMENT_NAME SEGMENT_TYPE EXTENTS MB
------ -------------------------------------- ------------------ ---------- ----------
INV MTL_MATERIAL_TRANSACTIONS_N8 INDEX 1593 1593
GOBO MTL_MATERIAL_TRANSACTIONS_N8 INDEX PARTITION 33 330
GOBO MTL_MATERIAL_TRANSACTIONS_N8 INDEX PARTITION 18 180
GOBO MTL_MATERIAL_TRANSACTIONS_N8 INDEX PARTITION 21 210
GOBO MTL_MATERIAL_TRANSACTIONS_N8 INDEX PARTITION 8 80
GOBO MTL_MATERIAL_TRANSACTIONS_N8 INDEX PARTITION 1 10



3.經過三天定時排程後，將執行時間統計如下表，經過 partition 後，
執行時間約可增進 15~20%的執行效能

select partition "partition", queryrange1, queryrange2,round(avg(Run)/60,2) "Avg Mins" , count(1) "Testing Counts"
from
(select partition, queryrange1, queryrange2,60*to_number(substr(runtime,4,2)) + to_number(substr(runtime,7,2))+ to_number(substr(runtime,9,3)) Run
from inv_parti_perf_chk ) group by partition, queryrange1, queryrange2

partition QUERYRANGE1 QUERYRANGE2 Avg Mins Testing Counts
---------- -------------- -------------- ---------- --------------
n 200701 200706 8.65 5
n 200704 200705 1.99 1
n 200801 200804 4.74 9
y 200701 200706 8.74 4
y 200801 200804 4.13 7




## 建立 partition 的語法
我是把 partition 建立在另外一個 schema
如果真的要在正式環境的話，
那有個方式可以透過 rename table 的方式，
再重新建立 partition table
CREATE TABLE MY.MTL_MATERIAL_TRANSACTIONS
PARTITION BY RANGE (TRANSACTION_DATE)
(
PARTITION TRANSACTION_2005
VALUES LESS THAN ( TO_DATE('01-jan-2006','dd-mon-yyyy')) TABLESPACE GU_PARTITION_TS,
PARTITION TRANSACTION_2006
VALUES LESS THAN ( TO_DATE('01-jan-2007','dd-mon-yyyy')) TABLESPACE GU_PARTITION_TS,
PARTITION TRANSACTION_2007
VALUES LESS THAN ( TO_DATE('01-jan-2008','dd-mon-yyyy')) TABLESPACE GU_PARTITION_TS,
PARTITION TRANSACTION_2008
VALUES LESS THAN ( TO_DATE('01-jan-2009','dd-mon-yyyy')) TABLESPACE GU_PARTITION_TS,
PARTITION TRANSACTION_new
VALUES LESS THAN (MAXVALUE) TABLESPACE GU_PARTITION_TS )
as select * from inv.MTL_MATERIAL_TRANSACTIONS where 1=2;



## 建立 local index 的一例
在 global index 與 local index的選擇上，
因為 global index 還得另外 create index partition
那 local index 剛好有 key 值可以套用在 table partition
所以，我試著用 local index 並 加上 compress 參數

CREATE INDEX GOBO.MTL_MATERIAL_TRANSACTIONS_N8 ON GOBO.MTL_MATERIAL_TRANSACTIONS
(TRANSACTION_SOURCE_TYPE_ID, ORGANIZATION_ID, TRANSACTION_DATE, TRANSACTION_SOURCE_NAME)
NOLOGGING
NOLOGGING local compress;


## Gather Statistics
建立好了 partition table /index 記得跑一次 gather statistics
exec fnd_stats.gather_table_stats('MY','MTL_MATERIAL_TRANSACTIONS',granularity=>'ALL');