Oracle notes (1)

I. database language
1. SQL language: standard language for relational databases
2. PL/SQL: Procedural Language
3. SQL * Plus: simple reports and operating system interfaces
4. After Oracle 8.01 appears:
(1) Data Partitioning technology: Applicable only to Versions later than 8.01. data is stored discretely and not on one hard disk. I/O performance is good and security performance is good.
(2) object technology: stored procedures, functions, packages, database triggers, dynamic SQL programming
(3) database permission management
(4) Data Integrity Constraints (Data Integrity Constraints)
2. Oracle Database Core, Database Administrator DBA
Database management and daily maintenance
Overall Database Design
Database Storage Structure Design: physical structure and logical structure
 
/*************************************** **************************************** *******************
Oracle operating system:
UNIX: Sun Solaris, HP-UX, AIX, Compaq-Tru64, SCO-UNIX and Linux (running on PC)
For Windows NT/2000
Oracle cannot be installed on P4 machines. To install Oracle, you must modify the installation file or download the patch.
How to install Oracle on P4 machines (only on Intel P4 machines ):
Copy the Oracle installation CD to the hard disk, and rename the/stage/components/oracle. swp. jre/win32/bin/symcjit. dll file to symcjit.org, and then start the installation.
**************************************** **************************************** *******************/
Database backup and recovery
Optimization and performance Adjustment
3. Application System Development
Iv. Application Server OAS
5. Clear the Oracle8i runtime environment in Windows NT/2000 (preparations before re-installation ):
1. Delete the Oracle8i registry:
Regedit.exe => HKLM => Software => ORACLE
2. Delete the Oracle8i service:
Regedit.exe => HKLM => System => CurrentControlset => Services starting with Oracle
3. Delete the Oracle8i Event Log:
Regedit.exe => HKLM => System => CurrentControlset => Services => Eventlog => Application => events starting with Oracle
4. Delete the Windows NT/2000 installation disk/Program Files/Oracle directory.
5. Delete Oracle8i Environment Variables
Control Panel => system => advanced => Environment Variables
(1) Delete CLASSPATH
(2) edit the PATH and delete the PATH related to Oracle.
6. Delete the Oracle8i menu
7. restart Windows NT/2000 and stop the service.
8. Delete the Oracle8i home directory.
/*
IP: 75.64.16.X
Mask: 255.255.248.0
GateWay: 75.64.16.3
DNS: 75.64.16.3
*/
[7. Oracle network configuration]
/*
(1) query database name:
SQL> select name from v $ database;
(2) query the database instance Name:
SQL> select instance_name from v $ instance;
(3) query the database service name:
SQL> select value from v $ parameter where name = 'service _ names '; // (lower case)
(4) query the global database name (sys user ):
SQL> select value $ from props $ where name = 'Global _ DB_NAME '; // the string is case sensitive.
*/
1. Oracle network driver uses SQL * Net v2.0 connection
Oracle for Win98-> SQL * Net Easy Configuration
2. Oracle network driver is connected using Net8
3. Use Net8i to connect to the Oracle Network Driver
Oracle program group-> Network Administration-> Net8 Assistant
[Create a tns connection]:
(1) Start Net8 Assistant
(2) Local> service name
(3) edit-> create...
(4) Page 1: network service name: specify a Host String for the network database to be used on the local machine and define it by yourself.
(5) Page 2: Protocol: TCP/IP (Internet Protocol)
(6) Page 3:
Host Name: Enter the IP address or domain name of the target machine.
Port Number: The default value is 1521, which does not need to be modified.
(7) page 4: (Oracle8i) service name: Enter the service name of the network database
(8) Page 5: complete.
(9) menu: File> Save network configuration.
Monitor user sessions:
SQL> select username, sid, serial #, machine from v $ session;
Delete user sessions:
SQL> alter system kill session 'sid, serail #';
/* Exercise
Sqlplus stud01/stud01
SQL> show user
SQL> select * from emp;
SQL> select * from dept;
*/
[// Use the following method to execute the previous SQL statement again (in SQL * Plus)
SQL> l // lowercase letter L, displays the previous SQL statement
1 * select username, sid, serial #, machine from v $ session
SQL> // forward slash: Execute the previous SQL statement again
SQL> set linesize 1000 // set the row width in SQL * Plus to 1000 characters.
SQL> connect system/manager @ orasjz // connect directly to the database on the other machine in SQL * Plus. The @ character is followed by the tnsname of the other machine.
]
4. manually configure the Oracle network connection: configure the "tnsnames. ora" file.
A. manually configure the Oracle network connection configuration file: tnsnames. ora
(1) In UNIX:/u01/app/oracle/product/8.1.6/network/admin/tnsnames. ora
(2) In Windows 98/NT/2000: d:/oracle/ora81/network/admin/tnsnames. ora
B. manually configure the Oracle listener process configuration file: listener. ora
(1) In UNIX:/u01/app/oracle/product/8.1.6/network/admin/listener. ora
Run the following command to start a process in UNIX:
$ Lsnrctl start // start the listening process
$ Lsnrctl status // display the listener process status
$ Lsnrctl stop // stop the listening process
(2) In Windows 98/NT/2000: d:/oracle/ora81/network/admin/listener. ora
The command for starting a process is the same as that for UNIX.

