Oracle commonly used fool problem 1000 asked (three)

88. Is the reaction function of chr ()?

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 do I 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 do I query a day of data?

Select * from table_name where trunc = 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 do I 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 archiving mode or running in non-invasive modes?

Go to 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 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 Count (ID) from AA) Total from Dual; 105. How to use SQL statement to find the N the N 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.dmp fromuser = user_old baser = 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 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 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 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>. 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;