Wednesday, June 15, 2011

SQL*Plus COPY Command


The SQL*Plus COPY command can copy data between two databases via SQL*Net .  The preferred method of doing this is to use SQL*Plus on the host where the database resides.  If performing the copy command from a client SQL*Net connection, the data is transferred through the client machine.

The copy command copies data from one Oracle instance to another.   The data is simply copied directly from a source to a target.  The format of the copy command is:

SQL> copy from  <source database> TO <target database>  destination_table (column_name, column_name...) USING query

The action can include:
  • Create      If the destination table already exists, copy will report an error, otherwise the table is created and the data is copied.
  • Replace   –  If the destination table exists, copy will drop and recreate the table with the newly copied data. Otherwise, it will create the table and populate it with the data.
  • Insert     –  If the destination table exists, copy inserts the new rows into the table. Otherwise, copy reports an error and aborts.
  • Append     Inserts the data into the table if it exists, otherwise it will create the table and then insert the data.   

 SQL> copy from scott/tiger@test1  to scott/tiger@orcl create new_emp using select * from emp;

Here "test1" is remote database and "orcl" is target database where the table new_emp is created.

Once the command above is executed, the copy utility displays the values of three parameters, each of which can be set with the SQL*Plus set command.  The arraysize specifies the number of rows that SQL*Plus will retrieve from the database at one time.  The copy commit parameter specifies how often a commit is performed and is related to the number of trips – one trip is the number of rows defined in arraysize.  Finally, the long parameter displays the maximum number of characters copied for each column with a LONG datatype.

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 NEW_EMP created.

   14 rows selected from scott@test1
   14 rows inserted into NEW_EMP.
   14 rows committed into NEW_EMP at scott@orcl

SQL> desc new_emp;
  Name                                                    Null?                      Type
 ----------------------------                     ---------------               -------------------
 EMPNO                                           NOT NULL                 NUMBER(4)
 ENAME                                                                              VARCHAR2(10)
 JOB                                                                                   VARCHAR2(9)
 MGR                                                                                  NUMBER(4)
 HIREDATE                                                                        DATE
 SAL                                                                                   NUMBER(7,2)
 COMM                                                                               NUMBER(7,2)
 DEPTNO                                                                           NUMBER(2)

The command above did not specify column names for the new table (new_emp).  As a result, the new table will have the same column names as the table being copied.  If different column names are required, they can be specified after the table name:

create new_emp (col1, col2, …)

However, if one column name is specified, they all must be specified.

A DBA could perform this same function with a database link from one database pointing to another.  The appeal of the copy command is that it only requires SQL*Net service names and proper privileges to get the job done.  For those environments that restrict the usage of database links, the copy utility can be leveraged.  In addition, the copy command provides many options, as defined by the actions create, replace, insert and append.

If the copy command is executed from a client PC to copy data from remote database DB0 to remote database DB1, the data will be copied from DB0 to the client PC and then to DB1.  For this reason, it is best to use SQL*Plus from either remote host and not require the data to travel through a client machine in order to reach its final destination.

The following command copied the table_with_one_million_rows table to new_table:

SQL> copy from scott/tiger@DB0  to scott/tiger@DB1 create new_table using select * from table_with_one_million_rows;

Array fetch/bind size is 5000. (arraysize is 5000)
Will commit after every 5000 array binds. (copycommit is 5000)
Maximum long size is 80. (long is 80)
Table NEW_TABLE created.

   1000000 rows selected from scott@DB0.
   1000000 rows inserted into NEW_TABLE.
   1000000 rows committed into NEW_TABLE at scott@DB1.

 The copy command becomes handy when we have limited (SELECT) access on the source database or we do not have OS level access for the server hosting the source database.

  
Enjoy     :-)

  

New initialization parameters and Obsolete parameter in Oracle 11gR1


The best way to find new parameters is to create a database link between the two versions and run a set query using MINUS.Here are the new initialization parameter which was introduce in oracle 11gr1(11.1.0.6).

asm_preferred_read_failure_groups
client_result_cache_lag
client_result_cache_size
commit_logging
commit_wait
control_management_pack_access
db_lost_write_protect
db_securefile
db_ultra_safe
ddl_lock_timeout
diagnostic_dest
enable_ddl_logging
global_txn_processes
java_jit_enabled
ldap_directory_sysauth
memory_max_target
memory_target
optimizer_capture_sql_plan_baselines
optimizer_use_invisible_indexes
optimizer_use_pending_statistics
optimizer_use_sql_plan_baselines
parallel_io_cap_enabled
plscope_settings
redo_transport_user
resource_manager_cpu_allocation
result_cache_max_result
result_cache_max_size
result_cache_mode
result_cache_remote_expiration
sec_case_sensitive_logon
sec_max_failed_login_attempts
sec_protocol_error_further_action
sec_protocol_error_trace_action
sec_return_server_release_banner
xml_db_events

The list of obsolete parameter in oracle 11gr1(11.1.0.6) are :

_dlm_send_timeout
_fast_start_instance_recovery_target
_lm_rcv_buffer_size
_log_archive_buffer_size
ddl_wait_for_locks
logmnr_max_persistent_sessions
plsql_compiler_flags
remote_archive_enable


Enjoy    :-)


Tuesday, June 14, 2011

32-bit Oracle Database server is *not* certified on Windows x64

Once while Monitering alert logfile, i found that the below error is occuring at every 10 sec. The error is as : 
Tue Jun 07 16:19:43 2011
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
Tue Jun 07 16:19:53 2011
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
Tue Jun 07 16:20:03 2011

After googling, I find that Oracle Support states that “Running 32-bit applications on a 64-bit operating system is generally referred to as “Compatibility-Mode” by many AMD64/EM64T hardware vendors. Irrespective of this terminology, 32-bit Oracle Database server is *not* certified on Windows x64. For the 32-bit Oracle database client, check the certification matrix for more details.”

Actually the Oracle database software installs without an issue.  It is when I created the actual database that I encountered the same repeated errors in my alert.log after startup. On searching Meta-link points to Doc. ID 1060806.1 which states the cause and solution as below

Cause  :     Installed 32-bit Oracle database software on a 64-bit MS Windows OS which is not supported.

Note: For the Database software, you can ONLY install the x64 version on MS Windows (x64).  You can NOT install the 32-bit version Database software on MS Windows (x64).

Solution  :   Install 32-bit Oracle database software only on 32-bit MS Windows OS.


Enjoy    :-)