# LISTENER. ORA Network Configuration File: d:/Oracle/Ora81/network/admin/listener. ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC0 ))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = ORA54) (PORT = 1521 ))
// Note: the HOST in the preceding line must be your HOST name. Otherwise, the listening may fail and you can use the IP address of your machine.
)
)
(DESCRIPTION =
(PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
)
(ADDRESS = (PROTOCOL = TCP) (HOST = ORA54) (PORT = 2481 ))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = d:/Oracle/Ora81)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ora54)
(ORACLE_HOME = d:/Oracle/Ora81)
(SID_NAME = ora54) // Note: ora54 is the SID Name of the database, which cannot be changed; otherwise, a listener error occurs.
)
)

5. Write the Host String to the Registry (simplified SQL * Plus logon)
Regedit.exe-> HKLM-> Software-> Oracle-> home0
Add Keyword: local (string), key value: host string name.
/* Copy the following content to A. reg file and double-click it in Windows 2000 to add or modify the local key value.
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/HOME0]
"Local" = "ora54"
*/
[SVRMGRL: How to Use Server Manager]
D:/> svrmgrl
Oracle Server Manager Release 3.1.6.0.0-Production
Copyright (c) 1997,1999, Oracle Corporation. All rights reserved.
Oracle8i Enterprise Edition Release 8.1.6.0.0-Production
With the Partitioning option
JServer Release 8.1.6.0.0-Production
SVRMGR> connect internal // use svrmgrl. The first command used after entering must be this command.
Password: // if the password is required, enter the oracle
Connection successful.
SVRMGR> select name from v $ database; // check the name of the currently used database
NAME
---------
ORA44
Select 1 line.
SVRMGR> shutdown immediate // close the currently used database
The database has been closed.
The database has been detached.
The ORACLE instance has been disabled.
SVRMGR> startup // start the currently used database. If it is invalid, use startup force
The ORACLE instance has been started.
The total number of global system regions is 24433932 bytes.
Fixed Size 70924 bytes
Variable Size 7507968 bytes
Database Buffers: 16777216 bytes
Redo Buffers 77824 bytes
ORA-00205 :?????????????????????
SVRMGR>
[Another example of starting an oracle database]
D:/> sqlplus internal/oracle
SQL> startup force // forcibly restart the database.

