Oracle RDBMS: Database Link fails with ORA-1017 & ORA-2063

If you try to create a database link between a 10g and a 11g instance. There is a good chance that you might get the following error:

ORA-01017: invalid username/password; logon denied

ORA-02063: preceding line from (database_link_name)

The reason is from Oracle 11g Release 1 the passwords are case-sensitive by default.

Example:

My target database is 11g - named TIGER

Create public database link TIGER connect to dbadmin identified by dbadmin1

using 'TIGER';

select * from v$instance@TIGER;

ORA-01017: invalid username/password; logon denied

ORA-02063: preceding line from TIGER

Solution:

1) On the originating 10g database server, drop and recreate the public database link using double quotes around the password (IDENTIFIED BY clause) for the user account on the 11g Database

Drop public database link TIGER;

Create public database link TIGER connect to dbadmin identified by "dbadmin1"

using 'TIGER';

OR

2) Use the 'SEC_CASE_SENSITIVE_LOGON' initialization parameter on the target (11g Database) to disable password case sensitivity in the destination database.

Dynamic change:

ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE SCOPE=BOTH;

PFILE change:

SEC_CASE_SENSITIVE_LOGON = FALSE

Reference: Database Link from 10g to 11g fails with ORA-1017 & ORA-2063 [ID 473716.1]

Oracle: Re-org using 'Move'

Everyone of us are familiar with the simple re-org command - alter table (table_name) move. 'Move' command can also help us migrate a table from one tablespace to another. But sometimes you might get and error:

ORA-01652: unable to extend temp segment by 8192 in tablespace (tablespace_name)

For example :

I have a huge table 'employees' which holds 1.5 GB in TS_TIGER. I re-org this table every week as the table undergoes a lot of inserts, updates and deletes. Last time when I ran the 'move' comm it failed:

alter table employees move;

ORA-01652: unable to extend temp segment by 8192 in tablespace TS_TIGER.

TS_TIGER had 1.5 GB of freespace and TEMP tablespace was not utilized either. Then why this error? The reason is Oracle uses temporary extents to build the table initially. Once the process is completed successfully, it converts the temporary segments to permanent one. This is just a dictionary update.

Oracle follows this methodology, in case the re-org fails (due to some error) , SMON would identify the temporary extents and just clean them up. The ORA-01652 error here simply means that the tablespace is short of space and you need to resize a datafile or add a datafile.