Just updated my photo ! Thanks to pion.ch photograph:-)
select column only if it exists
i need to display tablespace attributes
SQL> select tablespace_name, status, contents, logging, extent_management, allocation_type, segment_space_management, retention, bigfile from dba_tablespaces;
TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO SEGMEN RETENTION BIG
——————– ——— ——— ——— ———- ——— —— ———– —
SYSTEM ONLINE PERMANENT LOGGING LOCAL SYSTEM MANUAL NOT APPLY NO
looks fine. but what if I try that on my oracle7 database? I will get ORA-00904: invalid column name
ok, I will then select only the columns that exist! than select from dba_tablespaces
t.sql:
set termout off
def logging=””
def extent_management=””
def allocation_type=””
def segment_space_management=””
def retention=””
def bigfile=””
col logging new_v logging
col extent_management new_v extent_management
col allocation_type new_v allocation_type
col segment_space_management new_v segment_space_management
col retention new_v retention
col bigfile new_v bigfile
select ‘,logging’ logging from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’LOGGING’;
select ‘,extent_management’ extent_management from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’EXTENT_MANAGEMENT’;
select ‘,allocation_type’ allocation_type from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’ALLOCATION_TYPE’;
select ‘,segment_space_management’ segment_space_management from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’SEGMENT_SPACE_MANAGEMENT’;
select ‘,retention’ retention from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’RETENTION’;
select ‘,bigfile’ bigfile from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’BIGFILE’;
set termout on ver off
select tablespace_name, status, contents &logging &extent_management &allocation_type &segment_space_management &retention &bigfile from dba_tablespaces;
let’s try
[email protected] AS SYSDBA/7.3.4.5
SQL> @t
TABLESPACE_NAME STATUS CONTENTS
——————– ——— ———
SYSTEM ONLINE PERMANENT
[email protected] AS SYSDBA/8.1.7.4
SQL> @t
TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO
——————– ——— ——— ——— ———- ———
SYSTEM ONLINE PERMANENT LOGGING DICTIONARY USER
[email protected] AS SYSDBA/9.2.0.6
SQL> @t
TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO SEGMEN
——————– ——— ——— ——— ———- ——— ——
SYSTEM ONLINE PERMANENT LOGGING DICTIONARY USER MANUAL
[email protected] AS SYSDBA/10.2.0.1
SQL> @t
TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO SEGMEN RETENTION BIG
——————– ——— ——— ——— ———- ——— —— ———– —
SYSTEM ONLINE PERMANENT LOGGING LOCAL SYSTEM MANUAL NOT APPLY NO
one script for any version
dynamic number of columns
probably one of the most frequently asked question, you want to have a table like
ACCOUNTING OPERATIONS RESEARCH SALES
ANALYST 0 0 2 0
CLERK 1 0 2 1
MANAGER 0 0 1 1
PRESIDENT 1 0 0 0
SALESMAN 0 0 0 4
but the number and name of columns must be dynamic.
typically, I answer : this is not possible in plain sql. you need to use plsql.
well. It is not too much beautifoul, but I have a suggestion
step one:
generate the select
spool crosstab.sql
select text from (
select 1 i, ‘select job’ text from dual
union all
select 2+rownum, ‘, count(decode(deptno,’||deptno||’,deptno)) ‘||dname from
(select deptno,dname from dept order by dname)
union all
select 1e125, ‘ from emp group by job order by job;’ from dual
)
order by i
/
spool off
step two:
execute the generated query
@crosstab
JOB ACCOUNTING OPERATIONS RESEARCH SALES
——— ———- ———- ———- ———-
ANALYST 0 0 2 0
CLERK 1 0 2 1
MANAGER 1 0 1 1
PRESIDENT 1 0 0 0
SALESMAN 0 0 0 4
done!
10gR2
Well, the virus scanner did not let me download it 10gR2 Aix on monday. I called the IT-support. Finally, they allowed me to bypass the virus scanner and I managed to download the software + the doc within 10 minutes. Not bad!
Ok, the installer complained my maintenance level to be 5200-03. I upgraded it to 5200-06.
The oracle installer is not my best friend. I have many installations per servers, 7.3.4, 8.0.6, 8.1.7 32bits, 8.1.7 64bits (AIX4), 9.2, 10.1, 10.2… with different patch levels, and I consider installing and deinstalling with runinstaller to be a nightmare.
I did not find the “remove software” functionnality on the 10gR2 installer. Strange!
I created a new db. Got a warning that SQL_TRACE is deprecated, so I removed that parameter and my db is up and running.
pivot table
big mission today : explain the mess with pivot table.
let’s look the following query
SQL> select extract(year from hiredate) year, count(*) from emp group by extract(year from hiredate) order by year
YEAR COU
—– —
1980 1
1981 10
1982 1
1987 2
how do we get the years without hiredate with 0?
1) with a table of years
it is fast, and easy to undestand. But it require to create a table
create table year(year number);
insert into year values (1980);
insert into year values (1981);
insert into year values (1982);
insert into year values (1983);
insert into year values (1984);
insert into year values (1985);
insert into year values (1986);
insert into year values (1987);
insert into year values (1988);
insert into year values (1989);
SQL> select year, count(hiredate) COU from emp right join year on (extract(year from hiredate)=year) group by year order by year
YEAR COU
—– —
1980 1
1981 10
1982 1
1983 0
1984 0
1985 0
1986 0
1987 2
1988 0
1989 0
2) I have a few artifice to select from dual
A. union
SQL> select year, count(hiredate) COU from emp right join
(select 1980 year from dual
union all select 1981 from dual
union all select 1982 from dual
union all select 1983 from dual
union all select 1984 from dual
union all select 1985 from dual
union all select 1986 from dual
union all select 1987 from dual
union all select 1988 from dual
union all select 1989 from dual)
on (extract(year from hiredate)=year) group by year order by year;
ok for 10 rows, but not for 1000!
B. cube
a trick, which may not work in all versions
SQL> select year, count(hiredate) COU from emp right join
(select 1980+rownum year from
(select null from dual group by cube (null,null,null,null))
where rownum
not ok if the number of rows to be generated is volatile, sometimes 1, sometimes 1000000
C. connect by
even more dirty tricky, no guarantee that it will work
SQL> select year, count(hiredate) COU from emp right join
(select 1980+level year from dual connect by level
3) use rownum and all_objects
SQL> select year, count(hiredate) COU from emp right join
(select 1980+rownum year from all_objects where rownum
not scalable, you do not know how many rows are in all_objects (500,5000,50000?), and not performant at all, because all_objects is a complex view
4) PL/SQL pipelined table
perfectly scalable, quite fast, requires to create a few objects
SQL> create type t_year as TABLE OF number;
/
Type created.
SQL> create or replace function f_year(n1 number, n2 number) return t_year pipelined is
begin for i in n1..n2 loop pipe row(i); end loop; return; end;
/
SQL> select COLUMN_VALUE year, count(hiredate)
from emp right join
table(f_year((select min(extract(year from hiredate)) from emp), (select max(extract(year from hiredate)) from emp)))
on ( COLUMN_VALUE = extract(year from hiredate) )
group by COLUMN_VALUE
YEAR COU
—– —
1980 1
1981 10
1982 1
1983 0
1984 0
1985 0
1986 0
1987 2
I like the last one, because you can return 1 or 1000000000 rows, the query will not change. It will be fast for 1 row, and slow but will still work for 1000000000 rows.
group by does not sort
An user just posted an interresting question today. Why the Group By is crazy?
I summarize his example
SQL> SELECT NUM FROM (select 400 num from dual union select 310 from dual union select 220000 from dual) group by num,null;
NUM
——-
400
220000
310
Well, group by is “sorting”, but how? this seems crazy. Oracle use the sort algorythm he wants. He can ascending-sort, descending-sort, hash-sort, or any other internal algorythm.
Here, 220000 is before 310 because it is smaller in bytes.
Have a look
SELECT num,dump(num) FROM (select 400 num from dual union select 310 from dual union select 220000 from dual) group by num,null;
NUM DUMP(NUM)
———- ————————-
400 Typ=2 Len=2: 194,5
220000 Typ=2 Len=2: 195,23
310 Typ=2 Len=3: 194,4,11
Well, if you need to sort, use order by and read tom blog
Radio Free Tooting
Radio Free Tooting is apc blog… much better written than mine! wtg apc
return code
there is a myth of using sql.sqlcode in sqlplus
whenever sqlerror exit sql.sqlcode
this not ok. you should prefer whenever sqlerror exit failure or exit 1
Why? because unix return code is 8 bits long. so if you exit ora-600, you will get 88.
Let’s try it
$ sqlplus “/ as sysdba”
SQL> create user gaston identified by lagaffe quota 1k on users default tablespace users;
User created.
SQL> grant create table to gaston;
Grant succeeded.
SQL> whenever sqlerror exit sql.sqlcode
SQL> create table gaston.x as select * from all_objects;
create table gaston.x as select * from all_objects
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace ‘USERS’
Disconnected from …
$ echo $?
0
1536 in binary is 11000000000, so the 8 leftmost bits gives 0, which is definitely not fine in your unix code!
well, there is also a return code from the os, but it stopped working in 10g. it is the _RC defined variable
SQL> host ls /xxx
ls: 0653-341 The file /xxx does not exist.
SQL> def _rc
DEFINE _RC = “2” (CHAR)
many users asked “how to get the returned code of a procedure”. Well, this is a faq, a procedure is returning nothing. but you could have an out variable, then exit that code.
SQL> create or replace procedure p(o out number) is begin o:=1; end;
2 /
Procedure created.
SQL> var rc number
SQL> exec p(:rc)
PL/SQL procedure successfully completed.
SQL> exit :rc
Disconnected …
$ echo $?
1
OpenWorld SanFrancisco
Well, next month I am going to SF for OOW 2K5. I am invited by Oracle to participate to the Meet the expert session (otn underground). I cannot stand waiting to meet my oracle forums pals alive! I am also taking part to the xtreme sessions and I expect them to be xtreme!
It almost 20 years I have not been in the states. I am very excited !
I already installed DB 10gR2 on my notebook. It has been very easy. I first installed as a virtual machine (vmware) RedHat Entreprise Linux 3 update 3. Then Oracle. My collegues did a little bit complain about the 5 giga download, because the whole network was slow. Only 1 cd is necessary for DB installation. Rest is companion, client, clusterware, grid, etc…
I hope I can get a working 10gR2 grid in SF ! Even a beta release would be great 🙂
TO_CHAR(…, ‘D’)
How do I get than MONDAY=1, TUESDAY=2, WEDNESDAY=3 … ?
With to_char()
alter session set nls_territory=germany;
select to_char(sysdate,’DAY D’) from dual;
TUESDAY 2
With decode()
select decode(to_char(sysdate, ‘FMDAY’, ‘NLS_DATE_LANGUAGE=american’),’MONDAY’, ‘1’, ‘TUESDAY’, ‘2’, ‘…’)) from dual;
With mod()
As a reference, I take monday Jan 1st, 1000.
select mod(trunc(sysdate)-date ‘1000-01-01’,7)+1 from dual;
2
How do I trunc date to current monday?
trunc(date, ‘D’) or here is with my solution with 1000-01-01:
select trunc((sysdate-date ‘1000-01-01’)/7)*7+date ‘1000-01-01′ from dual;
thanks to jan-marcel idea, I found that one
trunc(date,’IW’) for current monday and date-trunc(date,’IW’)+1 for day number
deterministic
if I create a function for a materialized view with query rewrite or for a function based index, I must create hte deterministic.
f(x) = x*2 is deterministic. for a give x, f(x) will always be the same, f(5) will be always 10; always.
f(y) = sysdate+y is non-deterministic. For a given y, the return values may vary.
OK, we cannot use sysdate in mviews. What else should I say?
Well, some are less evident to find out!
Some limit cases:
to_date(‘2000′,’YYYY’) is non deterministic, it returns the 1st of current month, 2000, f. ex. 2000-07-01 or 2000-08-01
to_char(date ‘2000-01-01′,’DAY’) is non deterministic, it can deliver SATURDAY or SAMSTAG
to_char(date ‘2000-01-01′,’DAY’,’NLS_DATE_LANGUAGE=american’) is deterministic
to_char(date ‘2000-01-01′,’D’) is non deterministic, it can deliver 6,7,1 or 2, depending what your territory is (in Bangladesh, the week starts on friday)
timestamp ‘2005-10-30 02:30:00 Europe/Zurich’ is per default non deterministic, if you try it in summer, you will get time offset +02:00, and in winter you will get +01:00, because the period 02:00-02:59, Oct 30th, exists in both time zones. This is called time boundaries. I can make it deterministic by setting ERROR_ON_OVERLAP_TIME in the session, in which case boundaries will be rejected.
I can always define a function as deterministic, at my own risk…
For exemple if I have t(id,gender) {1,male;2,female}, I could have a function
f(id)=select gender from t where t.id=f.id;
and I could define it as deterministic. However, if I decide to set id=2 for female later, I am prepared to have corrupted data, wrong results, inconsistencies and even ORA-600 errors.
How old are you?
I just come back from holiday, I am quite busy at the moment. Here is a tiny function to get the age
trunc((to_char(sysdate,’YYYYMMDD’)-to_char(birthdate,’YYYYMMDD’))/10000)
it is much safer than add_months, because add_months do some conversion at the end of the month, and I would never accept to wait until Feb 29th, 2008 (28-2-1990 + 18*12 months) to be 18 if I were born Feb 28th, 1990.
exotic constraints
Today I read a post on metalink where the user wanted a unique constraint for not-null values…
Sounds easy, because Oracle never indexes null in btree index.
If I have only one column, I simply index it, it will work.
SQL> create table t66 ( n number);
Table created.
SQL> create unique index i66 on t66(n);
Index created.
SQL> insert into t66 values (null);
1 row created.
SQL> insert into t66 values (null);
1 row created.
The nulls are not indexed. In that post today, the user is using a two column index, and do not want to enforce that constraint when one of the column is null. No problem, we can use FBI to enforce this.
create index i on t(decode(col2,null,null,col1), decode(col1,null,null,col2));
so the index will contain only entries were both columns are not null.
Yesterday a user on forums.oracle.com wanted to have a not-different constraint, that is was only accepting entries [p;r] if [p;s] does not exist. It is quite hard to solved. I have read an interresting solution using ON-COMMIT-REFRESH materialized view with aggregates and constraints.
A long time ago, one user wanted a constraint “table should contain only one row”.
create unique index i on t(col*0);
would ensure at most one row, with col NOT NULL
I think I can do better.
Imagine the user wants always exactly one row in STATUS(code number)
create table STATUS_MAXONE(code number, n number default 0 primary key);
create view STATUS as select code from STATUS_MAXONE;
insert into STATUS values (null);
create table STATUS_MINONE(n number references STATUS_MAXONE(n));
insert into STATUS_MINONE values (0);
Now the user can update the view STATUS, but neither delete nor insert…
Sometimes, you can also have circular foreign key constraints, for example, a PERSON can only marry with someone who exists in PERSON, that is a PERSON.SPOUSE => PERSON.ID relation. This is also quite special… Actually, a person can only marry his spouse, that means if I am your SPOUSE, you are my SPOUSE! But there is no way to reference a column that may be null (we must reference a primary key).
Using unique index on FBI has the limitation of FBI: function must be deterministic. Same with triggers. complex constraints needs additional tables to enforce your business rules.
Like the p;r; accepting p;r; and q;s; but not p;s;
we could simply have a table containing p;s; with p as primary key…
unexpected results !
It makes you cry! It makes you claim you have found a bug! but it is working as specified!
1) subquery refers to a column of the main query
select * from emp where ename in (select ename from dept where deptno=10);
the query does not complain that column does not exist in dept. It is perfectly legal to specify a non-prefixed column of the main query in the subquery. This could be like
select * from tab where 'foo' in (select 'foo' from dict);
so the “in” clause is always true
2) “not in” does not deliver result
select sysdate from dual where 400 not in (select comm from emp);
this is because 400!=null is UNKOWN. Check the 3 state booleans operations!
it could be rewritten with a not exists, or in 10g with LNNVL
select sysdate from dual where lnnvl(400 in (select comm from emp));
3) rows are not delivered in the correct order
it is a wrong assumption to think that the rows will be delivered in a specific order if you do not specify order by.
4) table not found
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
GreatTable TABLE
SQL> desc GreatTable
ERROR:
ORA-04043: object GreatTable does not exist
Whoever it is, someone created a case sensitive table name.
SQL> desc "GreatTable"
Name Null? Type
----------------------- -------- ----------------
MagicColumn NUMBER(38,2)
set pages 50000
I have often see set pagesize 50000 or set pages 9999, just to avoid page break.
I wonder how many line break will appears when the select grow over 50000…
A cool solution is to use embedded with pagesize 0
SQL> set emb on pages 0 newp 0
The time has gone
Sure we know date. Oracle always store date and time in an internal format. It could be understood as a number of seconds since Jan 1st, -4712. It must be smaller than Jan 1st, 10000. Let’s say we do not care of the Y10K nowadays…
It works well and it is easy to add days and fraction of days. A common notation I use is date ‘YYYY-MM-DD’. This bypass the nls settings.
I add one day to 28-Feb-1900
select date ‘1900-02-28’ + 1 from dual;
01.03.1900
Cool it works! Try it in in excel and meet the Y1900 bug 🙂
The first problem I am aware off are zero dates insert by OCI in 8i, which look like 0000-00-00, but evaluates to somewhen in 10101 BC!
The second one, more serious, is the Y0 bug. There is no year 0 in our Era, because the zero was not know when our Calendar was created. But Oracle has a year 0.
Proof:
select date ‘0001-01-01’ – 1 from dual;
31.12.0000
And it is full of bugs!
SQL> select to_char(date ‘0000-01-01’, ‘DD/MM/YYYY’) from dual;
00/00/0000
SQL> select to_char(date ‘0000-01-01’, ‘DD/MM/YYYYSP’) from dual;
00/00/000000000000000000000000000000000000000000
SQL> select to_char(date ‘0000-01-01’, ‘DD/MM/YYYY JSP’) from dual;
01/01/0000 ONE MILLION SEVEN HUNDRED TWENTY-ONE THOUSAND FIFTY-EIGHT
Ok, what about timestamp?
Basically, a timestamp is a date + a fraction of seconds + evtl a timezone offset.
Again there, I like to use the ANSI notation TIMESTAMP ‘YYYY-MM-DD HH24:MI:SS.FF’
I try to stick to timezone region, it scales better over summer time than timezone offsets.
SQL> select timestamp ‘2005-10-30 01:30:00.00 Europe/Zurich’ + to_dsinterval(‘0 2:0:0’) from dual;
30.10.2005 02:30:00.00 EUROPE/ZURICH
Timezone conversion is done with AT
SQL> select current_timestamp at time zone ‘US/Central’ from dual;
14.06.2005 09:25:11.77 US/CENTRAL
Timestamps do not accept addition of numbers. The only think you can add is interval, but take care, it is no longer a pure “addition”, because the associativity and commutativity properties are not retained.
(1Mon + ts) + 1Day != 1Mon + (ts + 1Day)
ts + 1Mon + 1Day != ts + 1Day + 1Mon
The + seems to be a “group operator” there, not the mathematical addition.
Anyway, if you want to know when a baby born 2000-02-29 will have his 18th birthday, you should rather ask a lawyer than a dba 🙂
order by to_number ?
to_number is often subject to generate ORA-01722: invalid number.
When I deal with integers, I prefer lpad.
ex: sort pseudo-numeric
select * from t order by lpad(col,20);
1
2
10
It performs well, and do not generate ora-01722. ora-01722 does not mean that I have bad data.
select name,value from v$parameter where name like ‘%pool_size’ order by to_number(value);
This works on my db, but it is bad coding, because to_number could well be executed for other parameters. There is no guarantee that oracle execute the function only when the where is true.
It is also more flexible
ex: first alpha alphabetically, then number numerically
order by translate(col,’~0123456789′,’~’), lpad(col,20)
a
aa
z
2
3
11
In 10g, regular expression will ease complex sorts
1
login.sql
Hi,
I am not recommending the use of glogin.sql and login.sql to do sql computation. It is degrading the performance and the maintenability of the scripts run over the database.
Myself I am using a big login.sql, which is located in the “SQLPATH” variable. I tested it with 7.3, 8.1.7, 9.2 and 10.0 on AIX. Be aware, it has side effects!
Ok, here it is
host if ! [ -t 0 ] || ! ( unset PERLLIB PERL5LIB; /bin/perl -e ‘use POSIX qw/getpgrp tcgetpgrp/;open(TTY,”/dev/tty”) or exit 1;exit(tcgetpgrp(TTY)!=getpgrp());’ ); then :;else echo @login_ux_fg;fi >/tmp/loginsql.sql
host chmod 2>/dev/null 666 /tmp/loginsql.sql
@/tmp/loginsql
Surprised? Does this looks like sql?
Not really. I am actually checking that sqlplus is not piped (-t 0), and run in terminal (dev/tty), in foreground (getpgrp). Only then I am executing login_ux_fg.sql
Well here is my login_ux_fg.sql, with colors, terminal header and more…
— login_ux_fg.sql
def _editor=vi
set editfile /tmp/tmporacle.sql
set ver off pages 40000 lin 80 long 1000000000 longc 60000 trims on con .
col file_name format a60
col member format a41
col tablespace_name format a20
col db_link format a20
col host format a20
— I am not interrested in that when select * from system views
col ini_trans nopri
col max_trans nopri
col INITIAL_EXTENT nopri
col NEXT_EXTENT nopri
col MAX_EXTENTS nopri
col MIN_EXTENTS nopri
col PCT_INCREASE nopri
col PCT_THRESHOLD nopri
col INCLUDE_COLUMN nopri
col FREELISTS nopri
col FREELIST_GROUPS nopri
col PCT_FREE nopri
— sql error can occurs, if db is down or when no privilege. The show must go on
set termout off echo off arrays 7
whenever sqlerror continue
whenever oserror continue
— save the current line of the query and the query (10g)
host echo > /tmp/lastquery.sql; chmod 2>/dev/null 666 /tmp/lastquery.sql
spool /tmp/lastquerycurrentline.txt
list *
spool off
host chmod 2>/dev/null 666 /tmp/lastquerycurrentline.txt
save /tmp/lastquery.sql rep
host chmod 2>/dev/null 666 /tmp/lastquery.sql
col “_myprompt” new_value myprompt
col “_mytitle” new_value mytitle
col “_user” new_value _USER
col “_privilege” new_value _PRIVILEGE
col “_connect_identifier” new_value _CONNECT_IDENTIFIER
col “_o_release” new_value _O_RELEASE
— default to null
select ‘x’ “_user”, ‘x’ “_privilege”, ‘x’ “_connect_identifier”, ‘x’ “_o_release” from dual where 1=2;
select nvl(‘&_user’, user) “_user”, nvl(‘&_privilege’, decode(user,’SYS’,’AS SYSDBA’,’PUBLIC’,’AS SYSOPER’)) “_privilege”, nvl(‘&_o_release’,’0′) “_o_release” from dual;
select nvl(‘&_connect_identifier’,name) “_connect_identifier” from v$database;
select nvl(‘&_connect_identifier’,substr(global_name, 1, instr(global_name||’.’,’.’))) “_connect_identifier” from global_name;
— check if we support colors / linesize
host if echo $TERM | grep -qsE ‘xterm|dtterm’ ; then echo def _SYSDBA=\”`tput colf1`\”;echo def _NORMAL=\”`tput colf4`\”;echo def _SYSOPER=\”`tput colf5`\”;echo def _RESET=\”`tput sgr0`\”;echo set lin `tput cols`;else echo def _SYSDBA=\”\”;echo def _NORMAL=\”\”;echo def _SYSOPER=\”\”;echo def _RESET=\”\”;echo set lin 80; fi > /tmp/color.sql; chmod 2>/dev/null 666 /tmp/color.sql
@/tmp/color.sql
.
select
decode(‘&_PRIVILEGE’, ‘AS SYSDBA’, ‘&_SYSDBA’, ‘AS SYSOPER’, ‘&_SYSOPER’, ‘&_NORMAL’)||
substr(‘&_USER’||
decode(‘&_CONNECT_IDENTIFIER’, null, null, ‘@&_CONNECT_IDENTIFIER’)||
decode(‘&_PRIVILEGE’, null, null, ‘ &_PRIVILEGE’)||
decode(&_O_RELEASE, null, null,
‘/’||
trunc(&_O_RELEASE/100000000) || ‘.’ ||
mod(trunc(&_O_RELEASE/1000000),100) || ‘.’ ||
mod(trunc(&_O_RELEASE/10000),100) || ‘.’ ||
mod(trunc(&_O_RELEASE/100),100)
),
1, 33)||
‘&_RESET’||
chr(10)||’SQL> ‘ “_myprompt”
from dual;
select
chr(27)||
‘]2;&_USER’||
decode(‘&_CONNECT_IDENTIFIER’, null, null, ‘@&_CONNECT_IDENTIFIER’)||
rtrim(‘ &_PRIVILEGE’)||
decode(&_O_RELEASE, null, null,
‘/’||
trunc(&_O_RELEASE/100000000) || ‘.’ ||
mod(trunc(&_O_RELEASE/1000000),100) || ‘.’ ||
mod(trunc(&_O_RELEASE/10000),100) || ‘.’ ||
mod(trunc(&_O_RELEASE/100),100)
)||
chr(7) “_mytitle”
from dual;
create
.
del 1 last
get /tmp/lastquery
.
host if echo $TERM | grep -qsE ‘xterm|dtterm’ ; then echo;echo ‘&mytitle’;echo; fi
host /usr/bin/sed 1>/tmp/lastquerycurrentline.sql 2>/dev/null -n ‘s/*.*//p’ /tmp/lastquerycurrentline.txt
host chmod 2>/dev/null 666 /tmp/lastquerycurrentline.sql
@/tmp/lastquerycurrentline
col “_myprompt” clear
col “_mytitle” clear
col “_user” clear
col “_privilege” clear
col “_connect_identifier” clear
set sqlp “&myprompt”
undef myprompt mytitle _SYSDBA _SYSOPER _NORMAL _RESET
undef _RC
set arraysize 15
set termout on
Only in sqlplus 10g, this script is run at each connection. It is basically good, because the prompt will be recalculated. What less good is, is that I will have to set whenever error to continue. I found no way to reset it to its original value after connect. Same for termout
so if I have a script
whenever sqlerror exit
connect /
create table …
It will not work as expected. You can search on asktom.oracle.com or on forums.oracle.com, you will find no solution to that problem, but many posts of mines…
shell + sqlplus
How to send commands to sqlplus ?
Use stdin
$ ( echo prompt $(hostname) $(date); echo desc emp ) | sqlplus -s scott/tiger
dbsrv85a Mon Jun 6 17:01:46 CEST 2005
Name Null? Typ
—- —– —
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
How do you retrieve data from your database with the shell and sqlplus ?
USERS=$(echo “select ‘@’,username from dba_users;” | # this is my stdin
sqlplus -s “/ as sysdba” | # sqlplus as a filter
awk ‘/^ORA-/ /^SP2-/ { print|”cat >&2″;exit 1 }
/^@/ { print $2 }’ # search for @
)
if [ $? != 0 ] # awk returned error
then
echo error >&2
# exit 1
fi
for u in $USERS # do something
do
echo hello $u
done
hello SYSTEM
hello SYS
hello PERFSTAT
hello SCOTT
hello DIP
hello U01
hello EXFSYS
hello MON
hello DBSNMP
hello OUTLN
How do I get OS output from sqlplus ?
The high end solution is to use Java. However, this implies you have Java in the Database (cost memory), you have the necessary privileges (which can be easily misused to destroy your system), and you use plsql. Have a look at my blog about Disk Free space.
Ok, let’s imagine I do not want to install all that stuff.
SQL> host uptime
17:09pm up 84 days, 23:32, 14 users, load average: 1.13, 1.23, 1.28
How do I get the return code?
SQL> prompt return code is &_RC
return code is 0
But this does not work in 10g 🙁
How do I use the os user, hostname, system date, ip address?
Use SQL!
SQL> select sys_context(‘USERENV’,’OS_USER’), host_name, sysdate, UTL_INADDR.GET_HOST_ADDRESS(host_name) from v$instance;
oracle dbsrv85a 06.06.2005 17:14:46 147.50.59.167
Ok, I want to know the OS and store it in a variable!
Hmm, hmm… I like to do those kind of things by sourcing temp files
SQL> def os=””
SQL> host echo def os=”$(uname -s)” > /tmp/tmpos.sql
SQL> start /tmp/tmpos.sql
SQL> select ‘&os’ from dual;
AIX
sys_connect_by_path
sys_connect_by_path is the only function provided by Oracle to get the the hierarchy path in one field, and it is only concatenating.
I just found out a way of doing a sum of the path :
let’s imagine I want the sum of the salary of all my hierarchy.
select ename, sys_connect_by_path(ename,’/’) hierarchy, length(replace(sys_connect_by_path(lpad(‘ ‘,sal/10),’/’),’/’))*10 sal
from emp
connect by mgr=prior empno
start with mgr is null ;
ENAME HIERARCHY SAL
———- —————————— ——-
KING /KING 5000
JONES /KING/JONES 7970
SCOTT /KING/JONES/SCOTT 10970
ADAMS /KING/JONES/SCOTT/ADAMS 12070
FORD /KING/JONES/FORD 10970
SMITH /KING/JONES/FORD/SMITH 11770
BLAKE /KING/BLAKE 7850
ALLEN /KING/BLAKE/ALLEN 9450
WARD /KING/BLAKE/WARD 9100
MARTIN /KING/BLAKE/MARTIN 9100
TURNER /KING/BLAKE/TURNER 9350
JAMES /KING/BLAKE/JAMES 8800
CLARK /KING/CLARK 7450
MILLER /KING/CLARK/MILLER 8750
The LPAD is transforming a number in a string, then length will calculate the length of the calculated string. It is limited to 4000 char. That’s why I divided it by 10.
Let’s do it more flexible with PLSQL
create or replace function eval(expr varchar2) return number is retval number;
begin execute immediate ‘select ‘||expr||’ from dual’ into retval; return retval; end;
/
select ename, sys_connect_by_path(ename,’/’) hierarchy, eval(sys_connect_by_path(sal,’+’)) sal
from emp
connect by mgr=prior empno
start with mgr is null
/
Just using + to add, simple, is not it?
Grid without X
We just received new PCs. Brand new with XP. Nice? Let’s see!
As usual, I started my Exceed and logged on my AIX server. I tried to start the Grid Control Engine (opmn). Hard luck. Failed to start OC4J instance 🙁
The first problem is, when I installed the Grid, my old workstation DISPLAY name has been registered in opmn.xml!
The second problem, my server has no graphic card, and no X server running.
Actually, I do not need X on this server. But to let the Grid start, I will use Frame Buffer. In AIX, you install X11.vfb base package, then mkitab “xvfb:2:once:/usr/lpp/X11/bin/X -force -vfb :1 >tmp/x.txt 2>&1″ to start it at the next reboot. Start it now as well.
Then edit your opmn.xml file and set the DISPLAY variable to localhost:1
<variable id=”DISPLAY” value=”localhost:1″/>
Recursive SQL
One of the most common school exercice about recursion is the factorial. Guess what, I am going to do it in sql with hierarchies!
I use the following ln property :
x1*…*xn = exp(ln(x1)+..+ln(xn)))
Ok, here it is
SQL> select n, (select exp(sum(ln(level)))
from dual
connect by level<=n) "N!" from
(select rownum n from dual connect by level<7);
N N!
---- ----
1 1
2 2
3 6
4 24
5 120
6 720
Get disk space
I just read today on sun.com that checking disk space in java will be platform independent in the java.io.File class before 2007, add a few years until it is integrated in Oracle. But I cannot wait that long, so I decided to write my own code with “df” on my AIX box.
Ok, let’s do java. The horrible regexp there is parsing df.
create or replace and compile
java source named “Df”
as
import java.io.*;
public class Df
{
public static int getFree(String args)
{
return Integer.parseInt(df(args).replaceAll(“[^0-9]*[ ]*[1 ][0-9 ][0-9][0-9][%-][^ ]* [^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*”,””).trim());
}
public static String getFS(String args)
{
return df(args).replaceAll(“[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[1 ][0-9 ][0-9][0-9][%-][^ ]* [^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ /]*”,””).trim();
}
public static String df(String args)
{
String rc = “”;
try
{
Process p = Runtime.getRuntime().exec(“/bin/df -kt “+args);
int bufSize = 4096;
BufferedInputStream bis = new BufferedInputStream(p.getInputStream(), bufSize);
int len;
byte buffer[] = new byte[bufSize];
while ((len = bis.read(buffer, 0, bufSize)) != 1)
rc += new String(buffer, 0, len-1);
p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
return rc;
}
}
}
/
Now I create two functions
create or replace
function getFree( p_cmd in varchar2) return number
as
language java
name ‘Df.getFree(java.lang.String) return int’;
/
create or replace
function getFS( p_cmd in varchar2) return varchar2
as
language java
name ‘Df.getFS(java.lang.String) return String’;
/
Ok, let’s see if my files can autoextend
select file_name, BYTES/1024 K, INCREMENT_BY*BYTES/BLOCKS/1024 INC, MAXBYTES/1024 MAXKBYTES, GETFREE(FILE_NAME) FREE, GETFS(FILE_NAME) FS
from dba_data_files
FILE_NAME K INC MAXKBYTES FREE FS
—————————————— ———- ———- ———- ———- ————–
/dbms/oracle/LSC68/data/system01LSC68.dbf 332800 25600 2097152 3579528 /dev/lsc68
/dbms/oracle/LSC68/data/undo01LSC68.dbf 184320 2048 204800 3579528 /dev/lsc68
/dbms/oracle/LSC68/data/sysaux01LSC68.dbf 228352 25600 2097152 3579528 /dev/lsc68
/dbms/oracle/LSC68/data/users01LSC68.dbf 24576 2048 2097152 3579528 /dev/lsc68
/dbms/oracle/LSC68/data/sysaud01_LSC68.dbf 4096 5120 204800 3579528 /dev/lsc68
/app/oracle/product/10.1.0.3/dbs/t.dbf 1024 0 0 851784 /dev/ora10103
Sounds good! plenty of free space to let the files grow!
One example about hierarchies
Today morning I just received a question from a friend where I used hierarchies :
> Let’s assume a couple of persons have bought some cakes togeher and they want to eat it:
>
> Create table cake_owners
> (owner# number,
> cake# number,
> constraint cake_pk primary key (owner#,cake#)
> using index);
>
> insert into cake_owners values (1,100);
> insert into cake_owners values (1,200);
> insert into cake_owners values (2,200);
> insert into cake_owners values (2,300);
> insert into cake_owners values (3,300);
> —–
> insert into cake_owners values (4,500);
> —–
> insert into cake_owners values (6,600);
> insert into cake_owners values (7,600);
> —–
> commit;
>
> So owner 1 owns cake 100 and a part of cake 200. Owner 2 owns a part of cake 200 and a part of cake 300 where the reset is owned by 3.
> Owner 4 owns cake 500 alone and cake 600 is owned by 2 persones 6 and 7.
>
> Now I want to place all owners on one table who share parts of their cake so that all cakes can be eaten compleatly without leaving the table.
> The table must be as small as possible and I want to know how many tables are needed and how big each one must be, or who is sitting at it.
> Of course a person can sit only at one table.
>
> In this much simplyfied example I need 3 tables the biggest one needs 3 chairs.
This is typically solved with PL/SQL, but with plain SQL, I need a hierachy (cake=prior cake and ownerprior owner) or (cakeprior cake and owner=prior owner). This is going to loop, with 10g I will use nocycle. With connect by root and count, I will found out the table master with the most guests.
select dense_rank() over (order by rootowner) tableno, owner#
from (
select owner#,
row_number() over
(partition by owner# order by owner_c, rootowner) r,
rootowner
from (
select
rootowner,
count(distinct owner#) over
(partition by rootowner) owner_c,
owner#
from (
select
owner#, cake#,
connect_by_root owner# rootowner
from
cake_owners
connect by nocycle
(owner#prior owner# and cake#=prior cake#)
or
(owner#=prior owner# and cake#prior cake#)
)
)
) where r=1
order by tableno, owner#;
TABLENO OWNER#
——- ——
1 1
1 2
1 3
2 4
3 6
3 7
Hierarchical queries
The typical hierarchical query is you want to select your boss, and the boss of your boss, etc.
It could look like
select prior ename ename, ename mgr
from emp
connect by prior mgr=empno
start with ename=’SCOTT’;
SCOTT
SCOTT JONES
JONES KING
I start with Scott and the hierarchy is built. I can use the pseudo column LEVEL in hierarchical queries.
One of the biggest problem in hierarchical queries is ORA-01436: CONNECT BY loop in user data.
If you are your own boss, or if you are the boss of your boss, then you have built a cycle. It is probably not wished to have this relation, but it cannot be enforced by a constraint and before 10g, it was difficult to detect. In 10g, you have a new clause, CONNECT BY NOCYCLE, which detect cycles and give flag.
Here I want to present an alternative way of using hierarchies.
Situation:
I have five boxes of five different sizes. Tiny up to 5 liters. Small up to 10 liters. Medium up to 15 liters. Big up to 20 liters. Hudge up to 25 liters.
I have 3 fluids, and I want to find the smallest boxes.
Yeah! very easy, you do select fluids.volume, min(box.capacity) from fluids, box where capacity>=volume group by volume, do not you?
Ok, but I do not want to mix the fluids! So I need 3 different containers.
This means, I will start with the first product, find the smallest box, go to the second, find the smallest free box, and so on.
Hmm… It seems an impossible task with analytics, least, min, keep, lag, lead, ???
Ok, I am going to build a hierarchy, based on volume > prior volume and capacity > prior capacity.
Then I do a min with the sys_connect_by_path function.
select max(sys_connect_by_path(volume,’/’)) volume_path,
ltrim(min(lpad(sys_connect_by_path(capacity,’/’),999))) capacity_path
from box,
(select row_number() over (order by volume) r, count(*) over () c, volume from fluids)
where volume
connect by capacity > prior capacity and r > prior r;
/7/11/14 /10/15/20
There is also one more CONNECT I would like to briefly mention, is the connect without prior.
select level from dual connect by level<11;
It is a special way of creating pivot tables, but it is fairly dangerous, and could make your session / database hang, depending on your oracle version.
Oracle analytics in basic sql queries
When I first saw analytics appearing in Oracle last century, I did not realised they were going to change my way of writting basic SQL queries.
Some (Variance, deviance) are truely mathematical and still reserved for statistical analysis.
Here I will try to describe ROW_NUMBER :
Back in Oracle 7, I remember to have written a lot of reports using in max subqueries, like in
select deptno,ename,sal from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);
With analytics, I can rewrite it with
select deptno,ename,sal
from (
select emp.*, rank() over (
partition by deptno
order by sal desc) r
from emp
) where r=1;
If I want to get exactly one row per deptno, I could then write something like
select deptno,ename,sal
from (
select emp.*, row_number() over (
partition by deptno
order by sal desc) r
from emp
) where r=1;
or better
select deptno,ename,sal
from (
select emp.*, row_number() over (
partition by deptno
order by sal desc, empno) r
from emp
) where r=1;
row_number will select only one row. I prefer the second, because empno is a primary key and the result will be constant over time. In the first solution, I cannot determine which row will be returned, and Oracle may choose one on the morning, and another one in the afternoon, depending on the execution plan (new index/new stats/different load/…).
Note that I can also select the 2nd biggest salary (r=2), or the top 5 (r<=5) It is also very performant, because you are doing only one full table scan instead of two