SELECT INSTR(‘AAAC‘, ‘AAC‘) FROM DUAL; --2SELECT SUBSTR(‘ABC‘, 2, 1) FROM DUAL; --BSELECT ‘AA‘ || CHR(ASCII(‘B‘) + 1) FROM DUAL; -- AACSELECT * FROM V$VERSION;SELECT * FROM PRODUCT_COMPONENT_VERSION;-- 查询表空间SELECT UPPER(F.TABLESPACE_NAME) "表空间名",       D.TOT_GROOTTE_MB "表空间大小(M)",       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,                     2),               ‘990.99‘) "使用比",       F.TOTAL_BYTES "空闲空间(M)",       F.MAX_BYTES "最大块(M)"  FROM (SELECT TABLESPACE_NAME,               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,               ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES          FROM SYS.DBA_FREE_SPACE         GROUP BY TABLESPACE_NAME) F,       (SELECT DD.TABLESPACE_NAME,               ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB          FROM SYS.DBA_DATA_FILES DD         GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC;当误删除某些表时，可以通过命令恢复回来：flashback table tablename to befor drop； 查看是否开启了闪回功能：select flashback_on from v$database; 如果确定某些表确实可以删除，可以使用下面的语句，就不会出现BIN$开头的表了drop table table_name purge; 删除Bin开头的表,即已经drop但存在于回收站中的表：查询所有此类表SQL> select * from recyclebin where type=‘TABLE‘;用来删除回收站中所有的表SQL> PURGE RECYCLEBIN;用来删除指定的表SQL> PURGE TABLE TABLE_NAME;可以在的Drop表时不产生Bin型表SQL> DROP TABLE "TableName" purge;-- 查询数据库被锁住的进程SELECT A.SID, B.OWNER, OBJECT_NAME, OBJECT_TYPE  FROM V$LOCK A, ALL_OBJECTS B WHERE TYPE = ‘TM‘   AND A.ID1 = B.OBJECT_ID;SELECT SID, SERIAL# FROM V$SESSION WHERE SID = &SID;ALTER SYSTEM KILL SESSION ‘SID, SERIAL#’;SELECT SID, SERIAL#  FROM V$SESSION WHERE SID IN (SELECT A.SID                 FROM V$LOCK A, ALL_OBJECTS B                WHERE TYPE = ‘TM‘                  AND A.ID1 = B.OBJECT_ID);-- 查询/删除重复记录DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);SELECT *  FROM COM_ITEM_CODE E WHERE E.ROWID >       (SELECT MIN(X.ROWID) FROM COM_ITEM_CODE X WHERE X.BILL_NO = E.BILL_NO);

-- 反向递归SELECT H.HR_DEPT_CODE, H.ERP_DEPT_NAME, H.ERP_DEPT_CODE, H.ERP_DEPT_NAME  FROM (SELECT *          FROM AOL_SYS_ORGANIZATION         START WITH ORG_NO = ‘00164‘        CONNECT BY PARENT_ORG_ID = PRIOR LINE_ID) O,       AOL_COM_HRTOERP H WHERE O.ORG_NO = H.HR_DEPT_CODE;

-- 1、查看表所占空间SELECT TABLESPACE_NAME,       TO_CHAR(SUM(BYTES) / (1024 * 1024), ‘999G999D999‘) CNT_MB  FROM DBA_EXTENTS WHERE OWNER = ‘&OWNER‘   AND SEGMENT_NAME = ‘&TABLE_NAME‘   AND SEGMENT_TYPE LIKE ‘TABLE%‘ * / GROUP BY TABLESPACE_NAME;--有两种含义的表大小。--一种是分配给一个表的物理空间数量，而不管空间是否被使用。可以这样查询获得字节数：SELECT SEGMENT_NAME, BYTES FROM USER_SEGMENTS WHERE SEGMENT_TYPE = ‘TABLE‘;SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024  FROM USER_EXTENTS GROUP BY SEGMENT_NAME;-- 另一种表实际使用的空间。这样查询：ANALYZE TABLE ABCDEF COMPUTE STATISTICS;SELECT NUM_ROWS * AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME = ‘ABCDEF‘;SELECT NUM_ROWS, TABLE_NAME FROM USER_TABLES;

-- 存储过程的闪回SQL> set pagesize 0SQL> column text format a4000SQL> spool C:\7.textStarted spooling to C:\7.textSQL> SELECT text       FROM DBA_source AS OF TIMESTAMP TO_TIMESTAMP(‘2009-12-25 10:07:00‘, ‘YYYY-MM-DD HH24:MI:SS‘)      WHERE OWNER = ‘用户名‘        AND NAME = ‘存储过程名’       ORDER BY LINE     ;SQL> spool off;

-- 修改DBLINK的global_nameupdate global_name set global_name=‘oradb‘;