Running shell script from Oracle

It’s simple!!

Especially with 10g, it is even more simpler.

Here is an example:

Step 1: Create a shell script and grant execute priv to oracle user. I created a simple one:

bash-3.00$ cat /tmp/test.sh
#!/usr/bin/ksh
echo "Test succeeded `date`" >> /tmp/test.log

Note: Do not forget to start your shell script with the shell you are using.
>> #!/usr/bin/ksh should be present in your shell script otherwise, the job will fail with ORA-27369 job of type EXECUTABLE failed with

bash-3.00$ chmod 755 /tmp/test.sh

Step 2: Create the job:
Remember user doing the following should have “CREATE JOB” privilege.


SQL> BEGIN
2 DBMS_SCHEDULER.CREATE_JOB (
3 job_name => 'TEST_SHELL',
4 job_type => 'EXECUTABLE',
5 job_action => '/tmp/test.sh',
6 start_date => sysdate,
7 repeat_interval => 'FREQ=MINUTELY; INTERVAL=1', /* every one hour */
8 enabled => TRUE,
9 comments => 'Testing shell script from Oracle');
10 END;
11 /

PL/SQL procedure successfully completed.

Step 3: Check if the job is running:

bash-3.00$ cat /tmp/test.log
Test succeeded Wed Jan 31 01:02:23 PST 2007
Test succeeded Wed Jan 31 01:03:23 PST 2007
Test succeeded Wed Jan 31 01:04:23 PST 2007

Index skip Scan – how?

With Oracle 9i, CBO is equipped with many more features, one of them is “Index skip scan” .This means even if you have a composite index on more than one column and you use the non-prefix column alone in your SQL, it may still use index.

I said “may” because CBO will calculate the cost of using the index and if it is more than that of full table scan, then it may not use index.

Index skip scan works differently from a normal index (range) scan.
A normal range scan works from top to bottom first and then move horizontal.
But a Skip scan includes several range scans in it. Since the query lacks the leading column it will rewrite the query into smaller queries and each doing a range scan.

Ex:
SQL> create table test (a number, b number, c number);

Table created.

SQL> create index test_i on test(a,b);

Index created.

SQL> begin
2 for i in 1 .. 100000
3 loop
4 insert into test values(mod(i, 5), i, 100);
5 end loop;
6 commit;
7 end;
8 /

PL/SQL procedure successfully completed.


SQL> exec dbms_stats.gather_table_stats(ownname => 'SAC', tabname => 'test', cascade => true);

PL/SQL procedure successfully completed.

SQL> set autotrace trace exp
SQL> select * from test where b=95267;

Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=22 Card=1 Bytes=10)


1 0
TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (TABLE) (Cost=22 Card=1 Bytes=10)


2 1
INDEX (SKIP SCAN) OF 'TEST_I' (INDEX) (Cost=21 Card=1)


I above example, “select * from test where b=95267” was broken down to several small range scan queries. It was effectively equivalent to following

Select * from test where a=0 and b=95267
Union
Select * from test where a=1 and b=95267
Union
Select * from test where a=2 and b=95267
Union
Select * from test where a=3 and b=95267
Union
Select * from test where a=4 and b=95267;

In concrete, saying that skip scan is not as efficient as normal “single range scan” is correct. But yet saves some disk space and overhead of maintaining another index.

External Tables - 10g

Until 9i external tables could only be used for read purposes.
But 10g allows you to populate external tables.

here is how it does:

Step 1: - Create a directory

SQL> create directory my_dir as '/usr/test'; --- Make sure Oracle OS user has
privilege to write on this directory.

Step 2: - Grant permission to user SCOTT

SQL> grant read,write on directory my_dir to scott;

Step 3: - Create the External Table: -

SQL> CREATE TABLE scott.external_emp_dept
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY my_dir
LOCATION ('emp_dept.exp')
)
reject limit unlimited
AS
select e.ename,d.dname from emp e, dept d where
e.deptno=d.deptno;

SQL> select * from scott.external_emp_dept;

ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH

ENAME DNAME
---------- --------------
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING

And you will find a file generated i.e. 'emp_dept.exp' in /usr/test directory.

Now you can take this file to the target database and create an external table
and associate the file with that table.

Step 4: - to be executed on the source database
SQL> set long 2000
SQL> Select dbms_metadata.get_ddl('TABLE','EXTERNAL_EMP_DEPT') from dual;

The above command will give you the DDL command to create the table at target
database.

Similar note is also available on metalink 249321.1 which was also written by me when I was with Oracle Support.