Oracle FAQ (2)

71. Application of kernel parameters

SHMMAX

Meaning: This setting does not decide how much physical memory is used in the Oracle database or operating system, only determines the number of memory you can use. This setting does not affect the kernel resources of the operating system.

Setting method: 0.5 * entity memory

Example: set shmsys: shminfo_shmmax = 10485760

shmmin

Meaning: The minimum size of the shared memory.

Setting method: Usually set to 1.

Example: set shmsys: shminfo_shmmin = 1:

Shmmni

Meaning: The maximum number of share memory segments in the system.

Example: set shmsys: shminfo_shmmni = 100

shmseg

Meaning: The number of maximum shared memory can be used for each user process.

Example: set shmsys: shminfo_shmseg = 20:

Semmni

Meaning: The maximum number of Semaphore Identifierer in the system.

Setting method: Set the value of this variable to all of the max of all Oracle's instances of this Processes plus 10.

Example: set Semsys: seminfo_semmni = 100

Semmns

Meaning: The maximum number of EMAPHORES in the system.

Setting method: This value can be calculated in the following manner: the sum of the value of the processes of each Oracle instance, the sum of the value (remove the largest processes parameter) the number of the maximum of the processes × 2 10 × Oracle instance.

Example: set semsys: seminfo_semmp = 200

Semmsl:

Meaning: The maximum number of Semaphore in a set.

Setting method: Set the value of the largest processes in INITSID.ORA for 10 all Oracle instances.

Example: set Semsys: seminfo_semmsl = -200

72. Which users have SYSDBA, SYSOPER license?

SQL> Conn Sys / Change_on_install

SQL> SELECT * FROM V_ $ PWFILE_USERS;

73. How to back up one or more tables alone?

EXP user / password Tables = (Table 1, ..., Table 2)

74. How to back up one or more users separately?

Exp System / Manager Owner = (User 1, User 2, ..., User N) FILE = Export File

75. How do I make a full-text search on the CLOB field?

Select * from a where dbms_lob.instr (a.a, 'k', 1, 1)> 0;

76. How to display the current connection user

SHOW User

77. How to view the path to the profile file

Col file_name format A50

SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES / 1024/1024, File_Name from

DBA_DATA_FILES ORDER BY FILE_ID;

78. How to view existing rollback segments and their status

SQL> Col ​​Segment Format A30

SQL> SELECT

Segment_name, ooner, tablespace_name, segment_id, file_id, status from

DBA_ROLLBACK_SEGS

79. How do I change the CHECK scope of the initial definition of the field?

SQL> ALTER TABLE XXX DROP CONSTRAINT CONSTRAINT_NAME; then create a new constraint again:

SQL> ALTER TABLE XXX Add Constraint Constraint_name Check ();

80. What are the Oracle common system files?

Show these files by following the following view: V $ DATABASE, V $ DATAFILE, V $ logfile v $ controlfile

v $ parameter;

81. Inner connection inner Join

Select a. * From bsempms A, BSDPTMS b where A.dpt_no = B.DPT_NO;

82. How to connect

Select a. * From bsempms a, bsdptms b where A.dpt_no = B.DPT_NO ( );

Select a. * From bsempms A, BSDPTMS B wherea.dpt_no ( ) = b.dpt_no;

83. How to perform a script SQL file

SQL> @ $ PATH / filename.sql;

84. How to quickly empty a big table

SQL> TRUNCATE TABLE TABLE_NAME;

85. How many database instances do you check?

SQL> SELECT * FROM V $ INSTANCE;

86. How many tables do you query the database?

SQL> SELECT * from ALL_TABLES;

87. How to test the time used by the SQL statement

SQL> SET TIMING ON;

SQL> Select * from Tablename;

Everyone may encounter a lot of problems that seem difficult, especially for novices, today I will summarize it, release it to everyone,

I hope to help everyone! Discuss with everyone, make progress together!

It is not used by Oracle masters.

88. The reverse function of chr () is

ASCII ()

SELECT Char (65) from Dual;

SELECT ASCII ('a') from Dual;

89. Connection of strings

Select Concat (col1, col2) from table;

SELECT col1 || col2 from table;

90. How to guide the results from SELECT to a text file?

SQL> Spool C: /Abcd.txt;

SQL> SELECT *.

SQL> Spool OFF;

91. How to estimate the I / O number of SQL execution

SQL> Set autotrace on;

SQL> SELECT *.

Oral

SQL> SELECT * FROM V $ fileStat;

You can view the number of IO

92. How to change the field size under SQLPLUS

Alter Table Table_name Modify (Field_Name Varchar2 (100));

Change the big line, change, unless it is empty)

93. How to check a day of information

Select * from table_name where

Trunc (date field) = to_date ('2003-05-02', 'YYYY-MM-DD');

94. How do SQL statements insert all year dates?

Create Table Bsyear (D Date);

INSERT INTO BSYEAR

SELECT TO_DATE ('20030101', 'YYYYMMDD') ROWNUM-1

From all_Objects

Where rownum <= to_char (to_date ('20031231', 'YYYYMMDD'), 'DDD'); 95. If you modify the table name

Alter Table Old_table_name rename to new_table_name;

96. How do I get the return status value of the command?

SQLCODE = 0

97. How to know the permissions owned by the user

Select * from DBA_SYS_PRIVS;

98. What is the difference between Oracle9i downloaded from the Internet?

From the functional saying, Oracle has clearly defined; Oracle products downloaded from the website must not be used for commercial purposes, otherwise infringement.

99. How to determine that the database is running in the archive mode or running in non-archive mode?

Enter dbastudio, history -> Database ---> Archive View.

100. SQL> Startup Pfile and IFILE, SPFILED What is the difference?

Pfile is an Oracle traditional initialization parameter file, text format.

IFILE is similar to include in C language, used to introduce another file

SPFILE is new in 9i and is the default parameter file, binary format

STARTUP should only pick up PFILE

101. How to search for the first N record?

Select * from Employee Where Rownum

ORDER BY Empno;

102. How do I know how much the number of ORACLE supports on the machine

SQL> CONN INTERNAL;

SQL> Show Parameter Processes;

103. Can DB_BLOCK_SIZE can be modified?

Generally, it is not recommended.

104. Total number of records of two tables

SELECT (SELECT Count (ID) from AA) (SELECT Count (ID) from bb) total

From Dual;

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 USED_UBLK?

IT IS "harmless".

108. What does Connect String means

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. Does TABLESPACE are not greater than 4g?

no limit.

111. Return the minimum integer value equal to N

Select CEIL (n) from dual;

112. Return to less than or equal to N minimum value

SELECT FLOOR (N) from Dual;

113. Return to the last day of the current month

SELECT LAST_DAY (SYSDATE) from DUAL

114. How to import data imports between users

IMP system / manager file = aa.dmp fromuser = user_old touser = user_new

Rows = y indexes = y;

115. How to find the name of the primary key field of the database table

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

116. 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. Functions of two result sets

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

118. How to configure sequence

Built sequence seq_custid

CREATE SEQUENCE SEQ_CUSTID START 1 Incrememt by 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 the time point:

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

120>. Writing of the month of time point:

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

121>. Writing of the day of time:

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

122>. Writings at the time point:

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

123>. Writing method for time point:

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

124>. Writing of seconds in time:

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

125>. Date of taking the time point:

Select Trunc (sysdate) from dual;

126>. Take the time of time point:

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

127>. Date, time form becomes a characteristic

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>. Returns the weekly writing method:

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

130>. Returns 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 year of the first year of the year:

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

Everyone may encounter a lot of problems that seem difficult, especially for novices, today I will summarize it, release it to everyone,

I hope to help everyone! Discuss with everyone, make progress together!

It is not used by Oracle masters. Virtual fields

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 manifested as a table space (ORA-01560 error), and the return segment is extended to the parameters.

MaxExtents' value (ORA-01628) solution.

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) 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, colorn_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;