[ oracle ] datapump 101

ตัวอย่างด้านล่างเอามาจาก oracleinth

1 การ Export/Import แบบเจาะจงตาราง

expdp user/password@SID tables=table_name directory=_directory_ dumpfile=filename.dmp logfile=logname.log

impdp user/password@SID tables=table_name directory=_directory_ dumpfile=filename.dmp logfile=logname.log

2 การ Export/Import แบบเจาะจง Schema

expdp user/password@SID schemas=schema_name directory=_directory_ dumpfile=filename.dmp logfile=logname.log

impdp user/password@SID schemas=schema_name directory=_directory_ dumpfile=filename.dmp logfile=logname.log

3 การ Export/Import แบบทั้ง Database (แบบ Full)

User ที่จะทำการ Export/Import แบบ Full ได้นั้นจะต้องเป็น User ที่มีสิทธิ์ในการ Export/Import Full Database เท่านั้น ในที่นี้ยกตัวอย่างเป็น User: Sys Password: manager1 และพารามิเตอร์ full จะเป็นตัวบอกว่าจะทำการ Export/Import แบบ Full

expdp sys/manager1@SID full=Y directory=_directory_ dumpfile=filename.dmp logfile=logname.log

impdp sys/manager1@SID full=Y directory=_directory_ dumpfile=filename.dmp logfile=logname.log

โน๊ต เพิ่มเติมน้ะจ้ะ

ที่อยากทำคือ export หมดทั้ง schema

expdp system/system_password_naja@XE schemas=schema_name_naja directory=DATA_PUMP_DIR dumpfile=filename_naja.dmp logfile=filename_naja.log

– ตอนแรก ไปใส่ directory ที่สร้างไว้ใน DIRECTORY ของ system แต่ดันเจอ error

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39145: directory object parameter must be specified and non-null

– ตอนแรกสร้าง Directory Name ต่อไปพอไปใส่ Database Server Directory ด้วยเออเร่อ เปลี่ยนเป็น

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

ก้อดูเหมือนไม่ได้ช่วยอะไรมาก

สุดท้ายเลยเปลี่ยนไปใช้ DATA_PUMP_DIR แทนที่เป็น dir ที่สร้างขึ้น ซึ่งเราสามารถดู path ว่าแม่มไปเก็บไว้ที่ไหนได้จาก Query นี้

select directory_name, directory_path from dba_directories where directory_name like '%DATA_PUMP%';

path ของเราอยู่ที่ C:\oraclexe\app\oracle\admin\xe\dpdump\ น้ะจ้ะ

– ตอน import กลับ ทำเหมือนตอน export นี่แหละ ไม่มีปัญหามาก แต่ package หรือ function ที่ error ตั้งแต่แรก ที่ export มาอยู่แล้ว

oracle จะ แจ้ง error เป็น

ORA-39082: Object type PACKAGE_BODY:"schema_name_naja"."package_name_naja" created with compilation warnings

– เวงกรำ ดันไปเจอก้อน ที่ exp มา ( ไม่ใช่ expdp )

ORA-39143: dump file "filename_naja.DMP" may be
an original export dump file

เลยต้องใช้นี่แทน

imp username_naja/userpassword_naja file=D:\oraclexe\app\oracle\admin\XE\dpdump\DATABASENAME.DMP log=SEISANK.log full=y

แต่ก็ไม่วายเจอปัญหา

IMP-00017: following statement failed with ORACLE error 2304:
"CREATE TYPE "TYPENAME_NAJA" TIMESTAMP '2013-09-10:13:36:12' OID 'E600FB99B91976"
"0EE04011ACA8A85B3F'   AS OBJECT"
"("
"     bla_bla_bla                   VARCHAR2(2)"
");"
IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal

มีคน แนะนำให้ใช้ option TOID_NOVALIDATE
แต่ใช้แล้วมันก้อยังเหมือนเดิม
แมนนวลเอาจาก log ไป สร้างเองแม่ม

ถ้าเจอปัญหา

IMP-00013: only a DBA can import a file exported by another DBA

IMP-00000: Import terminated unsuccessfully

มีคนบอกให้ grant import full database/export full database ให้ user แต่เราเองทำแล้วไม่หาย

เลยให้ sysdba import ให้แม่ม

