Oracle common problem 1000 questions complete works (2)

101. How to search for the first N record?

Select * from Employee Where Rownum

ORDER BY Empno;

102. How do I know how much does the Oracle support on the machine?

SQL> CONN INTERNAL;

SQL> Show Parameter Processes;

103. Can DB_BLOCK_SIZE can be modified?

Generally, it is not recommended.

104. How to count the total number of records of two tables?

SELECT (SELECT CDUAL; Ount (ID) from AA) (SELECT Count (ID) from bb) Total from

105. How do I use the SQL statement to find the nth value in a column?

SELECT * FROM

(Select T. *, Dense_Rank () over (Order By Sal) Rank from Employee

WHERE RANK = N;

106. How do I add 2 years when giving an existing date? (

SELECT Add_MontHS (Sysdate, 24) from Dual;

107. What does it mean for use_ublk to negative?

IT IS "harmless".

108. What does Connect String mean?

It should be the content behind the service name in tnsnames.ora

109. How to expand the size of the Redo Log?

Create a temporary Redolog group, then switch the log, delete the previous log, and create a new log.

110. Is the TABLESPACE not greater than 4G?

no limit.

111. Return to the minimum integer value equal to N?

Select CEIL (n) from dual;

112. Returns the minimum integer value equal to N?

SELECT FLOOR (N) from Dual;

113. Return to the last day of the current month?

SELECT LAST_DAY (SYSDATE) from DUAL

114. How do you have different user data imports?

IMP system / manager file = aa.dindexes = y; mp fromuser = user_old trouser = user_new rows = y

115. How do I find the name of the primary key field of the database table?

SQL> Select * from user_constraints where constraint_type = 'p' and table_name = 'table_name';

116. What is the function of two result sets?

SQL> Select * from bsempms_old intersect select * from bsempms_new;

SQL> SELECT * from bsempms_old union select * from bsempms_new

SQL> SELECT * from bsempms_old union all selection * from bsempms_new;

117. What is the function of two result sets?

SQL> SELECT * from bsempms_old minus select * from bsempms_new;

118. How to configure sequence?

Built sequence seq_custid

CREATE SEQUENCESEQ_CUSTID START 1 IncremeMtby 1;

Time:

CREATE TABLE CUST

{Cust_ID Smallint Not Null, ...}

INSERT:

INSERT INTO TABLE CUST

VALUES (seq_cust.nextval, ...)

Common way of writing of each part of the date

119. Writing of the year of taking the time point:

Select to_char (sysdate, 'yyyy') from dual;

120. Wellness to take the month of time:

Select to_char (sysdate, 'mm') from dual;

121. Wellness to take the time of time:

Select to_char (sysdate, 'dd') from dual;

122. Writing at the time of time:

Select to_char (sysdate, 'hh24') from dual;

123. Writing method for taking the time point:

Select to_char (sysdate, 'mi') from dual;

124. Write the second time of the time point:

Select to_char (sysdate, 'ss') from dual;

125. Take the date of taking the time point:

Select Trunc (sysdate) from dual;

126. Wellness to take the time point:

Select to_char (sysdate, 'hh24: mi: ss') from DUAL

127. Date, time form becomes character form:

Select to_char (sysdate) from DUAL

128. Convert the string into a date or time form:

SELECT TO_DATE ('2003/08/01') from dual;

129. Return to the parameters of the week writing method:

Select to_char (sysdate, 'd') from dual;

130. Return to the first few days of the year:

Select to_char (sysdate, 'ddd') from dual;

131. Returns the number of seconds between the time values ​​specified in midnight and parameters:

Select to_char (sysdate, 'sssss') from dual;

132. Returns the first few weeks of the parameter:

Select to_char (sysdate, 'ww') from dual;

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 (600), 'sssss'), 'FMMI "N / 3600) ||' Hour '|| TO_CHARSS" second "'); (to_date (n, 3

DBMS_OUTPUT.PUT_LINE (RET);

END;

137. How do I query to do a big sorting process?

Select B.Tablespace, B.SEGFIA.USERNAME, A. OSUser, A.Statusle #, B.SEGBLK #, B.Blocks, A.SID, A.SERIAL #,

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 FROMV $ 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 rollback to the rollback of the transaction requires insufficient space for the value of MaxExtents, which is expressed as a table space full (ORA-01628) solution. (ORA-01560 error), returning segment extension

Add a file to the split segment 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) Asbegin

DBMS_OUTPUT.PUT_LINE ('input parameter is' || to_CHAR (i));

END;

SQL> WRAP INAME = a.sql;

PL / SQL Wrapper: Release 8.12001.7.0.0 - Production on Tue Nov 27 22:26:48

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 NAMA.PHYBLKRD PBR, A.PHYWRTS PYW, A.PHYE, B.FILE_NAME "File", A.Phyrds Pyr, BLKWRT 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.PHYTES, D. Pheyrds, D.PHYFILE #, 1, 2) "#", SUBSTR (C.NamWRTSE, 1, 30) "Name", C.Status,

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_indecolumn_namexes.table_name, user_indexes.index_name, uniqueness,

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.Valuratio "e) -c.value) / (A.Value B.Value)" Buffer HitFrom V $ SSSSTAT A, V $ SYSSTATB, V $ SYSSTAT C

Where a.statistic # = 38 andb.statianship # = 39

And C.statistic # = 40;

151. How to monitor the hit rate of the Dictionary buffer in SGA?

Select Parameter, Gets, GetMiratio, SSES, GETMISS / (GETS GETMISSES) * 100 "MISS

(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 (Pinhi "Reload Percent" TS-Reloads) / SUM (Pins "Hit Radio, SUM (Reloads) / Sum (Pins)

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_SIZEERROR_SIZE, SUM (CODE_SIZE) CODE_SIZE, SUM (ERROR_SIZE)

SUM (Source_Size) SUM (PARSEDSIZE_REQUIRED_SIZE) SUM (CODE_SIZE) SUM (Error_Size)

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, Val'sorts (Disk); UE from V $ sysstat where namein ('sorts (Memory),

156. How do I monitor the current database who is running what SQL statement?

Select Osuser, Username, SQL_Text from V $ Session A, V $ SQLText Bwhere 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 - getMissesfrom v $ rowcache; - usage - fixed) / sum (gets) "row cache"

Select SUM (Pins) ExecutionSexecuting "From V $ librarycache;", SUM (RELOADS) "Cache Misses While

The latter except for the former, this ratio is less than 1%, close to 0% is good.

Select SUM (Gets) Misses "Dictionary Gets", SUM (GetMisses) Dictionary Cache Get

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) Type = 'dispatcher'; / Sum (Totalq) "Dispatcher Waits" from v $ queue where

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;

Or SQL> SELECT User from DUAL;

160. How to view the high degree of debris?

Select segment_name table_name, count (*) extents

From dba_segmentsegment_names where oowner not in ('sys', 'system') group by

Having count (*) segment_name); = (Select max) from dba_segments group by

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 = '& TablesPACETABLESPACE_NAME, Segment_Name; _Name' and segment_type = 'table' group by

