Turn: Oracle Notes

Oracle Notes
1 How to uninstall Oracle Developer Server:

Uninstall the corresponding Oracleds project in the 1-1 Oracle Uninstall tool;
Search the registry for the alias of Oracleds home, delete the corresponding item;
Restart the computer;
Remove the installation directory for the corresponding oracleds;

2jinitiator Related questions:

2-1 Jinitiator is Oracle's own applet viewer tool;

2-2 you will automatically be prompted to install the program the first time you open the Web application, and you cannot open the program if you do not install it;

2-3 Jinitiator is running on client side

3 Common configurations for oc4j:

3-1 changing the port of oc4j
Open%oracle_home%\j2ee\home\config\http-web-site.xml File
Modify one of: Medium
"8888: Set Item (oc4j default port number is 8888)

3-2 Modify oc4j Default Web page file
In the%oracle_home%\j2ee\home\default-web-app directory, there are several ways to set the default Web page file for oc4j:
(1) Locate the intex.html file and replace the file;
(2) Delete the intex.html file and create the default.jsp file
(3) Delete the intex.html file and create the intex.jsp file
Note: oc4j is visible as a full Web server that can execute *.jsp files.
When you enter the IP address in the address bar of the browser: port number, OC4J will automatically find the default webpage file according to the settings.

3-3 Modify oc4j Default Web page file settings
Open%oracle_home%\j2ee\home\config\global-web-application.xml File
Modify them:

Index.html
default.jsp

The file name and order
Note: This configuration item is located at the end of the file
Another: According to the relevant documents, the configuration here can be implemented to modify the default Web page file settings, but I tried to seem unsuccessful, I do not know why.

3-4 New Icon File
In forms9i, the icon file should be a GIF file, which is stored in a fixed directory by default.
This directory is:%oracle_home%\cgenf61\admin\icons\pc

3-5 Modify oc4j default icon file suffix name and storage directory
Open%oracle_home%\forms90\java\forms\registry\registry.dat File
Modify them:
#
# The application level icon files is relative to the Documentbase
# example:icons/
# or an absolute URL.
# example:http://www.forms.net/~luser/d2k_project/
#
Default.icons.iconpath=
Default.icons.iconextension=gif
Note: This configuration item is located at the end of the file

3-6 Configuring the virtual path
In the process of implementing user requirements, it is often necessary to display the server's documents to the user or to provide users with the download.
At this point: web.show_document (' Http://Oracle_Home:8888/PathName/xxxx.xls ', ' _blank ') may be used;
At this point pathname does not refer to the actual path of the server side, but the oc4j virtual path.
Open%oracle_home%\j2ee\oracleds\application-deployments\defaults\defaultwebapp\orion-web.xml
Add or modify one of these:
Can be implemented to modify or set the oc4j virtual path
Note: You can also open%oracle_home%\j2ee\oracleds\application-deployments\forms\forms90web\orion-web.xml,
The difference is that the file link set here is: Http://Oracle_Home:8888/forms/xxxx.xls

3-7 Report File storage directory
Oracledeveloper reports, all need to generate RDF files stored on the server side,
The default directory is in the%oracle_home%\reports\integ directory,
Easily confused with system files, the method of modifying the report directory is as follows:
Open%oracle_home%\reports\conf\rwservlet.properties
Modify the Reports_path= option < if not, you can append. This option can also be appended to the system registry >

3-8 The Oracle identity removal above the IE window
Set the%oracle_home%\forms90\server\formsweb.cfg file, Logo=false

3-9 Modifying the window size of an applet in the IE window
Set the%oracle_home%\forms90\server\formsweb.cfg file, width=800/height=600

3-10 Modifying the default forms file
Set the%oracle_home%\forms90\server\formsweb.cfg file, FORMS=XXX.FMX

3-11 Modifying the default database connection string
Settings%oracle_home%\forms90\server\formsweb.cfg file, User=xxx/[email protected]