imp system/system_pwd_naja fromuser=the_exported_user touser=the_user_we_want_to_put_stuff_in
t file=D:\oraclexe\app\oracle\admin\XE\dpdump\filename_naja.dmp log=logname_ja.log

Tips from Robert Merkwürdigeliebe : to show the version of export server, or the encoding of the export/import server

#>imp username/password file=test.dmp show=y

Import: Release 10.2.0.3.0 - Production on Fri Nov 26 08:38:47 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)

ปัญหา encoding ที่ import done in AL32UTF8 ( UTF-8 )  แต่ export using JA16SJISTILDE ( Shift-JIS )

บน windows แก้โดยการเซต NLS_LANG ให้เหมือนกับ ที่ export มา

set NLS_LANG=JAPANESE_JAPAN.JA16SJISTILDE

NOTE :

oracle default user privilege naja

User Tab
Default Tablespace : USERS
Temporary Tablespace : TEMP

Roles Tab
Connect granted     default
Resource granted    default

System Privileges Tab
Create synonym    granted
Create view       granted
Debug any procedure granted
Debug connect session granted
Execute any procedure granted
Insert any table   granted
Select any table   granted
Unlimited tablespace granted
Update any table   granted 

[ oracle ] remote debugging

add debug privileges

remote debug

1. set up remote debugger `Listener` in `SQL Developer`

Right Click at connection > Remote Debug..

NOTE : use the real IP address, not localhost, not 127.0.0.1.

2. connect to the Listener that already created in 1)

For example, in the package, put the line

 DBMS_DEBUG_JDWP.CONNECT_TCP('192.168.XX.XX',4000);

If connection is success, the console will be like this.

Now, we can set the breakpoint, add the watch parameter ( right click at parameter > watch ), or even modify parameter on-the-fly ( right click at the watch console > modify value ).

NOTE :

if you don’t want to face

_MSG1=ORA-30677: session is already connected to a debugger, _STATUS=S} 

disconnect before connect by using the code below

 IF DBMS_DEBUG_JDWP.CURRENT_SESSION_ID IS NOT NULL THEN
DBMS_DEBUG_JDWP.DISCONNECT();
END IF;
DBMS_DEBUG_JDWP.CONNECT_TCP('192.168.XX.XX',4000);

ref : barrymcgillin,

thatjeffsmith,

ttlnews

changing webapp classpath loading order in junit

We have a legacy class that override the class from the jar file.

That override works well in webapp, but not in Junit. This is the Junit solution.

The load order of tomcat from ziggy

