The pieces of fw:oracle

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;--query table space Select UPPER (f.tablespace_name) "tablespace name", D.TOT_GROOTTE_MB "tablespace size (M)", D.tot_grootte_mb-f.total_bytes "used Space (M)", To_char (ROUND (d.tot_grootte_mb-f.total_bytes)/D.tot_grootte _MB * 100, 2), ' 990.99 ') "use ratio", f.total_bytes "free Space (M)", f.max_bytes "maximum block (m) "From (select Tablespace_name, ROUND (SUM (BYTES)/(1024x768 * 1024x768), 2) total_bytes, ROUND (MAX (B ytes)/(1024x768 * 1024x768), 2) Max_bytes from SYS. Dba_free_space GROUP by Tablespace_name) F, (SELECT DD. Tablespace_name, ROUND (SUM (DD). BYTES)/(1024x768 * 1024x768), 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; When you delete some tables by mistake,can be restored by command: Flashback table TableName to befor drop; see if the flashback function is turned on: select flashback_on from V$database; If you determine that some tables can indeed be deleted, you can use the following statement so that the table at the beginning of the bin$ will not appear, DROP table table_name purge; Delete the table at the beginning of the bin, which is the drop but exists in the Recycle Bin: query all such tables sql> select * from RecycleBin where type= ' table '; used to delete all tables in the Recycle Bin sql> PURGE RecycleBin: Used to delete the specified table sql> PURGE table table_name; The drop table can be dropped when the bin form is not generated sql> drop table "TableName" purge;-- Query database locked Process 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);--Query/delete duplicate record 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); 

--reverse recursion Select H.hr_dept_code, H.erp_dept_name, H.erp_dept_code, H.ERP_DEPT_NAME From (SELECT * to aol_sys_organization START with org_no = ' 00164 ' CONNECT by parent_org_id = PR IOR line_id) O, aol_com_hrtoerp H WHERE o.org_no = H.hr_dept_code; 

--1, view the space occupied by the table select Tablespace_name, To_char (SUM (BYTES)/(1024x768 * 1024x768), ' 999g999 D999 ') cnt_mb from dba_extents WHERE the OWNER = ' &owner ' and segment_name = ' &table_name ' and segment_type like The ' table% ' */GROUP by tablespace_name;--has two meanings for the table size. --One is the amount of physical space assigned to a table, regardless of whether the space is used or not. The number of bytes can be obtained by this query: 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;--Another table actually uses space. This query: 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; 

--flash back of stored procedure 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 = ' user name ' and        name = ' Stored procedure name '       ORDER by line     ; Sql> spool off;

The pieces of fw:oracle