Oracle notes (1)

xiaoxiao2021-03-06  101

1 Method for unloading Oracle Developer Server:

1-1 Oracle Uninstall Tool Uninstalling the corresponding Oracleds project; search the alias corresponding to ORACLEDS HOME in the registry, delete the corresponding item; restart your computer; delete the corresponding ORACLEDS installation directory;

Problem of 2Jinitiator:

2-1 Jinitiator is Oracle's own Applet Viewer tool;

2-2 When you open a web application, you will automatically prompt the program if you don't install it;

2-3 Jinitiator is running in the client side

3 OC4J's common configuration:

3-1 Change the port of OC4J Open% Oracle_Home% / J2EE / Home / config / http-web-site.xml file modified:

in

"8888: Setting item (OC4J default port number is 8888)

3-2 Modify the OC4J default web file In% Oracle_Home% / J2EE / HOME / DEFAULT-WEB-APP directory, there are a variety of ways to set the default web file file of OC4J: (1) Find the INTEX.html file, replace the file; (2) Delete the intex.html file, create a default.jsp file (3) Delete the intex.html file, create an Intex.JSP file Note: OC4J can be as a complete web server, the server can perform * .jsp files. When you enter an IP address in the address bar of the browser: After the port number, the OC4J will automatically find the default web file according to the settings.

3-3 Modify OC4J Default Web Page File Set The% Oracle_Home% / J2EE / Home / Config / Global-Web-Application.xml file modifies:

INDEX.HTML

DEFAULT.JSP

File name and order Note: This configuration item is located at the end of the file. Another: According to the relevant document, you can implement the modified default web file settings here, but I tried it seems that there is no success, I don't know why.

3-4 New Icon File In Forms9i, the icon file should be a GIF file. By default, it is stored in a fixed directory. This directory is:% Oracle_Home% / CGENF61 / Admin / ICONS / PC

3-5 Modify OC4J Default Icon File After the Human Oracle_Home% / Forms90 / Java / Forms / Registry / Registry.dat file modifies: # # The application level icon files area: 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 at the end of the file

3-6 Configuring Virtual Paths In implementing user requirements, you often need to display the server's document to the user or provide user downloads. At this time: Web.show_Document may be used ('http: // Oracle_Home: 8888 / Pathname / XXXX.XLS', '_ blank'); at this time, Pathname does not refer to the actual path of the server, but the virtual path of the OC4J. Open% ORACLE_HOME% / J2EE / DEFAULTS / Application-Deployments / Defaults / DefaultWebApp / Orion-Web.xml Add or modify it: you can implement modifications or set OC4J virtual path: 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 Store Reports of the Directory OracleDeveloper, you need to generate the RDF file to store the server side, the default directory is confused with the system file in% Oracle_Home% / Reports / Integ directory, modify the Report directory method is as follows: Open % Oracle_Home% / Reports / conf / rwservlet.properties Modify Reports_Path = Option

3-8 Oracle Identifies above the IE window Remove the% ORACLE_HOME% / FORMS90 / Server / FormWeb.cfg file, logo = false

3-9 Modify the window size setting of the Applet in the IE window Set% Oracle_Home% / Forms90 / Server / FormSweb.cfg file, width = 800 / height = 600

3-10 Modify the default Forms file settings% ORACLE_HOME% / FORMS90 / Server / FormSweb.cfg file, Forms = xxx.fmx

3-11 Modify the default database connection string Set the% ORACLE_HOME% / Forms90 / Server / FormWeb.cfg file, user = xxx / yyy @ dbserver

3-12 Place the Forms9i's program to run Set the% ORACLE_HOME% / FORMS90 / Server / FormWeb.cfg file, SeparateFrame = false

3-13 Modifying the Forms9i Application Background Figure Setting% Oracle_Home% / Forms90 / Server / FormWeb.cfg file, Background = VirualPath / XXX.gif Note: VirualPath is set at 6 Virtual path

4 Text Document (TXT) operation:

4-1 grammar

4-1-1 Get the handle and open the file l_myfile: = text_io.fopen (filename, l_mode); where L_Mode is "W", write data; L_Mode is "R", read data.