163. How do I know the storage situation in the table space?

Select segment_name, count (*) and oowner = '& Owner'From DBA_EXTENTS Where segment_type =' index '

GROUP BY segment_name;

164. How do I know more user sessions with CPU?

11 is CPU Used by this sessionselect A.SID, Spinal, OSuser, Value / 60 / 100D, Status, Substr (A.Program, 1, Value40) PROG, A.TERMI

From v $ sessions a, V $ PROCESS B, V $ SESSSTAT C

WHERE C.STATISTIDESC; C # = 11 and C.SID = a.sid and a.paddr = B.Addr Order by Value

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

Optimization method for SQL statement

174. / * all_ROWS * /

Indicates that the 对 语 选择 选择 优 方法 方法 方法 方法 方法 方法 方法 方法, 并 并 获得, 获得, 获得, 方法, 获得, 方法, 方法, 方法, 方法, 方法. 方法, 方法, 方法,

E.g:

SELECT / * ALL _ROWS * / EMP_NEMP_NO = 'CCBZPP'; O, EMP_NAM, DAT_IN from BSempms Where

175. / * first_ROWS * /

Indicates that the selection of the selection is based on overhead. Optimization method, and get the best response time, minimize resource consumption

E.g:

SELECT / * first_ROWS * / EMP_EMP_NO = 'CCBZZP'; NO, EMP_NAM, DAT_IN from BSempms Where

176. / * choose * /

Indicates that there is access table throughput in the data dictionary; the statistics will be based on the optimization method of overhead and the best

Indicates that if there is no statistics of the table in the data dictionary, it will be based on the optimization method of rule overhead;

E.g:

SELECT / * CHOOSE * / EMP_NO, EMP_NO = 'CCBZZP'; EMP_NAM, DAT_IN from BSempms Where

177. / * rule * /

Indicates the selection of rules based on rules.

E.g:

SELECT / * RULE * / EMP_NO, EMP_NO = 'CCBZZP'; EMP_NAM, DAT_IN from BSempms Where

178. / * Full (Table) * /

Indicates how globally scanned on the table. E.g:

SELECT / * FULL (; a) * / EMP_NO, EMP_NAM from bsempms a where EMP_NO = 'CCBZP'

179. / * RowId (Table) * /

Tip Explicitly indicate that the specified table is accessed according to ROWID.

E.g:

SELECT / * ROWID (BSEMPMS) * / * from bsempms where rowid> = 'aaaaaaaaaaaa

AND EMP_NO = 'CCBZZP';

180. / * Cluster (TABLE) * /

Tip Explicitly indicate the access method for selecting a cluster scan for the specified table, which is only valid for the cluster object.

E.g:

SELECT / * Cluster * / bsempms.emp_no, dpt_no from bsempms, BSDPTMS

WHERE DPT_NO = 'TEC304' and bsempms.dpt_no = bsdptms.dpt_no;

181. / * Index (Table Index_name) * /

Indicates the scanning method for selecting an index.

E.g:

SELECT / * Index (bsempms SexfewMale Bsempms * / from bsempms WHER_INDEX) USE SEX_INDEX BECAUSE TheREEE Sere Sex = 'm';

