AnySQL.net English

    We have a very hot post on itpub.net talking about this, the poster is me. I think of this topic after receiving several times of AUL/MyDUL recovery requests for dropping tablespace or dropping tables by wrong mouse click on GUI utilities (OEM, Toad, PL/SQL Developer).

    Another reason was that after interviewing some people, I found lots of people cannot write the correct simple command, or did not know how to query performance data from some important "V$XXX" views.

    I prefer command line tools as a DBA. SQL*Plus, AnySQL, OTop/OPMon/OTune have been my favorite tools when managing and tuning a database. AnySQL have been my replacement of SQL*Plus for common queries, it's java based, do not need "COLUMN xxx FORMAT xxx" to adjust the column width. OTop/OPMon/OTune have completely replay the role of Oracle statspack for me.

    Of cause we should not blame the tools itself, in the first cases, they should do the privilege management better. For the second case, command line utilities may really help. What's your opinion?

    I made some changes to the account logic for the online AUL licenses, no IP limit now. Different day will be counted, for the same day, every five access will be treated as one license request. Be careful not to refresh the same page, it will be counted.

    Following is the AUL license requests in this free month, and let me count the request times:

USER     NAME = free

// count = 1
DAY=20061013 IP=58.212.85.189 CODE=5IFW-1D8F-UB4U-MT6J-K89C
DAY=20061013 IP=66.211.156.98 CODE=OC0Y-USYX-PP5C-YAGD-O6AN

//count = 2
DAY=20061014 IP=200.6.100.10 CODE=AU3I-KV2S-KQKM-30VS-TV46
DAY=20061014 IP=65.113.143.60 CODE=9WBJ-9Q1A-74W1-UK7C-PO88
DAY=20061014 IP=65.113.143.60 CODE=UJ42-URTU-ZEPK-16NZ-2M7L
DAY=20061014 IP=69.140.195.22 CODE=F901-OX9H-RTAN-NMTT-RDON
DAY=20061014 IP=69.140.195.22 CODE=GLHF-22F5-TR7F-0KL1-NQS5

//count = 3
DAY=20061016 IP=124.168.94.122 CODE=8KPD-95HT-7GMJ-JPGI-OFUZ
DAY=20061016 IP=222.69.61.134 CODE=SIWU-P6P8-Y4JC-MOV5-KD52

//count = 4
DAY=20061017 IP=211.96.15.27 CODE=BOG3-FT04-3XMF-FTU6-4YSE
DAY=20061017 IP=212.68.138.137 CODE=SB9J-1XZT-KHNI-RO0T-IR6L
DAY=20061017 IP=58.20.108.23 CODE=FMDO-DU90-49H6-V1W9-DRUI
DAY=20061017 IP=61.178.48.199 CODE=EBIE-WHZT-5WCF-AB44-QDW0
DAY=20061017 IP=61.178.48.199 CODE=ZVKS-AQKH-PV6Y-BTK6-KAUE

//count = 5
DAY=20061017 IP=69.71.188.90 CODE=PTPP-MHWE-AN20-LSUY-0R2K

//count = 6
DAY=20061018 IP=218.244.187.201 CODE=XCX1-TAFL-2CIP-02LX-HCOK
DAY=20061018 IP=222.91.162.118 CODE=IZJP-DCO2-GWF7-X9DT-14D1
DAY=20061018 IP=222.91.162.118 CODE=YZ4G-TV7D-MRKC-F42B-WFVX
DAY=20061018 IP=222.91.162.118 CODE=ZM6K-26WC-G99N-LVNI-3L2R
DAY=20061018 IP=222.91.162.118 CODE=ZM6K-26WC-G99N-LVNI-3L2R

//count = 7
DAY=20061018 IP=222.91.162.118 CODE=ZM6K-26WC-G99N-LVNI-3L2R
DAY=20061018 IP=222.91.162.118 CODE=ZM6K-26WC-G99N-LVNI-3L2R
DAY=20061018 IP=60.63.172.202 CODE=EIEF-F8SL-WY7K-PXIO-LT47

USED = 7, TOTAL=20

    Current charge (1000 USD or 800 EUR) is really cheaper, if you ask for recovery service on contact me in accident, the charge will be at least 4 times of current value.

    Somebody asked me this question after a try of AUL. They stored data in NCHAR/NVARCHAR2 data type, and then they use AUL to recover their dropped tables as text file. But hit some problem with loading the recovered rows into database, because some of the NCHAR/NVARCHAR2 type columns' data is recovered as UNICODE character set (AL16UTFE as national character set in Oracle 9i).

    Create a demo table with two columns (COL1 NVARCHAR2(100), COL2 NVARCHAR2(100)), and insert two rows, then use AUL to dump the rows to text file for test. By default the columns are seperated by "|", as following:

N C O L 1| N C O L 2
N C O L 1| N C O L 2

    I checked the Oracle document (Oracle Database Utilies Guide), and search on Google, no answer found. Also SQL*Loader have CHARACTERSET and BYTEORDER option, it did not do help in this case, because just few columns are UNICODE format, and I don't know how to write a SQL*Loader control file for the test table.

    Who has the same issue before? Can you give me an correct answer? Thanks very much!

    I wrote the following SQL in my script, before I wrote I feel it not the best, but just cannot think of it.

SELECT
   REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
      REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TABLE_NAME,'0','#'),
         '1','#') ,'2','#') ,'3','#') ,'4','#')
             ,'5','#') ,'6','#') ,'7','#') ,'8','#') ,'9','#')  PATTERN_NAME
FROM USER_TABLES

    When taking metro line to home, I reminded the translate function, it's very very simple.

SELECT
   TRANSLATE(TABLE_NAME,'0123456789','##########') PATTERN_NAME
FROM USER_TABLES

    I want to write the following SQL to find out which tables should have the same structure, and them compare them.

SELECT TABLE_NAME FROM USER_TABLES
WHERE TRANSLATE(TABLE_NAME,'0123456789','##########') IN
(SELECT
   TRANSLATE(TABLE_NAME,'0123456789','##########') PATTERN_NAME
FROM USER_TABLES
GROUP BY TRANSLATE(TABLE_NAME,'0123456789','##########')
HAVING COUNT(*) > 1)

    Choose right way is very important for us. One of my friend said we could use "REGEXP_REPLACE(TABLE_NAME, '[1-9]', '#' )" in Oracle 10g.

    I created a composite index on DEPTNO and ENAME columns for EMP table under SCOTT schema. Then run a SQL with OR in the where clause, check the execute plan:

SQL> SELECT /*+ first_rows no_expand */ * FROM EMP
   2 WHERE deptNO=10 AND (ENAME IS NULL OR ENAME > 'A');

---------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes |
---------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     3 |   330 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     3 |   330 |
|*  2 |   INDEX RANGE SCAN          | IDX_EMP |     3 |       |
---------------------------------------------------------------

   1 - filter("ENAME" IS NULL OR "ENAME">'A')
   2 - access("DEPTNO"=10)

    The filter operation performed on the table access. Then I rewrite the SQL with a NVL function, and check execution plan again :

SQL> SELECT /*+ first_rows no_expand */ * FROM EMP
   2 WHERE deptNO=10 AND NVL(ENAME,'B') > 'A';

---------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes |
---------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     3 |   330 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     3 |   330 |
|*  2 |   INDEX RANGE SCAN          | IDX_EMP |     1 |       |
---------------------------------------------------------------

   2 - access("DEPTNO"=10)
       filter(NVL("ENAME",'B')>'A')

    In a real case of our database, the first SQL need more than 10k consistent gets, while the second just need 150 consistent gets.