Transferring(Copy, Clone) Files(Binary, Text etc.) In Oracle

Oracle provides a wide use file processing operations.Using Oracle's supplied packages dbms_file_transfer or utl_file it can be possible to copy files.  I simply demonstrate it:


C:\>sqlplus sys/sys@XE as sysdba;
SQL*Plus: Release 10.2.0.1.0 - Production on Per Mar 15 23:31:26 2007
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> grant create any directory to hr;
Grant succeeded.
SQL> grant execute on dbms_file_transfer to hr;
Grant succeeded.
SQL> connect hr/hr@XE
Connected.
SQL> create directory ftp_in as 'c:\ftp_in';
Directory created.
SQL> create directory backup_ftp as 'c:\backup_ftp';
Directory created.
SQL> grant execute on utl_file to hr;
grant execute on utl_file to hr
                 *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> connect sys/sys as sysdba
Connected.
SQL> grant execute on utl_file to hr;
Grant succeeded.
SQL> connect hr/hr@XE
Connected.
SQL> exec dbms_file_transfer.copy_file('ftp_in','15032007.dat','backup_ftp','150
32007.backup.dat' );
BEGIN dbms_file_transfer.copy_file('ftp_in','15032007.dat','backup_ftp','1503200
7.backup.dat' ); END;
*
ERROR at line 1:
ORA-19505: failed to identify file "c:\ftp_in\15032007.dat"
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch
O/S-Error: (OS 70) Uzaktaki sunucu durdurulmu? veya halen ba?latma i?lemi
s?r?yor.
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 84
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 193
ORA-06512: at line 1

SQL>
SQL>
SQL> exec utl_file.fcopy('FTP_IN','15032007.dat','BACKUP_FTP','15032007.backup.d
at' );
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> show parameter control_files
ORA-00942: table or view does not exist

SQL> connect sys/sys as sysdba
Connected.
SQL> grant select on v_$parameter to hr
  2  ;
Grant succeeded.
SQL> connect hr/hr@XE
Connected.
SQL> show parameter control_files;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      C:\ORACLEXE\ORADATA\XE\CONTROL
                                                 .DBF
SQL> create or replace directory data_files as 'C:\ORACLEXE\ORADATA\XE';
Directory created.
SQL>
SQL> BEGIN
  2    dbms_file_transfer.copy_file('data_files',
  3                                 'CONTROL.DBF',
  4                                 'BACKUP_FTP',
  5                                 'CONTROL.BAKUP.DBF');
  6  END;
  7  /
BEGIN
*
ERROR at line 1:
ORA-19504: failed to create file "c:\backup_ftp\CONTROL.BAKUP.DBF"
ORA-27038: created file already exists
OSD-04010: option specified, file already exists
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 84
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 193
ORA-06512: at line 2

SQL> host del c:\backup_ftp\CONTROL.BAKUP.DBF;
SQL> BEGIN
  2    dbms_file_transfer.copy_file('data_files',
  3                                 'CONTROL.DBF',
  4                                 'BACKUP_FTP',
  5                                 'CONTROL.BAKUP.DBF');
  6  END;
  7  /
PL/SQL procedure successfully completed.
SQL>

Oracle Directory Objects and Solution of ORA-29280 and ORA-32001

Today i ws debugging a PL/SQL program. The program was using a log file that is created with utl_file without Oracle's Directory object by PL/SQL on server side. During debugging i got some errors and fixed them. Below i described how to solve them:

SQL> DECLARE
  2    f utl_file.file_type;
  3  BEGIN
  4    f := utl_file.FOPEN('/data01/WORK/TMP', 'drop.txt', 'w');
  5    utl_file.fclose(f);
  6  END;
  7  /
DECLARE
  f utl_file.file_type;
BEGIN
  f := utl_file.FOPEN('/data01/WORK/TMP', 'drop.txt', 'w');
  utl_file.fclose(f);
END;
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at line 4
SQL> show parameter utl_file_dir
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      /data02/TMP
SQL>
SQL> ALTER system SET utl_file_dir = '/data01/WORK7/TMP' scope = spfile;
ALTER system SET utl_file_dir = '/data01/WORK7/TMP' scope = spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

SQL> create spfile from pfile;                                             
File created.
SQL> ALTER system SET utl_file_dir = '/data01/WORK7/TMP' scope = spfile;
ALTER system SET utl_file_dir = '/data01/WORK7/TMP' scope = spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  293601280 bytes
Fixed Size                  1302824 bytes
Variable Size              94117592 bytes
Database Buffers          197132288 bytes
Redo Buffers                1048576 bytes
Database mounted.
Database opened.
SQL> ALTER system SET utl_file_dir = '/data01/WORK7/TMP' scope = spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  293601280 bytes
Fixed Size                  1302824 bytes
Variable Size              94117592 bytes
Database Buffers          197132288 bytes
Redo Buffers                1048576 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /data01/app/oracle/product/10.
                                                 1/dbs/spfiletest.ora
SQL> show parameter utl_file_dir
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      /data01/WORK7/TMP
SQL>

Oracle has Directory objects that are stored as if they were tables in database. They do not have owners. So they can not possible to query such as select * from user_directories. They can be granted to users on read and write privilleges:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL> create directory test_directory as 'C:\TMP';
create directory test_directory as 'C:\TMP'
ORA-01031: insufficient privileges
SQL> connect sys/sys@XE as sysdba
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS
SQL> grant create any directory to hr;
Grant succeeded
SQL> connect hr/hr@XE
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL> create directory test_directory as 'C:\TMP';
Directory created
SQL> select * from user_directories;
select * from user_directories
ORA-00942: table or view does not exist
SQL> select * from all_directories;
SQL> grant read on directory test_directory to u1;
Grant succeeded

SQL> SELECT t.grantor, t.grantee, t.privilege
  2    FROM all_tab_privs t
  3   WHERE t.table_name = 'TEST_DIRECTORY';
GRANTOR                        GRANTEE                        PRIVILEGE
------------------------------ ------------------------------ ----------------------------------------
HR                             U1                             READ
SYS                            HR                             READ
SYS                            HR                             WRITE
SQL>

Sequence Generator In Oracle Database Management System - Part I

Sequence is an atomic unit that provides uniqueness of table columns, especially in multiuser environments. In general, sequences are used to generate primary key values for tables. A sequence in Oracle, is equivalent to idendity columns in Microsoft SQL Server. But Oracle provides a more flexible structure with sequences. Start value, increment by value, min-max values and cachebility features are given with Sequence( a.k.a sequence generator ) in Oracle database management system.
When using sequence in Oracle it must be taken into consideration to that it may not be possible to provide consecutive sequence numbers. If business has a restriction, it will be better to use own-made sequences. Because, with cachebility of sequences for performance reason, consecutive sequence numbers may not be used.

SQL> create sequence seq_test
  2  start with 1
  3  increment by 1
  4  cache 50;

Sequence created

SQL> SELECT seq_test.nextval FROM dual;

   NEXTVAL
----------
        1

SQL> alter system flush sga;

alter system flush sga

ORA-02000: missing SHARED_POOL/BUFFER_CACHE/GLOBAL CONTEXT keyword

SQL> alter system flush SHARED_POOL;

alter system flush SHARED_POOL

ORA-01031: insufficient privileges

SQL> conn sys/0000@XE as sysdba;
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS

SQL> grant alter system to hr;

Grant succeeded

SQL>  conn hr/hr@XE
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL> SELECT seq_test.nextval FROM dual;

   NEXTVAL
----------
        2

SQL> alter system flush SHARED_POOL;

System altered

SQL> SELECT seq_test.nextval FROM dual;

   NEXTVAL
----------
        51

SQL>


On requesting a sequence number, Oracle first check its internal cache storage areas. If Oracle finds a pre-allocated sequence number, uses it. This operation do not need any I/O. So in performance metrics, this is very efficient way. If Oracle does not find, it queries its internal tables to get last value. This can be found is [dba|all|user]_sequences.LAST_NUMBER column. Oracle then uses cache degree to hold consecutive sequence numbers in memory, SGA.

SQL> SELECT cache_size, last_number
  2    FROM user_sequences
  3   WHERE sequence_name = 'SEQ_TEST';

CACHE_SIZE LAST_NUMBER
---------- -----------
        50         51

SQL>


Oracle gives an option to create cacheless sequences. This is in one respect, retrieving every sequence from disk, instead of sequence cache of SGA. In multiuser environment that uses sequences, for instance OLTP systems that has a huge number of inserts, will decrease performance. Always memory is much more faster than disk. But the advantage of this option is may  provide consecutive sequence numbers. Do not forget nocache sequences will not gurantee consecutivity because of rollbacks.

SQL> alter sequence seq_test nocache;

Sequence altered

SQL> SELECT seq_test.nextval FROM dual;

   NEXTVAL
----------
        52

SQL>
SQL> SELECT cache_size, last_number
  2    FROM user_sequences
  3   WHERE sequence_name = 'SEQ_TEST';

CACHE_SIZE LAST_NUMBER
---------- -----------
         0          53

SQL> alter system flush SHARED_POOL;

System altered

SQL> SELECT seq_test.nextval FROM dual;

   NEXTVAL
----------
        53

SQL>


The performance effects of cached sequences versus cacheless sequences are dramatic. Cache degree does not have a linear equation with performance. Choosing a suitable number will effect best performance on system.

SQL> alter sequence seq_test nocache;

Sequence altered

SQL> drop table t;

Table dropped

SQL> set timing on

SQL> create table t as select seq_test.nextval from dual connect by level < 10001;

Table created

Executed in 5,888 seconds

SQL> alter sequence seq_test cache 5;

Sequence altered

Executed in 0,01 seconds

SQL> drop table t;

Table dropped

Executed in 0,041 seconds

SQL> create table t as select seq_test.nextval from dual connect by level < 10001;

Table created

Executed in 1,392 seconds

SQL> alter sequence seq_test cache 10;

Sequence altered

Executed in 0,01 seconds

SQL> drop table t;

Table dropped

Executed in 0,04 seconds

SQL> create table t as select seq_test.nextval from dual connect by level < 10001;

Table created

Executed in 0,811 seconds

SQL> alter sequence seq_test cache 100;

Sequence altered

Executed in 0,02 seconds

SQL> drop table t;

Table dropped

Executed in 0,05 seconds

SQL> create table t as select seq_test.nextval from dual connect by level < 10001;

Table created

Executed in 0,29 seconds

SQL> alter sequence seq_test cache 10000;

Sequence altered

Executed in 0 seconds

SQL> drop table t;

Table dropped

Executed in 0,03 seconds

SQL> create table t as select seq_test.nextval from dual connect by level < 10001;

Table created

Executed in 0,211 seconds

Establishing Source Code Security With WRAP In Oracle

In Oracle you can wrap your source code, packages, functions e.g., with built-in wrap utility. Securing source code is an important responsibility for enterprise projects. It is possible to compile a wrapped package. It can not possible to see a wrapped source code with selecting all_source view or using dbms_metada built-in package.

Below i demonstrate how can wrap function with wrap utility:


create or replace function get_day( pid_Date in date ) return varchar2 is
begin
  ---For debug, conditional compilation
  $if $$debug = true $then
    dbms_output.put_line( 'INFO@get_day>Date is ' || pid_Date );
  $end
  ---
  return to_char(pid_Date, 'DAY');
end;
/

Call WRAP Utility:


C:\oraclexe\app\oracle\product\10.2.0\server\BIN>wrap iname=get_day.fnc
PL/SQL Wrapper: Release 10.2.0.1.0- Production on Pzt Oca 01 16:44:07 2007
Copyright (c) 1993, 2004, Oracle.  All rights reserved.
Processing get_day.fnc to get_day.plb
C:\oraclexe\app\oracle\product\10.2.0\server\BIN>



Content Of WRAPPED function is

create or replace function get_day wrapped
a000000
b1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
d1 fb
S0SAZ1gHfv1nRx6IXc3IMDoPw0Ewg+lKNZmsZy8C2vjVGTg4lFGE6j4Jv3cCqwETsnti5tOQ
TtPOhrqM54t/KIt/uwoA6uDaYITTWVIus7q28C9WhgOCV9O7BJwVXHnuYUvBGhrUwPGmUok7
a4lrJXi0NW5JFt70hr18Qyl2e5btnC7DV6PojoPAVaS5ho5IavYgQN7bhr3wQvn+FzqbE7RW
YswPnikV5vf5wPZ9hTsLe2BOPf0KTiFp
/

Copy Table Data Across Databases/Schemas In Oracle

Sometimes you need to copy table across databases/schemas. Oracle provides some utilities for them. We can summarize them as follow:


Trasportable Tablespaces
IMPD/EXPD utilities(Import/Export data pump)
IMP/EXP(Import/Export)
SQL*Loader
CTAS(Create Table As Select) with dblinks
SQL*Plus COPY command


In my opinion, the easiest way is using the least famous one, the last one: SQL*Plus COPY command

I demonstrate a simple example to show how to copy table u1 schema to hr schema:



SQL> copy from u1/u1@XE to hr/hr@XE create ttttt using select * from t;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table TTTTT created.

   9 rows selected from u1@XE.
   9 rows inserted into TTTTT.
   9 rows committed into TTTTT at hr@XE.

SQL> conn hr/hr@XE
Connected.
SQL> select * from ttttt;

         I
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.

SQL>

 

Solution of "ORA-01552: cannot use system rollback segment for non-system tablespace "

Today I have struggled a problem while importing a dump file to oracle 9.2.0.7 database. The problem was about "ORA-01552: cannot use system rollback segment for non-system tablespace". I investigated the problem and find a solution...

SQL> show parameter undo_management;
NAME            TYPE       VALUE
--------------- ---------- ---------
undo_management string     MANUAL


SQL> show parameter undo_tablespace;

NAME            TYPE       VALUE
--------------- ---------- ---------
undo_tablespace string    

SQL> alter system set undo_management=AUTO scope=spfile
System altered.


SQL> alter system set undo_tablespace=UNDOTBS1 scope=spfile;
System altered.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup
ORACLE instance started.
Total System Global Area 253202360 bytes
Fixed Size 733112 bytes
Variable Size 218103808 bytes
Database Buffers 33554432 bytes
Redo Buffers 811008 bytes
Database mounted.
Database opened.

  SQL> show parameter undo_management;
NAME            TYPE       VALUE
--------------- ---------- ---------
undo_management string     AUTO


SQL> show parameter undo_tablespace;

NAME            TYPE       VALUE
--------------- ---------- ---------
undo_tablespace string     UNDOTBS1

Solution Of "ORA-25153: Temporary Tablespace is Empty"

Today I have struggled a problem while importing a dump file to oracle 9.2.0.7 database. The problem was about "ORA-25153: Temporary Tablespace is Empty". I investigated the problem and find a solution...
Accidentally creating two temporary tablespace(TEMP and TMP), making first one default(TEMP),without having any datafiles on it(TEMP). Dropping first one making default second one solved the problem.

SQL> SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'TEMPORARY';
TABLESPACE_NAME
------------------
TEMP
TMP


SQL> SELECT property_value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
-----------------
TEMP


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TMP;
Database altered.


SQL> SELECT property_value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
-------------------
TMP


SQL> SELECT file_name, tablespace_name FROM DBA_TEMP_FILES;
FILE_NAME                         TABLESPACE_NAME
--------------------------------- -------------------------
/data07/oradata/ashp02/tmp01.dbf  TMP


SQL> drop tablespace TEMP;
Tablespace dropped.


SQL> SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'TEMPORARY';
TABLESPACE_NAME
-----------------
TMP


SQL>

Solution Of "ORA-01658: unable to create INITIAL extent for segment in tablespace"

Today I have struggled a problem while importing a dump file to oracle 9.2.0.7 database. The problem was about "ORA-01658: unable to create INITIAL extent for segment in tablespace". I investigated the problem and find a solution...

One tablespace was created with following command:
"CREATE TABLESPACE DATA BLOCKSIZE 8192
DATAFILE '/data07/oradata/pd02/data01.dbf' SIZE 10000M
AUTOEXTEND ON NEXT 25000M MAXSIZE 32767M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
ONLINE PERMANENT NOLOGGING SEGMENT
SPACE MANAGEMENT AUTO;"
So, when tablespace needs a storage, it will try to allocate 25000M. When it could not allocate(for instance no space in device), it will cause above error.
Workaround may be creating manually datafiles with smaller sizes such as:
"ALTER TABLESPACE DATA
ADD DATAFILE '/data07/oradata/pd02/data02.dbf'
SIZE 3000M AUTOEXTEND ON;"