Problem: Data got committed in another/same session, cannot update row. 

Env: Oracle Sql Developer

How to Solve:

Uncheck below option

Continue reading →

Importing into Oracle Database Table using Sqlplus command

 sqlplus sys/oracle as sysdba

run this command at sqlplus prompt->

SQL>@"/mnt/hgfs/temp/QUERY.sql"; 

and dont forget to commit it at the end.

Continue reading →

Add quotation at the start and end of each line in Notepad++

Suppose we have column names

We want to prepare them to use in  a query and need to put double qutoes around them then put comma and combine all into one line.

press Ctrl + H and check Regular expression radio button.

 Find What: (.+) 

 Replace with: "\1"

it becomes

to put , (comma) at the ends of each line

 Find What: (.+) 

 Replace with: \1,

to combine them into one line

 Find What: \r\n 

 Replace with: 

Continue reading →

Search for a particular string in Oracle clob column

select *  
from TABLE_NAME
where dbms_lob.instr(COLUMNNAME,'searchtext') > 0;

Continue reading →

Fix ORA-28001 the password has expired

sqlplus sys as sysdba

alter user [username] identified by [password] account unlock;

Continue reading →

Shortcut solutions to Repetitive Tasks

How to add a character or some string to end of each line in a list of string?

Solution: Suppose that we want to add , (comma character) at the end of each line, using Notepad++

Replace

Find What: ^(.*)$
Replace: $0,

Continue reading →

ORA-28002: the password will expire within 7 days

If you get this error for your user and want to get rid of it, first get the profile of user with this query:

SELECT PROFILE
FROM dba_users
WHERE username = 'YOUR_USER';


You can check profile parameters:
 SELECT * FROM DBA_PROFILES WHERE PROFILE = 'DEFAULT';

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180

Notice that PASSWORD_LIFE_TIME has a value. We can change it to UNLIMITED with this pl/sql query:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Now checking by running previous query we see that its done successfully.
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED

Continue reading →