Bootstrap classes of your JVM
System class loader classes (described above)
/WEB-INF/classes of your web application
/WEB-INF/lib/*.jar of your web application
$CATALINA_HOME/common/classes
$CATALINA_HOME/common/endorsed/*.jar
$CATALINA_HOME/common/i18n/*.jar
$CATALINA_HOME/common/lib/*.jar
$CATALINA_BASE/shared/classes
$CATALINA_BASE/shared/lib/*.jar

Therefore, in Junit, we set the order of classpath correctly, and the problem gone.

java get table list and package arguments from oracle

static Connection conn;
 static Statement st;

public static void main(String args[]) throws Exception{
 DatabaseMetaData md = conn.getMetaData();
 ResultSet rs = md.getTables(null, null, "%", null);
 while (rs.next()) {
 System.out.println(rs.getString(3)); // list table name
 }

String procedureCat = "XXX0000"; // package name
 String procedureSchem = "USERNAMENAJA";
 String procedureName = "CALL";  // function

rs = md.getProcedureColumns(procedureCat, procedureSchem, procedureName, null);
  while (rs.next()) {
System.out.println("----");
System.out.println("column name: "+rs.getObject(4)); // column name
System.out.println("data type: "+new Integer(rs.getInt(6))); // data type
System.out.println("columnReturnTypeName "+rs.getString(7)); // column return type name
int dbColumnReturn = rs.getInt(5);
String procReturn = "";
System.out.println("column return type: "+dbColumnReturn); // column type
switch(dbColumnReturn)
{
case DatabaseMetaData.procedureColumnIn:
procReturn = "In";
break;
case DatabaseMetaData.procedureColumnOut:
procReturn = "Out";
break;
case DatabaseMetaData.procedureColumnInOut:
procReturn = "In/Out";
break;
case DatabaseMetaData.procedureColumnReturn:
procReturn = "return value";
break;
case DatabaseMetaData.procedureColumnResult:
procReturn = "return ResultSet";
default:
procReturn = "Unknown";
}
System.out.println("column return : " + procReturn);
}
 }

static {
 try {
 // Step 1: Load the JDBC driver.
 Class.forName("oracle.jdbc.driver.OracleDriver");
 System.out.println("Driver Loaded.");
 // Step 2: Establish the connection to the database.
 String url = "jdbc:oracle:thin:@192.168.XX.XX:1521:XE";

conn = DriverManager.getConnection(url, "username", "passwordnaja");
 System.out.println("Got Connection.");
<pre>st = conn.createStatement();
 } catch (Exception e) {
 System.err.println("Got an exception! ");
 e.printStackTrace();
 System.exit(0);
 }
 }

RESULT OF get function args

column name: null
data type: 1111
CLOB
column return type: 5
column return : return value
----
column name: IN_FUNC_NAME
data type: 12
VARCHAR2
column return type: 1
column return : In
----
column name: IN_PARAMS
data type: 12
VARCHAR2
column return type: 1
column return : In

NOTE :

1. data type(java.sql.Types)

1111 = Types.OTHER

2005 = Types.CLOB

12 = Types.VARCHAR

2. Reference for each col in getProcedureColumns() resultset

String procedureCatalog     = rs.getString(1);
String procedureSchema      = rs.getString(2);
String procedureName        = rs.getString(3);
String columnName           = rs.getString(4);
short  columnReturn         = rs.getShort(5);
int    columnDataType       = rs.getInt(6);
String columnReturnTypeName = rs.getString(7);
int    columnPrecision      = rs.getInt(8);
int    columnByteLength     = rs.getInt(9);
short  columnScale          = rs.getShort(10);
short  columnRadix          = rs.getShort(11);
short  columnNullable       = rs.getShort(12);
String columnRemarks        = rs.getString(13);

3. NOTE that, if package function return CLOB, java will return as Types.OTHER (1111 like above output ).

If we use Types.OTHER in ps.registerOutParameter(), we will face the following error.

org.seasar.framework.exception.SQLRuntimeException: [ESSR0072]SQLException(SQL=[], Message=[Invalid column type], ErrorCode=17004, SQLState=null)

We must pass Types.CLOB into ps.registerOutParameter(), not Types.OTHER.

ref : java2s, mkyong, jguru, Codo

[oracle] install and grant access to UTLMAIL, UTLSMTP, UTLFILE

DOS naja

C:\Users\User>cd C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin
C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin>sqlplus / as sysdba

Then

SQL> @utlmail
SQL> @utlsmtp
SQL> @utlfile
SQL> grant execute on utl_mail to username_naja;
SQL> grant execute on utl_smtp to username_naja;
SQL> grant execute on utl_file to username_naja;

ref : blogdaprima

[SOLVED] ORA-01653: unable to extend table SYS.IDL_UB2$ by 128 in tablespace SYSTEM

Problem

Error report:
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.IDL_UB2$ by 128 in tablespace SYSTEM
00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
(a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
can be corrected, do so; otherwise contact Oracle Support.

Validation

The tablespace SYSTEM is full. We can validate using the login `system` by the following SQL.

SELECT TABLESPACE_NAME
, FILE_NAME
, BYTES / 1024 / 1024 AS BYTES_MB
, AUTOEXTENSIBLE
, MAXBYTES  / 1024 / 1024 AS MAXBYTES_MB
FROM DBA_DATA_FILES;

If the `bytes` == `maxbytes`, then it is full.

Solution

This is how to extend the tablespace by manual.

ALTER DATABASE DATAFILE 'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF'
AUTOEXTEND ON NEXT 1M MAXSIZE 1024M;

Tips

How to shutdown oracle database

login sqlplus as sysdba

C : > sqlplus /  as sysdba

shutdown

SQL > shutdown immediate

or startup

SQL > startup

excel open in new program #kuayraiDDE

 

 

Excel options

 

ref : microsoft

[ oracle ] SQL developer opens multiple table tabs

Preferences