Posts

ORA-01552: cannot use system rollback segment for non-system tablespace ‘TEMP_NEW’

-
ORA-01552: cannot use system rollback segment for non-system tablespace ‘TEMP_NEW’ I faced the above error while creating the explain plan for one of the table in database as shown below Create an explain plan for analysing to create an index duration & size SQL> explain plan for create index idx_tpay_recon_C_OCCURDATE on PAYMENT_NEW.T_REPORT(C_OCCURDATE); explain plan for create index idx_tpay_recon_C_OCCURDATE on PAYMENT_NEW.T_REPORT(C_OCCURDATE) * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP_NEW' Check the status of undo tablespace SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string MANUAL undo_retention integer 900 undo_tablespace string UNDOTBS1 SQL> alter system set undo_management=auto scope=spfile; System altered
1 comment
Read more

ORA-01940: cannot drop a user that is currently connected

-
How to drop the user who is connected to database by force Normally if you try to drop the user by using the sqlplus, it shows the below error: SQL> drop user mir cascade; ORA-01940: cannot drop a user that is currently connected If you are sure to drop this user, Verify by yourself & crosscheck with assign developer users. Once confirm, Fallow below procedure Syntax: Find the session for the user/schema select sid,serial# from v$session where username = '<user_schema>'; Example: SQL> select sid,serial# from v$session where username = 'MIR';        SID    SERIAL# ---------- ----------         41      13745 Syntax: To kill the user session alter system kill session '<sid>,<serial#>'; Example: SQL> alter system kill session ’41,13745’; System altered. Now Drop the user: SQL> drop user  mir cascade; User dropped. -------------------------------------He
Post a Comment
Read more

Please find the Quick General query as per your requirement - Tested & Verified

-
Please find the Quick General query as per your requirement Find the status of the database SQL> select status from v$instance; STATUS ------------ OPEN SQL> select database_status from v$instance; DATABASE_STATUS ----------------- ACTIVE Or – To get the complete details of DB SQL> select * from v$instance; INSTANCE_NUMBER INSTANCE_NAME --------------- ---------------- HOST_NAME ---------------------------------------------------------------- VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT ----------------- --------- ------------ --- ---------- ------- --------------- LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO ---------- --- ----------------- ------------------ --------- ---               1 testdb testdb.local 11.2.0.4.0        17-AUG-17 OPEN         NO           1 STARTED ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO Find the uptim
1 comment
Read more