Oracle Common Problem Set (i)

Oracle
I. Common problems

1. Initial password after Oracle installation completed

Internal/oracle

Sys/change_on_install

System/manager

Scott/tiger

Sysman/oem_temp



2. Oracle9iAS WEB Cache's initial user and password?

Administrator/administrator



3. Oracle 8.0.5 How to create a data library

With Orainst. If you have a motif interface, you can use the orainst/m



4. Oracle 8.1.7 How to create a data library

Dbassist



5. Oracle 9i How to create a data library

Dbca



6. What does the bare devices in Oracle refer to?

The bare setup is the storage space that surrounds the file system's direct interview



7. How Oracle divide 64-bit/32bit version???

$ Sqlplus '/as SYSDBA '

Sql*plus:release 9.0.1.0.0-production on Mon June 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 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.

9i is not there, has been changed to use Sqlplus

Sqlplus/nolog

To the date-magazine.



9. Ask how to tell which machine the user is landing Oracle from

SELECT machine, terminal from v$session;



10. What language do you use to check the line?

DESC TABLE_NAME can check the structure of the table.

Select Field_name,... from ... We can check the value of the fence.

SELECT * FROM all_tables where table_name like '% '

SELECT * from All_tab_columns where table_name= '



11. How to get the trigger, the process, the function of the creation of the foot?

Desc User_source

User_triggers



12. How do you calculate the size of a table-occupied space?

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 can I view the maximum number of conversations?

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

This is for 200 customers.

SELECT * from V$license;

One of the biggest numbers of Sessions_highwater



14. How do I see the time when the system is locked?

SELECT * from V$locked_object;



15. How to run Oracle in a Archivelog way.

Init.ora

Log_archive_start = True

Restart DATABASE



16. How to obtain the user in the use of the data library

Select username from v$session;



17. What is the maximum number of entries in the data table?

The maximum number of columns in a table or video is 1000



18. How to find the SID of the data library

Select name from V$database;

You can also view the Init.ora file directly



19. How to pass Sqlplus on Oracle server to view the IP address of the machine

Select Sys_context (' Userenv ', ' ip_address ') from dual;

If you are on the mainland, you can only return 127.0.0.1, hehe



How do I adjust the library's time under UNIX?

Su-root

Date-u 08010000



21. How to crawl the memo type of blank data record in Oracle table

Select remark from Oms_flowrec where trim ("from remark") is not

null;



22. How to use BBB data to update the AAA table data (related to the fence)

UPDATE AAA SET bns_snm= (SELECT bns_snm from BBB WHERE

Aaa. DPT_NO=BBB. DPT_NO) WHERE BBB. Dpt_no is not NULL;



P4 Computer Installation Method

Convert SYMCJIT.DLL to Sysmcjit.old



24. How to inquire whether the server is Ops

SELECT * from V$option;

If parallel server=true has OPS,



25. How to inquire about the rights of each user

SELECT * from Dba_sys_privs;



26. How to move table space

ALTER TABLE table_name Move tablespace_name;



27. How to move an index to the table space

ALTER INDEX index_name REBUILD tablespace tablespace_name;



28. How to activate DBA STUDIO under Linux,unix

Oemapp Dbastudio



29. The image of the lock is

V$lock, V$locked_object, V$session, V$sqlarea, v$process;

Check the way to lock the 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, ' Exclusive ', 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, ' Exclusive ', 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

ALTER SYSTEM KILL session ' sid,serir# ';



How to modify the editor under Sqlplus

DEFINE _editor= "< Editor's full path >"--Must add dual quotes

To define a new editor, you can also write this in $oracle_home/sqlplus/admin/glogin.sql to make it permanent.



The Oracle product random function is

Dbms_random. RANDOM



Under Linux, check out the magnetic competition situation command

Sar-d



CPU competitive status commands under Linux

Sar-r



34. Consult the former user image

SELECT * from User_objects;

SELECT * from Dba_segments;



35. How to obtain error information

SELECT * from User_errors;



36. How to obtain a link

SELECT * from Dba_db_links;



37. View data in library characters

SELECT * from Nls_database_parameters;

SELECT * from V$nls_parameters;



38. Check the table space information

SELECT * from Dba_data_files;



