Oracle Common Fool question 1000 question (bis)

Oracle 46. 　　How do I add a carriage return in a string? Select ' Welcome to visit ' | | Chr (10) | | Www.CSDN.NET ' from dual; 　　47. How are the Chinese sorted? 　　Before Oracle9i, Chinese is sorted according to binary encoding. In oracle9i, according to pinyin, radical, stroke sorting function. Set the Nls_sort value Schinese_radical_m according to the radical (first order), stroke (second order) schinese_stroke_m sorted by stroke (first order), radical (second order) schinese_pinyin_m according to Pinyin　Preface 48. 　　Can the object name in Oracle8i be in Chinese? Can be 49. How do I change the Sql*plus startup option in Win? Sql*plus its own option settings we can set in $oracle_home/sqlplus/admin/glogin.sql. 　 50. How do I modify the default date for the Oracel database? 　 Alter session set nls_date_format= ' Yyyymmddhh24miss '; OR You can add a row to the Init.ora nls_date_format= ' Yyyymmddhh24miss ' 51. 　 How do I put a small table in a keep pool? ALTER TABLE XXX storage (buffer_pool keep); 　　52. How do I check if a patch is installed? Check that Orainventory 53. How do I make a SELECT statement automatically generate ordinal numbers for query results? Select Rownum,col from table; 54. How do I know the tablespace of a table in the data pants? Select Tablespace_name from User_tables where table_name= ' TEST '; There is a field in the select * from User_tables tablespace_name, (Oracle); SELECT * from Dba_segments where ...; 　　55. How can you quickly make a backup table like the original table? CREATE TABLE new_table as (SELECT * from old_table); 55. How to modify the proc under SqlplusEdure? Select Line,trim (text) t from User_source where name = ' an ' order by line; 　 56. How to remove procedure is accidentally locked? Alter system kill session, the session will be killed, but you have to find out her session ID or the process to change the name on it. 　 What is a SQL reference? is a manual of SQL, including syntax, functions, and so on, which is downloaded from the Documentation Center of the official Oracle website. 　 58. How do I view the status of the database? Unix under Ps-ef | grep ora windows to see if the service can be connected to the 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. Change the size of the data file? With ALTER DATABASE .... DataFile ...; Manually changing the size of the data file, there is no damage to the original data file. 　 61. How do I see what programs are running in Oracle? View V$sessions Table 62. How can I see how many tablespace there are in the database? SELECT * from Dba_tablespaces; 63. How do I modify the number of user connections for an Oracle database? Modify the Initsid.ora to enlarge the process and restart the database. 64. 　How do I find out when a record was last updated? You can use Logminer to see 65. How do I read and write files in Pl/sql? The Utl_file package allows users to read and write operating system files through Pl/sql. 66. How do I put "&" in a record? Insert into a values (translate (' at{&}t ', ' at{} ', ' at ')); How does EXP add query parameters? EXP user/pass file=a.dmp TABLES (BSEMPMS) query= ' WHERE emp_no=\ ' s09394\ ' \ ';68. 　 About oracle8i support for simplified and traditional character set issues? Zhs16gbkCan be 69. What software is Data guard? 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 '; The file has been created. sql> CREATE spfile= ' E:\ora9i\database pfileeygle. ORA ' from pfile= ' E:\ora9i\admin\eygle\pfile\init.ora '; The file has been created. 71. Application of kernel parameters? Shmmax meaning: This setting does not determine how much physical memory the Oracle database or operating system uses, but only determines the maximum amount of memory that can be used. 　　This setting also does not affect the kernel resources of the operating system. 　　Set method: 0.5* Physical Memory Example: set shmsys:shminfo_shmmax=10485760 shmmin meaning: The minimum size of shared memory. 　　Set method: Generally set to be 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 can be used by each user process. 　　Example: Set shmsys:shminfo_shmseg=20:semmni meaning: The maximum number of semaphore identifierer in the system. 　　Set method: The value of this variable is set to the Init.ora of all Oracle instances on this system and the value of that processes plus 10. 　　Example: Set semsys:seminfo_semmni=100 semmns Meaning: The maximum number of emaphores in the system. Set method: This value can be computed in the following way: the sum of the values of the processes inside the Initsid.ora of each Oracle instance (minus the maximum processes parameter) + The largest processesx2+10xoracle instance of theNumber. 　　Example: Set semsys:seminfo_semmns=200 SEMMSL: Meaning: The maximum number of semaphore in a set. 　　Set up method: Sets the value of the largest processes in the Initsid.ora of all Oracle instances 10+. Example: Set semsys:seminfo_semmsl=-200 72. How do I see which users have Sysdba, sysoper permissions? Sql>conn sys/change_on_install Sql>select * from V_$pwfile_users; 　 73. How do I back up one or more tables separately? Exp User/Password tables= (table 1,..., table 2) 74. 　How do I back up one or more users separately? Exp System/manager owner= (user 1, user 2,..., user n) file= export file 75. How do I retrieve Full-text for a CLOB field? SELECT * from A WHERE dbms_lob.instr (a.a, ' K ', 1,1) >0; 　 76. How do I display the current connection user? Show USER 77. How do I see the path where the data file is placed? Col file_name format A50 sql> Select Tablespace_name,file_id,bytes/1024/1024,file_name from Dba_data_files order by fi le_id; 78. How do I view existing rollback segments and their status? Sql> Col segment Format A30 sql> SELECT segment_name,owner,tablespace_name,segment_id,file_id,status from Dba_ Rollback_segs 79. How do I change the check scope 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 (); What are the common system files for Oracle? These file information is displayed through 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 outside? 　 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 do I execute this SQL file? sql>@ $PATH/filename.sql; 84. How to quickly clear a large table? Sql>truncate table table_name; 　85. How many statistics are available? Sql>select * from V$instance; 86. How many tables are there in the data library? Sql>select * from All_tables; 87. How do I test the time that SQL sentence is performed? 　Sql>set timing on; Sql>select * FROM TableName;