Being Oracle DBA we all are aware of the relationship between instance and database. Today we are going to have look on “SQL*Plus Customize User Profile“. On daily basis, DBAs works on SQL*Plus at many stages of the database. Execution of all commands on the correct database is the main focus of any DBA. We have to perform multiple extra efforts. Sometimes DBA’s has to know which time they have executed the command and how long it takes to execute the command. Sometimes execution of commands in different database causes lots of costs. We may have miracle if Being DBA we can check out, on which database what commands are fired.
Being human we have a solution of each and every problem. With the help of customizing user profile can save DBA’s time and it will help them to see which database they are working on along with the user. so It will reduce the risk to execute a command in the wrong database.
Here the username is “Test” and the Connect Identifier is a College
[oracle@server ~]$ sqlplus test SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 17 14:41:46 2017 Copyright (c) 1982, 2011, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Session altered. test@college02:41:50> select count(*) from BOOK_LIST; COUNT(*) ---------- 725592 Elapsed: 00:00:00.05
How:
Create login.sql file in oracle user Home Directory. You can customize your session by setting parameter in a login.sql file.
login.sql
— login.sql
— SQL*Plus user login startup file
— This script is automatically run after glogin.sql
/* Description:
SQL*Plus command-line supports a User Customize Profile Script. In this script, user can customize their session. This Customize script generally named login.sql.This script should be located in user home directory. WHen SQL*PLUS start, it automatically searches for the user profile and executes the commands it contains. A user profile is not used in iSQL*Plus.
*/
-- Set the database date format to show time ALTER SESSION SET nls_date_format = 'HH:MI:SS'; -- Set USER,CONNECT_IDENTIFIER,DATE SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _DATE> " -- Set timing on to see query execution time SET timing on;
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Telegram Channel: https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