3-12 put the forms9i program in the Java window Run < in general, in the Serlet of IE window run >
Set the%oracle_home%\forms90\server\formsweb.cfg file,
separateframe=false< General,separateframe=true>

3-13 Modifying the forms9i application background < original background image for Oracle Forms sevices>
Set the%oracle_home%\forms90\server\formsweb.cfg file, background=virualpath/xxx.gif
Note: Virualpath is a virtual path set at 6

4 Text Document (TXT) operation:

4-1 syntax

4-1-1 gets the handle and opens the file
L_myfile:= Text_io. Fopen (FileName, L_mode);
Where the L_mode is "W", the data is written and the data is read when the L_mode is "R".

4-1-2 Writing data
Text_io. Putf (MYFILE, ' xxx ');

4-1-3 read out data
Text_io. GetLine (MYFILE,L_STR);

4-1-4 forward line
Text_io. New_line;

4-1-5 closing files
Text_io. FClose

Note: The newline character is Chr (10) during writing to the file.

4-2 Example

4-2-1 Writing to text document
Declare
L_myfile Text_io. File_type;
Filename Varchar2 (+): = ' c:\TestText.Txt ';
Begin
--Create File
Myfile: = Text_io. Fopen (Filename, ' W ');
--Write characters
Text_io. Putf (Myfile, ' 111Test ');
Text_io. Putf (Myfile, CHR (10));
Text_io. Putf (Myfile, ' 222Test ');
Text_io. Putf (Myfile, CHR (10));
--Close File
Text_io. Fclose (Myfile);
End;

4-2-2 reading a document
DECLARE
L_myfile Text_io.file_type;
Filename Varchar2 (+): = ' c:\TestText.Txt ';
L_str VARCHAR2 (500);
BEGIN
--Open File
L_myfile:=text_io. Fopen (filename, ' R ');
--Read the contents of the file in a loop
Loop
<>
Begin
--read a line of text
Text_io. Get_line (L_MYFILE,L_STR);
--show what is read
Message (L_STR);
--forward line
Text_io. New_line;
--File Read complete response
Exception
When No_data_found Then
Exit;
END Startreadtext;
End Loop;
--Close File
Text_io. Fclose (L_myfile);
END;
5 automatically keep the network card active:

5-1 Description: For a slightly new NIC, there is a default setting under W2K:
When the network cable is disconnected, the IP of the website is automatically canceled. This can cause some service errors to run TCP/IP.
This article describes: even if you do not plug in the network cable, but also to keep the IP address.

5-2 Action: Parameters: New Double-byte word (DWord) DisableDHCPMediaSense (note case),
Set to Hex 1 (path: hey_local_machine\system\currentcontrolset\services\tcpip\paramenters)
Without the above configuration, when the network cable is disconnected, using route print under DOS, you cannot see the following lines: (Only two lines are visible)

6 Correct removal of Oracle

6-1 It is often found that the method of running Setup.exe is not feasible. Often prompt a lot of such mistakes.

6-2 Automatic + manual removal method:

6-2-1 Control Panel/management tools/services to stop all service on Oracle.

6-2-2 Delete c:/oracle and c:/programfiles/oracle

6-2-3 run regedit and will hkey-local-machine/software/and
hkey-local-system/currentcontrolset/services/All content about Oracle is removed

6-2-4 Restart your computer.

7 plsql single quote "'" problem

7-1 single quotes in use is a string of the split symbol, if you need to directly reference the single quotation marks need two ways:

7-1-1 the first common way: In Oracle, there is such a declaration: two single quotation marks are used consecutively to denote a text reference to a single single quotation mark;
Example: Select ' I ' M Chinese ' from dual
The result is: I ' m Chinese

7-1-2 Another way: Use the ASCII code character corresponding to the numeric value, and then use Chr () ASCII character conversion function for reference;
Example: SELECT ' Test: ' | | Chr (38) | | Chr (in) from dual;
The result is:test:& '
Can be used in practical applications: Select ASCII (' a ') from dual;
The result is: 97