Oracle's interal user to password

Modify Sqlnet. ORA

Sqlnet. Authentication_services= (NTS)



40. The solution of the JAVA.EXE

It's usually a oracleorahomexihttpserver change to manual activation.

X is 8 or 9



41. How to add to the table and column?

Sql>comment on table are ' table annotations ';

The note has been created.

Sql>comment on column table. Column is ' column annotation ';

The note has been created.

Sql> SELECT * from user_tab_comments where comments isn't null;



42. How do I look at the space occupied by each table?

sql> Col tablespace format A20

Sql> Select

b.file_id file ID number,

B.tablespace_name table space Name,

B.bytes the number of bits,

(B.bytes-sum (NVL (a.bytes,0))) has been used,

Sum (NVL (a.bytes,0)) The remainder of the space,

Sum (NVL (a.bytes,0))/(b.bytes) *100 remainder percent

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. What if Oracle is designed to be MTS or a dedicated mode?

#dispatchers = "(protocol=tcp) (SERVICE=SIDXDB)"

Plus is MTS, which is a specialized mode, and Sid refers to your actual example name.



44. How to know the system's pre-SCN

Select Max (KTUXESCNW * Power (2) + KTUXESCNB) from X$ktuxe;



45. Please ask how to take milliseconds in Oracle

9i is not supported, 9i starts with timestamp.

9i can be used with select Systimestamp from dual;

There are a lot of things that you might see when you're using Oracle, especially for starters, today I'm simply going to wrap it up and give it to everyone,

I hope we can help! And we explore together, together to move!



It is not necessary for Oracle masters to be seen.



46. How to add a car back to the string?

Select ' Welcome to visit ' | | Chr (10) | | Www.CSDN.NET ' from dual;



47. How are the Chinese sorted?

Before the oracle9i, Chinese is sorted according to the binary encoding.

In Oracle9i, we have added the sorting function according to pinyin, radical and pen. Set Nls_sort value

Schinese_radical_m is sorted by radical (first order), stroke (second order)

Schinese_stroke_m according to the pen plan (first order), radical (second order) Order

Schinese_pinyin_m sorted by Pinyin



Can the object name in Oracle8i be in Chinese?

OK



49. How do I change the sql*plus activate option in Win?

Sql*plus's own preferences we can set in $oracle_home/sqlplus/admin/glogin.sql.



50. How to modify the default date of the Oracel data library

Alter session set nls_date_format= ' Yyyymmddhh24miss ';

OR

You can add a row to the Init.ora

nls_date_format= ' Yyyymmddhh24miss '



51. How to put a small table into the Keep pool

ALTER TABLE XXX storage (buffer_pool keep);



52. How to check if a patch has been installed

Check that Orainventory



53. How to make a select sentence make the search results automatically generate sequence numbers

Select Rownum,col from table;



54. How to know the tablespace of a table in a data pant

Select Tablespace_name from User_tables where table_name= ' TEST ';

There is a tablespace_name in the select * from User_tables, (Oracle);

SELECT * from Dba_segments where ...;



55. How can I quickly make a backup table like the original table

CREATE TABLE new_table as (SELECT * from old_table);



55. How to modify the procedure under Sqlplus

Select Line,trim (text) t from User_source where name = ' an ' ORDER by

Line



56. How to remove procedure was accidentally locked

Alter system kill session, kill that session, but you have to find out her session ID first.

Or

It's OK to change the name of the process again.



What is SQL reference?

is a copy of the SQL manual, including languages, functions, and so on, Oracle official website of the document center is downloaded.



58. How to view the state of the data library

Under Unix

Ps-ef | grep ora

Under Windows

See if the service is up

Is it possible to connect to the data library



59. Please ask 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

With ALTER DATABASE .... DataFile ...;

Manually changing the size of the data file has 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 are in the library?

SELECT * from Dba_tablespaces;



63. How do I modify the Oracle data library's user connections?

Modify the Initsid.ora to increase the process and restart the data library.



64. How to find the last update of a record

You can use Logminer to see



65. How to read and write documents in Pl/sql?

Utl_file package allows the user to read and write the 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 simple and complex character sets problems?

ZHS16GBK can support



What software is the 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.

Author: ccbzzp