Virtual field
133. CURRVAL and NEXTVAL
Create a sequence for the table
CREATE SEQUENCE EMPSEQ ...
SELECT Empseq.currval from dual
Numerical value of automatic insertion sequence
INSERT INTO EMP
VALUES (Empseq.NextVal, 'Lewis', 'Clerk',
7902, Sysdate, 1200, NULL, 20);
134. Rownum
Press the serial number of the line sorted
Select * from Emp Where RownuM <10;
135. RowID
Return line physical address
Select rowid, ename from Emp where deptno = 20;
136. Convert N-second 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 "Division" SS "Second"');
DBMS_OUTPUT.PUT_LINE (RET);
END;
137. How do I query to do a big sorting process?
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 the SQL statement of the relatively large sorted 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 repeated records?
SELECT * from home_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 a repeated record?
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 || '
'From tab;
SQL> spool off
Then perform view1.sql.
SQL> @ view1.sql;
142. ORA-01555 Snapshot Too OLD solution
Increase the value of Minextents, increase the size of the zone, set 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 aa.sql file
Create or Replace Procedure Testcb (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 cases?
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 the contention of 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 of 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 $ fileestat a, dba_data_files b
Where a.file # = B.File_ID
Order by b.tablespapa_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 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- (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 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 do I 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 the hit rate of the SGA medium-red log cache, should be less than 1%
Select Name, Gets, Misses, Immediate_Gets, IMMEDIATE_MISS,
Decode (Gets, 0, 0, Misses / Gets * 100) Ratio1,
Decode (IMMEDIATE_GETS IMMEDIATE_MISS, 0, 0,
IMMEDIATE_MISSES / (IMMEDIAT_GETS IMMEDIATE_MISSES) * 100) Ratio2
From v $ latch where name in ('redo allocation');
155. Monitor memory and hard disk sorting ratio, it is best to make it less than .10, increase sort_area_size
Select Name, Value from V $ SSSTAT WHERE NAME IN ('Sorts'); 156. How do you 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 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;
When this value is greater than 0.5, the parameters need 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 is close to MTS_MAX_SERVERS, the parameters need to increase
159. How do I know the ID number of the current user?
SQL> Show user;
Oral
SQL> SELECT User from Dual;
160. How to view the high degree of debris?
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 do I know the storage situation in the table space?
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 situation in the table space?
Select segment_name, count (*) from dba_extents where segment_type = 'index' and owner = '& Owner'group by segment_name;
164. How do I know more user sessions with CPU?
11 is CPU buy 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 $ SESSSTAT 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 file?
Take 8i as an example
$ Oracle_home / network / admin / sqlnet.ora
169. How do I know warning log files?
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 the data dictionary view?
Take 8i as an example
$ Oracle_home / rdbms / admin / catalog.sql
172. How do I know the data dictionary view of the Audit?
Take 8i as an example
$ Oracle_home / rdbms / admin / cataudit.sql
173. How do I know the data dictionary view of establishing a snapshot?
Take 8i as an example
$ Oracle_home / rdbms / admin / catsnap.sql
