Common Oracle questions 1000 (part 4)

133. currval and nextval
Create sequence for table
Create sequence emsequq ...;
Select empseq. currval from dual;
Automatically inserted Sequence Value
Insert into EMP
Values (em1_q. nextval, 'Lewis ', 'cler ',
7902, sysdate, 1200, null, 20 );

134. rownum
Sequence Number of the rows sorted by settings
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 the time/minute/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), 'ssss '), 'fmmi "Minute" SS "second "');
Dbms_output.put_line (RET );
End;

137. How can I query processes with relatively large sorting volumes?
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 can I query SQL statements of relatively large sorting processes?
Select/* + ordered */SQL _text from V $ sqltext
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 to 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 to quickly compile all views?
SQL> spool view1. SQL
SQL> select 'alter view' | tname |'
Compile; 'from tab;
SQL> spool off
Then run view1. SQL.
SQL> @ view1. SQL;

142. Solutions for ORA-01555 snapshot too old
Increase the minextents value, increase the partition size, and set a high optimal value.

143. The rollback segment space required by the transaction is insufficient as the tablespace is full (ORA-01560 error), and the rollback segment extension reaches the value of the maxextents parameter (ORA-01628.
Add a file to the tablespace of the rollback segment or make the existing file larger; Increase the value of maxextents.

144. How to encrypt Oracle stored procedures?
The following stored procedures are stored in the AA. SQL file:
Create or replace procedure testccb (I in number)
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,200 0. All rights reserved.
Processing AA. SQL to AA. PLB
Run 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, 0) "curr", count (*) "tot"
From v $ session_wait
Group by event order by 4;

146. How do I roll back the contention of segments?
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 a 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 a 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 can I find all indexes under a 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 the SGA 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 to monitor the hit rate of the shared cache in SGA, which should 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. Monitor the log cache hit rate in SGA, which should be less than 1%
Select name, gets, misses, immediate_gets, immediate_misses,
Decode (gets, 100, misses/gets *) 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. Monitor the sorting ratio of memory to hard disk, preferably make it smaller than. 10, increase sort_area_size
Select name, value from V $ sysstat where name in ('sorts (memory) ', 'sorts (Disk )');

156. How to monitor who is running what SQL statements in the current database?
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 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 is divided by the former. This ratio is less than 1%, and it is better to close to 0%.

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;
When the value is greater than 0.5, the parameter must 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 is close to mts_max_servers, increase the Parameter

159. How do I know the ID of the current user?
SQL> show user;
Or
SQL> Select User from dual;

160. How to View tables with high fragmentation levels?
Select segment_name table_name, count (*) extents
From dba_segments where owner not in ('sys ', 'system') group by segment_name
Having count (*) = (select max (count (*) from dba_segments group by segment_name );

162. How do I know the storage of tables in the tablespace?
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 the storage of indexes in the tablespace?
Select segment_name, count (*) from dba_extents where segment_type = 'index' and owner = '& owner'
Group by segment_name;

164. How do I know user sessions with many CPUs?
11 is CPU used by this session

Select a. Sid, spid, status, substr (A. Program, 60/100) prog, A. Terminal, osuser, value/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 SQL * Net Environment files?
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?
Take 8i as an Example
$ ORACLE_HOME/rdbms/admin/standard. SQL

171. How do I know how to create a data dictionary view?
Take 8i as an Example
$ ORACLE_HOME/rdbms/admin/CATALOG. SQL

172. How do I know how to create a data dictionary view for auditing?
Take 8i as an Example
$ ORACLE_HOME/rdbms/admin/cataudit. SQL

173. How do I know how to create a data dictionary view for a snapshot?
Take 8i as an Example
$ ORACLE_HOME/rdbms/admin/catsnap. SQL