Monday, October 1, 2018

Oracle - ORACLE error 959 encountered when importing with imp


If the dump is exported with imp, you cannot use impdp and you may face issue such as


IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'XXXXXX' does not exist


Below are the steps to work around it

1) Create the user (Note: for simplicity, I had grant TESTER all privileges)

$ sqlplus sys as sysdba
SQL> drop user TESTER cascade;
User dropped.
SQL> create user TESTER identified by TESTER default tablespace USERS;
User created.
SQL> grant all privileges to TESTER;
Grant succeeded.
SQL> exit



2) Generate the index SQL file

$ imp tester/tester file=somedata.dmp log=somedata.log indexfile=somedata.sql full=y


3) Open the indexfile (somedata.sql) in a text editor that can do find and replace over an entire file, and issue the following find and replace statements IN ORDER (ignore the single quotes.. '):

* Find: 'REM'  Replace:
* Find: '"source_tablespace"' Replace: '"USERS"'
* Find: '...' Replace: 'REM ...'
* Find: 'CONNECT' Replace: 'REM CONNECT'

Save the indexfile

4) Perform the dump. But since TESTER is an admin, we need another admin account (some_admin_user) to import the dump

$ imp some_admin_user/some_admin_user_password file=somedata.dmp log=somedata.log grants=n fromuser=SOME_OTHER_USER touser=TESTER
. . importing table                "ABC"          8 rows imported
.....snip.....
IMP-00017: following statement failed with ORACLE error 959:
...... snip.............
CREATE TABLE "SOMETABLE"
...... snip.....
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'XYZ' does not exist
....snip.........
About to enable constraints...
Import terminated successfully with warnings.


You can see that one of the table (e.g SOMETABLE) has an ORA-00959: tablespace 'XYZ' does not exist

4) Locate the problematic table in the indexfile and load it into the database. This involve manual work such as looking for the create table and alter table SQL statement in the indexFile. After locating all these SQL statement, put them into an sql file (e.g some_table.sql)

Then, run the SQL against the database

$ sqlplus some_admin_user/some_admin_user_password @some_table.sql


5) Import data to specific table


$ imp  some_admin_user/some_admin_user_password file=somedata.dmp log=somedata.log grants=n fromuser=SOME_OTHER_USER touser=TESTER tables=SOMETABLE DATA_ONLY=Y
....snip.......
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. . importing table            "SOMETABLE "          10 rows imported
Import terminated successfully without warnings.


Tuesday, September 25, 2018

Hibernate - @Lob with PostgreSQL


In Java, if you declare

@Lob
@Column(name = "Remark")
protected String Remark;

Hibernate will generate the column as Text data type.

CREATE TABLE public.sometable
(
    id integer NOT NULL DEFAULT ,
    remark text COLLATE pg_catalog."default",
 )

Everything is good so far, however, when you query the database with select statement (e.g select * from sometable), it will show



In my test case, all remark column are empty string. So, what are those numbers? These numbers are the UID of the LOB for each remark. In order to view the original text, one may need to run


select id, convert_from(loread(
      lo_open(remark::int, x'40000'::int), x'40000'::int), 'UTF-8')
  AS remark from mission


Monday, August 27, 2018

Turning off overwrite/overtype mode

I had a little irritating problem today when I am typing in WhatApp Web. For some reason, the text in the textbox is always in overtype mode. I checked the INS key, the onscreen keyboard, etc... and nothing obvious to me. After some research, I finally figure out that pressing "CAP + INS" key together will turn on and off overtype mode. 

Tuesday, July 10, 2018

Tomcat - removing server info

Below are the steps to remove Tomcat Server Information

1. Make sure Tomcat is not running
2. Navigate to "Tomcat Installation"/lib
3. Backup catalina.jar
4. vi catalina.jar
5. select "org/apache/catalina/util/ServerInfo.properties" and remove the server info
6. Then, save the file and restart Tomcat

Sunday, June 3, 2018

Boostrap Validator - Reset validated field

If you work with Boostrap Validator (http://1000hz.github.io/bootstrap-validator/), sometime, you may 
want to reset validated field.

If you have added





You will need to remove has-error, has-danger and has-success.

For example

$('#from_group').removeClass('has-error has-danger has-success');

If you have added







You will need to remove glyphicon-remove and glyphicon-ok class

For example,

$('#glyphicon_span').removeClass('glyphicon-remove glyphicon-ok');

If you have added










You will need to empty the inner html

For example,

$('#error_div').html("");

Sunday, May 20, 2018

Bootstrap Validator - Submit button remain disabled even when form is valid



If you work with Boostrap Validator (http://1000hz.github.io/bootstrap-validator/), you may experience an issue when your entire form is valid, however, the submit button remains disabled. Furthermore, if you check the form feedback div via console, e.g.


console.log("danger: " + $('#submit-form').find('.has-danger').length);
console.log("error: " + $('#submit-form').find('.has-error').length);
console.log("success: " + $('#submit-form').find('.has-success').length);


all danger, error and success class could not be found.

The reason: You have disabled but required fields in your form.

To workaround it, you could set

data-validate="false"


in your field and only enable validation as required, e.g


create.click(function(event){
//enable the field
document.getElementById("name").disabled = false;
//enable data validation
$("#name").attr('data-validate', true);
// Tell validator there's a change - update and validate
form.validator('update').validator('validate');
// print data validation attribute
console.log( $("#name").attr('data-validate') );
});

Thursday, April 19, 2018

Oracle - ORA-01502: index XXX.YYYY or partition of such index is in unusable state

If you encounter an Oracle error

ORA-01502: index XXX.YYYY or partition of such index is in unusable state

You could apply the following fix at the database

1. Log into the database, for example

sqlplus sys as sysdba

2. Run the following SQL

SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;' FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE';

3. The above SQL will return multiple Alter statement which the index has to be rebuild. For example

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;'
--------------------------------------------------------------------------------
ALTER INDEX MYUSER.TABLE1 REBUILD;
ALTER INDEX MYUSER.TABLE2 REBUILD;

4. Run all of the alter statement to make the index usable again and it should fix the issue.

Reference:

1. http://dba.stackexchange.com/questions/3754/ora-01502-index-or-partition-of-such-index-is-in-usable-state-problem

Oracle - ORACLE error 959 encountered when importing with imp

If the dump is exported with imp, you cannot use impdp and you may face issue such as IMP-00003: ORACLE error 959 encountered ORA-009...