Virtual Field 133. Currval and NextVal Create Sequence Create Sequence Empseq ...; Select Empseq.CurrVal from Dual; Auto Insert Sequence Numerical Insert Into Emp Values (Empseq.NextVal, 'Lewis', 'Clerk', 7902, Sysdate , 1200, NULL, 20); 134. Rownum Press Select * from Emp Where Rownum <10; 135. RowId, Returning, ENAME FROM EMP Where dePTNO = 20; 136. N second conversion to time second format? Set ServerOut on Declare N Number: = 1000000; RET VARCHAR2 (100); Begin Ret: = Trunc (N / 3600) || 'Hour' || To_Char (TO_DATE (MOD (N, 3600), 'Sssss'),' FMMI "points" SS "second" '); dbms_output.put_line (re); end; 137. How to query the process of making a relatively large sort? Select B.Tablespace, B. Segfile #, B.SEGBLK #, B. Blocks, A.SID, A.SERIAL #, A.USERNAME, A. OSUSER, A.STATUS A. SESSION A, V $ Sort_USAGE B WHERE A.SADDR = B.SESSION_ADDR ORDER BY B.TABLESPACE, B.SEGFILE #, B.SEGBLK #, B.BLOCKS; 138. How to query the SQL statement of the process of making a relatively large sort? SELECT / * Ordered * / SQL_Text from V $ SQLText a where a.hash_value = (Select SQL_Hash_Value from V $ Session B Where B.SID = & SID AND B.SERIAL # = & Serial) Order by Piece ASC; 139. How to find repetition recording? Select * from table_name where rowid! = (Select max (rowid) from table_name d where table_name.col1 = d.col1 and table_name.col2 = d.col2); 140. How to delete repeat records? Delete from table_name where rowid! = (SELECT MAX (ROWID) from Table_Name D Where Table_Name.col1 = D.COL1 and TABLE_NAME.COL2 = D.COL2); 141. How to quickly compile all views? SQL> Spool View1.sql SQL> SELECT 'ALTER View' || TName || 'Compile;' from Tab; SQL> Spool Off and execute View1.sql.
SQL> @ view1.sql; 142. ORA-01555 Snapshot Too OLD solution Adds the value of MineXtents, adds a high Optimal value. 143. The spatial space of the transaction requirements is not enough, and it is characterized by a table space (ORA-01560 error), the rollback segment extends to the value of the parameter maxExtents (ORA-01628) solution. Add a file to the split table space Or make the existing file be large; increase the value of MaxExtents.
144. How to encrypt the Oracle stored procedure? The following stored procedure content is placed in the aa.sql file Create or Replace Procedure TestCCB (i in number) as begin dbms_output.put_line ('input parameter is' || to_Char (i)); end; sql> wrap iname = a.sql PL / SQL Wrapper: Release 8.1.7.0.0 - Production on Tue Nov 27 22:26:48 2001 Copyright (c) Oracle Corporation 1993, 2000. All Rights Reserved. Processing aa.sql to aa.plb Run AA.PLB SQL> @ aa.plb; 145. How to monitor the wait for the case? Select Event, SUM (Decode (Wait_Time, 0, 0, 1)) "Prev", SUM (Decode (Wait_time, 0, 1, 0)) "Curr", count (*) "Tot" from v $ session_wait group by Event ORDER BY 4; 146. How to return to the paradigm? Select Name, Waits, Gets, Waits / Gets "Ratio" from v $ ROLLSTAT C, V $ ROLLNAME D Where C.USN = D.USN; 147. How to monitor the I / O ratio of tablespace? Select B.TablesPACE_NAME NAME, B.FILE_NAME "File", A.Phyrds Pyr, A.Phyblkrd PBR, A.PhyWRTS PYW, A.PHYBLKWRT PBW from V $ Filestat A, DBA_DATA_FILES B Where A.File # = B.File_ID ORDER By B.TablesPACE_NAME; 148. How to monitor the I / O ratio of the file system? Select Substr (C.FILE #, 1, 2) "#", Substr (C.Name, 1, 30) "Name", C.Status, C.BYTES, D. Phyrds, D.Phyword from V $ DataFile C , V $ filestat d where c.file # = D.File #; 149. How do I find all the indexes under a certain user? select user_indexes.table_name, user_indexes.index_name, uniqueness, column_name from user_ind_columns, user_indexes where user_ind_columns.index_name = user_indexes.index_name and user_ind_columns.table_name = user_indexes.table_name order by user_indexes.table_type, user_indexes.table_name, user_indexes.index_name, column_position; 150. How to monitor SGA's hit rate? Select A.Value B.Value "Logical_reads", C.Value "Phys_Reads", Round (100 * ((A.Value
B.Value) -c.value) / (A. Value B.Value) "Buffer Hit Ratio" from V $ Sysstat A, V $ Sysstat B, V $ sysstat c where a.statistic # = 38 and B. Statistic # = 39 and C.Statistic # = 40; 151. How to monitor the hit rate of the Dictionary buffer in SGA? Select Parameter, Gets, GetMisses, getMisses / (Gets GetMisses) * 100 "Miss Ratio", (1- (SUM (getMisses) / (SUM (Gets) SUM (GetMisses))) * 100 "Hit Ratio" from V $ ROWCACHE WHERE GETS GETMISSES <> 0 group by parameter, gets, getmisses; 152. How should I monitor the hit rate of the CCD in SGA, should it be less than 1%? Select SUM (Pins) "Total Pins", SUM (Reloads) "Total Reloads", SUM (RELOADS) / SUM (PINS) * 100 Libcache from V $ librarycache; select sum (pinhits-reloads) / sum (pins "HIT Radio, SUM (RELOADS) / SUM (PINS) "Reload Percent" from v $ librarycache; 153. How to display the category and size of all database objects? select count (name) num_instances, type, sum (source_size) source_size, sum (parsed_size) parsed_size, sum (code_size) code_size, sum (error_size) error_size, sum (source_size) sum (parsed_size) sum (code_size) sum ( error_size) size_required from dba_object_size group by type order by 2; 154. monitoring SGA redo log buffer hit ratio should be less than 1% SELECT name, gets, misses, immediate_gets, immediate_misses, Decode (gets, 0,0, misses / gets * 100) ratio1, Decode (immediate_gets immediate_misses, 0,0, immediate_misses / (immediate_gets immediate_misses) * 100) ratio2 FROM v $ latch WHERE name IN ( 'redo allocation', 'redo copy'); 155. monitoring Sort and hard disk sorting ratio, it is best to make it less than .10, add sort_area_size select name, value from v $ sortstat where name in ('sorts "', 'sorts (DISK)'); 156. How to monitor current database Who is running what SQL statement?
Select OSuser, Username, SQL_Text from V $ Session A, V $ SQLText B where A.sql_address = B.Address Order by Address, Piece; 157. How to Monitor Dictionary Buffers? SELECT (SUM (Pins - Reloads)) / SUM (Pins "LIB Cache" from v $ librarycache; select (sum (gets - getmisses - usage - fixed) / sum (gets) "Row Cache" from v $ rowcache; Select SUM (Pins) "Executions", SUM (RELOADS) "Cache Misses While Executing" from V $ librarycache; the latter except for the former, this ratio is less than 1%, close to 0% is good.
Select Sum (Gets) "Dictionary Gets", SUM (GetMisses) "Dictionary Cache Get Misses" from V $ ROWCACHE 158. Monitor MTS SELECT Busy / (Busy IDle) "Shared Servers Busy" from V $ Dispatcher; this value is greater than 0.5 When parameter needs to increase Select SUM (Wait) / SUM (Totalq) "Dispatcher Waits" from v $ queue where type = 'dispatcher'; select count (*) from v $ dispatcher; select servers_highwater from v $ mts; servers_highwater close When MTS_MAX_SERVERS, the parameter needs to increase 159. How do I know the current user ID number? SQL> Show user; or sql> select user from dual; 160. How to view the degradation level? Select segment_name table_name, count (*) Extents from DBA_SEGMENTS WHERE OWNER NOT IN ('sys', 'system') Group by segment_name haVing count (*) = (select max) from dba_segments group by segment_name); 162. How to know the store in the table space ? case select segment_name, sum (bytes), count (*) ext_quan from dba_extents where tablespace_name = '& tablespace_name' and segment_type = 'tABLE' group by tablespace_name, segment_name; how 163. know that the stored situation index table space select segment_name? , count (*) from DBA_EXTENTS Where segment_type = 'Index' and Owner = '& Owner' Group by segment_name; 164, how to know how many users using CPU session? 11 is CPU Used by this session Select A.SID, SPID, STATUS, SUBSTR (A.Program, 1, 40 ) PROG, A.Terminal, Osuser, Value / 60/100 Value from V $ sessions a, v $ process b, v $ sesstat c where c.statistic # = 11 and c.SID = a.sid and a.paddr = B.Addr Order by Value DESC;
