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 →