FW:Oracle之七零八落

標籤：des   blog   io   使用   ar   for   資料   art   div   

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‘; 

FW:Oracle之七零八落