Hey Scott, where have you been ?

Today I missed Scott in my emp table. When selecting from EMP, Scott is not there. Gone… Ok, let’s recreate the scott schema. C:> sqlplus / as sysdba SQL*Plus: Release 10.2.0.3.0 – Production Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 SYS@lsc02> spool scott.txt SYS@lsc02> drop […]

How to cron?

RTFM is not the best answer … man crontab SunOS 5.10 Last change: 10 Nov 2005 User Commands crontab(1) A crontab file consists of lines of six fields each. The fields are separated by spaces or tabs. The first five are integer patterns that specify the following: minute (0-59), hour (0-23), day of the month […]

drop all objects

warning: the script below is destructive and not 100% safe update: it is unsafe to drop SYS_ objects, check for instance note 579399.1 A question was posted on the french forums of developez.net about how to drop all objects of an user. The drop user toto cascade; followed by create user toto identified by tott; […]

select distinct collect

I answered a question on otn today about distinct. Reprased, how to select distinct collection? select job, collect(distinct deptno) deptnos from emp group by job; JOB DEPTNOS ——— ——————————————- ANALYST SYSTPTJCzBffh0AjgQ59n0o3QCA==(20) CLERK SYSTPTJCzBffh0AjgQ59n0o3QCA==(10, 20, 30) MANAGER SYSTPTJCzBffh0AjgQ59n0o3QCA==(10, 20, 30) PRESIDENT SYSTPTJCzBffh0AjgQ59n0o3QCA==(10) SALESMAN SYSTPTJCzBffh0AjgQ59n0o3QCA==(30) 5 rows selected. select distinct collect(distinct deptno) deptnos from emp group by […]

Select bottom rows

Today I answered a question on otn regarding order by. Let’s build a different test case. You have a view that contain an order by. You want to retrieve the last 5 records. However you do not have access to the sort column. SYS@LSC01> GRANT CREATE VIEW TO USER_A IDENTIFIED BY A; Grant succeeded. SYS@LSC01> […]