Oracle classic Q & A list [common]

Oracle classic Q & A list [common]

Oracle classic Q & A list [common] Directories 01. What is the initial password after oracle is installed? 02. What is the initial Default User and password of Oracle9iAS Web Cache? 03. How to Create a database in Oracle 8.0.5? 04. How to Create a database in Oracle 8.1.7? 05. How to Create a database in Oracle 9i? 06. What does bare device in Oracle mean? 07. How Does Oracle differentiate 64-bit/32bit versions ??? 08. What does svrmgr mean? 09. How can I tell from which machine a user logs on to Oracle? 10. What statements are used to query fields? 11. How can I obtain the script for creating triggers, processes, and functions? 12. How do I calculate the space occupied by a table? 13. How do I view the maximum number of sessions? 14. How can I view the transaction time locked by the system? 15. How to run Oracle in archivelog mode. 16. How to obtain which users are using the database? 17. What is the maximum number of fields in the data table? 18. How can I find the database Sid? 19. How can I view the local IP address through sqlplus on the Oracle server? 20. How can I adjust the database time in UNIX? 21. In Oracle table, how does one capture data records with null memo fields? 22. How to Use BBB table data to update AAA table data (associated fields) 23. P4 Computer Installation Method 24. Why query server? ops? 25. How can I query the permissions of each user? 26. How do I move a table tablespace? 27. How can I move an index to a tablespace? 28. In Linux and UNIX, how does one activate DBA studio? 29. Which of the following objects can query the lock status? 30. How to unlock it? 31. How can I modify the editor under sqlplus? 32. What are the random functions generated by Oracle? 33. What is the command for querying disk competition conditions in Linux? 33. What is the command for querying CPU competition conditions in Linux? 34. query the current user object? 35. How to Get error information? 36. How do I obtain the link status? 37. Check the database character status? 38. query table space information? 39. Do Oracle interal users need a password? 40. How can I solve the problem of Java. EXE? 41. How do I add comments to tables and columns? 42. How can I view the disk usage of each tablespace? 43. If Oracle is set to MTS or dedicated mode? 44. How can I know the current SCN Number of the system? 45. How can I retrieve milliseconds in Oracle? 46. How do I add a carriage return to the string? 47. How is Chinese sorted? 48. Can the object name in Oracle8i be in Chinese? 49. How to change the SQL * Plus startup option in win? 50. How do I modify the default date of the replicel database? 51. How to put small tables into the keep pool? 52. How do I check whether a patch is installed? 53. How can I enable the SELECT statement to automatically generate sequence numbers for the query results? 54. How do I know the tablespace of a table in data pants? 55. How can I quickly create a backup table that is the same as the original table? 55. How can I modify procedure under sqlplus? 56. How to unlock procedure from accidental locking? 57. What is SQL reference? 58. How can I view the database status? 59. How can I modify the primary key of a table? 60. How to change the data file size? 61. How to check which programs are running in Oracle? 62. How many tablespaces are there in the database? 63. How do I modify the number of user connections in the Oracle database? 64. How can I find the last update time of a record? 65. how to read and write files in PL/SQL? 66. How to put "&" into a record? 67. How does exp add query parameters? 68. Question about Oracle8i's support for simplified and traditional character sets? 69. What is data guard? 70. How to Create a spfile? 71. Kernel Parameter application? 72. How can I check which users have sysdba and sysoper permissions? 73. How to back up one or more tables separately? 74. How to back up one or more users separately? 75. How can I perform full-text search on the clob field? 76. How do I display the current connected user? 77. How do I view the data file placement path? 78. How can I view the existing rollback segments and their statuses? 79. How do I change the Check range of a field's initial definition? 80. What are common Oracle system files? 81. Inner join? 82. How do I connect externally? 83. How to execute a script SQL file? 84. How can I quickly clear a large table? 85. How many database instances are there? 86. How to query the number of tables in the database? 87. How does one test the time used for SQL statement execution? 88. What is the inverse function of CHR? 89. String connection 90. How to export the select result to a text file? 91. How to estimate the number of I/O executed by SQL? 92. How can I change the field size in sqlplus? 93. How do I query data of a certain day? 94. How do I insert the SQL statement into the year-round date? 95. If you want to modify the table name? 96. How to obtain the return status value of the command? 97. How do I know the user's permissions? 98. What is the difference between the Oracle9i downloaded from the internet and the standard edition sold on the market? 99. How can I determine whether the database is running in archive mode or non-archive mode? 100. SQL> what is the difference between startup pfile and ifile and spfiled? ========================================================== ========================================================== ====================================== 1. What is the initial password after oracle is installed? Internal/Oracle Sys/change_on_install System/Manager Scott/Tiger Sysman/oem_temp 2. What is the initial Default User and password of Oracle9iAS Web Cache? Administrator/Administrator 3. How to Create a database in Oracle 8.0.5? Use orainst. If there is a motif interface, you can use orainst/m 4. How to Create a database in Oracle 8.1.7? Dbassist 5. How to Create a database in Oracle 9i? Dbca 6. What does bare device in Oracle mean? A bare device is a storage space that is directly accessed by bypassing the file system. 7. How Does Oracle differentiate 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: Oracle9i Enterprise Edition Release 9.0.1.0.0-Production With the partitioning Option 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. No under 9i. I already used sqlplus. Sqlplus/nolog Changed to archive log type 9. How can I tell from which machine a user logs on to Oracle? Select machine, terminal from V $ session; 10. What statements are used to query fields? Desc table_name: You can query the table structure. Select field_name,... from... can query the field value Select * From all_tables where table_name like '%' Select * From all_tab_columns where table_name = '?? ' 11. How can I obtain the script for creating triggers, processes, and functions? Desc user_source User_triggers 12. How do I calculate 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_queue_processes integer 4 Log_archive_max_processes integer 1 Processes integer 200 There are 200 users. Select * from V $ license; Sessions_highwater records the maximum number of sessions that have been reached. 14. How can I view the transaction time locked by the system? Select * from V $ locked_object; 15. How to run Oracle in archivelog mode. Init. ora Log_archive_start = true Restart Database 16. How to obtain which users are using the database? Select username from V $ session; 17. What is the maximum number of fields in the data table? The maximum number of columns in a table or view is 1000. 18. How can I find the database Sid? Select name from V $ database; You can also directly view the init. ora file. 19. How can I view the local IP address through sqlplus on the Oracle server? Select sys_context ('userenv', 'IP _ address') from dual; If you log on to the local database, you can only return 127.0.0.1. 20. How can I adjust the database time in UNIX? Su-Root Date-u 08010000 21. In Oracle table, how does one capture data records with null memo fields? Select remark from oms_flowrec where trim (''from remark) is not null; 22. How to Use BBB table data to update AAA table data (associated fields) 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. Why query server? ops? Select * from V $ option; If Parallel Server = true, OPs can 25. How can I query the permissions of each user? Select * From dba_sys_privs; 26. How do I move a table tablespace? Alter table table_name move tablespace_name; 27. How can I move an index to a tablespace? Alter index index_name rebuild tablespace tablespace_name; 28. In Linux and UNIX, how does one activate DBA studio? Oemapp dbastudio 29. Which of the following objects can query the lock status? V $ lock, V $ locked_object, V $ session, V $ sqlarea, V $ process; To query a locked table: 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, 'Clusive ', 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, 'clusive ', 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. How to unlock it? Alter system kill session 'sid, serir #'; 31. How can I modify the editor under sqlplus? Define _ editor = "<full path of the editor>" -- double quotation marks must be added. To define a new editor, you can also write this in $ ORACLE_HOME/sqlplus/admin/glogin. SQL to make it permanently valid. 32. What are the random functions generated by Oracle? Dbms_random.random 33. What is the command for querying disk competition conditions in Linux? Sar-d 33. What is the command for querying CPU competition conditions in Linux? Sar-R 34. query the current user object? Select * From user_objects; Select * From dba_segments; 35. How to Get error information? Select * From user_errors; 36. How do I obtain the link status? Select * From dba_db_links; 37. Check the database character status? Select * From nls_database_parameters; Select * from V $ nls_parameters; 38. query table space information? Select * From dba_data_files; 39. Do Oracle interal users need a password? Modify sqlnet. ora Sqlnet. authentication_services = (CNT) 40. How can I solve the problem of Java. EXE? Generally, you can change oracleorahomexihttpserver to manual activation. X is 8 or 9 41. How do I add comments to tables and columns? SQL> comment on table is 'table annotation '; Note created. SQL> comment on column table. The column is 'column annotation '; Note created. SQL> select * From user_tab_comments where comments is not null; 42. How can I view the disk usage of each tablespace? SQL> Col tablespace format A20 SQL> select B. ID of the file_id file, B. tablespace_name: name of the tablespace, B. bytes, (B. bytes-sum (nvl (A. bytes, 0) already in use, Sum (nvl (A. bytes, 0) remaining space, Sum (nvl (A. bytes, 0)/(B. bytes) * 100 percentage remaining 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 Oracle is set to MTS or dedicated mode? # Dispatchers = "(Protocol = TCP) (Service = sidxdb )" When MTS is added, the Comment mode is dedicated, And the SID indicates 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 can I retrieve milliseconds in Oracle? Not Supported before 9i, 9i starts to have timestamp. 9i can use select distinct imestamp from dual; When applying Oracle, you may encounter many seemingly difficult problems. Especially for new users, today I will summarize it and release it to everyone. I hope it will be helpful to everyone! We will discuss with you. Same progress! For Oracle experts, you don't have to look at it. 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/'/" then /'/"﹔ 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. It only determines the maximum amount of memory 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 processes value in init. ora of all Oracle instances on the 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 as follows: total number of processes in initsid. ora of each Oracle instance (excluding the maximum processes parameter) + 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', 1, 1)> 0; 76. How do I 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; When applying Oracle, you may encounter many seemingly difficult problems. Especially for new users, today I will summarize it and release it to everyone. I hope it will be helpful to everyone! We will discuss with you. Same progress! For Oracle experts, you don't have to look at it. 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 the website cannot be used for commercial purposes, or else 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.