Run SQLcl commands from SQL Developer

SQLcl has many new little tricks that can help us on a daily basis, below is an example how we can execute SQLcl command from SQL developer:

Here is an info <table>

infotable

Here is an DDL for the same table:

ddl <table name>

ddltable

Advertisements

12c DISPLAY_NAME column in v$event_name view

in 12c Oracle has introduced and new field in the v$event_name view called DISPLAY_NAME.

After review the contents on more then 1800 events, I noticed the most display name were the same as the name with exception of a 19.

Here are they:

Using timing in SQL*Plus

Using timing feature on SQL*Plus one can monitor elapsed time.

Example:
— start timer with the name monitor_timing, name is optional
SQL> timing start monitor_timing

SQL> select sysdate from dual;

SYSDATE
———
11-APR-10

— display time elapsed for current timer
SQL> timing show
timing for: monitor_timing
Elapsed: 00:00:21.35

SQL> select count(1) FROM dba_objects;

COUNT(1)
———-
9728

— show elapsed time
SQL> timing show
timing for: monitor_timing
Elapsed: 00:00:46.16

— stop timer and it clears the current timer
SQL> timing stop
timing for: monitor_timing
Elapsed: 00:00:54.86

— clears all timers and prints the elapsed time of all the timers
SQL> clear timing

Note: One can start more that one timer at the same time.

How to skip blank lines in between SQL statements in SQL*Plus?

If a SQL script file has blank lines in between SQL statements one may see errors when executing the script in SQL*Plus so if one would like to ignore the blanklines in SQL*Plus one can ignore them by set blanklines on.

Example:
SQL> select object_name
2
SQL> from dba_objects;
SP2-0734: unknown command beginning “from dba_o…” – rest of line ignored.
SQL> set sqlblanklines on
SQL> select object_name
2
3 from dba_objects
4 where rownum < 3;

OBJECT_NAME
——————————————————————————–
ICOL$
I_USER1
— Disable blank lines in between SQL statements
SQL> set sqlblanklines off