Replace Consecutive Blanks With a Single Blank

We can use regexp_replace() function to replace multiple consecutive blanks with a single blank. For example, we have the following table.

SQL> select * from tbl_text;

        ID VAL
---------- --------------------------------
         1 hello     world !
         1 how   are     you doing

In the following query, regexp_replace() finds patterns that have 2 or more spaces and replace them with single blank.

SQL> select val, regexp_replace(val, '[[:space:]]{2,}',' ' ') val2 from tbl_text;

VAL                              VAL2
-------------------------------- --------------------------------
hello     world !                hello world !
how   are     you doing          how are you doing

Insert a Record into a Table Every Second

We can using Oracle function dbms_lock.sleep(n_seconds) to run queries at certain interval, such as inserting data into a table every second. Function dbms_lock.sleep(n_seconds) will suspend the session for n_seconds. To illustrate this function, we insert the system time into a table every second 20 times.

SQL> create table tbl_x (id number, dt date);

SQL> begin
for i in 1..20 loop
insert into tbl_x values(i, sysdate);
dbms_lock.sleep(1);
end loop;
commit;
end;
SQL> /

SQL> select * from tbl_x;

 ID DT
---------- -------------------
  1 12-12-2014 12:44:41
  2 12-12-2014 12:44:42
  3 12-12-2014 12:44:43
  4 12-12-2014 12:44:44
  5 12-12-2014 12:44:45
  6 12-12-2014 12:44:46
  7 12-12-2014 12:44:47
  8 12-12-2014 12:44:48
  9 12-12-2014 12:44:49
 10 12-12-2014 12:44:50
 11 12-12-2014 12:44:51
 12 12-12-2014 12:44:52
 13 12-12-2014 12:44:53
 14 12-12-2014 12:44:54
 15 12-12-2014 12:44:55
 16 12-12-2014 12:44:56
 17 12-12-2014 12:44:57
 18 12-12-2014 12:44:58
 19 12-12-2014 12:44:59
 20 12-12-2014 12:45:00

20 rows selected.

As it is shown above, records are inserted every 1 second.

Oracle User Privileges for Data Analysts

As data analysts, we often perform various data work within databases such as Oracle. I have found the following user privileges are very helpful. If possible, database administrators should grant us these privileges.

PRIVILEGE
----------------------------------------
CREATE TABLE
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE MATERIALIZED VIEW
CREATE ANY DIRECTORY
CREATE JOB
CREATE MINING MODEL

Create Views Based on Nonexistent Tables

In Oracle, it is possible to create views first based on nonexistent tables. After views are created, we can create the underlying tables and the view will work.
To create a view based on tables that do not exist, we using "create force view" as shown below.

SQL> create force view v_tbla as select * from tbla;
Warning: View created with compilation errors.

It is OK that we got the compilation errors. The key word "force" is necessary. Without it, the view will not be created.

SQL> create view v_tbla as select * from tbla;
create view v_tbla as select * from tbla
                                    *
ERROR at line 1:
ORA-00942: table or view does not exist

If we query this view, we will get an error message. This is fine. The error message will disappear after we create the underlying table tbla.

SQL> select * from v_tbla;
select * from v_tbla
              *
ERROR at line 1:
ORA-04063: view "DMUSER.V_TBLA" has errors

Now we create table that the view is based on and populate it with data. As we see, we can query the view!

SQL> create table tbla (id number, val varchar2(32));
Table created.

SQL> insert into tbla values (1,'hello');
1 row created.

SQL> select * from tbla;

 ID VAL
---------- --------------------------------
  1 hello

SQL> select * from v_tbla;

 ID VAL
---------- --------------------------------
  1 hello

Display Long CLOB Text in Sqlplus

In the following example, the display of CLOB text is truncated.

SQL> create table tbl_test (id number, val clob);

Table created.

SQL> insert into tbl_test values(1,'this is a test.ddlkjdfklj dflkjdlkj 
 jdfhi  dfdlkjflkdlkoieuiooiop jdofoidu  baskjhfp  dfdk dkfiegps 
  dfdalkfaidel hdlfjdlflkd dafjdflkdi igod kdigl dfodfud');

1 row created.

SQL> select * from tbl_test;

        ID
----------
VAL
--------------------------------------------------------------------------------
         1
this is a test.ddlkjdfklj  dflkjdlkj  jdfhi  dfdlkjflkdlkoieuiooiop 
jdofoidu  ba

We can use "set long" to specify how many bytes of CLOB to display. In the following example, "set long 2000" tells sqlplus to display up to 2000 bytes of CLOB text.

SQL> set long 2000
SQL> select * from tbl_test;

        ID
----------
VAL
--------------------------------------------------------------------------------
         1
this is a test.ddlkjdfklj  dflkjdlkj  jdfhi  dfdlkjflkdlkoieuiooiop 
jdofoidu  baskjhfp  dfdk  dkfiegps   dfdalkfaidel hdlfjdlflkd dafjdflkdi
 igod kdigl dfodfud 

Dump the Oracle Schema

We can use Oracle data pump utility to dump the whole schema or selected database objects. The following command dump the schema.

$ expdp prod/XXXX schemas=prod directory=ORA_DATA_EBS_BK dumpfile=prod_bk.dmp logfile=prod_bk.log

The above command creates two files under directory ORA_DATA_EBS_BK.

-rw-r--r-- 1 oracle dba       5129 Dec  7 08:07 prod_bk.log
-rw-r----- 1 oracle dba    2650112 Dec  7 08:07 prod_bk.dmp

We can import them back into a database using impdp command.

$ impdp prod/XXXX schemas=prod directory=ORA_DATA_EBS_BK dumpfile=prod_bk.dmp