Oracle query statement (oracle basic commands are all one)

1. create user username identified by password; // create a username and password for oracle and oracle
2. grant connect, resource, dba to username; // authorize grant connect, resource, dba, sysdba to username;
3. connect username/password.
4. select table_name, column_name from user_tab_columns where table_name = 'mview _ log'; // query the table name and field name in the table.
5. How to execute a script SQL file? SQL> @ PATH/filename. SQL;
6. oracle oledb providers execute multiple SQL statements in command, which is slightly different from SQL server. SQL Server only needs to use ";" to separate multiple SQL statements. Oracle must comply with ORACLE call specifications, that is, in addition to semicolons, you must use begin/end; to enclose the statement body.
The C # description 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 <n
9. Find all tables under the user: select * from tab;
2. display the current connected user
SQL> show user
3. view the users in the system
SQL> select * from all_users;
4. Create and authorize a user
SQL> create user a identified by a; (created in the SYSTEM tablespace by default)
SQL> grant connect, resource to;
5. connect to a new user
SQL> conn 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
9. Insert a new record
SQL> insert into a values (1 );
10. query records
SQL> select * from;
11. change history
SQL> update a set a = 2;
12. delete records
SQL> delete from;
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 do I add a carriage return to the string?
Select 'Welcome to visit' | chr (10) | 'www. CSDN. net' from dual;
47. How is Chinese sorted?
Before Oracle9i, Chinese characters were sorted by binary encoding.
The sorting by pinyin, radical, and stroke function is added to oracle9i. Set the NLS_SORT Value
SCHINESE_RADICAL_M is sorted by the beginning (first order) and strokes (Second Order ).
SCHINESE_STROKE_M is sorted by strokes (first order) and heads (Second Order ).
SCHINESE_PINYIN_M is sorted by pinyin
48. Can the object name in Oracle8i be in Chinese?
Yes
49. How to change the SQL * Plus startup option in WIN?
You can set SQL * PLUS options in $ ORACLE_HOME/sqlplus/admin/glogin. SQL.
50. How do I modify the default date of the replicel 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 small tables into the keep pool?
Alter table xxx storage (buffer_pool keep );
52. How do I check whether a patch is installed?
Check that oraInventory
53. How can I enable the select statement to automatically generate sequence numbers for the query results?
Select rownum, COL from table;
54. How do I know the tablespace of a table in data pants?
Select tablespace_name from user_tables where table_name = 'test ';
Select * from user_tables has a field TABLESPACE_NAME, (oracle );
Select * from dba_segments where ...;
55. How can I quickly create a backup table that is the same as the original table?
Create table new_table as (select * from old_table );
55. How can I modify procedure under sqlplus?
Select line, trim (text) t from user_source where name = 'A' order by line;
56. How to unlock PROCEDURE from accidental locking?
Alter system kill session to kill the session, but you need to first find out her session id
Or
Change the name of the process.
57. What is SQL Reference?
Is an SQL user manual, including syntax, functions, and so on. The oracle official website documentation center has downloaded.
58. How can I view the database status?
In unix
Ps-ef | grep ora
Windows
Check whether the service is up
Can I connect to a database?
59. How can I 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. How to change the data file size?
Use alter database... DATAFILE ....;
Manually changing the size of the data file does not damage the original data file.
61. How to check which programs are running in ORACLE?
View the v $ sessions table
62. How many tablespaces are there in the database?
Select * from dba_tablespaces;
63. How do I modify the number of user connections in the oracle database?
Modify initSID. ora, increase process, and restart the database.
64. How can I find the last update time of a record?
You can use logminer to view
65. how to read and write files in PL/SQL?
The UTL_FILE package allows you 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 does EXP add QUERY parameters?
Exp user/pass file = A. dmp tables (BSEMPMS)
QUERY = '"WHERE EMP_NO = \ 's09394 \' \" success \'\"﹔
68. Question about oracle8i's support for simplified and traditional character sets?
ZHS16GBK supported
69. What is Data Guard?
Is the replacement product of Standby.
70. How to Create a 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. Kernel Parameter application?
Shmmax
Meaning: This setting does not determine the actual amount of physical memory used by the Oracle database or operating system.
The maximum number of memories that can be used. This setting does not affect the kernel resources of the operating system.
Setting Method: 0.5 * physical memory
Example: Set shmsys: shminfo_shmmax = 10485760
Shmmin
Description: 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
Description: Maximum number of semaphore identifierer in the system.
Setting Method: set the value of this variable to the maximum value in init. ora of all Oracle instances on this system.
The value of the processes of add 10.
Example: Set semsys: seminfo_semmni = 100
Semmns
Meaning: the maximum number of emaphores in the system.
Setting method: the value can be calculated as follows:
The sum of the values of processes (excluding the largest Processes parameter) + the largest Processes × 2 + 10 ×
Number of Oracle instances.
Example: Set semsys: seminfo_semmns = 200
Semmsl:
Meaning: the maximum number of semaphore in a set.
Setting Method: Set to the maximum Processes value in InitSID. ora of 10 + Oracle instances.
Example: Set semsys: seminfo_semmsl =-200
72. How can I check which users have SYSDBA and SYSOPER permissions?
SQL> conn sys/change_on_install
SQL> select * from V _ $ PWFILE_USERS;
73. How to back up one or more tables separately?
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 can I perform full-text search on the CLOB field?
SELECT * from a where dbms_lob.instr (a. a, 'k',)> 0; 76. How to display the current connected user?
SHOW USER
77. How do I 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 can I view the existing rollback segments and their statuses?
SQL> col segment format a30
SQL> SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME, SEGMENT_ID, FILE _ ID, STATUS FROM
DBA_ROLLBACK_SEGS
79. How do I change the Check range of a field's initial definition?
SQL> alter table xxx drop constraint constraint_name;
Then create a new constraint:
SQL> alter table xxx add constraint constraint_name check ();
80. What are common Oracle system files?
The following view displays the file information: 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 do I 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 can I quickly clear a large table?
SQL> truncate table table_name;
85. How many database instances are there?
SQL> SELECT * FROM V $ INSTANCE;
86. How to query the number of tables in the database?
SQL> select * from all_tables;
87. How does one test the time used for SQL statement execution?
SQL> set timing on;
SQL> select * from tablename;
88. What is the inverse function of CHR?
ASCII ()
Select char (65) from dual;
Select ascii ('A') from dual;
89. String connection
Select concat (COL1, COL2) from table;
SELECT COL1 | COL2 from table;
90. How to export the select result to a text file?
SQL> SPOOL C: \ ABCD. TXT;
SQL> select * from table;
SQL> spool off;
91. How to estimate the number of I/O executed by SQL?
SQL> SET AUTOTRACE ON;
SQL> SELECT * FROM TABLE;
OR
SQL> SELECT * FROM v $ filestat;
I/O count can be viewed
92. How can I change the field size in sqlplus?
Alter table table_name modify (field_name varchar2 (100 ));
It cannot be changed to a large row (unless it is empty)
93. How do I query data of a certain day?
Select * from table_name where trunc (Date Field) = to_date ('2017-05-02 ', 'yyyy-mm-
Dd ');
94. How do I insert the SQL statement into the year-round date?
Create table BSYEAR (d date );
Insert into BSYEAR
Select to_date ('20140901', 'yyyymmdd') + rownum-1
From all_objects
Where rownum <= to_char (to_date ('201312', 'yyyymmdd'), 'ddd ');
95. If you want to modify the table name?
Alter table old_table_name rename to new_table_name;
96. How to obtain the return status value of the command?
Sqlcode = 0
97. How do I know the user's permissions?
SELECT * FROM dba_sys_privs;
98. What is the difference between the ORACLE9I downloaded from the internet and the standard edition sold on the market?
In terms of functions, there is no difference, except that oracle has clearly stipulated that oracle products downloaded from websites cannot be used
Commercial use, otherwise infringement.
99. How can I determine whether the database is running in archive mode or non-archive mode?
Go to dbastudio, and choose history> database> archive.
100. SQL> what is the difference between startup pfile and ifile and spfiled?
Pfile is a traditional Oracle initialization parameter file in text format.
Ifile is similar to the include in C language and is used to introduce another file
Spfile is a new and default parameter file in 9i, in binary format.
After startup, only pfile can be connected.
101. How to search for the first N records?
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 method for retrieving the year of the time point:
SELECT TO_CHAR (SYSDATE, 'yyyy') from dual;
120>. Write the statement for getting the month of 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 changed to character form
SELECT TO_CHAR (SYSDATE) from dual;
128>. Convert string to date or time form:
SELECT TO_DATE ('2014/1/01') from dual; 2003/08>. specify the number of weeks for the returned parameter:
SELECT TO_CHAR (SYSDATE, 'D') from dual;
130>. Return the number of days in a year as follows:
SELECT TO_CHAR (SYSDATE, 'ddd ') from dual;
131>. returns the number of seconds between midnight and the specified time value in the parameter:
SELECT TO_CHAR (SYSDATE, 'ssss') 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 inserted Sequence Value
Insert into emp
VALUES (em1_q. nextval, 'Lewis ', 'cler ',
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) | 'hourly '| to_char (to_date (mod (n, 3600), 'sss'), 'fmmi"
"Ss" seconds "');
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 extended
A solution for the value (ORA-01628) of the parameter MAXEXTENTS.
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. 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 in SGA, which should 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 the category and size of all database objects?
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 log cache hit rate in SGA, which should be less than 1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode (gets, 100, misses/gets *) 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 to hard disk, preferably make it smaller 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 running what SQL statements in the current database?
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. This ratio is less than 1%, and it is better to close to 0%.
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 the value is greater than 0.5, the parameter must 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, increase the Parameter
159. How do I know the ID of the current user?
SQL> SHOW USER;
OR
SQL> select user from dual;
160. How to View tables with high fragmentation levels?
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
Segment_name );
162. How do I know the storage of tables in the tablespace?
Select segment_name, sum (bytes), count (*) ext_quan from dba_extents where
Tablespace_name = '& tablespace_name' and segment_type = 'table' group
Tablespace_name, segment_name;
163. How do I know the storage of indexes in the tablespace?
Select segment_name, count (*) from dba_extents where segment_type = 'index' and
Owner = '& owner'
Group by segment_name;
164. How do I know user sessions with many CPUs?
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 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 files?
Take 8I as an Example
$ ORACLE_HOME/NETWORK/ADMIN/SQLNET. ORA
169. How do I know the warning log file?
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 how to create a data dictionary view?
Take 8I as an Example
$ ORACLE_HOME/RDBMS/ADMIN/CATALOG. SQL
172. How do I know how to create a data dictionary view for auditing?
Take 8I as an Example
$ ORACLE_HOME/RDBMS/ADMIN/CATAUDIT. SQL
173. How do I know how to create a data dictionary view for a snapshot?
Take 8I as an Example
$ ORACLE_HOME/RDBMS/ADMIN/CATSNAP. SQL
This article describes how to optimize SQL statements! It is mainly based on ORACLE9I.
174./* + ALL_ROWS */
It indicates that the overhead-based optimization method is selected for the statement block, and the optimal throughput is obtained to minimize the resource consumption.
For example:
SELECT/* + ALL + _ ROWS */EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'ccbzzp ';
175./* + FIRST_ROWS */
It indicates that the overhead-based optimization method is selected for the statement block and the optimal response time is obtained to minimize the resource consumption.
For example:
SELECT/* + FIRST_ROWS */EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'ccbzzp'; 176./* + CHOOSE */
It indicates that if the data dictionary contains statistics about the access table, the overhead-based optimization method is used to obtain the optimal throughput;
It indicates that if no statistical information of the Access Table is found in the data dictionary, the rule overhead-based optimization method is used;
For example:
SELECT/* + CHOOSE */EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'ccbzzp ';
177./* + RULE */
Indicates the rule-based Optimization Method 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 used to globally scan the table.
For example:
SELECT/* + FULL (A) */EMP_NO, EMP_NAM from bsempms a where EMP_NO = 'ccbzzp ';
179./* + ROWID (TABLE )*/
The prompt explicitly indicates that the specified table is accessed Based on ROWID.
For example:
SELECT/* + ROWID (BSEMPMS) */* from bsempms where rowid> = 'aaaaaaaaaaaaaaa'
AND EMP_NO = 'ccbzzp ';
180./* + CLUSTER (TABLE )*/
The prompt clearly indicates the access method for the selected cluster scan for the specified table, which is only valid for the cluster object.
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 method used to scan the selected index on the table.
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 method used to scan the index in ascending order.