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 used by Oracle masters.
1. Initial password after the Oracle installation? InterNal / Oracle Sys / Change_on_install system / manager scott / tiger sysman / oem_temp
2. Oracle9ias web cache's initial default user and password? 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 do Oracle 9i creates a database? DBCA
6. What is the bare equipment in Oracle? Bare equipment is the storage space that bypasses the file system directly access.
7. How can Oracle distinguish between 64-bit / 32bit versions? ? ? $ 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 is not, it has changed to SQLPlus / Nolog to archive log type with SQLPLUS
9. How do I distinguish which machine login from Oracle? Select Machine, Terminal from V $ session;
10. Using Shi language query fields? 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 * ALL_TAB_COLUMNS where TABLE_NAME = '??'
11. How to get a trigger, process, and function creation script? DESC User_Source User_Triggers
12. How to calculate the size of a space occupied? 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 to view the maximum number of sessions? Select * from v $ parameter where name like 'proc%'; SQL> SQL> show parameter processes
Name Type Value ---------------------------------------------- -------------------------- AQ_TM_PROCESS INTEGER 1 DB_WRITER_PROCESS INTEGER 1 JOB_QUEUE_PROCESS INTEGER 4 log_archive_max_processes INTEGER 1 Processes Integer 200
Here is 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 ArchiveLog. Init.ora log_archive_start = True Restart Database
16. How do you get any users in using the database Select UserName from V $ session;
17. What is the maximum number of fields in the data sheet? The maximum number of columns in the table or view is 1000
18. How to find the database's SID? SID? SELECT NAME FROM V $ Database; you can also view the init.ora file directly
19. How to view this machine IP address on the Oracle server? Select sys_context ('useerenv', 'ip_address') from dual; if it is logged to the local database, only 127.0.0.1, huh, huh
20. How do I adjust the time when IIIX? Su -root Date -u 08010000
21. How to grab the MEMO type field in Oracle Table as an empty data record? Select Remark from OMS_FlowRec Where Trim ('' from remark) is not null;
22. How to update the information of the AAA table (associated field) Update AAA SET BNS_SNM = (SELECT BNS_SNM from BBB WHERE AAA.DPT_NO = BBB.DPT_NO) Where bbb.dpt_no is not null;
23. P4 Computer installation method change symcjit.dll to sysmcjit.old
24. Which query server is OPS? SELECT * FROM V $ OPTION; if Parallel Server = true has OPS
25. What is the permission for each user? SELECT * from DBA_SYS_PRIVS;
26. How to move the table space? Alter Table Table_name Move TableSpace_name;
27. How to use index mobile tablespace? Alter index index_name rebuild tablespace tablespace_name; 28. How to activate DBA Studio? Oemapp DBASTUDIO in Linux, Unix?
29. The subject of query lock is available? 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', '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 || '(' || 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. How to unlock? Alter System Kill Session 'SID, Serir #';
31. How to modify the editor under SQLPLUS?
Define _Editor = "
32. Oracle generates a random function? DBMS_Random.random
33. Query Disk Competition Commands under Linux? SAR -D
33. Query the CPU competition under Linux? SAR -R
34. Query the current user object? Select * from user_Objects;
Select * from DBA_SEGMENTS;
35. How to get an error message? SELECT * from user_erro
36. How to get link conditions? Select * from DBA_DB_LINKS;
37. View database characteristics? Select * from nls_database_parameters; select * from v $ nls_parameters;
38. Query table space information? SELECT * from DBA_DATA_FILES;
39. ORACLE INTERAL user wants a password? Modify SQLNET.ORA SQLNET.AUThentication_Services = (NTS)
40. What is the solution to Java.exe? Generally, it is 8 or 9 that change OracleraHomexihttpserver to hand-activated.
41. How to give a list, column incorporation? SQL> Comment on Table Table IS 'Table Note'; comments have been created. SQL> Comment on column table. Column is 'column annotation'; comment has been created. SQL> Select * from user_tab_comments where comments is not null; 42. How do I see how each table space takes up? 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 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 of the system? SELECT MAX (KTuxescnw * Power (2, 32) KTuxescnb) from x $ KTUXE;
45. How do I don't support it in Oracle? 9i, 9i begins with TimeStamp. 9i can use Select SystemStamp from Dual;
46. How do I get a return in the string? Select 'Welcome to Visit' || CHR (10) || 'www.9cbs.net' from DUAL
47. How is the 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), sorting SCHINESE_STROKE_M Schinese_Stroke_m according to the stroke (first order), the first (second order) Sort Schinese_pinyin_m Sort by Pinyin
48. Can the object name in Oracle8i use 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 = 'yyyyymmddhh24miss'; or may be plus a line of NLS_DATE_FORMAT = 'YYYYMMDDHH24MISS' in INIT.ORA
51. How do I put a small table in the Keep pool? Alter Table XXX Storage (Buffer_Pool Keep);
52. How do I check if a patch? Check That ORAINVENTORY is installed
53. How to make the SELECT statement automatically generate a serial number? Select Rownum, Col from Table;
54. How do I know tablespace pants in a data table where the 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 ruffle Can you make a backup table as the original table? Create Table new_table as (SELECT * from Old_Table);
55. How to modify Procedure? Select Line, Trim (Text) t from User_Source Where Name = 'A' Order by line under SQLPLUS
56. How to remove procedure is accidentally locked? ALTER system kill session, kill the session, but you have to check that her session ID OR will re-change the name.
57. Is SQL Reference be something? It is a SQL manual, including grammar, functions, etc., the document center of Oracle official website has downloads.
58. How to view the status of the database? Unix PS-EF | GREP ORA Windows See if the service is getting up if you can 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 ....
61. How do you check in Oracle in run? View v $ sessions table
62. How do you see how many TableSpace? Select * from DBA_TABLESPACES in the database;
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 to detect the last update time of a record? You can use the Logminer to see
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 {&} T', '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 be supported
69. What software is Data Guard? Is the replacement of Standby
70. How to create a spfile?
SQL> Connect / As Sysdba SQL> SELECT * FROM V $ VERSION; SQL> CREATE PFILE from Pfile; 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? SQL> conn sys / change_on_install sql> select * from v_ $ pWFILE_USERS;
73. How to back up one or more tables alone? 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 do I have a full-text search for a CLOB field? Select * from a where dbms_lob.instr (a.a, '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 existing rollback segments and its 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 initially defined by a field? 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? Display these files from the following view: V $ DATABASE, V $ DATAFILE, V $ logfile v $ ControlFile V $ Parameter;
81. Inner connection 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 a script SQL file? 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 do I test the time used by the SQL statement? SQL> Set Timing On; SQL> Select * from TableName;
88. The reverse function of chr () is? ASCII () SELECT Char (65) from Dual; Select ASCII ('a') from dual
89. Connection of the 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: /abcd.txt; sql> select * from table; sql> spool off;
91. How to estimate the I / O number of SQL execution? 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 big line, change, unless it is empty)
93. How do I queries a day of data? Select * from table_name where trunc = to_date ('2003-05-02', 'YYYY-MM-DD'); 94. How do SQL statements insert 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 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 Is there? Pfile is an Oracle traditional initialization parameter file, text format. Ifile is similar to include in C language, it is used to introduce another file into spfile. It is 9i new and is the default parameter file. After binary format startup, only PFile should
101. How to search for the first N record? Select * from Employee WHERE ROWNUM 102. How do I know how much concurrent users 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 do I use the SQL statement to find the nth value in a column? Select * from (Select T. *, Dense_Rank () over (Order By Sal) where rank = n; 106. How do I add 2 years when giving an existing date? (SELECT Add_MontHS (Sysdate, 24) from Dual; 107. USED_UBLK is a negative value indicating what mean? It is "harmless". 108. What does Connect string means? 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? There is no limit. 111. Return the minimum integer value equal to N? SELECT CEIL (N) from DUAL; 112. Returns less than or equal to N. SELECT FLOOR (N) from Dual; 113. Returns the last day of the current month? SELECT LAST_DAY (SYSDATE) from DUAL; 114. How to Data Import in Different User Data? 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 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 SELECT * FROM BSEMPMS_NEW; 117. Function 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; CREATE TABLE CUST {Cust_ID Smallint Not Null, ...} Insert Insert Into Table Cust Values (seq_cust.nextval, ...) The commonly used part of the date 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>. Write actions at the time of time: select to_char (sysdate, 'dd') from dual; 122>. Writing at the time of time: select to_char (sysdate, 'hh24') from dual; 123>. Writing method for time point: select to_char (sysdate, 'mi') from dual; 124>. Write the secondary second: select to_char (sysdate, 'ss') from dual; 125>. Date of taking the time point: SELECT TRUNC (SYSDATE) from DUAL 126>. Writing time of time point: select to_char (sysdate, 'hh24: mi: ss') from dual; 127>. Date, time form becomes a characteristic 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>. Type of Weekly Writings for the parameters: 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>. Return to the first few weeks of the year: select to_char (sysdate, 'ww') from dual; virtual field 133. CURRVAL and NEXTVAL create sequences for tables 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 sequentially sets the serial number Select * from EMP WHERE ROWNUM <10; 135. Return of Return Return SELECT ROWID, ENAME FROM EMP Where DEPTNO = 20; 136. Convert N-second 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); 137. How do I query to do a big sorting process? 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 do I query the SQL statement of the relatively large sorted 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 do I find repeated records? Select * from table_name where rowid! = (Select max (rowid) from table_name.col1 = d.col1 and table_name.col2 = d.col2); 140. How to delete a repeated record? 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, increasing the size of the zone, set 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 Testcb (i in number) as begin dbms_output.put_line ('input parameter is' || to_CHAR (i)); 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 cases? 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 the contention of the segment? 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 table space? 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 to 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 do I 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 hit rate of the SGA medium-red log cache, should be less than 1% SELECT NAME, GETS, MISSES, IMMEDIATE_GETS, IMMEDIATE_MISS, DECODE (Gets, 0, 0, Misses / Gets * 100) Ratio1, Decode (IMMEDIATE_GETS IMMEDIATE_MISSESSESSES , 0, 0, IMMEDIATE_MISSES / (IMMEDIATE_GETS IMMEDIATE_MISS * 100) Ratio2 from V $ latch where name in ('redo allocation', 'redo copy "); 155. Monitor memory and hard disk sorting ratio, it is best to make it less than .10, add sort_area_size select name, value from v $ sysstat where name in ('sorts ",' sorts (disk)); 156. How do I monitor the current database who is running 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 do I 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 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; When the value is greater than 0.5, the parameters need 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 is close to MTS_MAX_SERVERS, parameters need to increase 159. How do I know the ID number of the current user? SQL> Show user; or sql> select user from dual; 160. How to view the debris 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 (count (* )) fROM dba_segments GROUP BY segment_name);.? 162 how do you know memory situation 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 do I know the storage situation in the table space? Select segment_name, count (*) from dba_extents where segment_type = 'index' and owner = '& oowner' group by segment_name; 164. How do I know how many users using 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 VALUE / 60/100 VALUE FROM VALUE / 60/100 VALUE FROM V $ SESSION A, V $ Process B, V $ SESSSTAT 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 to know the listener parameter file? Take 8i as an example $ Oracle_Home / Network / Admin / Listener.ora 167. How do I know TNS connection file? Take 8i $ Oracle_home / network / admin / tnsnames.ora 168. How do I know SQL * NET environment file? Take 8i as an example $ Oracle_Home / Network / Admin / SQLNET.ORA 169. How do I know warning log files? Take 8i $ 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 I know the establishment of a data dictionary view? Take 8i as an example $ oracle_home / rdbms / admin / catalog.sql 172. How do I know the establishment of an audit data dictionary view? Take 8i as an example $ oracle_home / rdbms / admin / cataudit.sql 173. How do I know to build a snapshot data dictionary view? Take 8i as an example $ oracle_home / rdbms / admin / catsnap.sql
