Lock Account Automatically with INACTIVE_ACCOUNT_TIME

Lock Account Automatically with  INACTIVE_ACCOUNT_TIME

Introduction

In Oracle 12.2 Release We can use the INACTIVE_ACCOUNT_TIME resource parameter in profile to automatically lock the account of a database user who has not logged in to the database instance in a specified number of days.In Production database or Testing database or Development database

1. By default, it is set to UNLIMITED.

2. The minimum setting is 15 and the maximum is 24855.

SQL> select RESOURCE_NAME,limit from dba_profiles where profile='DEFAULT';

 

RESOURCE_NAME                               LIMIT

------------------------------------------- -----------------------

COMPOSITE_LIMIT                                UNLIMITED

SESSIONS_PER_USER                            UNLIMITED

CPU_PER_SESSION                             UNLIMITED

CPU_PER_CALL                                   UNLIMITED

LOGICAL_READS_PER_SESSION                   UNLIMITED

LOGICAL_READS_PER_CALL                      UNLIMITED

IDLE_TIME                                        UNLIMITED

CONNECT_TIME                                UNLIMITED

PRIVATE_SGA                                     UNLIMITED

FAILED_LOGIN_ATTEMPTS                       10

PASSWORD_LIFE_TIME                          180

PASSWORD_REUSE_TIME                         UNLIMITED

PASSWORD_REUSE_MAX                          UNLIMITED

PASSWORD_VERIFY_FUNCTION                    NULL

PASSWORD_LOCK_TIME                          1

PASSWORD_GRACE_TIME                         7

INACTIVE_ACCOUNT_TIME                       UNLIMITED ----------- > This is the resource_name introduced in oracle 12.2.

 

17 rows selected.

 

To make an account lock automatically after 30 days of inactivity, Create a profile by setting INACTIVE_ACCOUNT_TIME to 30 and Set the profile to that user.

 

   CREATE PROFILE "ENDUSERINACTIVE"

    LIMIT

         COMPOSITE_LIMIT UNLIMITED

         SESSIONS_PER_USER UNLIMITED

         CPU_PER_SESSION UNLIMITED

         CPU_PER_CALL UNLIMITED

         LOGICAL_READS_PER_SESSION UNLIMITED

         LOGICAL_READS_PER_CALL UNLIMITED

         IDLE_TIME UNLIMITED

         CONNECT_TIME UNLIMITED

         PRIVATE_SGA UNLIMITED

         FAILED_LOGIN_ATTEMPTS 10

         PASSWORD_LIFE_TIME 1552000/86400

         PASSWORD_REUSE_TIME UNLIMITED

         PASSWORD_REUSE_MAX UNLIMITED

         PASSWORD_VERIFY_FUNCTION NULL

         PASSWORD_LOCK_TIME 86400/86400

         PASSWORD_GRACE_TIME 604800/86400

         INACTIVE_ACCOUNT_TIME 30;

 

SQL>  select RESOURCE_NAME,limit from dba_profiles where profile='ENDUSERINACTIVE' and resource_name='INACTIVE_ACCOUNT_TIME';

 

RESOURCE_NAME                               LIMIT

-------------------------------------------   -----------------------

INACTIVE_ACCOUNT_TIME                       30

 

SQL> CREATE USER chaitanya identified by chaitanya123 profile ENDUSERINACTIVE;

 

User created.

If you try to give a value less than 15, it will throw error like – ORA-02377: invalid profile limit INACTIVE_ACCOUNT_TIME

 

   CREATE PROFILE "ENDUSERINACTIVE"

    LIMIT

         COMPOSITE_LIMIT UNLIMITED

         SESSIONS_PER_USER UNLIMITED

         CPU_PER_SESSION UNLIMITED

         CPU_PER_CALL UNLIMITED

         LOGICAL_READS_PER_SESSION UNLIMITED

         LOGICAL_READS_PER_CALL UNLIMITED

         IDLE_TIME UNLIMITED

         CONNECT_TIME UNLIMITED

         PRIVATE_SGA UNLIMITED

         FAILED_LOGIN_ATTEMPTS 10

         PASSWORD_LIFE_TIME 15552000/86400

         PASSWORD_REUSE_TIME UNLIMITED

         PASSWORD_REUSE_MAX UNLIMITED

         PASSWORD_VERIFY_FUNCTION NULL

         PASSWORD_LOCK_TIME 86400/86400

         PASSWORD_GRACE_TIME 604800/86400

         INACTIVE_ACCOUNT_TIME 10;

 

   CREATE PROFILE "ENDUSERINACTIVE"

*

ERROR at line 1:

ORA-02377: invalid profile limit INACTIVE_ACCOUNT_TIME

Note: Info On Lock Acoount Automatically with INACTIVE_ACCOUNT_TIME it may be differ in your environment like production,testing ,development and naming conventions etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

 

ORA-12985 Tablespace Users is Read Only, Cannot Drop Column

 ORA-12985 Tablespace Users is Read Only, Cannot Drop Column 

Introduction

If you try to Drop a column from a table in a read only ,you got an error like this ORA-12985 Tablespace Users is Read Only, Cannot Drop Column if you really drop the column ,you must put the tablespace into READ WRITE mode  

Probem:

While dropping a column, in a paticular owner and particlar object below got an errorORA-12985 Tablespace Users is Read Only, Cannot Drop Column ,while drop a column in tablespace

 SQL> alter table chaitanyadba.master03 drop (OWNER,OBJECT_NAME);

alter table chaitanyadba.master03 drop (OWNER,OBJECT_NAME)

*

ERROR at line 1:

ORA-12985 Tablespace Users is Read Only, Cannot Drop Column 

Now let us Start the process to find out the Solution

The Object which is trying to drop belongs to a table sapce is in read only mode

Step 1 : Find the tablespace of that particular table

SQL> select tablespace_name from dba_segments where segment_name='MASTER03';

 

TABLESPACE_NAME

------------------------------

USERS

Step 2 : Find the staus of the tablespace

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';

 

TABLESPACE_NAME                STATUS

------------------------------ --------------------

USERS                                       READ ONLY

 

Step 3: Inorder to Drop a column ,We need to make the tablespace READ and WRITE mode only  

SQL> ALTER TABLESPACE users READ WRITE;

 

Tablespace altered.

 

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';

 

TABLESPACE_NAME                STATUS

------------------------------ ------------------

USERS                                          ONLINE

 

SQL> alter table chaitanyadba.master03 drop (OWNER,OBJECT_NAME);

 

Table altered.

Note : Info on ORA-12985 Tablespace Users is Read Only, Cannot Drop Column  it may differin your environment like production,testing,development and naming conventions etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME