Oracle faq one thousand [101 to 200]

101. How to search for the first N records?

SQL> SELECT * FROM empLOYEE WHERE ROWNUM <n ORDER BY empno;
 

 

102. How many concurrent users are supported by Oracle on the machine?
SQL> conn internal;
SQL> show parameter processes;

103. Can db_block_size be modified?
Generally, this is not recommended.

104. How do I count the total number of records in two tables?

Select (select count (id) from aa) + (select count (id) from bb) Total from dual;

105. How to use SQL statements to find the nth limit in a column?

Select * from (select t. *, dense_rank () over (order by sal) rank from employee) where rank = N;
 

106. How can I add 2 years to an existing date?

Select add_months (sysdate, 24) from dual;
 
107. What does USED_UBLK mean when it is negative?
It is "harmless ".

108. What does Connect string mean?
It should be the content following the service name in tnsnames. ora.

109. How to Increase the redo log size?
Create a temporary redolog group, switch logs, delete previous logs, and create new logs.

110. Can tablespace be greater than 4 GB?
No restrictions.

 
111. Is the minimum integer greater than or equal to N returned?

Select ceil (N) from dual;
 
112. Is the minimum integer less than or equal to N returned?

Select floor (N) from dual;
113. Return the last day of the current month?

SELECT LAST_DAY (SYSDATE) from dual;
 

114. How to import data between different users?

Imp system/manager file = AA. dmp fromuser = USER_OLD TOUSER = USER_NEW ROWS = y indexes = 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 are the mutual addition functions 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 SELECT * FROM BSEMPMS_NEW;

 

117. What are the mutual subtraction functions of two result sets?
SQL> SELECT * FROM BSEMPMS_OLD MINUS SELECT * FROM BSEMPMS_NEW;

118. how to configure Sequence?

-- Create sequence seq_custid
Create sequence seq_custid start 1 incrememt by 1;
-- When creating a table:
Create table cust
{Cust_id smallint not null,
...}
-- Insert:
Insert into table cust
Values (seq_cust.nextval ,...)
Common Writing of various parts of a date

119. Write the year at the time point:

 

SELECT TO_CHAR (SYSDATE, YYYY) from dual;
 
120. Write the statement for getting the month at the time point:

SELECT TO_CHAR (SYSDATE, MM) from dual;
 

121. Write the date at the time point:

SELECT TO_CHAR (SYSDATE, DD) from dual;

122. Write the time point:

SELECT TO_CHAR (SYSDATE, HH24) from dual;

123. Write the time points:

 

SELECT TO_CHAR (SYSDATE, MI) from dual;
 
124. The second method for getting the time point:

 

SELECT TO_CHAR (SYSDATE, SS) from dual;
 
125. Write the date at the time point:

Select trunc (SYSDATE) from dual;

126. Write the time for getting the time point:

SELECT TO_CHAR (SYSDATE, HH24: MI: SS) from dual;

127. Date, time form into character form:

 

SELECT TO_CHAR (SYSDATE) from dual;

128. Convert string to date or time form:

 

SELECT TO_DATE (2003/08/01) from dual;
 
129. Statement of the returned parameter day of the week:

 

SELECT TO_CHAR (SYSDATE, D) from dual;
 
130. Write the day of the year of the returned parameter:

 

SELECT TO_CHAR (SYSDATE, DDD) from dual;

131. Return the number of seconds between midnight and the specified time value in the parameter:

 

SELECT TO_CHAR (SYSDATE, SSSSS) from dual;

132. Statement of the week of the year in the return parameter:

 

SELECT TO_CHAR (SYSDATE, WW) from dual;
 

Virtual Field

133. CURRVAL and nextval

-- Create sequence for table
Create sequence emsequq ...;
SELECT empseq. currval from dual;
-- Automatically Insert the Sequence Value
Insert into emp
VALUES (em1_q. nextval, LEWIS, CLERK,
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), sssss), 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 parameter MAXEXTENTS (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. v