Oracle common dummies question 1000 full set of questions (1)

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. Oracle 8.0.5

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.OracleWhat does a bare device mean?
A bare device is a storage space that is directly accessed by bypassing the file system.
7. How Does Oracle differentiate between 64-bit and 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, not under Server Manager 9i. It has been changed to sqlplus.
Change sqlplus/nolog 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 tableHowCapture data records that are empty in the memo type column?
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. How do I query the server? Is it 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 start 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>" -- you must add double quotation marks to define the new editor. You can also write this in $ ORACLE_HOME/sqlplus/admin/glogin. SQL to make it permanently valid.
32. How Does Oracle generate random numbers?
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 oracleorahomihttpserver to manual start, and 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 = sidxd"
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 IOracleMillisecond?
Not Supported before 9i, 9i starts to have timestamp.
9i can use select distinct imestamp from dual;
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 value of nls_sort:
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 ';
Alternatively, you can add the line nls_date_format = 'yyyymmddhh24mis' in init. ora'
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 you can change the name of the process.
57. What is SQL reference?
Is an SQL user manual, including syntax, functions, and so on. The documentation center on the official Oracle website provides download.
58. How can I view the database status?
In Unix
PS-Ef | grep ora
Windows
Check whether the service is up; whether 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. 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 viewOracleWhat programs are running?
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: initsid of each Oracle instance. the sum of the values of the processes in ora (excluding the maximum processes parameter) + the maximum 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 to view data file placement paths?
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;
88. 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; check Io count
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 spfile?
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.