Stop Oracle Password Expiry – Set Password Life Time to Unlimited

Oracle Password Life Time Set to Unlimited
-------------------------------------------------------

In Oracle database, sometimes user password gets expired automatically.
This happens because PASSWORD_LIFE_TIME is defined in the user profile.

To avoid password expiry, we can set password life time to UNLIMITED.

This is mostly required for application users and service accounts.


Step 1: Check user profile

First, check which profile is assigned to the user.

SELECT username, profile
FROM dba_users
WHERE username = 'ROHIT';

Most of the time, user is using the DEFAULT profile.


Step 2: Set password life time to unlimited

If user is using DEFAULT profile, run below command.

ALTER PROFILE DEFAULT
LIMIT PASSWORD_LIFE_TIME UNLIMITED;

If user is using some other profile, replace profile name.

ALTER PROFILE APP_PROFILE
LIMIT PASSWORD_LIFE_TIME UNLIMITED;


Step 3: Verify the setting

After changing the profile, verify the password life time value.

SELECT profile, resource_name, limit
FROM dba_profiles
WHERE resource_name = 'PASSWORD_LIFE_TIME';


Step 4: Reset expired password (if required)

If user password is already expired, reset the password and unlock the account.

ALTER USER ROHIT IDENTIFIED BY new_password;
ALTER USER ROHIT ACCOUNT UNLOCK;


Now the user password will not expire automatically.

This setting is very useful for application schemas and batch users.
For normal users, password expiry is recommended for better security.

Oracle Role Creation With Examples

Oracle Role Creation With Examples

In this blog we will discuss how to create a role in Oracle Database and
how to give rights to users using that role.

Roles are very useful because instead of giving permissions to each user
one by one, we can put all permissions inside a role and then assign
that role to users.

Create Role 

Example: CREATE ROLE view_role;

Grant Privileges to Role 

Example: GRANT CREATE SESSION TO view_role;
GRANT SELECT ANY TABLE TO view_role;

We can also give table-level rights. Example: GRANT SELECT ON
hr.employees TO view_role;

Create User 

Example: CREATE USER rohit IDENTIFIED BY rohit123;

Grant Role to User 

Example: GRANT view_role TO rohit;

Revoke Privileges from Role

Example: REVOKE SELECT ANY TABLE FROM
view_role;

Drop Role

Example: DROP ROLE view_role;

Conclusion Using roles in Oracle is very helpful for managing user
permissions. Instead of granting many privileges to each user
separately, it is better to create one role, assign all required rights
to that role, and then grant the role to users. This makes
administration easy and clean.

Top 10 Useful Linux Commands

 

 

 

Here are going to discuss Top 10 Important useful LINUX Commands.

 

ls command

ls command is used for lists the files and directories under folder.

 

pwd command

pwd command is useful for checking the present working directory.

 

cd command

cd command is useful for changing the directory or going to specific directory.

Examples :- cd /home/oracle

                   cd /home

                   cd .. ( For going one directory upwardfrom current directory)

 

cp command

cp command is useful for copying one file to another file in same or different location.

Example :- cp file1 file2 (for copying file1 into file2 and file1 is also available there after copy)

                  cp file1 /target/file2 (for copying file1 content into /target location with name file2 )

 

mv command

mv command is useful for move a file or folder to another location.

Example :- mv /source_location/file1 /target_location/        (For moving file1 to /target_location)

                 mv /source_location/file1 /target_location/file2 ( For renaming file to file2 on target location )

 

rm command

 rm command is used for deleting files from directory.

Example: rm /source_location/file1 ( for removing file1 from /source_location)

               rm file1 file2                  ( for removing file1 and file2 from current working directory)

               rm *                               ( for removing all files from current location)

 

 NOTE:- Always be double sure about using rm command

mkdir command

mkdir command is used for creating directory

Example : mkdir /source_location        (For creating /source_location directory under / (root))

               mkdir /source_location/location2     ( for creating location2 folder inside /source_location/ )

 

rmdir command

rmdir command is used for removing directories if they are empty.

Example :- rmdir /source_location/*

 

df command

df command for getting information about file system and its space

Example :- (df –h for displaying information regarding file system and its space usage)

 

help command

help command is useful for getting help for commands.

mkdir --help

 ls --help

 

 

 

 

 

 

 

 

 

How to Check the Oracle Database Version

 

 

For checking Database Version we can use the Below SQL Statement.

 

1.       select * from v$version;

 

 

Or We Also can use

2.       select version from v$instance;

               

 

Thanks

 

How to Extend BIGFILE Tablespace Datafile size in Oracle

How to Extend BIGFILE Tablespace Datafile size in Oracle                                                                

In This blog I am going to show how to extend BIGFILE Tablespace Datafile.

First of all need to check whether the tablespace is BIGFILE or not 

.For this issue below SQL statement.

1.       select tablespace_name,BIGFILE from dba_tablespaces ;

Example :-

TABLESPACE_NAME                 BIG

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

USERS                                            YES

SYSAUX                                          NO

From above result USERS Tablespace is BIGFILE Tablespace.

After this need to use below SQL statement for knowing the datafile associated with USERS Tablespace.

2.       select file_name ,tablespace_name, BYTES/1024/1024/1024 size_GB  from dba_data_files where tablespace_name='USERS';

FILE_NAME                                        TABLESPACE_NAME                       SIZE_GB

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

/Datafile/users01.dbf                                USERS                                                   2410

 

Issue below command to extend users tablespace datafile by only 1 GB for testing purpose only.

3.       Alter database datafile ‘/Datafile/users01.dbf’ resize 2411G;

Above query will extend the USERS tablespace datafile to 2411G.