Oracle query statement collection (oracle basic command collection one)

1.create user username identified by password;//Create username and password oracle,oracle
2.grant connect,resource,dba to username;//Authorize grant connect,resource,dba,sysdba to username;
3.connect username/password//Enter.
4.select table_name,column_name from user_tab_columns where table_name='mview_log';//Query the table name, field name, etc. in the table.
5. How to execute a script SQL file? SQL>@PATH/filename.sql;
6.Oracle oledb provider's execution of multiple SQL statements in command is slightly different from SQL SERVER. SQL Server only needs to use " ;" splits multiple SQL statements, and Oracle needs to comply with the ORACLE calling specifications, that is, in addition to semicolon separation, the statement body must also be surrounded by begin /end;.
The description using C# should be as follows:
this. oleDbCommand1.CommandText = "begin INSERT INTO GROUP_INFO (GROUP_ID, GROUP_NAME) VALUES (1, \'2\'); INSERT INTO GROUP_INFO(GROUP_ID, GROUP_NAME) VALUES (2, \'2\'); end;";
7. Query all tables under the user select distinct table_name from user_tab_columns;
8. How to search for the first N records? Select a.*,rownum from (select * from cardkind order by cardkind) a where rownum 9. Find all tables under the user: select * from tab;
2. Display the currently connected user
SQL> show user
3. Check which users the system has
SQL> select * from all_users;
4. Create a new user and authorize it
SQL> create user a identified by a; (The default is built in SYSTEM table space)
SQL> grant connect,resource to a;
5. Connect to a new user
SQL> conn a/a
6. Query all objects under the current user
SQL> select * from tab;
7. Create the first table
SQL> create table a(a number);
8. Query the table structure
SQL> desc a
9. Insert new Record
SQL> insert into a values(1);
10. Query record
SQL> select * from a;
11. Change record
SQL> update a set a=2;
12. Delete records
SQL> delete from a;
13. Rollback
SQL> roll;
SQL> rollback;
14. Submit
SQL> commit;
select * from
(select t.*,dense_rank() over (order by cardkind) rank from cardkind t)
where rank = 2;
46. How to add a carriage return to a string?
select 'Welcome to visit'||chr(10)||'www.CSDN.NET' from dual ;
47. How are Chinese sorted?
Before Oracle9i, Chinese was sorted according to binary encoding.
The function of sorting by pinyin, radicals and strokes has been added in oracle9i. Set NLS_SORT value
SCHINESE_RADICAL_M Sort by radical (first order), stroke (second order)
SCHINESE_STROKE_M Sort by stroke (first order), radical (second order)
SCHINESE_PINYIN_M Sort by pinyin
48. Can object names in Oracle8i be in Chinese?
Yes
49. How to change the SQL*Plus startup options in WIN?
SQL*PLUS’s own option settings can be set in $ORACLE_HOME/sqlplus/admin/glogin.sql.
50. How to modify the default date of the oracle database?
alter session set nls_date_format='yyyymmddhh24miss';
OR
You can add a line in init.ora
nls_date_format='yyyymmddhh24miss'
51. How to put a small table into the keep pool?
alter table xxx storage(buffer_pool keep);
52. How to check whether a patch is installed?
check that oraInventory
53 . How to make the select statement automatically generate a serial number for the query results?
select rownum,COL from table;
54. How to know the tablespace where a certain table in the data pants is located?
select tablespace_name from user_tables where table_name=' TEST';
select * from user_tables There is a field TABLESPACE_NAME, (oracle);
select * from dba_segments where …;
55. How can you quickly make a backup table that is the same as the original table?
create table new_table as (select * from old_table);
55. How to modify procedure under sqlplus?
select line,trim(text) t from user_source where name ='A' order by line;
56. How to release PROCEDURE from being accidentally locked?
alter system kill session, kill that session, but you have to find out its session id first
or
Just rename the process .
57. What is SQL Reference?
is a sql manual, including syntax, functions, etc., which can be downloaded from the document center of the oracle official website.
58. How to check the status of the database?
under unix
ps -ef | grep ora
under windows
See if the service is up
Can you connect to the database
59. How to modify the primary key of a table?
alter table aaa
drop constraint aaa_key ;
alter table aaa
add constraint aaa_key primary key(a1,b1) ;
60. Change the size of the data file?
Use ALTER DATABASE .... DATAFILE .... ;
Manually changing the size of the data file will cause any damage to the original data file.
61. How to check which programs are running in ORACLE?
View v$sessions table
62. How can I see how many tablespaces the database has?
select * from dba_tablespaces;
63. How to modify the number of user connections in the Oracle database?
Modify initSID.ora, increase the process size, and restart the database.
64. How to find out the last update time of a record?
You can use logminer to check
65. How to read in PL/SQL Write a file?
UTL_FILE package allows users to read and write operating system files through PL/SQL.
66. How to put "&" into a record?
insert into a values (translate ('at{&}t','at{}','at'));
67. How to add QUERY parameter to EXP?
EXP USER/PASS FILE=A.DMP TABLES(BSEMMPMS)
QUERY='"WHERE EMP_NO=\'S09394\'\" |
68. Regarding the character set problem of oracle8i supporting simplified and traditional Chinese?
ZHS16GBK can support
69. What software is Data Guard?
It is the replacement product of Standby
70. How to create SPFILE?
SQL> connect / as sysdba
SQL> select * from v$version;
SQL> create pfile from spfile;
SQL> CREATE SPFILE FROM PFILE='E:\ora9i\admin\eygle\pfile\init.ora';
The file has been created.
SQL> CREATE SPFILE='E:\ora9i\database\SPFILEEYGLE.ORA' FROM
PFILE='E:\ora9i\admin\eygle\pfile\init.ora';
The file has been created.
71. Application of kernel parameters?
shmmax
Meaning: This setting does not determine how much physical memory the Oracle database or operating system uses, it only determines
the maximum amount of memory that can be used. This setting also does not affect the operating system's kernel resources.
Setting method: 0.5*physical memory
Example: Set shmsys:shminfo_shmmax=10485760
shmmin
Meaning: The minimum size of shared memory.
Setting method: Generally set to 1.
Example: Set shmsys:shminfo_shmmin=1:
shmmni
Meaning: The maximum number of shared memory segments in the system.
Example: Set shmsys:shminfo_shmmni=100
shmseg
Meaning: The maximum number of shared memory segments that each user process can use.
Example: Set shmsys:shminfo_shmseg=20:
semmni
Meaning: The maximum number of semaphore identifiers in the system.
Setting method: Set the value of this variable to the value of the largest
processes in init.ora of all Oracle instances on this system 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 way: the sum of the values of
processes in the initSID.ora of each Oracle instance (excluding the largest Processes parameter) + the largest Processes × 2 + 10 ×
The number of Oracle instances.
Example: Set semsys:seminfo_semmns=200
semmsl:
Meaning: The maximum number of semaphore in a set.
Setting method: Set to 10 + the largest Processes value in InitSID.ora of all Oracle instances.
Example: Set semsys:seminfo_semmsl=-200
72. How to check which users have SYSDBA and SYSOPER permissions?
SQL>conn sys/change_on_install
SQL>select * from V_$PWFILE_USERS;
73. How to backup one or more tables individually?
exp user/password tables=(Table 1,...,Table 2)
74. How to backup one or more users individually?
exp system/manager owner=(User 1, User 2,…, User n) file=Export file
75. How to perform full-text search on CLOB field?
SELECT * FROM A WHERE dbms_lob.instr(a.a,'K',1,1)>0; 76. How to display the currently connected user?
SHOW USER
77. How to view the data file placement Path?
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,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE _ID,STATUS FROM
DBA_ROLLBACK_SEGS
79. How to change the Check range of a field's initial definition?
SQL> alter table xxx drop constraint constraint_name;
Create new constraints later:
SQL> alter table xxx add constraint constraint_name check();
80. What are the commonly used Oracle system files?
Display these file information through the following views: v$database, v$datafile, v$logfile v$controlfile
v$parameter;
81. INNER JOIN?
Select a.* from bsempms a, bsdptms b where a.dpt_no=b.dpt_no;
82. How to connect externally?
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 execute a script SQL file?
SQL>@$PATH/filename.sql;
84. How to quickly Clear a large table?
SQL>truncate table table_name;
85. How to check how many database instances there are?
SQL>SELECT * FROM V$INSTANCE;
86. How to check how many tables there are in the database ?
SQL>select * from all_tables;
87. How to test the time it takes to execute a SQL statement?
SQL>set timing on ;
SQL>select * from tablename;
88. CHR What is the inverse function of ()?
ASCII()
SELECT CHAR(65) FROM DUAL;
SELECT ASCII('A') FROM DUAL;
89. String concatenation
SELECT CONCAT(COL1,COL2) FROM TABLE;
SELECT COL1||COL2 FROM TABLE;
90. How to export the select results to a text file?
SQL>SPOOL C:\ABCD.TXT;
SQL>select * from table;
SQL >spool off;
91. How to estimate the number of I/Os executed by SQL?
SQL>SET AUTOTRACE ON ;
SQL>SELECT * FROM TABLE;
OR
SQL>SELECT * FROM v$filestat ;
You can check the IO number
92. How to change fields under sqlplus Size?
alter table table_name modify (field_name varchar2(100));
Change the row to a larger size, but not to a smaller row (unless they are all empty)
93. How to query the data of a certain day?
select * from table_name where trunc(date field)=to_date('2003-05-02','yyyy-mm-
dd');
94. How to insert the whole year's date using sql statement?
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. How to modify the table name?
alter table old_table_name rename to new_table_name;
96. How to get the return status value of the command?
sqlcode=0
97. How to know the permissions a user has?
SELECT * FROM dba_sys_privs;
98. What is the difference between ORACLE9I downloaded from the Internet and the standard version sold on the market?
There is no functional difference, except that Oracle has expressly stipulated that Oracle products downloaded from the website must not be used for
commercial purposes, otherwise there will be infringement.
99. How to determine whether the database is running in archive mode or non-archive mode?
Enter dbastudio, Process-->Database--->Archive View.
100. What is the difference between sql>startup pfile, ifile, and spfiled?
pfile is Oracle’s traditional initialization parameter file, in 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
After startup, only pfile should be accessible
101. How to search for the first N records?
SELECT * FROM empLOYEE WHERE ROWNUM < n ORDER BY empno;
102. How to know the number of concurrent users supported by Oracle on the machine?
SQL>conn internal;
SQL>show parameter processes;
103. Can db_block_size be modified?
Generally not, it is not recommended.
104. How to 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 statement How to find the Nth largest value in a column?
select * from
(select t.*,dense_rank() over (order by sal) rank from employee)
where rank = N;
106. How to add 2 years? (
select add_months(sysdate,24) from dual;
107. What does it mean when USED_UBLK is negative?
It is "harmless".
108. What does Connect string mean?
It should be the content after the service name in tnsnames.ora
109. How to expand the size of REDO LOG?
Create a temporary redolog group, then switch logs, delete the previous log, and create a new log. #110. Can tablespace not be larger than 4G?
No limit.
111. Return the smallest integer value greater than or equal to N?
SELECT CEIL(N) FROM DUAL;
112. Return less than or equal to N Minimum integer value?
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 to find the name of the primary key field of a database table?
SQL>SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE='P' and
table_name='TABLE_NAME';
116. Function to add two result sets to each other ?
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. Function that subtracts 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,
...}
When inserting:
insert into table cust
values( seq_cust.nextval, ...)
Commonly used ways of writing each part of the date
119>. How to write the year of the time point:
SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL;
120>. How to get the month of a time point:
SELECT TO_CHAR(SYSDATE,'MM') FROM DUAL;
121>. How to get the day of a time point:
SELECT TO_CHAR(SYSDATE, 'DD') FROM DUAL;
122>. How to get the hour at a time point:
SELECT TO_CHAR(SYSDATE,'HH24') FROM DUAL;
123>. How to get the minute at a time point:
SELECT TO_CHAR(SYSDATE,'MI') FROM DUAL;
124>. How to get the seconds of the time point:
SELECT TO_CHAR(SYSDATE,'SS') FROM DUAL;
125>. How to get the date at a time point:
SELECT TRUNC(SYSDATE) FROM DUAL;
126>. How to get the time at a time point:
SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') FROM DUAL;
127>. Convert a string into a date or time format:
SELECT TO_DATE(' 2003/08/01') FROM DUAL; 129>. Return the day of the week of the parameter:
SELECT TO_CHAR(SYSDATE,'D') FROM DUAL;
130>. Return the day of the year for the parameter How to write days:
SELECT TO_CHAR(SYSDATE,'DDD') FROM DUAL;
131>. How to return the number of seconds between midnight and the time value specified in the parameter:
SELECT TO_CHAR(SYSDATE ,'SSSSS') FROM DUAL;
132>. Return the week of the year in 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 ;
Automatically insert the value of the sequence
INSERT INTO emp
VALUES ( empseq.nextval, 'LEWIS', 'CLERK',
7902, SYSDATE, 1200, NULL, 20) ;
134. ROWNUM
Serial number of rows sorted by settings
SELECT * FROM emp WHERE ROWNUM < 10 ;
135. ROWID
Return the physical address of the row
SELECT ROWID, ename FROM emp WHERE deptno = 20 ;
136. Convert N seconds to hours, minutes and seconds 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"minutes
"ss"seconds"');
dbms_output.put_line(ret);
end;
137. How to query the process of doing a relatively large sort?
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 to query SQL statement for relatively large sorting 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 to 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 Remove 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 execute VIEW1 .SQL is enough.
SQL >@VIEW1.SQL;
142. Solution to ORA-01555 SNAPSHOT TOO OLD
Increase the value of MINEXTENTS, increase the size of the extent, and set a high OPTIMAL value.
143. The rollback segment space required by the transaction is not enough, which is manifested as the table space is full (ORA-01560 error), and the rollback segment extension reaches the value of the parameter MAXEXTENTS (ORA-01628).
Add files to the rollback segment table space or make existing files larger; increase the value of MAXEXTENTS.
144. How to encrypt ORACLE stored procedures?
The following stored procedure content is placed in the AA.SQL file
create or replace procedure testCCB(i in number) as
begin
dbms_output.put_line('The 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
Running AA.plb
SQL> @AA.plb ;
145. How to monitor the wait of a case ?
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 rollback segment contention?
select name, waits, gets, waits/gets "Ratio"
from v$rollstat C, v$rollname D
where C.usn = D.usn;
147. How to monitor table space I/O ratio?
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 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 to find all 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 the hit rate of SGA?
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-(sum(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 shared cache area 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 to display all database objects Category and size?
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. Monitor the hit rate of the redo log cache in SGA, it should be less than 1 %
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses) *100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
155. Monitor the sorting ratio of memory and hard disk, it is best to make it Less than .10, increase sort_area_size
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts
(disk)');
156. How to monitor who is in the current database What SQL statement is run?
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
157. How to 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 is divided by the former, the ratio is less than 1% , close to 0% is better.
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 parameter needs 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 parameter needs to be increased
159. How to know the ID number of the current user?
SQL>SHOW USER;
OR
SQL>select user from dual;
160. How to view highly fragmented tables?
SELECT segment_name table_name, COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY
segment_name);
162 . How to know the storage situation of the table 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 to know how the index is stored in the table space?
select segment_name,count(*) from dba_extents where segment_type='INDEX' and
owner='&owner'
group by segment_name;
164. How to know the user session that uses more CPU?
11 is cpu used by this session
select a.sid,spid,status,substr(a.program,1,40)
prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=11 and c.sid= a.sid and a.paddr=b.addr order by value desc;
165. How to know the listener log file?
Take 8I as an example
$ORACLE_HOME/NETWORK/LOG/LISTENER.LOG
166. How to know the listener parameter file?
Take 8I as an example
$ORACLE_HOME/NETWORK/ADMIN/LISTENER.ORA
167. How to know the TNS connection file?
Take 8I as an example
$ORACLE_HOME/NETWORK/ADMIN/TNSNAMES.ORA
168. How to know the Sql*Net environment file?
Take 8I as an example
$ORACLE_HOME/NETWORK/ADMIN/SQLNET.ORA
169. How Know the warning log file?
Take 8I as an example
$ORACLE_HOME/ADMIN/SID/BDUMP/SIDALRT.LOG
170. How to know the basic structure?
Take 8I as an example
$ORACLE_HOME/ RDBMS/ADMIN/STANDARD.SQL
171. How do you know how to create a data dictionary view?
Take 8I as an example
$ORACLE_HOME/RDBMS/ADMIN/CATALOG.SQL
172. How do you know how to create audit data Dictionary view?
Take 8I as an example
$ORACLE_HOME/RDBMS/ADMIN/CATAUDIT.SQL
173. How to know how to create a data dictionary view for snapshot?
Take 8I as an example
$ORACLE_HOME/ RDBMS/ADMIN/CATSNAP.SQL
This lecture mainly talks about the optimization method of SQL statements! Mainly based on ORACLE9I.
174. /*+ALL_ROWS*/
Indicates the cost-based optimization of statement block selection Method, and obtain the best throughput to minimize resource consumption.
For example:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';
175 . /*+FIRST_ROWS*/
Indicates that the cost-based optimization method is selected for the statement block, and the best response time is obtained to minimize resource consumption.
For example:
SELECT /*+FIRST_ROWS*/ EMP_NO ,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP'; 176. /*+CHOOSE*/
Indicates that if there is statistical information about the access table in the data dictionary, the cost-based optimization method will be used to obtain the best throughput. ;
Indicates that if there is no statistical information about the access table in the data dictionary, the optimization method will be based on the rule cost;
For example:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=' CCBZZP';
177. /*+RULE*/
Indicates that the rule-based optimization method is selected for the statement block.
For example:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';
178. /*+FULL(TABLE)*/
Indicates the method of selecting a global scan for the table.
For example:
SELECT /*+FULL(A)* / EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='CCBZZP';
179. /*+ROWID(TABLE)*/
The prompt clearly indicates that the specified table is accessed based on ROWID.
For example:
SELECT /*+ROWID(BSEMMPMS)*/ * FROM BSEMMPMS WHERE ROWID>='AAAAAAAAAAAAAAA'
AND EMP_NO='CCBZZP';
180. /*+CLUSTER(TABLE)*/
The prompt clearly indicates that the access method of cluster scan is selected for the specified table, which is only valid for cluster objects.
For example:
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 table selection index.
For example:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE
BSEMPMS */ FROM BSEMPMS WHERE SEX='M' ;
182. /*+INDEX_ASC(TABLE INDEX_NAME)*/
Indicates the scanning method for table selection index in ascending order.

More oracle query statement collection (oracle basic command collection one) related articles Please pay attention to PHP Chinese website!