Solution For ORA-28001 AND ORA-28002 ORACLE DATABASE ERROR

PROBLEM :

When you working on Oracle you may get this issue that Your account may locked, or password is expired.

To solve this problem here is the solution, it may take less than a minute. Oracle give you 28001 OR 28002 error code.

SOLUTION :

Connect as SYS AS SYSDBA user.

For example my “SCOTT” user is locked and password is expired for  “SCOTT” user.

SQL > select username,account_status from dba_users;

It will so you list of all user and status of the user’s account.

Now my scott account is “EXPIRED & LOCKED”

First of all UNLOCK the user account.

SQL> ALTER USER scott ACCOUNT UNLOCK;

Now again execute previous query.

SQL > select username,account_status from dba_users;

                Now my scott account is “EXPIRED”

Secondly, execute the following query.

SQL>  select 'alter user "'||d.username||'" identified by values '''||u.password||''';' c

           from dba_users d, sys.user$ u

           where d.username = upper('&&username')

           and u.user# = d.user_id;

                It will give you update query. Execute that update query.

SQL > alter user "SCOTT" identified by values 'F894844C34402B67';

That’s all your work is done.

Now check the status of the scott user.

SQL > select username,account_status from dba_users;

                Now my scott account is “OPEN”

IF YOU HAVE ANY OTHER SOLUTION THAN PLEASE COMMENT IT.

ORACLE ORA-00600 ERROR SOLUTION

Some time we get a error from oracle, ORA-00600, and here is the solution for that.

PROBLEM :

ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[260], [12510], [12578], [], [], [], [], [], [], []


SOLUTION :

SQL> conn
SQL>sys as sysdba
SQL>password

SQL> Startup mount;

SQL>Show parameter control_files;

             Will give the list of the control files with full path of the files.

SQL>SELECT a.member,a.group#,b.status FROM v$logfile a, v$log b WHERE a.group#=b.group# AND b.status = 'CURRENT';

    Will give redo log file not down name of the redolog file with full path.

SQL>recover database using backup controlfile until cancel;

    Write the full Path of redolog file when asked.
    And hit enter.
    Will Give message "Log Applied. Media recovery complete."
   
SQL>alter database open resetlogs;
   
    Will give message "Database altered."

Now connect as your local user.

















IF YOU HAVE ANOTHER WAY TO SOLVE IT PLEASE SHARE IT AS COMMENT

ORACLE SELECT RECORD BETWEEN No1. TO No2.

In some cases we have to select the records from the database table between some specific value. Like I want to select record 1 TO 10 at first time 11 TO 20 at second time like this.

For this i have a solution with just a single query:

------------------------------------------------------------------------------------------------------------
SELECT * 

FROM (SELECT ALIAS1.*, ROW_NUMBER() OVER(ORDER BY column_name) AS MYROW FROM table_name ALIAS1 WHERE ALIAS1.column_name = where_clause) 

WHERE (MYROW BETWEEN 1 AND 10);
---------------------------------------------------------------------------------------------------------------------------------

EXPLANATION:-

In this Query Inner select query will select all the record from given table_name that satisfy the Inner WHERE clause and provide row number to each record and make it as table alias MYROW. Now outer WHERE clause will select 1 to 10 record from that record and display it as final output.

IF YOU HAVE BETTER WAY TO DO THIS THEN PLEASE PROVIDE IT AS COMMENT IT WILL VERY HELPFUL.