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 Sort by the first (first order), the stroke (second order)
Schinese_stroke_m Sort by the stroke (first order), the first (second order)
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 = 'YYYYMMDDH24MISS'
Oral
Can plus a line in Init.ora
NLS_DATE_FORMAT = 'YYYYMMDDHH24MISS'
51. How do I put the small table in the KeeP pool?
ALTER TABLE XXX Storage (Buffer_Pool Keep);
52. How do I check if a Patch is installed?
Check That ORAINVENTORY
53. How to make the SELECT statement automatically generate the serial number to the query result?
Select Rownum, Col from Table;
54. How do I know the tableSpace in a table in pattern?
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 can I make a backup table with the original table?
CREATE TABLE NEW_TABLE AS (SELECT * from Old_Table);
55. How do I modify Procedure under SQLPLUS?
SELECT LINE, TRIM (TEX) T from user_source where name = 'a' order by line;
56. How to remove procedure to lock it unexpected?
ALTER SYSTEM KILL Session, kill the session, but you have to check her session ID first
oral
Retreat the process again.
57. What is SQL Reference?
It is a SQL manual, including grammar, functions, etc., the document center of Oracle official website has downloads.
58. How do I view the status of the database?
Under UNIX
PS-EF | GREP ORA
Under Windows
Whether the service is getting up
Can I connect to a 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 ....;
Handmade 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 tablespaces do you have?
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?
Can be viewed with Logminer
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 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. Application of kernel parameters?
SHMMAX
Meaning: This setting does not decide how much physical memory exactly the 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: set shmsys: shminfo_shmmax = 10485760
shmmin
Meaning: Share the minimum size of memory.
Setting method: Usually set to 1.
Example: set shmsys: shminfo_shmmin = 1:
Shmmni
Meaning: The maximum number of total memory segments in the system.
Example: set shmsys: shminfo_shmmni = 100
shmseg
Meaning: The number of 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
Meaning: 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_semmp = 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 do I display the current connection user?
SHOW User
77. How do I view the path to 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 check the existing rollback segment 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. Connect Inner Join?
Select a. * From bsempms A, BSDPTMS b where A.dpt_no = B.DPT_NO;
82. How to get an external connection?
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;