4-1-2 Write data text_io.putf (myfile, 'xxx'); 4-1-3 Read data text_io.getline (MyFile, L_STR);

4-1-4 advance_io.new_line;

4-1-5 Close Document Text_io.fclose

Note: In the process of writing, the newline character is CHR (10)

4-2 instance

4-2-1 Write Text Document Declare L_myfile Text_io.file_Type; FileName Varchar2 (100): = 'C: /TestText.txt'; Begin - Create File Myfile: = Text_io.fopen (FileName, 'W'); - Write character text_io.putf (myfile, '111test'); text_io.putf (MyFile, Chr (10)); text_io.putf (myfile, '222test'); Text_io.putf (MyFile, Chr (10)) ; - Close the file text_io.fclose (myfile);

4-2-2 Read Document Declare L_MYFILE TEXT_IO.FILE_TYPE; FileName Varchar2 (100): = 'c: /testtext.txt'; l_str varchar2 (500); begin - Open file l_myfile: = text_io.fopen (FileName, 'R'); - cyclic read file content LOOP <

>

Begin

- Read a line of words

Text_io.get_Line (l_myfile, l_str);

- Show the content read

Message (L_STR);

-

TEXT_IO.NEW_LINE;

- Document reading is responsive

EXCEPTION

When no_data_foundim

EXIT;

End StartReadText;

End loop;

- Close the file

TEXT_IO.FCLOSE (L_MYFILE);

END;

5 Automatically keep the NIC activation:

5-1 Description: For a slightly new network card, there is a default setting under W2K: When the network cable is disconnected, the website's IP is automatically canceled. This may result in some service error that runs TCP / IP. This article introduces: Even if there is no network cable, let the NIC keep the IP address.

5-2 Operation: Parameters: New Double-word (DWORD) DisableDhcpmediasense (Sign-in), set to Hexadecimal 1 (Path: Hey_LOCAL_MACHINE / SYSTEM / CURRENTCONTROLSET / SERVICES / TCPIP / Paramenters) If not the above configuration, When the network cable is disconnected, use Route Print under DOS, you can't see the following: (only two lines can be seen)

6 Correctly remove Oracle

6-1 Frequently discovered that the method of running setup.exe is not feasible. It is often prompting a lot of such errors.

6-2 Automatic Manual Delete Method:

6-2-1 Control Panel / Manage Tools / Services stopped all about Oracle's service.

6-2-2 Delete C: / Oracle and C: / ProgramFiles / Oracle

6-2-3 Running Regedit, remove the HKEY-local-machine / currentcontrolset / services / about Oracle's content to complete the 6-2-4 restart computer.

7 PLSQL in single quotation marks "'" problem

7-1 Single quotes are split symbols of strings, if you need to quote single quotes directly, you need to use two ways:

7-1-1 The first common way: There is such a declaration in Oracle: Two single quotes continuously use text references to a single single quotation; for example: select 'i'm chinese' from dual results: i 'm Chinese

