AnySQL.net English

    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.

    In AnySQL, we could manipulate LONG/LONG RAW/BLOB/CLOB easily by uploading a client file to the database, however it's impossible in SQL*Plus:

    Create a table with LONG type column, as following:

ASQL> DESC T_LONG

NO# NAME             NULLABLE TYPE
--- ---------------- -------- ----
  1 COL1                      LONG

    Now I will insert a shell script file in to the table, you can insert or update this field by declare a CLOB (for LONG and CLOB columns) or BLOB (for LONG RAW and BLOB columns) type host variable, and define it a file name (including path) as variable value, then reference it (start with ":") in the SQL as following:

ASQL> VAR P_LONG CLOB
ASQL> define p_long=otop
ASQL> insert into t_long values (:p_long);

1 rows affected.

ASQL> commit;

    And we could directly query the long value type in AnySQL directly, maximumly 64KB will be displayed:

    I have found a small mistake in the SQL Loader control file generated by AUL/MyDUL, if you use the control file directly, it will skip the first row of the text file. That's because I copy the SQL Loader parameters from another text unload tool which put the column name at the first line, while AUL/MyDUL did not put the column name as the first line of recovered text file. Please remove the "SKIP=1" in the "OPTIONS(..." line. So sorry for the mistake.

    Old example of the control file:

--
-- Generated by AUL/MyDUL, for table hr.test
--
OPTIONS(DIRECT=TRUE,READSIZE=4194304,ERRORS=-1,SKIP=1,ROWS=50000)

    I have updated the software and uploaded it, new example:

--
-- Generated by AUL/MyDUL, for table hr.test
--
OPTIONS(DIRECT=TRUE,READSIZE=4194304,ERRORS=-1,ROWS=50000)

    If you used to recover data by AUL/MyDUL in text format, please re-input the first line of the text file. Sorry again!