182. / * Index_asc (Table Index_name) * /

Indicates the scan method for selecting an index ascending order to the table.

E.g:

SELECT / * INDEX_ASC (bsempmsdpt_no = 'ccbzz'; pk_bsempms) * / from bsempms where

183. / * index_combine * /

Selecting a bitmap to specify a table, selecting a bit graph index, if index_combine does not provide an index of parameters,

Boolean combination.

E.g:

SELECT / * INDEX_COMBINE (bsempms sal_bmi hidate_bmi) * / * from bsempms

WHERE SAL <5000000 and HIREDATE

184. / * index_join (Table Index_name) * /

Tip Clear command Optimizer Use the index as an access path.

E.g:

SELECT / * INDEX_JOIN (bsempms Sal_hmi Hiredate_BMI) * / Sal, Hiredate

From bsempms where Sal <60000;

185. / * Index_Desc (Table Index_name) * /

Indicates the scanning method of the index descending order to the table.

E.g:

Select / * indexdpt_no = 'ccbzzp'; _ DESC (bsempms pk_bsempms) * / from bsempms where

186. / * index_ffs (Table Index_name) * /

Performing a fast total index scan for the specified table, not a full meter scan.

E.g:

Select / * indexdpt_no = 'TEC305'; _ FFS (bsempms in_empnam) * / * from bsempms where

187. / * add_equal table index_nam1, index_nam2, ... * / prompts to clearly perform the selection of the planning, and the scanning of several single colony indexes.

E.g:

SELECT / * Indexwhere EMP_NO = 'CCBZP' AN_FFS (bsempms IN_DPTNO, IN_EMD DPT_NO = 'TDC306'; PNO, IN_SEX) * / * from bsempms

188. / * USE_CONCAT * /

Convert the OR condition behind the WHERE in the query to unionall combination query.

E.g:

SELECT / * USE_CONCAT * / * from bsempms WherePT_NO = 'TDC506' and SEX = 'm';

189. / * NO_EXPAND * /

Extensions for OR or In-LI behind WHERE. ST query statement, NO_EXPAN will prevent it from based on optimizer

E.g:

SELECT / * NO_EXPAND * / * from bsempms where dpt_no = 'TDC506' and sex = 'm';

190. / * noWrite * /

It is forbidden to rewrite the query of the query block.

191. / * Rewrite * /

The view can be used as a parameter.

192. / * Merge (Table) * /

Ability to consolidate the respective queries of the view.

E.g:

SELECT / * MERGE (V) * / a.emp_no, a.emp_nam, b.dpt_no from bsempms A (Selet DPT_NO, AVG (SAL) AS AVG_SAL from BSempms B Group by dpt_no) V where a.dpt_no = v.dpt_no And A.SAL> V.AVG_SAL;

193. / * no_merge (Table) * /

For views with merger are no longer merged.

E.g:

SELECT / * NO_MERGE (V) * / a.emp_no, a.emp_nam, b.dpt_no from bsempms A (Selet DPT_NO, AVG (SAL) AS AVG_SAL from BSempms B Group By DPT_NO) V where a.dpt_no = v.dpt_no And A.SAL> V.AVG_SAL;

194. / * ordered * /

The order in which the form appears in the FROM, ordered the Oracle in this order.

E.g:

SELECT / * Ordered * / a.col1table3 c where a.col1 = b.col1 and b.c, b.col2, c.col3 from table1 a, table2 b, ol1 = c.col1;

195. / * USE_NL (Table) * /

Connect the specified table with the row of nesting connections, and use the specified table as the internal table.

E.g:

SELECT /* ORDERBSEMPMS.EMP_NAM FROM BSEED USE_NL (BSEMPMS) * / BSDPTMSMPMS, BSDPTMS WHERE BSEMPMS..DPT_NO, BSEMPMS.EMP_NO, DPT_NO = BSDPTMS.DPT_NO;. 196 / * USE_MERGE (TABLE) * /

Connect the specified table with other row sources by merged sorting.

E.g:

SELECT / * USE_MBSEMPMS.DPT_NO=BSDPTMS.DERGE (BSEMPMS ,BSDPTMS) * * fpt_no; rom bsempms, BSDPTMS WHERE

197. / * Use_hash (Table) * /

Connect the specified table with other row sources through a hash connection.

E.g:

SELECT / * USE_HBSEMPMS.DPT_NO=BSDPTMS.DASH (BSEMPMS ,BSDPTMS) * * frpt_no; om bsempms, BSDPTMS WHERE

198. / * driving_site (TABLE) * /

Enforce the query execution with different locations selected from Oracle.

E.g:

SELECT / * DRIVING_SITE (DEPTBSEMPMS.DPT_NO=DEPT.DPT_NO ;199. / * ) * / * From bsempms, dept @ bsdptms whereleading (table) * /

The specified table is used as the head table in the connection.

200. / * Cache (Table) * /

When full measuring scan is performed, the Cache prompts the most recent use of the LRU to place the retrieval block of the table in the buffer cache.

E.g:

SELECT / * FULL (BSEMPMS) CAHE (BSEMPMS) * / EMP_NAM from bsempms;