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
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.
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
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.
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