This is just a simple trick which is not well-known but makes life easier when using the good ol’ SQL*Plus commandprompt (and many people still do!).
If you set the SQLPROMPT environment variabele (either manually or in the login.sql) the standard prompt is replaced by the name of the instance:
SQL> set sqlprompt "_CONNECT_IDENTIFIER>"
mydb>
I use this in combination with the login.sql. I’m not sure if this was a feature before Oracle 10g, but recently I noticed that the login.sql is executed after every login, and not only when you start a new prompt. And that means that everytime you change your connection, the sqlprompt also changes according to the value of _CONNECT_IDENTIFIER. This probably will prevent many mistakes because you’re typing update-statements in the "wrong" environment!
These substitution variables are available from SQL*Plus 9.2 onwards. Other values you can use for sqlprompt are _USER, _DATE, _O_VERSION and _O_RELEASE. They speak for themselves I guess!
mydb> set sqlprompt "_USER _CONNECT_IDENTIFIER> "
SCOTT mydb>
OK, that went wrong (now hopefully in the write order):
—
—
—
—
—
define uniqueness=”uniqueness”
column uniqueness new_value uniqueness
— ——————————————————————————-
set termout off
— Table dba_indexes has changed in Oracle 8
— Next piece of code switches the column name of the “create index” cursor.
select ‘decode(i.uniqueness,”BITMAP”,i.uniqueness,”NORMAL”)’ uniqueness
from dual where &&_O_RELEASE like ‘7%’
union
select ‘i.index_type’ uniqueness
from dual where &&_O_RELEASE like ‘8%’
;
— ——————————————————————————-
set termout on
set define ~
set serveroutput on size 1000000
—
—
—
—
— etc, etc, etc —
—
—
—
—
— Rebuild index
CURSOR c_index( p_index IN CHAR
, p_schema IN CHAR
)
IS
SELECT i.owner idxowner
, i.index_name
, ~uniqueness indextype
, decode(i.uniqueness, ‘UNIQUE’,’ UNIQUE ‘,’ ‘) uq
, i.table_name
, i.table_owner
, i.tablespace_name
, i.initial_extent
, i.next_extent
, i.min_extents
, i.max_extents
, i.pct_increase
, i.freelists
, i.pct_free
, decode(c.constraint_type,’P’,’PRIMARY KEY’
,’U’,’UNIQUE’
,c.constraint_type) constype
, c.constraint_name
, c.status
, c.owner consowner
FROM sys.dba_indexes i
, sys.dba_constraints c
WHERE (c.table_name(+) = i.table_name
AND c.constraint_name(+) = i.index_name
AND c.owner(+) = i.table_owner)
AND i.table_type = ‘TABLE’
AND i.index_name = p_index
AND i.owner = p_schema
ORDER BY i.index_name
;
—
—
—
—
—
—
— etc, etc, etc —
—
—
—
BEGIN
—
…etc…etc
—
—
—
IF check3(i).s_segment_type=’INDEX’
THEN
FOR r_index IN c_index(check3(i).s_segment_name,check3(i).s_owner)
LOOP
EXIT WHEN c_index%NOTFOUND;
create_check3(i).r_statement := ‘alter index ‘||r_index.idxowner||’.’||r_index.index_name
||chr(10)
||’REBUILD ONLINE’
||chr(10)
||’PCTFREE ‘||r_index.pct_free
||chr(10)
||’storage’
||chr(10)
||'(‘
||chr(10)
||’initial ‘||v_seg_initial||’K’
||chr(10)
||’next ‘||v_seg_nextext||’K’
||chr(10)
||’pctincrease ‘||r_index.pct_increase
||chr(10)
||’minextents ‘||r_index.min_extents
||chr(10)
||’maxextents ‘||v_max_extents
||chr(10)
||’freelists ‘||r_index.freelists
||chr(10)
||’)’
||chr(10)
||’tablespace ‘||r_index.tablespace_name
||’;’
;
dbms_output.put_line(create_check3(i).r_statement);
dbms_output.put_line(‘–‘);
END LOOP;
The _O_RELEASE variable reminded me on a small trick I once used.
A while ago (understatement), I had to write a script that could be executed on Oracle 7 and on Oracle 8. In Oracle 7 the dba_indexes table had normal and bitmapped indicators in the same column. In Oracle 8, they added a extra column regarding bitmap indexes. My dynamically created “CREATE INDEX” statements via a cursor where based on the info in DBA_INDEXES.
The following gives you an idea how i solved it:
define uniqueness=”uniqueness”
column uniqueness new_value uniqueness
— ——————————————————————————-
set termout off
— Table dba_indexes has changed in Oracle 8
— Next piece of code switches the column name of the “create index” cursor.
select ‘decode(i.uniqueness,”BITMAP”,i.uniqueness,”NORMAL”)’ uniqueness
from dual where &&_O_RELEASE like ‘7%’
…etc…etc…etc
IF check3(i).s_segment_type=’INDEX’
THEN
FOR r_index IN c_index(check3(i).s_segment_name,check3(i).s_owner)
LOOP
EXIT WHEN c_index%NOTFOUND;
create_check3(i).r_statement := ‘alter index ‘||r_index.idxowner||’.’||r_index.index_name
||chr(10)
||’REBUILD ONLINE’
||chr(10)
||’PCTFREE ‘||r_index.pct_free
||chr(10)
||’storage’
||chr(10)
||'(‘
||chr(10)
||’initial ‘||v_seg_initial||’K’
||chr(10)
||’next ‘||v_seg_nextext||’K’
||chr(10)
||’pctincrease ‘||r_index.pct_increase
||chr(10)
||’minextents ‘||r_index.min_extents
||chr(10)
||’maxextents ‘||v_max_extents
||chr(10)
||’freelists ‘||r_index.freelists
||chr(10)
||’)’
||chr(10)
||’tablespace ‘||r_index.tablespace_name
||’;’
;
dbms_output.put_line(create_check3(i).r_statement);
dbms_output.put_line(‘–‘);
END LOOP;
union
select ‘i.index_type’ uniqueness
from dual where &&_O_RELEASE like ‘8%’
;
— ——————————————————————————-
set termout on
set define ~
..
..etc,etc
..
— Rebuild index
CURSOR c_index( p_index IN CHAR
, p_schema IN CHAR
)
IS
SELECT i.owner idxowner
, i.index_name
, ~uniqueness indextype
, decode(i.uniqueness, ‘UNIQUE’,’ UNIQUE ‘,’ ‘) uq
, i.table_name
, i.table_owner
, i.tablespace_name
, i.initial_extent
, i.next_extent
, i.min_extents
, i.max_extents
, i.pct_increase
, i.freelists
, i.pct_free
, decode(c.constraint_type,’P’,’PRIMARY KEY’
,’U’,’UNIQUE’
,c.constraint_type) constype
, c.constraint_name
, c.status
, c.owner consowner
FROM sys.dba_indexes i
, sys.dba_constraints c
WHERE (c.table_name(+) = i.table_name
AND c.constraint_name(+) = i.index_name
AND c.owner(+) = i.table_owner)
AND i.table_type = ‘TABLE’
AND i.index_name = p_index
AND i.owner = p_schema
ORDER BY i.index_name
;
@arnoud
the login.sql and glogin.sql can be found normally in $ORACLE_HOME/sqlplus/admin. Useful parameters can be set in these files to, for example, format you explain plan output differently (use among others “show all” and “define” in sqlplus to see some parameters you could use). Be also aware that the files are useful in hacking the system (if someone can write into it, OS/SQL commands in it will be executed during startup of sqlplus)
See also http://www.adp-gmbh.ch/ora/sqlplus/login.html.
Yes Patrick, the call to login.sql happens on every connect (not just the furst time you start SQL*Plus) since 10g – see this article on Oracle-Base: http://www.oracle-base.com/articles/10g/SQLPlusEnhancements10g.php Regards Nigel
You can create the login.sql yourself and put it in the startup-directory of your SQL*Plus.
Typically, the contents of login.sql can be something like this:
set sqlprompt “_CONNECT_IDENTIFIER> ”
exec dbms_output.enable( 50000 );
set serveroutput on
set feedback on
set timing on
col object_name format a20
exec ams_common.INIT_AUDIT_INFO;
Great trick, Patrick. Thanks for sharing it.
Maybe you can also tell where to find the login.sql?
Arnoud