DDL to create table scott.emp and DML to load data

Purpose
=======
I often use emp (and dept) table to build test cases, to prove concept and explore new features.
It's not easy to find the original DDL and DML to create the table and load the data.
So I just get it by myself. I think these DDL/DML are useful to you too.

Notes: I hope you don't have tables named "dept" and "emp" in your application schema.

SQL>


drop table dept purge;

CREATE TABLE dept (
 deptno NUMBER(2),
 dname  VARCHAR2(15),
 loc    VARCHAR2(15),
 constraint dept_pk primary key (deptno)
);

INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');

COMMIT;

drop table emp purge;

CREATE TABLE EMP
( EMPNO NUMBER(4) NOT NULL,
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7, 2),
  COMM NUMBER(7, 2),
  DEPTNO NUMBER(2),
  constraint emp_pk primary key (Empno),
  CONSTRAINT emp_fk1 FOREIGN KEY (deptno) REFERENCES dept
);

INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK',    7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD',  'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER',  7839, TO_DATE('2-APR-1981',  'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

commit;


Demo de-duplicate on emp.ename
=======

INSERT INTO EMP VALUES (7935, 'MILLER', 'ANALYST', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
INSERT INTO EMP VALUES (7936, 'MILLER', 'SALESMAN', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
commit;

A deduplicate will be like this,

DELETE FROM scott.emp
WHERE ROWID IN
 (
  SELECT rid
    FROM (SELECT /*x parallel(a) */
                 ROWID rid, ename,
                 ROW_NUMBER () OVER (PARTITION BY ename ORDER BY empno) rn
            FROM scott.emp a)
  WHERE rn <> 1
);


Gather table statistics 
=======
begin
    dbms_stats.gather_table_stats(
        ownname      => user,
        tabname      =>'EMP',
        estimate_percent => 0.2,
        method_opt   => 'for all columns size 1'
    );
end;
/

- 木匠

ORA-00911: invalid character

This one is simple, but useful.  :)

When compile  a VIEW, the error "ORA-00911: invalid character" poped up.

@biprd_abc.com> @/dev/SQL/v_nov09.sql

    AND f.COL_ABC not in

                          *

ERROR at line 444:

ORA-00911: invalid character

=====

 not in

 not in

========

Copy above 2 "not in" into a text editor, you'll see the invisible Special Character.

TextWrangler can show the invisible Special Characters.  I couldn't find this function in SubLime Text 2.

The solution is simple too: remove the string contains Special Character, and then type them again.

Thanks,

Charlie

runsql.sh shell tool to run a SQL file

Purpose: Run SQL or PL/SQL in a file.

Background:

For the SQL that takes a long time to finish, we usually let them run as a backend job in database server. E.g. rebuild index, Online Table Redefinition.
This shell tool will make it simple to call a SQL_file.
.
When doing low level things I prefer to run as sys via sqlplus on the local server (not via client sqlplus and never thru tool).  General good practice.  Eliminate workstation or network flaps etc from causing issues.
.

Notes: use full name when referencing the database objects. e.g. scott.emp;

cat  $SQLPATH/runsql.sh
.
{code start}

#Goal: run SQL in a file, so we can run it with nohup in background,


if [ $# != 1 ]
then
 echo Usage: $0 "SQL file_name"
 echo Usage 2: nohup $0 "SQL_file_name > 0.log &"
 exit
else
 echo $1
 sql_file=$1
fi

#my_path=$(pwd)
#. $HOME/.bash_profile
#echo $my_path

sqlplus -S / as sysdba  <<+++
set wrap off
--set feedback off
set pagesize 0
set verify off
set termout off

set echo off
set verify off
set arraysize 2000
set linesize 5000
set trimspool on
set long 5000
set serveroutput on
set timing on

column filename new_val filename

select '$sql_file'||'_'|| instance_name || to_char(sysdate, '_yyyymmdd_hh24miss')||'.log' filename
from v\$instance;

spool &filename

select sysdate from dual;

@$sql_file

exit;

+++

MAIL_TO=your_name@abc.com
CC=your_group_name@abc.com

tail "$sql_file"*.log | mail -s "SQL $sql_file done at $(hostname) $ORACLE_SID" -c $CC $MAIL_TO

{code end}
.