Posted by I just posted a video on how I get the most out of my SQL*Plus usage. You can check it out here
I reference various elements of my login.sql file throughout the video – so here’s login.sql file – feel free to utilise any parts of it for your benefit.
col column_name format a30
col sql_text format a64
col segment_name format a30 trunc
col tablespace_name format a20 trunc
col program format a30 trunc
col what format a50 word_wrapped
col plan_plus_exp format a100
set lines 130
set pages 99
set trimspool on
set exitcommit OFF
set arraysize 100
REM set serverout on
set termout off
define gname = 'SQL'
column global_name new_value gname
define IS12 = ""
define NOT12 = ""
col x new_value IS12
col y new_value NOT12
with rel as
( select substr(regexp_replace(banner,'^.*Release '),1,2) rel
from v$version
where rownum = 1 )
select
case when rel = '12' then '--' end y,
case when rel != '12' then '--' end x
from rel;
set verify off
select lower(user) || '@'||
&&IS12 case when sys_context('USERENV','CON_NAME') != 'CDB$ROOT' then lower(sys_context('USERENV','CON_NAME'))
&&IS12 else
lower(instance_name)
&&IS12 end
||
chr(10)||'SQL' global_name
from v$instance;
set sqlprompt '&&gname> '
host title &&gname
set verify on
set termout on
col profile format a30
col object_name format a30
col table_name format a30
col view_name format a30
col username format a30
col user_name format a30
Learning is not a spectator sport 
Thanks for sharing Connor.
I have always wondered how you reproduce step-by-step commands that you ran and its output results at every step, when answering questions on AskTom. Do you spool the commands to a text file and then copy-paste onto the forum OR is there some other trick that you use?
I would love to be able to reproduce what commands I ran and its results at every step so that I can share it with multiple team members or when giving a presentation.
If you are doing DBA work then selects in your glogin.sql can cause ‘issues’.
For example, you’ll be able to stop your database using Oracle Enterprise Manager Cloud Control, but you won’t be able to start it back up again using OEM. If you put them into a login.sql file you shouldn’t have issues.
Thanks for that Rich. I’m assuming this is self-discovery in not the most pleasant way? 🙂 If so, can you elaborate more on what the issue is.
Cheers,
C
Basically what happens is this: If you connect to an instance that isn’t fully up, then your select statements in your login.sql and/or glogin.sql will generate an error (as would be expected).
Unfortunately, some tools interpret this error as ‘something is broken and I should stop’.OEM CC is one of those tools. So, when you shut down your instance using the OEM CC Console, it works just fine. But, if you try to start up the instance using the agent deployed on a remote database server, OEM CC sees that error, and says “Something is wrong, I can’t start this database.” Now, that’s not true, but OEM CC doesn’t know that.
Does that make it clearer? (I thought that is what I said before…)
Thanks for the info. I’m just curious how the tool detects that given that its pretty much wrapped in set termout off/on
Hi Connor,
Thanks for sharing the tips. Some of those tips would save a lot of time. Would you mind sharing the scripts you are using such as tab.sql, desc.sql etc on your github account so that we could leverage some of those scripts.
Thanks
Enjoy 🙂 https://github.com/connormcd/misc-scripts/tree/master/sql
Awesome, thanks Connor!
Every file appears to have the following content:
The file cannot be copied onto itself.
0 file(s) copied.
Yup, I’m also seeing the above message when I open every file under the [sql] sub-folder.
Oh dear 🙂 Looks like a scripting error when I was cleaning them up.
I’ll take a look at get back to you
Sorry about that.
OK, should be fixed now. Please do a fresh pull
Did a fresh pull….looks good now, thanks!
Awesome! Thanks Connor