8 Oracle Chinese date format and English date format problem resolution method (two):

8-1 using To_date (' 2002-jan-26 ', ' yyyy-mon-dd ', ' nls_date_language = American ')
For example:
SELECT * FROM emp
where HireDate

8-2 use
ALTER SESSION SET nls_language = AMERICAN;
ALTER SESSION SET nls_date_format= ' dd-mon-yyyy '
Change the current character set and date format;

9 using ' # ' and copy () to implement dynamic compound condition query of SELECT statement

9-1 idea: "# = (or like) ' condition value ' before a block query, and then use Copy to assign a value to the corresponding data item in the block,
The form will make a combination of conditions by judging the # number and omitting the # number;
For example
Copy (' # = ' | |:blk_query. EMPNO, ' blk_emp. EMPNO ');

Ten Sqlplus Subtotal

10-1 Common Sqlplus Commands:
SELECT * from tab;
--Displays all objects of the current user;
SELECT * from AllUsers;
--Query all users owned by the system
Create user test identified by test;
Grant Connect,resource to test;
Drop user test;
--Establishment, authorization, deletion of users;
(a) Append
--Add to end of line
(c) Change
--Replace or delete
(i) input
--Add a row
(l) IST
--List the rows in the buffer
(r) UN
--Execution of rows in the buffer
@ file name (. sql)
--Execute the script file "file name. sql"
@@ 文件名 name (. sql)
--Execute another "file name. sql" script file in one file
Save
--Save the buffer's data to disk
Get
--Transfer the commands in the file to the buffer
Start
--Execute commands in the buffer to be transferred into
Spool
Spool file name
Spool off
--Spool files (various operations and execution results are saved to the disk file with the default file name extension. lst)
Set Feedback off
--Default on, to remove the total information at the end
Set PageSize 50
--Default 14, number of records returned at one time
Set timing on
--default off, show execution time
Set Sqlprompt "Sql>"
--Set default prompt, default value is "Sql>"
Set autocommit on
--Set whether to submit automatically, default to OFF
Set pause on
--default is off, set pause, will stop the screen display, wait for the ENTER key, and then display the next page
Define a = "20000101 12:01:01"
--Define local variables, if you want a constant like a carriage return that is included in the various displays,
For example:
Sql> select &a from dual;
Sql> Select ' 20000101 12:01:01 ' from dual
--
Any ()
--to compare a value to each value in the table, a relationship is established and returns true
For example:
Sql> SELECT * from emp where sal = any (Select sal from emp where deptno = 20)
--
UNION
--collection of and, without duplicate rows
Union All
--collection of and, containing duplicate rows
Intersect
--the intersection of the collection, does not contain duplicate rows
Minus
--the difference of the collection, does not contain duplicate rows

Subtotal DBA

11-1 The purpose of establishing the system tablespace is to keep tables with the same purpose as possible to improve efficiency and to store only data dictionaries

11-2 Database <-table space <-data file <-oracle block

11-3 a segment segment can belong to only one table space, but may belong to more than one data file

11-4 A zone extent can only belong to one data file, i.e. interval (extent) cannot span data files

11-5 a single transaction cannot span multiple rollback segments

11-6 Index Table does not contain ROWID values

11-7 there is no benefit to having a different size rollback segment

11-8 a transaction is written to the redo log even if it is not committed.

Subtotal Exp/imp

12-1 backing up one or more users
D:\>exp Scott/tiger file= Export File
D:\>exp System/manager owner= (user 1, user 2,..., user n) file= export file

12-2 backing up one or more tables
D:\>exp User/Password tables= table
D:\>exp User/Password tables= (table 1,..., table 2)
D:\>exp system/manager tables= (user. Table)
D:\>exp System/manager tables= (User 1. Table 1, User 2. Table 2)

12-3 Importing the specified table
D:\>exp Scott/tiger File=a.dmp
D:\>imp test/test Fromuser=scott tables=emp file=a.dmp
D:\>imp test/test tables=dept file=a.dmp

