Oracle Common Fool question 1000 question (iv)

Oracle   Virtual field 133. 　 Currval and Nextval Create a sequence created SEQUENCE empseq for the table ...; 　 SELECT Empseq.currval from DUAL; Inserts the numeric values of the sequence into the EMP values (empseq.nextval, ' LEWIS ', ' clerk ', 7902, Sysdate, 1200, NULL, 20); 134. RowNum the ordinal of a sorted row SELECT * from emp WHERE rownum < 10; 135. ROWID returns the physical address of the row, SELECT ROWID, ename from emp WHERE deptno = 20; 　 136. Convert n seconds to last minute format? 　 Set serverout on declare N number: = 1000000; 　 RET VARCHAR2 (100); BEGIN RET: = Trunc (n/3600) | | ' Hour ' | | 　 To_char (mod (n,3600), ' sssss '), ' fmmi ' cent ' ss ' seconds '); 　 Dbms_output.put_line (ret); End 　 137. How do I query the process of making a larger sort? SELECT B.tablespace, b.segfile#, b.segblk#, B.blocks, A.sid, a.serial#, A.username, A.osuser, a.status from V$session A,v$sort_usage b WHERE a.saddr = b.session_addr ORDER by B.tablespace, b.segfile#, b.segblk#, b.blocks; 　 138. How do I query for SQL statements that make a larger sort of process? 　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 do I find duplicate records? 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 do I delete duplicate 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 do I quickly compile all views? SQL >spool VIEW1. SQL SQL >select ' ALTER VIEW ' | | 　 tname| | ' COMPILE; ' 　 From TAB; 　 SQL >spool off and then executes View1.sql. SQL > @VIEW1. SQL; The solution of ORA-01555 SNAPSHOT TOO old is to increase the minextents value, increase the size of the area, and set a high optimal value. 　 143. Transaction requirements of the rollback segment space is not enough, the performance of the table space with full (ORA-01560 error), rollback segment extension reached the value of the parameter maxextents (ORA-01628) solution. Add a file to the rollback segment tablespace or make an existing file larger; Increase the value of the maxextents. 　　144. How do I encrypt Oracle's stored procedures? The following stored procedure contents are 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: 2001 Copyright (c) Oracle Corporation 1993, 2000. 　　All Rights Reserved. Processing Aa.sql to AA.PLB runs AA.PLB sql> @AA. PLB; 　 145. How to monitor the waiting of cases? Select Event,sum (Decode (wait_time,0,0,1)) "Prev", Sum (Decode (wait_time,0,1,0)) "Curr", COUNT (*) "Tot" from V$session_w AIT GROUP BY event order by 4; 　 146. How do I roll back the contention for the segment? 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 in table space? Select B.tablespace_name name,b.file_name "file", A.phyrds Pyr, A.PHYBLKRD, Pbr,a.phywrts pyw, A.phyblkwrt PBW from V$fi Lestat 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.phywrts from V$datafile C, V$filestat D where c.file# = d.file#; 　 149. How to find all the indexes under a user? Select User_indexes.table_name, User_indexes.index_name,uniqueness, column_name from User_ind_columns, User_indexes W Here 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 the percentage of SGA? Select A.value + b.value "logical_reads", C.value "Phys_reads", Round ((a.value+b.value)-c.value)/(A.value+b.val UE)) "BUFFER HIT RATIO" from V$sysstat A, V$sysstat B, v$sysstat c where a.statistic# = and b.statistic# = c.statistic# = 40; 　 151. How to monitor the hit rate of the dictionary buffer in the SGA? Select parameter, gets,getmisses, getmisses/(gets+getmisses) *100 "Miss Ratio", (SUM (getmisses)/(SUM (gets) +sum ( getmisses))) *100 "Hit ratio" from V$rowcache where gets+getmisses <>0 Group by parameter, gets, getmisses; 　 152. How to monitor the percentage of the shared cache in the SGA, should be less than 1%? 　 Select SUM (Pins) "Total pins", sum (reloads) "Total reloads", sum (reloads)/sum (Pins) *100 to Libcache from V$librarycache; Select SUM (pinhits-reloads)/sum (Pins) "Hit Radio", SUM (reloads)/sum (pins) "Reload percent" from V$librarycache; 　 153. How do i show the categories and sizes 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 the ratio of redo log buffers in the SGA should be less than 1% SELECT name, gets, misses, immediate_gets, immediate_misses, Decode (gets,0,0,misses/ge ts*100) Ratio1, Decode (immediate_gets+immediate_misses,0,0, immediate_misses/(immediate_gets+immediate_misses) * Ratio2 from V$latch WHERE name in (' Redo allocation ', ' redo copy '); 　　　155. Monitor memory and HDD sorting ratios, preferably to make it less than. 10, add sort_area_size SELECT name, value from V$sysstat WHERE name in (' Sorts (memory) ', ' sorts (disk) '); 　 156. How to monitor the 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 do I monitor the dictionary buffer? 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 divided by 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. 　 Monitoring MTS Select busy/(busy+idle) "Shared servers Busy" from V$dispatcher; 　 When this value is greater than 0.5, the parameter needs to be increased select sum (wait)/sum (TOTALQ) "Dispatcher waits" from V$queue where type= ' dispatcher '; 　 Select COUNT (*) from V$dispatcher; 　 Select Servers_highwater from V$mts; When Servers_highwater approach mts_max_servers, the parameters need to be increased by 159. 　　How do I know the ID number of the current user? 　　Sql>show USER; or sql>select user from dual; 　 160. How can I view a table with a high degree of fragmentation? SELECT segment_name table_name, COUNT (*) extents from dba_segments WHERE owner is in (' SYS ', ' SYSTEM ') GROUP by segmen T_name has COUNT (*) = (SELECT MAX (COUNT (*)) from dba_segments GROUP by segment_name); 162. How to know the table in the table emptyWhere is the storage in between? 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; 　 163. How do I know how the index is stored in the 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 to use CPU user session? 　 11 is CPU used by the session Select A.SID,SPID,STATUS,SUBSTR (a.program,1,40) prog,a.terminal,osuser,value/60/100 value From V$session 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 ; 　 165. How do I know the listener log file? Take 8I as an example $ORACLE _home/network/log/listener. LOG 166. 　 How do I know the listener parameter file? Take 8I as an example $ORACLE _home/network/admin/listener. ORA 167. 　 How do I know the TNS connection file? Take 8I as an example $ORACLE _home/network/admin/tnsnames. ORA 168. 　 How do I know the sql*net environment file? Take 8I as an example $ORACLE _home/network/admin/sqlnet. ORA 169. 　 How do I know the warning log file? Take 8I as an example $ORACLE _HOME/ADMIN/SID/BDUMP/SIDALRT. LOG 170. 　 How do I know the basic structure? Taking 8I as an example $ORACLE _home/rdbms/Admin/standard. SQL 171. 　 How do I know how to build a data dictionary view? Take 8I as an example $ORACLE _home/rdbms/admin/catalog. SQL 172. 　 How do I know how to build an audit with a data dictionary view? Take 8I as an example $ORACLE _home/rdbms/admin/cataudit. SQL 173. 　 How do I know how to set up a snapshot with a data dictionary view? Take 8I as an example $ORACLE _home/rdbms/admin/catsnap. Sql