Oracle SQL * PLUS basic and sqlplus command Xiangjie -2

Feb 1 posted in Oracle SQL (RSS), 1179 views.

§ 1.10 using the SQL Worksheet in the new version of Oracle, the system provides a graphical SQL called SQL Worksheet working environment, which is part of the ORACLE Enterprise Manager. Use it can be removed from the SQL> edit the line under the command of inconvenience. It is completely in a graphical manner. It starts with the following methods:
l start from the ORACLE Enterprise Manager
l start from the Administrtor Tools
l from the operating system to start
rn
Here's the operating system to start the SQL Worksheet tool to work these steps:
1) Click on the SQL Worksheet icon, choose Start - "programs -> Oracle-OraHome81-> database Administration-> SQLPLUS Worksheet. SQLPLUS Worksheet is selected after the following screen:
rn
Figure 1.10-1 login screen using the SQL Worksheet
rn
In the user name, password and imports of services ORACLE valid user name, password and connection string and then click OK after entering the picture as shown in Figure 1.10-2.
rn
2) When entered after the screen shown in Figure 1.10-2. You can work. The current version is in the top of the screen enter the command, the bottom of the screen is the system displays the results.
rn
3) We will click the cursor to the top of the screen, enter the SQL commands, such as the type:
select ename, sal from scott.emp;
Enter the completion of command, with the implementation of the cursor icon to click on the left side, Oracle will be the results displayed in the screen below:
rn
Figure 1. 10-2 after a successful login screen
rn
Figure 1.10-3 query command and display the results of the screen
rn
From the SQL Worksheet tool can be seen, the tool provides a very rich functionality. The following gives a brief functional description:
rn
1. File:
l change the database connection: you can connect to another user and database up;
l Open: You can open an existing file;
l will enter the Save As: save the current command to a file;
l will be displayed below the result saved as a file.
2. Editor:
l Cut: The characters in the currently selected shear;
l Copy: Cut the selected characters;
l Paste: The paste buffer characters;
l Select: all the characters on the current screen Select All;
l All Clear: Clear all the contents of the current.
rn
3. Worksheet:
l Execution: execution of the current order;
l run a local script: run an operating system. SQL file (when clicked, prompts the input file name);
l Command history: view the command history;
l at a command: Display on a command.
rn
Tip: If your SQL Worksheet are not shown in Chinese characters (which appears chaotic character), you NT/2000/98 your environment variable settings of Chinese characters the same as with the server just fine. Chinese characters in the WINDOWS NT environment variables to set these steps:
rn
Using regedit to modify the registry of all ORACLE character set items, such as:
1) Click on: Start = "Run =" type regedit;
2) Click "HEY_LOCAL_MACHINE" the left "+" to expand the directory;
3) Click on the "SOFTWARE" the left "+" to expand the directory;
4) Click on the "ORACLE" item;
5) Revise the ORACLE under the tree for all "NLS" variables and props $ in agreement.
rn
On the Chinese environment variable settings, please refer to the "Oracle8i/9I the primary database management."
rn
-----------------------------------------------
rn
1, Oracle startup and shutdown
1, in the stand-alone environment, in order to start or shut down ORACLE system must first switch to the ORACLE user, as follows
su - oracle
rn
a, start the ORACLE system
oracle> svrmgrl
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> quit
rn
b, shut down ORACLE system
oracle> svrmgrl
SVRMGR> connect internal
SVRMGR> shutdown
SVRMGR> quit
rn
Start oracle9i database command:
$ Sqlplus / nolog
rn
SQL * Plus: Release 9.2.0.1.0 - ProdUCtion on Fri Oct 31 13:53:53 2003
rn
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
rn
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup ^ C
rn
SQL> startup
ORACLE instance started.
rn
2, in the two-plane environment, in order to start or shut down ORACLE system must first switch to root user, as follows
su - root
rn
a, start the ORACLE system
hareg-y oracle
rn
b, shut down ORACLE system
hareg-n oracle
rn
What are a few to start Oracle database method
rn
Description:
rn
There are several ways to start:
1, startup nomount
Non-installation start, in this way to launch the next executable: rebuild control file, rebuild the database
rn
Read init.ora file to start the instance, that launched SGA and background processes, which started just init.ora file.
rn
2, startup mount dbname
Installation starts, this approach to launch the next executable:
Database log archiving,
Database media recovery,
The data file online or offline,
To re-locate data files, redo log files.
rn
Implementation of the "nomount", then open the control file, confirm data files and online log file location,
But this time not data files and log files and checks.
rn
3, startup open dbname
The first implementation of the "nomount", then run the "mount", then open the Redo log file, including all databases, including documents,
This way can access data in the database.
rn
4, startup, equivalent to the following three commands
startup nomount
alter database mount
alter database open
rn
5, startup restrict
Constrained in this way can be activated to start the database, but only allowed to have a certain privileged users to access non-privileged user access, the following tips:
ERROR:
ORA-01035: ORACLE only allows users with the RESTRICTED SESSION privileges to use
rn
6, startup force
Forced to start way when you can not close the database, you can use startup force to complete the closure of the database to close the database, then the normal database commands to start
rn
7, startup pfile = parameter file names start with initialization parameter file means the first to read parameter file, and then set the parameter file to start the database in the cases of: startup pfile = E: Oracleadminoradbpfileinit.ora
rn
8, startup EXCLUSIVE
2, the user how to effectively use of ORACLE data dictionary data dictionary is an important part of the database, which arise as a database created with the database and change,
Reflected in sys user at some of the tables and views. Data dictionary name is uppercase English characters.
rn
The data dictionary, there is user information, the user's access information, all data objects information, table constraints, such as statistical analysis of the database view.
We can not manually modify the data dictionary information.
rn
In many cases, the general ORACLE users do not know how to effectively use it.
rn
dictionary all the data dictionary table names and explanations, it has a synonym dict
dict_column all the data dictionary table field names and explanations
rn
If we want to query the data dictionary with the index, you can this with the following SQL statement:
rn
SQL> select * from dictionary where instr (comments, 'index')> 0;
rn
If we want to know user_indexes table details the meaning of each field name, you can use the following clause in SQL statement:
rn
SQL> select column_name, comments from dict_columns where table_name = 'USER_INDEXES';
rn
And so on, you can easily know the details of the name of the data dictionary and explanation do not see the other documentation of ORACLE.
rn
The following are listed by category of some commonly used ORACLE user queries the data dictionary to use.
rn
1, the user
rn
View the current user's default tablespace
rn
SQL> select username, default_tablespace from user_users;
rn
View the current user's role
SQL> select * from user_role_privs;
rn
View the current user's system privileges, and table-level permissions
SQL> select * from user_sys_privs;
SQL> select * from user_tab_privs;
rn
2, Table
rn
See the table of all users
SQL> select * from user_tables;
rn
See the table name contains characters log
SQL> select object_name, object_id from user_objects
where instr (object_name, 'LOG')> 0;
rn
View of a table creation time
SQL> select object_name, created from user_objects where object_name = upper ( '& table_name');
rn
See the size of a table
SQL> select sum (bytes) / (1024 * 1024) as "size (M)" from user_segments
where segment_name = upper ( '& table_name');
rn
View on the ORACLE table in the memory area where
SQL> select table_name, cache from user_tables where instr (cache, 'Y')> 0;
rn
3, index
rn
View the index number and type of
SQL> select index_name, index_type, table_name from user_indexes order by table_name;
rn
View the index fields have been indexed
SQL> select * from user_ind_columns where index_name = upper ( '& index_name');
rn
View the index size of the
SQL> select sum (bytes) / (1024 * 1024) as "size (M)" from user_segments
where segment_name = upper ( '& index_name');
rn
4, serial number
rn
View serial number, last_number is the current value of
SQL> select * from user_sequences;
rn
5, view
rn
View the name of the view
SQL> select view_name from user_views;
rn
View of the select statement to create the view
SQL> set view_name, text_length from user_views;
SQL> set long 2000; Note: You can view text_length value is set according to set long the size of the
SQL> select text from user_views where view_name = upper ( '& view_name');
rn
6, synonyms
rn
See the name of the synonym
SQL> select * from user_synonyms;
rn
7, constraints
rn
View the constraints of a table
SQL> select constraint_name, constraint_type, search_condition, r_constraint_name
from user_constraints where table_name = upper ( '& table_name');
rn
SQL> select c.constraint_name, c.constraint_type, cc.column_name
from user_constraints c, user_cons_columns cc
where c.owner = upper ( '& table_owner') and c.table_name = upper ( '& table_name')
and c.owner = cc.owner and c.constraint_name = cc.constraint_name
order by cc.position;
rn
8, stored functions and procedures
rn
View the status of functions and procedures
SQL> select object_name, status from user_objects where object_type = 'FUNCTION';
SQL> select object_name, status from user_objects where object_type = 'PROCEDURE';
rn
View the source code for functions and procedures
SQL> select text from all_source where owner = user and name = upper ( '& plsql_name');
rn
3, view database, SQL
1, view the table space name and size of the
rn
select t.tablespace_name, round (sum (bytes / (1024 * 1024)), 0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;
rn
2, view tablespace physical file name and size of the
rn
select tablespace_name, file_id, file_name,
round (bytes / (1024 * 1024), 0) total_space
from dba_data_files
order by tablespace_name;
rn
3, view the name and size of rollback segments
rn
select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent, (next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v $ rollstat v
Where r.segment_id = v.usn (+)
order by segment_name;
rn
4, view control files
rn
select name from v $ controlfile;
rn
5, view the log file
rn
select member from v $ logfile;
rn
6, view the table space is being used
rn
select sum (bytes) / (1024 * 1024) as free_space, tablespace_name
from dba_free_space
group by tablespace_name;
rn
SELECT A. TABLESPACE_NAME, A. BYTES TOTAL, B. BYTES USED, C. BYTES FREE,
rn
(B. BYTES * 100) / A. BYTES "% USED", (C. BYTES * 100) / A. BYTES "% FREE"
FROM SYS.SM $ TS_AVAIL A, SYS.SM $ TS_USED B, SYS.SM $ TS_FREE C
WHERE A. TABLESPACE_NAME = B. TABLESPACE_NAME AND A. TABLESPACE_NAME = C. TABLESPACE_NAME;
rn
7, view the database object library
rn
select owner, object_type, status, count (*) count # from all_objects group by owner, object_type, status;
rn
8, view the version of the database
rn
Select version FROM Product_component_version
Where SUBSTR (PRODUCT, 1,6) = 'Oracle';
rn
9, see the database creation date and archiving methods
rn
Select Created, Log_Mode, Log_Mode From V $ Database; 4, ORACLE management of user connections
rn
With the system administrator to see the current database, there are several user connections:
rn
SQL> select username, sid, serial # from v $ session;
rn
If you want to stop a connection with the
rn
SQL> alter system kill session 'sid, serial #';
rn
If this command not work, find its UNIX process number
rn
SQL> select pro.spid from v $ session ses, v $ process pro where ses.sid = 21 and ses.paddr = pro.addr;
rn
Note: 21 is the number of a connection sid
rn
Then use the kill command to kill this process ID.
rn
5, SQL * PLUS using the
a, almost into the SQL * Plus
$ sqlplus username / password
rn
Exit SQL * Plus
SQL> exit
rn
b, In sqlplus under the help information lists all the SQL commands and SQL * Plus commands
SQL> help
Commands are listed in a specific information
SQL> help command name
rn
c, shows the table structure of the command DESCRIBE
SQL> DESC table name
rn
d, SQL * Plus editing commands shown in the SQL buffer commands
SQL> L
rn
First of all you want to modify the SQL command line becomes the current line to be corrected
SQL> n
Modify the contents of the command with the CHANGE
SQL> c / old / new re-verify that the correct
SQL> L
rn
Using the INPUT command in the SQL buffer to add one or more rows
SQL> i
SQL> input
rn
e, call the external system editor
SQL> edit the file name you can use the DEFINE command to set system variable EDITOR to change the type of text editor in the file are defined as follows login.sql row
DEFINE_EDITOR = vi
rn
f, run the command file
SQL> START test
SQL> @ test
rn
Common SQL * Plus Statements
a, the table to create, modify, delete command to create the table the following format:
create table table name (column description list);
rn
As the base table to add a new column command is as follows:
ALTER TABLE table ADD (column description list)
Example: In order to test the table to increase an Age, used to store the age
sql> alter table test
add (Age number (3));
rn
Modify the base table column definition command is as follows:
ALTER TABLE table name
MODIFY (column name data type)
Example: The test table in the Count column width extended to 10 characters
sql> alter atble test
modify (County char (10));
rn
b, will be a table delete statement the following format:
DORP TABLE table name;
Example: Table to delete will also delete the table data and table definitions
sql> drop table test
rn
c, table space, create, delete 6, ORACLE logical backup of the SH file
rn
Full backup of the SH file: eXP_comp.sh
rn
rq = `date +"% m% d "`
rn
su - oracle-c "exp system / manager full = y inctype = complete file = / oracle / export / db_comp $ rq.dmp"
rn
SH cumulative backup file: exp_cumu.sh
rn
rq = `date +"% m% d "`
rn
su - oracle-c "exp system / manager full = y inctype = cumulative file = / oracle / export / db_cumu $ rq.dmp"
rn
SH incremental backup files: exp_incr.sh
rn
rq = `date +"% m% d "`
rn
su - oracle-c "exp system / manager full = y inctype = incremental file = / oracle / export / db_incr $ rq.dmp"
rn
root user crontab file
/ var / spool / cron / crontabs / root to add the following content
rn
0 2 1 * * / oracle / exp_comp.sh
rn
30 2 * * 0-5 / oracle / exp_incr.sh
rn
45 2 * * 6 / oracle / exp_cumu.sh
rn
Of course, this timetable can be changed according to different needs, and this is just one example.
rn
7, ORACLE commonly used in SQL syntax and data objects
rn
1. Data control statements (DML) part of the
rn
1.INSERT (insert record to the data table statement)
rn
INSERT INTO table (field name 1, field name 2, ... ...) VALUES (value 1, the value 2, ... ...);
INSERT INTO table (field name 1, field name 2, ... ...) SELECT (field name 1, field name 2, ... ...) FROM another table name;
rn
String type field values must be enclosed in single quotation marks, for example: 'GOOD DAY'
If the field value contains a single quote inside 'the need for a string conversion, we replace it into two single quotation marks''.
String type field value exceeds the length of the definition of an error, preferably inserted before the length of the checksum.
rn
The date field the field value can be a database system time with the current SYSDATE, accurate to seconds, or use string to date type function TO_DATE ('2001-08-01 ',' YYYY-MM-DD ')
TO_DATE () There are many date formats, you can see ORACLE DOC.
Year - month - day hours: minutes: seconds format YYYY-MM-DD HH24: MI: SS
rn
INSERT maximum operational length of the string is less than equal to 4000 single-byte, if you want to insert a longer string, please consider using CLOB field type,
Way to use ORACLE Lane comes DBMS_LOB package.
rn
If you use the INSERT, when starting from an automatic increase in the serial number, it should first establish a serial number
CREATE SEQUENCE serial number of the name (preferably the table name + serial number tag) INCREMENT BY 1 START WITH 1
MAXVALUE 99999 CYCLE NOCACHE;
One of the biggest values determined by the length of the field, if the definition of automatic increase in the serial number NUMBER (6), max 999999
INSERT statement inserts the value of this field: the name of the serial number. NEXTVAL
rn
2.DELETE (delete data table records statement)
rn
DELETE FROM table WHERE conditions;
rn
Note: does not delete the record and the release of ORACLE data block inside the occupied table space. Only those who have been deleted data blocks marked as unused.
rn
If you really want to delete all the records of a large table, you can use TRUNCATE command, it can block the release of the data table space occupied by
TRUNCATE TABLE table;
This action can not be rollback.
rn
3.UPDATE (modify data table records statement)
rn
UPDATE table SET field name 1 = value 1, field name 2 = value 2, ... ... WHERE condition;
rn
If you modify the value of N is not an assignment or definition, will clear the contents of the original record of NULL, preferably before modifying non-empty calibration;
Over the definition of the length of the value of N would be an error, it is best inserted before the length of the check ..
rn
Note:
A. above SQL statement is added to the tables are row-level locks,
Upon completion, must be added the end of the command COMMIT transaction processing in order to come into force,
Otherwise, change is not necessarily written to the database.
If you want to withdraw these operations, you can use the command ROLLBACK recovery.
rn
B. run the INSERT, DELETE, and UPDATE statements before the best estimates of what the record may be operating range,
It should be limited to small (10000 records) within the framework. Or ORACLE uses a great deal with this thing rollback segment.
Procedures to respond to slow or even loss of response. If the number of records on the hundreds of thousands of these operations, these SQL statements can be sub-sub-sub-complete,
Together with confirmation of things during COMMIT processing. 2. Data definition (DDL) part of the
rn
1.CREATE (to create tables, indexes, views, synonyms, procedures, functions, database links, etc.)
rn
ORACLE common field types are
CHAR fixed-length strings
VARCHAR2 variable-length strings
NUMBER (M, N) number of type M is the median total length, N is the length of the decimal
DATE date type
rn
When we are young to create the table is not empty field on the front of the field may be empty on the back
rn
Can be used to create the table field names in Chinese, but the best of the field names in English
rn
To create the table fields can be added to the default value, for example, DEFAULT SYSDATE
So each insertion and modification, do not have access to procedures for operation of this field action time
rn
Create a table can be added to the field, for example, the constraints do not allow duplicate UNIQUE, keyword PRIMARY KEY
rn
2.ALTER (changing tables, indexes, views, etc.)
rn
To change the name of the table
ALTER TABLE table 1 TO table 2;
rn
In the table behind the increase in a field
ALTER TABLE table name ADD name of the field names described in paragraph;
rn
Modify the definition of the field description table
ALTER TABLE table MODIFY field name field name description;
rn
Field to the table with constraint
ALTER TABLE table name ADD CONSTRAINT constraint name PRIMARY KEY (field name);
rn
ALTER TABLE table name ADD CONSTRAINT constraint name UNIQUE (field names);
rn
Put the table on the database, or remove the memory area
ALTER TABLE table name CACHE;
ALTER TABLE table name NOCACHE;
rn
3.DROP (delete tables, indexes, views, synonyms, procedures, functions, database links, etc.)
rn
Remove the table and all its constraints
DROP TABLE table CASCADE CONSTRAINTS;
rn
4.TRUNCATE (empty table all the records, retaining the table structure)
rn
TRUNCATE table;
rn
3. Query (SELECT) part of the
rn
SELECT field name 1, field name 2, ... ... FROM table 1, [table 2, ... ...] WHERE conditions;
rn
Field names can be brought into the functions such as: COUNT (*), MIN (field names), MAX (field names), AVG (field names), DISTINCT (field names),
TO_CHAR (DATE field names, 'YYYY-MM-DD HH24: MI: SS')
rn
NVL (EXPR1, EXPR2) function is explained:
IF EXPR1 = NULL
RETURN EXPR2
ELSE
RETURN EXPR1
rn
DECODE (AA, V1, R1, V2, R2 ....) function, explained:
IF AA = V1 THEN RETURN R1
IF AA = V2 THEN RETURN R2
.. ... ELSE
RETURN NULL
rn
LPAD (char1, n, char2) function is explained:
Character char1 according to the number of bits n to develop shows, less than the number of bits to replace the string with the left side of the space char2
rn
Field names can be between the arithmetic such as: (a field name field name * 1) / 3
rn
For example, can be nested query: SELECT ... ... FROM
(SELECT ... ... FROM table 1, [table 2, ... ...] WHERE condition) WHERE condition 2;
rn
2 the results of query operations such as the collection can be done: and set UNION (remove duplicate records), and set UNION ALL (do not remove duplicate records), and poor collection MINUS, intersection INTERSECT
rn
Group Inquiries
SELECT field name 1, field name 2, ... ... FROM table 1, [table 2, ... ...] GROUP BY field name 1
[HAVING conditions];
rn
The connection between two or more tables query
rn
SELECT field name 1, field name 2, ... ... FROM table 1, [table 2, ... ...] WHERE
Table 1. Field name = table name 2. Field name [AND ... ...];
rn
SELECT field name 1, field name 2, ... ... FROM table 1, [table 2, ... ...] WHERE
Table 1. Field name = table name 2. Field name (+) [AND ... ...];
rn
With (+) of the field position auto-fill empty value
rn
Query result set sorting operation, the default sort is ascending ASC, descending order is DESC
rn
SELECT field name 1, field name 2, ... ... FROM table 1, [table 2, ... ...]
ORDER BY field name 1, field name 2 DESC;
rn
String comparison method of fuzzy
rn
INSTR (field name, 'string') "0
Field Name LIKE 'string%' [ '% string%']
rn
Each table has a hidden field ROWID, which marks the uniqueness of records.
rn
4. ORACLE where commonly used data objects (SCHEMA)
rn
1. Index (INDEX)
rn
CREATE INDEX index name ON table (field 1, [field 2, ... ...]);
ALTER INDEX index name REBUILD;
rn
The best index of a table should not exceed three (except for special big table), the best single-field index, combined with an analysis of the implementation of the SQL statement,
Can also be a combination of a multi-field index and function-based index
rn
ORACLE8.1.7 string can index the maximum length of 1578 single-byte
ORACLE8.0.6 string can index the maximum length of 758 single-byte
rn
2. The view (VIEW)
rn
CREATE VIEW view name AS SELECT .... FROM ... ..;
ALTER VIEW view name COMPILE;
rn
The view is only one SQL query, which can form complex relationship between the simplicity of.
rn
3. Synonym (SYNONMY)
CREATE SYNONYM synonym name FOR table;
CREATE SYNONYM synonym name FOR table @ database link name;
rn
4. Database link (DATABASE LINK)
CREATE DATABASE LINK database link name CONNECT TO username IDENTIFIED BY password USING 'database connection string';
rn
Database connection string can be NET8 EASY CONFIG or directly modify the TNSNAMES.ORA where defined.
rn
Database Parameters global_name = true to request the database link name with the same remote database name
rn
Database global name can be found using the following command
SELECT * FROM GLOBAL_NAME;
rn
Queries the remote database table
SELECT ... ... FROM table @ database link name;
rn
5. Rights Management (DCL) statements
rn
1.GRANT the system assigns permissions to access a collection of commonly used has the following three:
CONNECT (basic connectivity), RESOURCE (Programming), DBA (database management)
rn
Commonly used data objects have the following five rights:
ALL ON data object names, SELECT ON data object names, UPDATE ON data object names,
DELETE ON data object name, INSERT ON data object name, ALTER ON data object name
rn
GRANT CONNECT, RESOURCE TO username;
GRANT SELECT ON table TO user name;
GRANT SELECT, INSERT, DELETE ON table TO user name one, user name 2;
rn
2.REVOKE recovery Permissions
rn
REVOKE CONNECT, RESOURCE FROM username;
REVOKE SELECT ON table FROM user name;
REVOKE SELECT, INSERT, DELETE ON table FROM user name one, user name 2;
rn
Query the database Error No. 63:
select orgaddr, destaddr from sm_histable0116 where error_code = '63 ';
rn
Query the database user accounts and the largest issued to submit the largest number: select MSISDN, TCOS, OCOS from ms_usertable;
rn
Query the database in the sum of the various error codes:
select error_code, count (*) from sm_histable0513 group by error_code order
by error_code;
rn
Query statements in the database, then a single statistical types of queries.
select sum (Successcount) from tbl_MiddleMt0411 where ServiceType2 = 111
select sum (successcount), servicetype from tbl_middlemt0411 group by servicetype
rn

This article comes from CSDN blog, reproduced please indicate source: http://blog.csdn.net/jack_rao/archive/2009/05/11/4168371.aspx