12-4 Logging Log Information
D:\>IMP80 Username/[email protected] file=impfile.dmp Log=mylog.log
D:\>EXP80 Username/[email protected] file=expfile.dmp Log=mylog.log
After running, all log messages are recorded in the MyLog.log.

12-5 Description:
--If the export user does not have DBA authority, the import user can not specify the Fromuser, touser parameters
--If the export user has DBA authority, the import user must also have DBA authority

13 Full-Text search for CLOB objects

13-1 If you use the LIKE keyword, the error will occur;

13-2 The following methods should be used:
Sql> CREATE Table A (a clob);

Sql> INSERT into a values (' 1234 ');

Sql> INSERT into a values (' 5648 ');

Sql> SELECT * from A WHERE dbms_lob.instr (a.a, ' a ', ") >0;

1234


Sql> SELECT * from A WHERE dbms_lob.instr (A.A, ' 5 ', >0;)

5648

14 View of various objects under the current user
14-1 table:
SELECT * from Cat;
SELECT * from tab;
Select table_name from User_tables;
14-2 Views:
Select text from User_views where View_name=upper (' &view_name ');
14-3 Index:
Select Index_name,table_owner,table_name,tablespace_name,status from User_indexes ORDER by TABLE_NAME;
14-4 triggers:
Select Trigger_name,trigger_type,table_owner,table_name,status from User_triggers;
14-5 Snapshots:
Select Owner,name,master,table_name,last_refresh,next from User_snapshots order by Owner,next;
14-6 Synonyms:
select * FROM SYN;
14-7 sequence:
select * from SEQ;
14-8 Database Link:
SELECT * from User_db_links;
14-9 Constraint Restrictions:
Select Table_name,constraint_name,search_condition,status from User_constraints;
14-10 This user reads permissions from other user objects:
SELECT * from User_tab_privs;
14-11 System privileges owned by this user:
SELECT * from User_sys_privs;
14-12 Users:
SELECT * from All_users order by user_id;
14-13 table Space remaining free space situation:
Select Tablespace_name,sum (bytes) Total bytes, max (bytes), COUNT (*) from Dba_free_space GROUP by Tablespace_name;
14-14 Data dictionary:
Select table_name from DICT ORDER by TABLE_NAME;
14-15 Lock and resource information:
SELECT * from V$lock; DDL lock not included
14-16 Database Character Set:
Select name,value$ from props$ where name= ' nls_characterset ';
14-17 Inin.ora Parameters:
Select Name,value from V$parameter order by name;
14-18 SQL Shared Pool:
Select Sql_text from V$sqlarea;
14-19 Database:
SELECT * FROM V$database
14-20 Control Files:
SELECT * from V$controlfile;
14-21 Redo log File information:
SELECT * from V$logfile;
14-22 log file information from the control file:
SELECT * from V$log;
14-23 data file information from the control file:
SELECT * from V$datafile;
14-24 NLS Parameter Current value:
SELECT * from V$nls_parameters;
14-25 Oracle Version information:
SELECT * from V$version;
14-26 describe the background process:
SELECT * from V$bgprocess;
14-27 View version information:
SELECT * from Product_component_version;

15 Use the FOR UPDATE clause of Select to lock records at query time
--Create the Experiment table 1
CREATE Table A (a number);
Insert into a values (1);
Commit

SELECT * from a for update;
-OR
SELECT * from a for update of A.A; (Column A of Table a)

--New open a sql*plus window
Conn Test/test (second session on test user)
Delete from A;

-At this point, the system pauses, waits to be unlocked,
--As long as a roll is issued in the first window, or a commit;

The connection problem in select, the use of join;

16-1 Cross Connection (Cartesian product)

SELECT * from Table1,table2;

--results return all possible combinations that contain table1,table2

16-2 equivalent connections (using the WHERE clause)

SELECT o.item,o.printno,a.yds as Yds from Proll o,proll_use2001 A;
WHERE O.item=a.item

-the result returns the data in the second table that contains the first table, equivalent to the intersection

16-3

The setting of the form path path is intended to allow Oracle develop to recognize the path to the project,
Make the project, such as the attached sub-Library, automatically recognized by Oracel develop

17-1 Method: Modify the registry in the "\hkey_local_machine\software\oracle\home1" in the Append

18 Modifying the startup parameters of the oracel9i

18-1 with 8i different 9i default is from Spfile.ora file boot, and 8i is from pfile file boot,
8i after modifying the parameters must restart the database to take effect, 9i introduced SPFile, it is a binary file,
cannot be modified directly, but you can modify the parameters dynamically using ALTER SYSTEM or ALTER session.
And all changes can take effect immediately.

18-2 SPFile is a binary file that can be backed up using Rman, and the first time the database is started requires
Pfile to create the spfile,winnt default directory under ${oracle_home}\datebase, Init${oracelsid}.ora in
a${oracle_home}\admin\db_nam\pfile\, Oracle data startup is from spfile${oracle_sid}.ora->
Spfile.ora->init${oraclesid}.ora order to find the startup parameters file.

18-3
Create SPFile from pfile command to create a spfile file;

Startup pfile= ' E:\oracle\admin\eyglen\pfile\int.ora ' uses pfile file to start;

startup using SPFile file;

18-4 you can set the SPFile file path change in pfile: spfile= ' E:\oracle\spfiletest.ora '

18-5 alter system set LOG_ARCHIVE_START=FALSE scope=spfile the command can be modified directly spfile

18-6 Three optional parameters for scope:
MEMORY: Changing the current instance
SPFile: Change SPFile
Both: changing instances and SPFile

18-7 can modify the SPFile file after shutdown immediate

18-8 Show parameter SPFile command to determine if the SPFile file is being used

19 Add constraints to fields in Oracle constraints, such as primary key, uniqueness, foreign key, etc. combined with client-side update checking and error control,
You can achieve the purpose of controlling the data check in the remote view.

20 delete duplicate records that exist in the table to create a unique new table from the repeating record table

20-1 deleting duplicate records
Delete from Test where rowid!= (
Select Max (ROWID) from Test b
where test.id=b.id)

21-2 uniqueness of the new table
CREATE TABLE Miyu_new as
Select distinct Id,title,text from Miyu;

Usage of CREATE SEQUENCE

21-1 CREATE SEQUENCE DDL statements cannot be used in a where or PL/SQL PROCEDURE clause

21-2 pseudo-column nextval,currval is used with create sequence, but Currval must be used at least once nextval to use

A note on the usage of ROWNUM

22-1 generally rownum cannot write the condition with the > or = sign (only with the < number is possible)
/* ERROR */
SELECT * FROM (SELECT * from Fesco.ssk ORDER by SSBH) where rownum<200 and rownum>=100;
/* Correct */
SELECT * FROM (select RowNum rownum1, a.* from Fesco.ssk a order by SSBH) where rownum1<200 and rownum1>=100;

22-2 using rownum for similar sorting after taking the first 3 actions

/* error, such an operation is not necessarily the first N records to be sorted, because RowNum is assigned to each line before ordering */
Select Sal form emp where rownum<=3 order by Sal
/* Correct */
Return the top 3 with the lowest wage:
Select Sal from (select Sal from emp Order by Sal) where rownum<=3;

23 Use of Dynamic SQL

23-1 using the "EXECUTE IMMEDIATE" statement to refer to non-query statements (DML,DDL)

/* Instance 1,DDL statement */
EXECUTE IMMEDIATE
' CREATE TABLE testtable (col1 VARCHR (10)) ';
/* Instance 2, anonymous PL/SQL statement block */
V_plsqlblock: =
' BEGIN
For V_rec in (SELECT * from execute_table) LOOP
Dbms_output. Put_Line (V_REC.COL1);
END LOOP;
END; ';
EXECUTE IMMEDIATE V_plsqlblock;

Turn: Oracle Notes