Everyone may encounter a lot of problems that seem to be difficult, especially for novices, today I will summarize it, release it to everyone, I hope to help everyone! Discuss with everyone, and make progress together! It is not to see for Oracle masters. Administrator / Administrator 3. Oracle 8.0.5 How to create a database? Use ORAINST.
If there is a Motif interface, you can use ORAINST / M 4. ORACLE 8.1.7 how to create a database? DBAssist 5. How to create a database? DBCA 6. What is the bare device in Oracle? Naked equipment is bypassing the file system Access storage space 7. How can oracle distinguish between 64-bit / 32bit version? ? ? $ Sqlplus '/ AS SYSDBA' SQL * Plus:. Release 9.0.1.0.0 - Production on Mon Jul 14 17:01:09 2003 (c) Copyright 2001 Oracle Corporation All rights reserved Connected to: Oracle9i Enterprise Edition Release 9.0.. 1.0.0 - Production with the parting optioning jserver Release 9.0.1.0.0 - Production SQL> SELECT * FROM V $ VERSION; Banner ---------------------- ---------------------------------------- Oracle9i Enterprise Edition Release 9.0.1.0. 0 - Production PL / SQL Release 9.0.1.0.0 - Production Core 9.0.1.0.0 Production TNS for Solaris: Version 9.0.1.0.0 - Production NLSRTL VERSION 9.0.1.0.0 - Production SQL> 8. What does SVRMGR mean? SVRMGRL, SERVER Manager. 9i, there is no, it has changed to SQLPlus / Nolog to archive log type 9. How to distinguish which machine is logged in Oracle? Select Machine, Terminal from V $ session; 10. What statement is used inquate? DESC TABLE_NAME You can query the structure of the table SELECT FIELD_NAME, ... from ... You can query the value of the field Select * from all_tables where table_name like '%' select * from all_tab_columns where Table_name = '??' 11. How to get the trigger, Process, creation script of function? Desc user_source user_triggers 12. How to calculate the size of the space occupied by a table? select owner, table_name, NUM_ROWS, BLOCKS * AAA / 1024/1024 "Size M", EMPTY_BLOCKS, LAST_ANALYZED from dba_tables where table_name = 'XXX'; Here: AAA is the value of db_block_size; XXX is the table name you want to check 13 How do I view the maximum number of sessions? Select * from v $ parameter where name like 'proc%'; SQL> SQL>
Show parameter processes name type value ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------- AQ_TM_Processes Integer 1 db_writer_processes INTEGER 1 JOB_QUE_PROCESS INTEGER 4 log_archive_max_proces INTEGER 1 Processes Integer 200 Here 200 users. Select * from v $ license; where the sessions_highwater record has reached the maximum number of sessions 14. How do I check the system locked transaction time? Select * from V $ locked_Object; 15. How to run Oracle in the way ArchiveLog.
Init.ora log_archive_start = True Restart Database 16. How to get any users in using the database Select UserName from V $ session; 17. How much is the maximum number of fields in the data table? How is the maximum number of columns in the table or view? The database's SID? SID? SID? SID? Select Name from V $ database can also be viewed directly. If it is logged in to the local database, you can only return 127.0.0.1, huh, huh 20. How to adjust the time when you adjust the database under UNIX? Su -root Date -u 08010000 21. How to capture the MEMO type field in Oracle Table is an empty data record? Select Remark from OMS_FlowRec Where Trim ('' from remark) is not null; 22. How to use the BBB table Update the information of the AAA table (associated field) Update AAA SET BNS_SNM = (SELECT BNS_SNM from bbb.dpt_no) where bbb.dpt_no is not null; 23. P4 computer installation method Change Symcjit.dll For sysmcjit.old 24. Which query Server is OPS? SELECT * FROM V $ Option; if Parallel Server = true has OPS Energy 25. What is the permission for each user? Select * from dba_sys_privs; 26. How to move the table ? table space ALTER tABLE TABLE_NAME mOVE TABLESPACE_NAME; 27. how to move the index table space ALTER iNDEX INDEX_NAME REBUILD tABLESPACE TABLESPACE_NAME;? 28. how to start the DBA STUDIO under LINUX, UNIX oEMAPP dBASTUDIO 29. Object status queries locks have V?? $ LOCK, V $ locked_object, v $ session, V $ SQLAREA, V $ process; Query lock table method: select s.SID session_id, s.username, decode (LMODE, 0, 'NONE', 1, 'NULL ', 2,' ROW-S (SS) ', 3,' Row-x (SX) ', 4,' Share ', 5,' S / Row-x (SSX) ', 6,' Exclusive ', TO_CHAR (LMODE)) MODE_HELD, DECODE (Request, 0, 'None', 1, 'NULL', 2, 'ROW-S (SS)', 3, 'ROW-X (SX)', 4, 'Share', 5, 'S / ROW-X (SSX) ', 6,' Exclusive ', TO_CHAR (REQUEST)) MODE_REQUESTED, O.OWNER ||'. '|| O.Object_name ||' ('|| O.Object_Type ||') '
, S.Type Lock_Type, L.ID1 LOCK_ID1, L.ID2 LOCK_ID2 from V $ LOCK L, SYS.DBA_OBJECTS O, V $ session s where l.sid = s.SID and L.ID1 = O.Object_ID; 30. Unlock? Alter System Kill Session 'SID, SERIR #'; 31. How to Modify Editor Under SQLPLUS? Define_Editor = "
SQL> Select * from user_tab_comments where comments is not null; 42. How do I see the disks of each table space? SQL> COL TABLESPACAT A20 SQL> SELECT B.FILE_ID File ID Number, B.Tablespace_name Table Space Name, B.bytes bytes (B.Bytes-Sum (NVL (A.Bytes, 0))) is used, SUM (NVL (A.bytes, 0)) Remaining Space, SUM (NVL (A.Bytes, 0)) / (B.bytes) * 100 Remaining Percentage from DBA_FREE_SPACE A, DBA_DATA_FILES B Where A.File_ID = B.File_ID Group By B.Tablespace_name, B.File_ID, B.BYTES ORDER BY B.FILE_ID 43. If the ORACLE is set to MTS or a dedicated mode? #dispatches = "(Service = tcp)" Plus is MTS, the comment is a dedicated mode, and the SID refers to your instance name. 44. How can I know the current SCN number? Select Max (KTuxescnw * Power (2, 32) ktuxescnb) from x $ ktuxe; 45. How do I don't support it in Oracle? 9i does not support, 9i starts with TimeStamp 9i can use Select SystemStamp from Dual;
46. How do I get a return in the string? SELECT 'WELCOME TO VIT' || CHR (10) || How does "in Chinese sorted? Before Oracle9i, Chinese is sorted according to binary coding. In Oracle9i, in accordance with Pinyin, Ministry, Sprinkler. Set NLS_SORT Value SCHINESE_RADICAL_M According to the first (first order), sort SCHINESE_STROKE_M Schinese_Stroke_m according to the stroke (first order), the first (second order) Sort Schinese_pinyin_m Follow the Pinyin 48. Oracle8i Object Name can be used in Chinese ? Can 49. How to change the SQL * PLUS boot option in WIN? SQL * Plus own option settings We can set in $ oracle_home / sqlplus / admin / glogin.sql. 50. How to modify the default date of the ORACEL database? ALTER session set nls_date_format = 'YYYYMMDDHH24MISS'; or can be plus a line of NLS_DATE_FORMAT = 'YYYYMMDDH24MISS' 51. How to put a small table in the KEEP pool? Alter Table XXX Storage? (Buffer_pool Keep); 52. How to check if a Patch? Check That ORAINVENTORY 53 is installed? How to make the SELECT statement automatically generate a serial number? Select Rownum, colom table; 54. How to know a table in pattern ? the tablespace 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 to quickly do the same original table and a backup table? CREATE TABLE NEW_TABLE AS (SELECT * OLD_TABLE); 55. How to modify ProCedure? Select Line, Trim (Text) t from User_Source where name = 'a' Order by line; 56. How to remove procedure is unlocked? ALter System Kill Session, kill the session, but you have to check that her session ID OR will re-change the name. 57. What is SQL Reference? It is a SQL manual, including grammar, functions, etc. Document Center for Oracle official website. 58. How to view the status of the database? UNIX PS-EF | GREP ORA Windows Whether the service is watched or not Database 59. How to modify the primary key of a table? 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, there is no damage to the original data file.
61. How do you check in Oracle in run? View V $ Sessions Table 62. How can I see how many TableSpace? Select * from DBA_TABLESPACES; 63. How do I modify the number of user connections to the Oracle database? Modify INITSID.ORA, increase the process, restart the database. 64. How do I find the last update time of a record? You can use the Logminer to view 65. How to read and write files in PL / SQL? The UTL_FILE package allows the user to read the operating system file via the PL / SQL. 66. How to put "&" in a record? INSERT INTO a VALUES (TRANSLATE ('at {}', 'at')); 67. How does EXP add Query parameter? Exp user / pass file = a.dmp tables (bsempms) query = '"where EMP_NO = /' s09394 / '/"; 68. About Oracle8i support Simplified and Traditional character sets? ZHS16GBK can support 69. What software is Data Guard? It is STANDBY's replacement product 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 '; files have been created. SQL> CREATE SPFILE = 'E: /ora9i/database/spfileeygle.ora' from pfile = 'e: /ora9i/admin/eygle/pfile/init.ora'; files have been created. 71. Application of kernel parameters? SHMMAX mean: This setting does not decide how much physical memory is used in Oracle database or operating system, only determines the number of memory available. This setting does not affect the kernel resources of the operating system. Setting method: 0.5 * Physical memory example: SHMINFO_SHMMAX = 10485760 shmmin meaning: Shared memory minimum size. Setting method: Usually set to 1. Example: set shmsys: shminfo_shmmin = 1: SHMMNI meaning: The maximum number of memory segments in the system. Example: set shmsys: shminfo_shmmni = 100 shmseg Meaning: The number of maximum shared memory segments per user process can be used. 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 Oracle's instances of all Oracle's instances of the processes to add 10. Example: Set Semsys: seminfo_semmni = 100 SemMns Emnity: 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_semmns = 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 permission? 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 to retrieve the CLOB field full-text search? Select * from a where dbms_lob.instr (AA, 'K', 1, 1)> 0; 76. How to display the current connection user? Show user 77. How to view the path placed by the data 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 the 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 do I change the initial defined Check range? 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? These file information is displayed by the following view: V $ Database, V $ DataFile, V $ logfile v $ controlfile v $ parameter; 81. Connect 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 script SQL files? 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;
88. The anti-function of chr () is? ASCII () Select char (65) from Dual; select ASCII ('a') from Dual; 89. String 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: /abd.txt; sql> select * from table; sql> spool off; 91. How to estimate SQL execution I / O number? SQL> set autotrace on; SQL> Select * from table; or 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)); changing the row, the change is small (unless it is empty) 93. How to Query your data? Select * from table_name where trunc = to_date ('2003-05-02', 'YYYY-MM-DD'); 94. How is the SQL statement inserts a full date? 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 to 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, which is used to introduce another file into spfile. It is 9i added and is the default parameter file. After binary format startup, only PFILE 101 will be connected to the PFILE 101. How to search for the first N record? Select * from Employee WHERE ROWNUM
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 the SQL statement to find the n value in a column? Select * from (Select T. *, Dense_rank () over (Order By Sal) where rank = n; 106. How to add 2 years to existing dates? (SELECT add_months (sysdate, 24) from Dual; 107. What does it mean for negative? IT IS "HARMLESS". 108. What does Connect String refers to what? Should be 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, create a new log.
110. Is the TABLESPACE not greater than 4g? No limit. 111. Return to the minimum integer value equal to Is N? SELECT CEIL (N) from Dual; 112. Return to equal to N, minimum integer value? Select floor (n) from dual; 113 . Returns the last day of the current month? SELECT LAST_DAY (SYSDATE) from Dual; 114. How to Divas Data Imports Different User Data? IMP System / Manager File = aa.dmp fromuser = User_old Touser = USER_NEW ROWS = Y indexes = Y; 115. How to ? Get the name of the primary key field in the database table SQL> SELECT * FROM user_constraints WHERE cONSTRAINT_TYPE = 'P' and table_name = 'tABLE_NAME';? 116. plus two result sets mutual function 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. Save two result sets mutual function SQL> SELECT * FROM BSEMPMS_OLD mINUS SELECT * FROM BSEMPMS_NEW;? 118 how to configure Sequence build sequence seq_custid create sequence seq_custid start 1 incrememt by 1; when built table:? create table cust {cust_id smallint not null, ...} when insert: insert into table cust values (seq_cust.nextval, ... The commonly used part of the different parts of the date 119>. Writing of the time point: select to_char (sysdate, 'yyyy') from Dual 120>. Writing of the month of the time point: select to_char (sysdate, 'mm') from dual; 121>. Writing at the time of time point: select to_char (sysdate, 'dd') from dual; 122>. Writing at the time of time point: select to_char (sysdate, 'hh24') from dual; 123>. Writing method in time point: select to_char (sysdate, 'mi') from Dual; 124>. Take time point秒 的 的: select to_char (sysdate, 'ss') from dual; 125>. Date of Take the time point: Select Trunc (sysdate) from dual; 126>. Take the time of the 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 cell string into a date or time form: SELECT TO_DATE (' 2003 / 08/01 ') from dual; 129>. Return to the parameters of the week of the week: 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 second between the time values specified in midnight and parameters Number of ways: select to_char (sysdate, 'sssss') from dual; 132>. Return parameters for the first few weeks of one year: select to_char (sysdate, 'ww') from Dual; Welcome everyone to discuss !
Virtual Field 133. Currval and NextVal Create Sequence Create Sequence Empseq ...; Select Empseq.CurrVal from Dual; Auto Insert Sequence Numerical Insert Into Emp Values (Empseq.NextVal, 'Lewis', 'Clerk', 7902, Sysdate , 1200, NULL, 20); 134. Rownum Press Select * from Emp Where Rownum <10; 135. RowId, Returning, ENAME FROM EMP Where dePTNO = 20; 136. N second conversion 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 "points" SS "second" '); dbms_output.put_line (re); end; 137. How to query the process of making a relatively large sort? Select B.Tablespace, B. Segfile #, B.SEGBLK #, B. Blocks, A.SID, A.SERIAL #, A.USERNAME, A. OSUSER, A.STATUS A. 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 the SQL statement of the process of making a relatively large sort? 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 repetition recording? 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 repeat 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 and execute View1.sql.
SQL> @ view1.sql; 142. ORA-01555 Snapshot Too OLD solution Adds the value of MineXtents, adds a high Optimal value. 143. The spatial space of the transaction requirements is not enough, and it is characterized by a table space (ORA-01560 error), the rollback segment extends to the value of the parameter maxExtents (ORA-01628) solution. Add a file to the split 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 the aa.sql file Create or Replace Procedure TestCCB (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 wait for the 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 return to the paradigm? 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 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 the file system? Select Substr (C.FILE #, 1, 2) "#", Substr (C.Name, 1, 30) "Name", C.Status, C.BYTES, D. Phyrds, D.Phyword 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, 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 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; 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 should I monitor the hit rate of the CCD 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 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. monitoring SGA redo log buffer hit ratio 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. monitoring Sort and hard disk sorting ratio, it is best to make it less than .10, add sort_area_size select name, value from v $ sortstat where name in ('sorts "', 'sorts (DISK)'); 156. How to monitor current database Who is running what SQL statement?
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 Dictionary Buffers? 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 except for the former, this ratio is less than 1%, close to 0% is good.
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; this value is greater than 0.5 When parameter needs to increase Select SUM (Wait) / SUM (Totalq) "Dispatcher Waits" from v $ queue where type = 'dispatcher'; select count (*) from v $ dispatcher; select servers_highwater from v $ mts; servers_highwater close When MTS_MAX_SERVERS, the parameter needs to increase 159. How do I know the current user ID number? SQL> Show user; or sql> select user from dual; 160. How to view the degradation level? Select segment_name table_name, count (*) Extents from DBA_SEGMENTS WHERE OWNER NOT IN ('sys', 'system') Group by segment_name haVing count (*) = (select max) from dba_segments group by segment_name); 162. How to know the store in the table space ? case 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; how 163. know that the stored situation index table space select segment_name? , count (*) from DBA_EXTENTS Where segment_type = 'Index' and Owner = '& Owner' Group by segment_name; 164, how to know how many users using CPU session? 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 $ sessions 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;
