AnySQL.net English

    Oracle client is not required when using AnySQL, and you do not need to configure tnsnames when connecting to database, as following:

ASQL> conn system/oracle@localhost:1521:xe
Database connected.

    I combine some useful SQLs in AnySQL as "ora key arg ..." command, it's very easy to use, such as get the space information of tablespaces:

ASQL> ora tsfree

TABLESPACE FILES SIZE_MB FREE_MB MAXFREE PCT_USED PCT_FREE
---------- ----- ------- ------- ------- -------- --------
SYSAUX        1    430      3    2.44    99.3      0.7
USERS          1    100  98.06  98.06    1.94    98.06
SYSTEM        1    340    6.75    5.94    98.01    1.99
UNDO          1      90  14.19      3    84.24    15.76

4 rows returned.

    And in AnySQL you do not need to setting the column length when querying, it will automatically calculate the maximum length, so you can omit lots of "col ... format ..." commands, lets query the view v$datafile now:

ASQL> SELECT NAME,BYTES FROM V$DATAFILE;

NAME                                  BYTES
--------------------------------- ---------
C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF 356515840
C:\ORACLEXE\ORADATA\XE\UNDO.DBF    94371840
C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF 450887680
C:\ORACLEXE\ORADATA\XE\USERS.DBF  104857600

    And lots of other news, I will introduce them later.

    AnySQL was wrote in Java language, so it could be run in Unix/Linux, the downloaded file is for Windows, but you can move it to Unix/Linux by the following steps (first you need to install JRE 1.4 or above version, else you cannot run it):

1, Where to install it? For example: /usr/AnySQL.

2, Make a sub directory "jlib", For example: /usr/AnySQL/jlib.

3, Copy oasql.jar and oracle.jar to jlib directory, For example: /usr/AnySQL/jlib

4, Create a shell file named with "asql" to start AnySQL.

5, Put the directory to PATH variable, you can just type asql to start AnySQL.

    The shell file "asql" to start AnySQL could be (What I am currently using):

#!/bin/sh

ASQL_CMD=`which $0`
ASQL_HOME=`dirname $ASQL_CMD`
$JAVA_HOME/bin/java -server -Xms8m -Xmx16m \
    -cp $ASQL_HOME/jlib/oasql.jar com.asql.tools.ASQL $*

    No Oracle client required, and you could run it in character mode. Welcome to deploy it under Unix/Linux.

    The "DESCRIBE" command in AnySQL tell you more information than SQL*Plus, as following, these information is useful for DBA:

ASQL> desc scott.emp

NO# NAME                      NULLABLE TYPE        
--- ------------------------- -------- ------------
  1 EMPNO                     NOT NULL NUMBER(4)  
  2 ENAME                              VARCHAR2(10)
  3 JOB                                VARCHAR2(9)
  4 MGR                                NUMBER(4)  
  5 HIREDATE                           DATE        
  6 SAL                                NUMBER(7,2)
  7 COMM                               NUMBER(7,2)
  8 DEPTNO                             NUMBER(2)  

TYPE  ISUNQ  INDEX_NAME       NO# COLUMN_NAME DESCEND
------ ------ --------------- --- ----------- -------
NORMAL UNIQUE PK_EMP            1 EMPNO       ASC    

PARTITIONED AVG_ROW_LEN NUM_ROWS BLOCKS EMPTY_BLOCKS
----------- ----------- -------- ------ ------------
NO      

    Before doing some DDL(Rename, Drop etc.), we should check the object dependencies of the underline object, will it invalid some triggers on other tables? with the "DEPEND" command, it will tell which objects it depended on and which objects depend on this object. If you get procedure or view compile failure, you could use this command to find out the root cause.

ASQL> DEPEND SYS.DBMS_SUMMARY

Reference:
TYPE         D_OWNER D_NAME           D_TYPE       DEPEND
------------ ------- ---------------- ------------ ------
PACKAGE BODY SYS     STANDARD         PACKAGE      HARD
PACKAGE      SYS     STANDARD         PACKAGE      HARD
PACKAGE BODY SYS     DBMS_SUMMARY     PACKAGE      HARD
PACKAGE BODY PUBLIC  DBMS_OLAP        SYNONYM      HARD
PACKAGE BODY SYS     DBMS_SUMREF_UTIL PACKAGE      HARD
PACKAGE BODY SYS     DBMS_SUMADVISOR  PACKAGE      HARD
PACKAGE BODY SYS     DBMS_OLAP        NON-EXISTENT HARD

Referenced By:
TYPE    R_OWNER R_NAME       R_TYPE       DEPEND
------- ------- ------------ ------------ ------
PACKAGE PUBLIC  DBMS_SUMMARY SYNONYM      HARD
PACKAGE PUBLIC  DBMS_OLAP    SYNONYM      HARD
PACKAGE SYS     DBMS_SUMMARY PACKAGE BODY HARD

    In my other tools or in STATSPACK, we always see a number called "hash value", use the following command to get the full SQL text:

ASQL> ora hash 3109775760

SELECT /* AnySQL */ SQL_TEXT "SQL Executing"
FROM V$SQLTEXT_WITH_NEWLINES
WHERE HASH_VALUE = TO_NUMBER(:1)
ORDER BY PIECE

    More features will be introduced later.

    Following are the objects list in the test schema:

ASQL> select * from tab;

TNAME                   TABTYPE CLUSTERID
----------------------- ------- ---------
A_V                     VIEW
T_HASH                  TABLE
T_LOB                   TABLE
T_LONG                  TABLE

11 rows returned.

    How to get the difinition SQL for a view, or source code of a procedure? Using the "SOURCE" command:

ASQL> source anysql.a_v

select "TNAME","TABTYPE","CLUSTERID" from tab

    Get a table recreation syntax (for reference only, still under testing ...) :

ASQL> source anysql.t_lob

CREATE TABLE ANYSQL.T_LOB
(
   FNAME VARCHAR2(20) ,
   FTEXT CLOB
)
TABLESPACE USERS INITRANS 1 PCTFREE 10
STORAGE ( FREELIST GROUPS 1 FREELISTS 1)
NOCACHE LOGGING
/

    Get the table and relative indexes' size:

    When tuning, SQL execution plan is very important for a DBA, in AnySQL I provide few ways to implement it.

    Using "EXPLAIN PLAN ..." to get the plan of a execution plan, there is no "FOR" keywords compare to the command in SQL*Plus.

ASQL> EXPLAIN PLAN SELECT * FROM T_HASH;

SQLPLAN                                    COST  CARD KBYTE PS PE
------------------------------------------ ---- ----- ----- -- --
  0     SELECT STATEMENT Optimizer=RULE      20 10000    59      
  1   0   PARTITION HASH (ALL)                              1  8
  2   1     TABLE ACCESS (FULL) OF T_HASH    20 10000    59 1  8

    Display the execution plan from V$SQL_PLAN (current executing plan) by SQL hash value with the "ORA PLAN hash_value" command:

ASQL> ORA PLAN 821132411

SQLPLAN                                   COST  CARD KBYTE PS PE
----------------------------------------- ---- ----- ----- -- --
  0     SELECT STATEMENT Optimizer=RULE     20                  
  1   0   PARTITION HASH (ALL)                             1  8
  2   1     TABLE ACCESS (FULL) OF T_HASH   20 10000    59 1  8

3 rows returned.

    Get the SQL text by a SQL hash value, and then run explain plan command to get the execution plan by "ORA XPLAN hash_value" command, will display both the SQL text and execution plan.