[Change Password]
SQL> grant connect to system identified by NewPassword; // if you use a number as the password, enclose it in double quotation marks.
SQL> grant connect to sys identified by NewPassword;
SQL> alter user system identified by NewPassword;
SQL> alter user sys identified by NewPassword;
SQL> password // enter the original password
// Note: The above password modification method is equivalent. sys and system users can change passwords for each other. If sys and system users forget their passwords, use the following method:
D:/> svrmgrl
Oracle Server Manager Release 3.1.6.0.0-Production
Copyright (c) 1997,1999, Oracle Corporation. All rights reserved.
Oracle8i Enterprise Edition Release 8.1.6.0.0-Production
With the Partitioning option
JServer Release 8.1.6.0.0-Production
SVRMGR> connect internal/oracle
Connection successful.
SVRMGR> grant connect to system identified by manager;
The statement has been processed.
SVRMGR> exit
The server management program ends.
[2002.04.18]
// ================================================ ========================================================== ======================================
8. Oracle product composition
Query database option products:
SQL> select * from v $ option;
It is generally True. If it is False, you can double-click to activate it.
/* Cartridges (Small Product) plug-in, (large product) option (options )*/
[SYS users are the users with the highest permissions in Oracle databases.]
[SQL * Plus logon method]
1. c:/> sqlplus "/as sysdba"
2. c:/> sqlplus internal
// Note: the preceding two methods can be used in the graphical logon interface of SQL * Plus.
// Use this logon method to log in. All users used are SYS.
[This logon method occurs because an ORA_DBA local group exists in the NT user group. All users in this group use operating system authentication, that is, the user in this group does not need a password when logging on to the database.]
[Orapwd command: Modify the password of an internal user]
/* Orapwd command line parameters
D:/> orapwd
Usage: orapwd file = <fname> password = <password> entries = <users>
Where
File-name of password file (mand ),
Password-password for SYS and INTERNAL (mand ),
Entries-maximum number of distinct DBA and OPERs (opt ),
There are no spaces around the equal-to (=) character .*/
Modify the internal password (the default internal password is oracle) authentication method (Steps A and B ):
A. Modify the Oracle logon authentication method:
Modify the SQLNET. ORA file.
SQLNET. AUTHENTICATION_SERVICES = (CNT) // Add the # sign in front of this line, and you can change the Oracle Authentication Method from operating system authentication to Oracle Authentication.
SQLNET. ORA file location:
UNIX:/u01/app/oracle/product/8.1.6/network/admin/sqlnet. ora
Windows NT/2000: d:/oracle/ora81/network/admin/sqlnet. ora
B. Modify the Internal password:
Windows NT/2000:
C:/> ORAPWD file = d:/oracle/ora81/database/pwdora8i. ora
Password = YourPassword // The password you want to set
Entries = 30
/* ORAPWD file = d:/oracle/ora81/database/pwdora54.ora password = qev entries = 30 */
UNIX:
$ Orapwd file = $ ORACLE_HOME/dbs/orapwSID
Password = YourPassword
Entries = 30
Then restart the Oracle service (Service> OracleServiceHOSTNAME ).
/* Before executing the preceding command, delete or rename the pwdora8i. ora (or orapwSID) file in the corresponding directory because the password file cannot be renamed. */
[Tkprof tracking File Sorting tool]
$ Tkprof x. trc x.txt
Windows 2000/NT:
D:/oracle/admin/db_name/udump/*. trc
UNIX:/u01/app/oracle/amdin/db_name/udump/*. trc //*/

9. Oracle Data Partitioning technology: used later than 8.0
(1) LOB (Large Object) big Object data:
1. BLOB: stores binary data, such as binary data, videos, and sounds. It is used to replace the Long raw type (Data Types earlier than Oracle7.0 will not be supported in the future)
2. CLOB: stores large characters, such as resume, which is used to replace the long field.
3. NCLOB: support for other ethnic languages
(1) database character set NLS:
Server: NLS_CHARACTERSET: (if the character set is the following, Oracle databases can store Chinese characters)
ZHS16GBK (Oraclei8, Oracle8)
ZHS16CGB231280 (Oracle7.3, 8, 8i)
This parameter is located in the data dictionary to query the core character set (language, date, currency, etc ):
SQL> select * from nls_database_parameters;
Client: NLS_LANG = Simplified Chinese_CHINA.ZHS16GBK (if not set, the default value is English)
Regedit.exe
In UNIX Client:
$ NLS_LANG = "simplified chinese" _ china. zhs16gbk
$ Export NLS_LANG
// Put the preceding environment variables in the. profile file.
/*************************************** ****************************** **************************
(1) sys is a user, and internal is a method used to start and close the database. internal is no longer used after 9.0, all of which are sys.
(2) SQL> set com v7 // temporarily return the 8i version to version 7
(3) SQL> alter system suspend; // use the internal user to execute and freeze data.
SQL> alter system resume; // unfreeze and restore to normal
**************************************** **************************************** ********************************/
(2) how to modify the core character set of the Oracle database:
[*] Modifying the data dictionary (using SYS users ):
SQL> update props $ set value $ = 'zhs16gbk' where name = 'nls _ CHARACTERSET ';
SQL> commit;
Then restart the database.
4. BFILE: external file storage, storing data on the server hard disk
10. Oracle 8i Database changes:
1. Network Computing Database
2. Support for IFS (Internet File System)
3. Integrated Java Virtual Machine