Oracle common problem 1000 questions complete set (1)

xiaoxiao2021-03-06  152

1. The initial password after the Oracle installation is completed?

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. How to create a database in Oracle 8.0.5?

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 naked device in Oracle?

Naked equipment is the storage space that bypass direct access to the file system

7. How can Oracle distinguish between 64-bit / 32bit versions?

$ SQLPLUS '/ As Sysdba'

SQL * Plus: Release 9.0.1.0.0 - Production on Monjul 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 PartInog 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 9i is not, have been changed to SQLPLUS

SQLPlus / Nolog becomes archived

9. How do I tell which machine is logged in Oracle?

Select Machine, Terminal from v $ session;

10. What statement is used inquate?

DESC TABLE_NAME can look up 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 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_ROEMPTY_BLOCKS, LAST_ANALYZED WS, Blocks * AAA / 1024/1024 "SIZE M",

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_Processes INTEGER 1

Job_Queue_Processes INTEGER 4

LOG_ARCHIVE_MAX_PROCESSSS 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 the way ArchiveLog?

Init.ora

LOG_ARCHIVE_START = TRUE

Restart Database

16. How do you get what 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 the table or view is 1000

18. How to find the SID of the database?

Select Name from V $ Database; you can also directly view the init.ora file

19. How do I view this machine IP address via SQLPLUS on an Oracle server?

Select Sys_Context ('Userenv', 'IP_Address') from Dual;

If it is logged in to the local database, you can only return 127.0.0.1

20. How do I adjust the time for the database under UNIX?

Su -root

Date -u 08010000

21. How to grab the MEMO type bar as an empty data record in Oracle Table?

Select Remark From OMS_FlowRec Where Trim ('' from remark) is not null;

22. How to use the BBB table to update the data of the AAA table (associated field)

Update aaa set bns_snm = (Select bwhere bbb.dpt_no is not null; ns_snm from bbb where aaa.dpt_no = bbb.dpt_no)

23. P4 computer installation method

Change Symcjit.dll to Sysmcjit.OLD

24. How to query Server is OPS?

Select * from V $ Option;

If Parallel Server = True has OPS

25. How do I query each user's permissions?

Select * from DBA_SYS_PRIVS;

26. How do I move the table space?

Alter Table Table_name Move TableSpace_name;

27. How do I use the index mobile watch space?

Alter Index Index_name rebuild tablespace tablespace_name; 28. How to start DBA Studio under Linux, Unix?

Oemapp DBASTUDIO

29. Is the subject of query lock?

V $ LOCK, V $ locked_Object, V $ Session, V $ SQLAREA, V $ Process;

The method of querying the lock table:

Select S.SID Session'Row-S (SS) ', 3,' ROW-X To_CHAR (LMODE)) MODE_HEL3, 'ROW-X (SX)', 4, 'Shachar (Request)) MODE_REQUOBJECT_TYPE ||') ' , S.TypeSys.dba_Objects O, V $ SES_ID, S.USERNAME, DECODE (LMOD (SX) ', 4,' Share ', 5,' S / Rowd, Decode (Request, 0, 'None're', 5 , 'S / row-x (ssx)', 6, ested, o.owner || '.' || O.Objeclock_type, l.id1 lock_id1, sion s where l.sid = S.SID AE, 0, ' None ', 1,' NULL ', 2, -X (SSX)', 6, 'Exclusive', 1, 'Null', 2, 'Row-S (SS)', 'Exclusive', TO_T_NAME || ' ('|| OLID2 LOCK_ID2 from V $ LOCK L, ND 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 = "" - must be added to define a new editor, or write this in $ oracle_home / sqlplus / admin / glogin.sql to make it permanently valid.

32. Oracle generates a function of random number?

DBMS_Random.random

33. Query disk competition commands under Linux?

SAR -D

33. Query the CPU competitive situation command 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 *.

36. How to get a link situation?

SELECT * FROM DBA_DB_LINKS;

37. View the status of database characters?

Select * from NLS_DATABASE_PARAMETERS;

Select * from V $ nls_parameters; 38. Query Table Space Information?

Select * from DBA_DATA_FILES;

39. Oracle's intend user wants a password?

Modify SQLNET.ORA

SQLNET.AUTHENTICATION_SERVICES = (NTS)

40. What is the solution to Java.exe?

Generally, OracleraHomexihttpserver is changed to handle, X is 8 or 9

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';

Comments have been created.

SQL> SELECT * from user_tab_comments where comments is not null;

42. How do I check the condition 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)))))

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 = "(protocol = tcp) (Service = SIDXD"

Plus is the 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 WOM X $ KTUXE;

45. How do I get milliseconds in Oracle?

No support before 9i, 9i starts with TimeStamp.

9i can use Select SystimeStamp from Dual;

46. ​​How do I get a return in the string?

Select 'Welcome to Visit' || CHR (10) || 'www.csdn.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 the 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 setnls_date_format = 'YYYYMMDDHH24MISS'

Or can add a line of NLS_DATE_FORMAT = 'YYYYMMDDH24MISS' in Init.ra

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 (Text) T fr; om user_source where name = 'a' Order by line

56. How to remove procedure to lock it unexpected?

ALTER SYSTEM KILSESSION ID L Session, kill the session, but you have to check her first.

Or re-change the name of the process.

57. What is SQL Reference?

Is a SQL manual, including grammar, functions, etc., Document Center of Oracle official website provides downloads.

58. How do I view the status of the database?

Under UNIX

PS-EF | GREP ORA

Under Windows

See if the service is getting up; can you 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 {}', 'at'); 67. How to add Query parameters?

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?

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 whether the number of memory available can be used. This setting is also an ACLE database or how much physical memory is used in the operating system, only decide that it 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 the value of the processes plus 10. The most in init.ora's instance of all Oracle on this system

Example: set Semsys: seminfo_semmni = 100

Semmns

Meaning: The maximum number of EMAPHORES in the system.

Setting method: This value can pass the sum of the values ​​of Processes below (remove the number of the largest prooracle instances.) Calculate: INITSID.ORA in each Oracle instance Cesses parameter) the largest processes × 2 10 ×

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 file information by following the following view: V $ V $ Parameter; Database, V $ DATAFILE, V $ logfile v $ ControlFile

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;

88. The anti-function of chr ()?

ASCII ()

SELECT Char (65) from Dual;

SELECT ASCII ('a') from Dual;

89. Connection of strings

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 *.

SQL> Spool OFF;

91. How to estimate the I / O number of SQL execution?

SQL> Set autotrace on;

SQL> SELECT *.

or

SQL> SELECT * FROM V $ fileStat; you can view the number of IO

92. How do I change the field size under SQLPLUS?

Alter Table Table_name Modify (Field_Name Varchar2 (100));

Change the big line, change, unless it is empty)

93. How do I query a day of data?

Select * from table_name whehyyy-mm-dd '); Re Trunc (Date field) = to_date (' 2003-05-02 ',

94. How do SQL statements insert all year dates?

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 say, it is not necessary to use commercial use, otherwise infringement. Oracle has a clear text; Oracle production downloaded from the website

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, SPFILE What is the difference?

Pfile is an Oracle traditional initialization parameter file, text format;

IFILE is similar to include Include in the C language, used to introduce another file;

SPFILE is new in 9i and is the default parameter file, binary format; STARTUP should only pick up PFILE.

转载请注明原文地址:https://www.9cbs.com/read-127683.html

New Post(0)