7-1-2: Use the value corresponding to the characters in the ASCII code, then use the chr () ASCII character conversion function to reference; for example: select 'test:' || chr (38) || CHR (39 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 (two):

8-1 use to_date ('2002-JAN-26', 'YYYY-MON-DD', 'NLS_DATE_LANGUAGUAGUAGUAGUAGAN'), for example: Select * from Emp where Hiredate

8-2 Use the ALTER Session Set NLS_LANGUAGE = American; ALTER session set nls_date_format = 'DD-MON-YYYY' changes the current character set and date format;

9 Use '#' and copy () to implement the dynamic composite condition query of the SELECT statement

9-1 Idea: "# = (or LIKE) 'Condition Value'" before the data block query, and then assign the corresponding data item in the data block using the COPY, and the Form will be combined by judging the ##, and Omit # 号; for example, COPY ('# =' ||: BLK_QUERY.EMPNO, 'BLK_EMP.EMPNO');

10 SQLPLUS small meter

10-1 Common SQLPLUS Command: Select * from Tab; - Display all objects of the current user; select * from allusers; - All users owned by the query system Create User test iDentified by Test; Grant Connect, Resource to Test; Drop User test; - Establish, authorize, delete users; (a) append - Add to Row (C) Change - Alternative or Delete (i) Input - Add a line (L) IST - List the buffer Row (R) UN - performs row @ file name (.sql) in the buffer - Execute "file name. SQL" script file @@ file name (.sql) - perform another one in a file "File Name. SQL" script file Save - Turn the buffer's data store GET - the command in the file to the buffer START - Execute the command spoolspool file name spool off " Machine file (various operations and execution result stores to disk files, the default file extension is .lst) set feedback off - default ON, remove the total information set Pagesize 50 - Default 14, a return number set TIMING ON - Default Off, Display Execution Time Set SQLPROMPT "SQL>" - Set the default prompt, the default value is "SQL>" set autonommit on - Set whether it is automatically submitted, the default is Offset Pause ON - default is OFF , Set the pause, make the screen display stop, wait to press the Enter key, then display the next page Define a = '' '20000101 12:01:01' '- Define local variables, if you want to use a similar in various The constant of the carriageway included in the display, for example: SQL> Select & a from dual; sql> select '20000101 12:01:01' from dual - any () - Put a value and each value in the table In comparison, there is a relationship to be established, returning True, for example: SQL> SELECT * FROM EMP WHERE SALE SALE SALE SAL = Any (Select Sal from Emp Where Deptno = 20) - Union-- Collection and does not include duplicate line union all - Collection And, including repeated line intersect-collections, do not include the difference in repeating line minus - collections, does not contain heavy Replenishing 11 dba small gauge

11-1 Establishing the purpose of the SYSTEM table space is to try the same table as the destination to improve the efficiency of use, and should only store data dictionary.

11-2 Database <- Table Space <- Data File <-Oracle Block

11-3 A segment segment can only belong to a table space, but can belong to multiple data files

11-4 A zone extent can only belong to a data file, that is, intervals, unable to cross data files

11-5 a single business can't cross multiple returns

11-6 index table does not include RowID values

11-7 There is no benefit to have a rolling segment with different sizes.

11-8 A transaction will be written to the redo log even if it is not submitted.

12 EXP / IMP small meter

12-1 Backup 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 Backup One or more Table 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 Import Specify Table D: /> Exp Scott / Tiger File = a.dmp D: /> IMP test / test fromuser = scott Tables = EMP file = a.dmpd: /> IMP test / test tables = dept file = a.dmp 12-4 record log information D: /> IMP80 username / password @ alias file = ompfile. Dmp log = mylog.logd: /> eXP80 username / password @ alias file = expfile.dmp log = mylog.log After running, all LOG information is recorded in myLog.log.

12-5 Description: - If the export user does not have DBA permissions, the import user can use the fromuser, the Touser parameter - If the export user has DBA permissions, the import user must have DBA permissions.

13 full-text search on Clob objects

13-1 If you use the LIKE keyword, an 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 (AA, '12', 1, 1)> 0; 1234 SQL > Select * from a where dbms_lob.instr (AA, '5', 1, 1)> 0; 5648

14 Views on the current user's various objects 14-1 table: select * from cat; select * from tab; select table_name from user_tables; 14-2 view: SELECT TEXT from user_views where view_name = Upper 14-3 index: select index_name, table_owner, table_name, tablespace_name, status from user_indexes order by table_name; 14-4 trigger: select trigger_name, trigger_type, table_owner, table_name, status from user_triggers; 14-5 snapshot: select owner, name, Master, Table_name, Last_Refresh, Next from User_Snapshots Order By Owner, Next; 14-6 Synonym: Select * from SYN; 14-7 Sequence: Select * from SEQ; 14-8 Database Link: SELECT * FROM User_DB_LINKS; 14-9 Constraint: Select Table_name, constraint_name, search_condition, status from user_constraints; 14-10 This user reads permissions for other user objects: SELECT * from user_tab_privs; 14-11 The system rights 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 byte number, max (bytes), count (*) from dba_free_space group by tablespace_name; 14- 14 Data Dictionary: SELECT TABLE_NAME FROM DICT ORDER B y Table_Name; 14-15 Lock and Resource Information: SELECT * FROM V $ LOCK; excluding DDL lock 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 $ DATABASE14-20 Control File: SELECT * FROM V $ ControlFile 14-21 Reform 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 Description Background Process: SELECT * FROM V $ BGPROCESS; 14-27 View version information: select * from product_component_version; 15 Use SELECT for Update clause to lock when query - Create an experimental table 1CREATE TABLE A (A Number); INSERT INTO A VALUES (1); commit; select * from a for update; - or select * from a for update of aa; (a list of table a) - New Open a SQL * PLUS window Conn Test / Test (the second session on the TEST user) delete from a; Command, you can release the lock state

16 SELECT connection issues, Join usage;

16-1 cross-connect (Cartesi)

Select * from table1, table2;

- Result Returns all possible combinations of Table1, Table2

16-2 Equal Connection (using a WHERE clause)

Select O.Item, O.Printno, A.yds as Yds from Proll O, PROLL_USE2001 A; WHERE O.Item = a.Item

- Result Returns data contained in the first table in the second table, equivalent to intersection

16-3

17 Form Path path settings, the purpose is to let Oracle Development recognize the path of the project, so that the items such as additional subroutine are automatically recognized by ORACEL Development.

17-1 Method: Modify the "/ HKEY_LOCAL_MACHINE / SOFTWARE / ORACLE / HOME1" in the registry

18 Modify ORACEL9I startup parameters

18-1 and 8i Different 9i defaults are started from the spfile.ora file, and 8i is started from the PFile file. After the 8i modifies the parameters, it must restart the database before it can take effect. 9i introduces SPFILE, it is a binary file, can not be modified directly However, you can use ALTER System or ALTER SESSION to dynamically modify the parameters, and all changes can take effect immediately.

18-2 SPFile is a binary file, you can use RMAN to back up, the first database starts to create spfile, Winnt Default catalog under $ {oracle_home} / datebase, init $ {ORACELSID}. In a $ {Oracle_Home} / admin / DB_NAM / PFILE / Under, Oracle Data Starting is from spfile $ {oracle_sid}. mar-> spfile.ora-> init $ {oraclesid}. ORA's order to find the startup parameter file.

18-3 CREATE SPFILE from Pfile Commands Establish a spfile file;

Startup pfile = 'e: /oracle/admin/eyglen/pfile/int.ora' starts using the Pfile file;

Startup starts using the spfile file;

18-4 Setting SPFILE file path changes in Pfile: SPFILE = 'E: / Oracle/SpFiletest.ora'

18-5 ALTER system set log_archive_start = false scope = SPFILE This command can directly modify the three optional parameters of SPFILE18-6 Scope: Memory: Change the current instance SPFile: Change SPFileBoth: Change the instance and SPFile

18-7 You can modify the spfile file after shutdown immediate

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

19 In the constraint conditions in Oracle, the constraints of the fields, such as primary keys, uniqueness, foreign bonds, etc., can achieve the purpose of control over data inspections in the remote view.

20 Delete the repeated record in the table, create a new table from the repeated record table

20-1 Delete Repeated Record Delete from test where rowid! = (Select max (rowid) from test bwhere test.id = B.ID)

21-2 Uniqueness New Table Create Table Miyu_New As Select Distinct ID, Title, Text from Miyu

21 Usage of Create SEQUENCE

21-1 CREATE SEQUENCE DDL statement cannot be used in the process clause of WHERE or PL / SQL

21-2 Pseudo Colum NEXTVAL, CURRVAL and CREATE SEQUENCE are used together, but Currval must be used at least once next NEXTVAL can be used

22 Rownum usage

22-1 General Rownum cannot be used in> or = number to write conditions (only by = 100; / * Correct * / select * from (SELECT * FROM1, A. * From feesco.ssk a order by ssbh) Where rownum1 <200 and rownum1> = 100;

22-2 Use ROWNUM to make a similar sorting and take the first 3 operation

/ * Error, this operation is not necessarily to get the first N records after sorting, because ROWNUM assigns a value to each line before sorting * / SELECT SAL FORM EMP WHERE ROWNUM <= 3 ORDER BY SAL / * Correct * / Return salaries least Top 3: Select Sal from (select Sal from Emp Order By Sal) Where rownum <= 3;

23 Dynamic SQL

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

/ * Example 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.put_line (v_rec.col1); end loop; end; '; execute immediate v_plsqlblock;

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

New Post(0)