Oracle Basic Syntax

Oracle Basic Syntax

I. Startup and shutdown of Oracle
1, in a single-machine environment
To start or shut down an Oracle system, you must first switch to an Oracle user, as follows
Su-oracle
A. Start the Oracle system
Oracle>svrmgrl
Svrmgr>connect Internal
Svrmgr>startup
Svrmgr>quit
B. Shut down the Oracle system
Oracle>svrmgrl
Svrmgr>connect Internal
Svrmgr>shutdown
Svrmgr>quit
To start the oracle9i database command:
$ sqlplus/nolog
Sql*plus:release 9.2.0.1.0-production on Fri Oct 31 13:53:53 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Sql> Connect/as SYSDBA
Connected to an idle instance.
Sql> Startup^c
Sql> Startup
ORACLE instance started.
2, in the dual-machine environment
To start or shut down an Oracle system, you must first switch to the root user, as follows
Su-root
A. Start the Oracle system
Hareg-y Oracle
B. Shut down the Oracle system
Hareg-n Oracle
What kinds of startup methods are available for Oracle databases
Description
There are several ways to start:
1, Startup Nomount
Non-installation boot, this way startup executable: Rebuild the control file, rebuild the database
Read the Init.ora file and start instance, which starts the SGA and background process, which requires only init.ora files.
2. Startup Mount DBName
Installation starts, this way starts under executable:
Database log archiving,
Database Media Recovery,
Bring the data file online or offline,
Relocate the data file, redo the log file.
Execute "nomount", then open the control file, confirm the location of the data file and the online log file,
However, the data files and log files are not checked for validation at this time.
3. Startup Open dbname
Execute "nomount" first, then execute "Mount", and then open all the database files including the redo log file,
This way you can access the data in the database.
4, startup, equal to the following three commands
Startup Nomount
ALTER DATABASE Mount
ALTER DATABASE Open
5, startup restrict
Constrained mode start
This way the database can be started, but only users with certain privileges are allowed access
When a non-privileged user accesses, the following prompt appears:
ERROR:
Ora-01035:oracle only allow users with RESTRICTED SESSION permissions to use
6. Startup force
Forced start mode
When the database cannot be closed, you can use startup force to complete the shutdown of the database
Close the database first and then execute the normal startup database command
7. Startup pfile= parameter file name
Start mode with initialization parameter file
Read the parameter file first, and then start the database by setting it in the parameter file
Example: Startup Pfile=e:oracleadminoradbpfileinit.ora
8, startup EXCLUSIVE
Ii. how the user can effectively use the data dictionary
Oracle's data dictionary is one of the most important parts of a database, which is generated as a result of database changes,
manifests as some tables and views under the SYS user. The data dictionary name is an uppercase English character.
Data dictionary contains user information, user's permission information, all data object information, table constraints, statistical analysis database view and so on.
We cannot manually modify the information in the data dictionary.
Many times, the average Oracle user does not know how to use it effectively.
Dictionary The name and interpretation of all data dictionary tables, it has a synonym dict
Dict_column All data dictionary table field names and explanations
If we want to query the data dictionary associated with the index, we can use the following SQL statement:
Sql>select * from dictionary where InStr (comments, ' index ') >0;
If we want to know the detailed meaning of each field name in the User_indexes table, you can use this SQL statement:
Sql>select column_name,comments from Dict_columns where table_name= ' user_indexes ';
And so on, you can easily know the detailed name and explanation of the data dictionary without looking at other Oracle documentation.
The following is a list of the query usage methods for some Oracle user-common data dictionaries by category.
1. Users
View the default tablespace for the current user
Sql>select username,default_tablespace from User_users;
View the role of the current user
Sql>select * from User_role_privs;
View system and table-level permissions for the current user
Sql>select * from User_sys_privs;
Sql>select * from User_tab_privs;
2. Table
View all the tables under the user
Sql>select * from User_tables;
View a table with a name that contains a log character
Sql>select object_name,object_id from User_objects
where InStr (object_name, ' LOG ') >0;
To view the creation time of a table
Sql>select object_name,created from User_objects where Object_name=upper (' &table_name ');
View the size of a table
Sql>select sum (bytes)/(1024*1024) as "size (M)" from user_segments
where Segment_name=upper (' &table_name ');
View tables placed in Oracle's memory area
Sql>select Table_name,cache from User_tables where InStr (Cache, ' Y ') >0;
3. Index
View index number and category
Sql>select Index_name,index_type,table_name from User_indexes ORDER by TABLE_NAME;
View the fields indexed by the index
Sql>select * from User_ind_columns where Index_name=upper (' &index_name ');
To view the size of an index
Sql>select sum (bytes)/(1024*1024) as "size (M)" from user_segments
where Segment_name=upper (' &index_name ');
4. Serial number
View serial number, Last_number is the current value
Sql>select * from User_sequences;
5. View
View the name of the view
Sql>select view_name from User_views;
View the SELECT statement that created the view
Sql>set view_name,text_length from User_views;
Sql>set Long 2000; Description: The size of the set long can be set according to the Text_length value of the view
Sql>select text from User_views where View_name=upper (' &view_name ');
6. Synonyms
View the name of a synonym
Sql>select * from user_synonyms;
7. Constraint conditions
View constraints on a table
Sql>select constraint_name, Constraint_type,search_condition, R_constraint_name
From user_constraints WHERE table_name = UPPER (' &table_name ');
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;
8. Storage functions and procedures
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 ';
View source code for functions and procedures
Sql>select text from All_source where Owner=user and Name=upper (' &plsql_name ');
Third, view the SQL of the database
1. View the name and size of the table space
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;
2. View the table space physical file name and size
Select Tablespace_name, file_id, file_name,
Round (bytes/(1024*1024), 0) Total_space
From Dba_data_files
Order BY Tablespace_name;
3. Check the rollback segment name and size
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;
4. View Control files
Select name from V$controlfile;
5. Viewing log files
Select member from V$logfile;
6. View the usage of table space
Select sum (bytes)/(1024*1024) as Free_space,tablespace_name
From Dba_free_space
Group BY Tablespace_name;
SELECT a.tablespace_name,a.bytes total,b.bytes used, c.bytes free,
(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;
7. View Database Library objects
Select owner, object_type, Status, COUNT (*) count# from All_objects Group by owner, object_type, status;
8. View the version of the database
Select version from Product_component_version
Where SUBSTR (product,1,6) = ' Oracle ';
9. View the date the database was created and how it was archived
Select Created, Log_mode, Log_mode from V$database;
Iv. Management of Oracle user connections
With the system administrator, view the current database with several user connections:
Sql> select username,sid,serial# from V$session;
If you want to stop a connection
Sql> alter system kill session ' sid,serial# ';
If this command doesn't work, find it. Number of UNIX processes
Sql> Select Pro.spid from V$session ses,v$process Pro where ses.sid=21 and ses.paddr=pro.addr;
Description: 21 is the number of SIDS for a connection
Then kill the process number with the KILL command.
V. Use of Sql*plus
A, near into the Sql*plus
$sqlplus User name/password
Exit Sql*plus
Sql>exit
B. Get help information under Sqlplus
List all SQL commands and Sql*plus commands
Sql>help
Listing information for a specific command
SQL>HELP command name
c, Show table Structure command describe
SQL>DESC Table Name
D, edit commands in Sql*plus
Show SQL Buffer command
Sql>l
Modify SQL commands
The first thing to do is to change the line to go forward.
Sql>n
Modify content with the change command
sql>c/Old/New
Re-confirm that it is correct
Sql>l
Use the input command to add one or more rows to the SQL buffer
Sql>i
Sql> Input Content
E. Calling the External System Editor
Sql>edit file name
You can use the Define command to set the system Variable editor to change the type of the text editor, and define the following line in the Login.sql file
Define_editor=vi
F. Run the command file
Sql>start Test
Sql> @test
Common Sql*plus Statements
A, table creation, modification, deletion
The command format for creating the table is as follows:
CREATE TABLE table name (column description list);
Add a new column command to the base table as follows:
ALTER table name ADD (column description list)
Example: Add a column of age to the test table to hold the ages
Sql>alter table Test
Add (age number (3));
Modify the Base table column Definition command as follows:
ALTER Table Table Name
MODIFY (column name data type)
Example: The Count column width in the test table is extended to 10 characters
Sql>alter atble Test
Modify (county char (10));
b, the format of a table DELETE statement is as follows:
Dorp table name;
Example: Table deletion will delete both the table's data and the table definition
Sql>drop table Test
C, table space creation, deletion
Vi. Oracle Logical Backup of SH file
Full backup of sh file: exp_comp.sh
rq= ' date + '%m%d '
SU-ORACLE-C "Exp System/manager full=y inctype=complete file=/oracle/export/db_comp$rq.dmp"
Cumulative backup of SH files: exp_cumu.sh
rq= ' date + '%m%d '
SU-ORACLE-C "Exp System/manager full=y inctype=cumulative file=/oracle/export/db_cumu$rq.dmp"
SH file for incremental backup: exp_incr.sh
rq= ' date + '%m%d '
SU-ORACLE-C "Exp System/manager full=y inctype=incremental file=/oracle/export/db_incr$rq.dmp"
Root User crontab file
/var/spool/cron/crontabs/root Add the following content
0 2 1 * */oracle/exp_comp.sh
2 * * 0-5/oracle/exp_incr.sh
2 * * 6/oracle/exp_cumu.sh
Of course, this timetable can be changed according to different needs, this is just an example.
VII. Common SQL syntax and data Objects for ORACLE
I. Data Control statement (DML) section
1.INSERT (insert a recorded statement into the data table)
INSERT into table name (field name 1, field Name 2, ...) Values (value 1, value 2, ...);
INSERT into table name (field name 1, field Name 2, ...) SELECT (field name 1, field Name 2, ...) from another table name;
field values for string types must be enclosed in single quotation marks, for example: ' Good day '
If the field value contains a single quotation mark ' required for string conversion, we replace it with two single quotes '.
A string type with a field value that exceeds the defined length can be an error, preferably a length check before inserting.
field values for date fields can be sysdate with the current database's system time, accurate to seconds
or convert a string into a date-type function to_date (' 2001-08-01 ', ' yyyy-mm-dd ')
To_date () also has a number of date formats, which can be found in Oracle DOC.
Year-month-day hour: minute: Format of seconds Yyyy-mm-dd HH24:MI:SS
The maximum operand length of an insert is less than or equal to 4,000 single bytes, and if you want to insert a longer string, consider the field with the Clob type,
method to borrow the Dbms_lob package that comes with Oracle.
If you want to use a serial number that automatically grows from 1 when you insert it, you should first create a serial number
Name of the CREATE SEQUENCE serial number (preferably table name + serial number tag) INCREMENT by 1 START with 1
MAXVALUE 99999 CYCLE NOCACHE;
Where the maximum value is determined by the length of the field, if the defined auto-grow serial number is no. (6), the maximum value is 999999
Insert statement inserts this field value as: The name of the serial number. Nextval
2.DELETE (Delete statements recorded in the data table)
DELETE from table name WHERE condition;
Note: Deleting a record does not release the block table space that is occupied in Oracle. It only marks those chunks of data that have been deleted as unused.
If you are sure you want to delete all the records from a large table, you can use the TRUNCATE command, which frees up the data block table space that is occupied
TRUNCATE table name;
This action is not fallback.
3.UPDATE (Modify the statement recorded in the data table)
Update table name SET field name 1= value 1, field name 2= value 2, ... WHERE condition;
If the modified value n is not assigned or defined, it will clear the original record content to null, preferably before the modification of non-null check;
A value of n exceeds the defined length error, preferably a length check before inserting:
Precautions:
A. The above SQL statement adds row-level locks to the table.
Once the confirmation is complete, you must add the command COMMIT to the end of the transaction to formally take effect.
Otherwise, changes are not necessarily written to the database.
If you want to recall these operations, you can restore them with the command ROLLBACK.
B. Before running insert, DELETE and UPDATE statements, it is best to estimate the range of records that might be manipulated.
It should be confined to a smaller (10,000 records) range. Otherwise, Oracle handles this thing with a large fallback segment.
The program responds slowly or even loses its response. If you record more than 100,000 of these operations, you can divide these SQL statements into sub-sections,
Plus commit to confirm things processing.
Two. Data definition (DDL) section
1.CREATE (Create tables, indexes, views, synonyms, procedures, functions, database links, etc.)
Oracle's Common field types are
CHAR fixed-length string
VARCHAR2 variable-length strings
Number (m,n) digit M is the total length of the digits, and N is the length of the decimal
Date type
When you create a table, you put smaller, non-empty fields in front of them, and possibly empty fields behind
You can use a Chinese field name when creating a table, but it's best to use the field name in English
You can add a default value to a field when you create a table, such as the defaults sysdate
In this way, each time you insert and modify the field, you do not have to use the program to get the action.
You can add constraints to a field when you create a table
For example, duplicate unique is not allowed, keyword PRIMARY key
2.ALTER (change tables, indexes, views, etc.)
Change the name of a table
ALTER table name 1 to table name 2;
Add a field to the back of the table
ALTER table name ADD field name description;
Modify the definition description of a table field
ALTER Table name modify field name description;
Add constraints to the fields in the table
ALTER table name ADD CONSTRAINT constraint name PRIMARY KEY (field name);
ALTER table name ADD CONSTRAINT constraint name UNIQUE (field name);
Place the table in or out of the memory area of the database
ALTER table name CACHE;
ALTER table name NOCACHE;
3.DROP (delete tables, indexes, views, synonyms, procedures, functions, database links, etc.)
Delete the table and all its constraints
DROP table name CASCADE CONSTRAINTS;
4.TRUNCATE (clears all records in the table, preserves the structure of the tables)
TRUNCATE table name;
Three. Query statement (SELECT) section
Select Field Name 1, field Name 2, ... From table name 1, [table Name 2, ...] WHERE condition;
Field names can be brought into the function
For example: COUNT (*), MIN (field name), MAX (field name), AVG (field name), DISTINCT (field name),
To_char (date field name, ' Yyyy-mm-dd HH24:MI:SS ')
NVL (EXPR1, EXPR2) function
Explain:
IF Expr1=null
RETURN EXPR2
ELSE
RETURN EXPR1
DECODE (Aa,v1,r1,v2,r2 ...) Function
Explain:
IF Aa=v1 then RETURN R1
IF Aa=v2 then RETURN R2
.. ...
ELSE
RETURN NULL
Lpad (CHAR1,N,CHAR2) function
Explain:
The character char1 is displayed by the number of digits n, and the insufficient digits replace the left empty with the CHAR2 string
Arithmetic operations can be performed between field names
For example: (Field name 1* field name 1)/3
Query statements can be nested
For example: SELECT ... From
(SELECT ... From table name 1, [table Name 2, ...] Where condition) where Condition 2;
The result of two query statements can be used as a collection operation
For example: Union (remove duplicate records), and set union ALL (do not remove duplicate records), difference set minus, intersection intersect
Group queries
Select Field Name 1, field Name 2, ... From table name 1, [table Name 2, ...] GROUP by field name 1
[Having conditions];
Connection query between more than two tables
Select Field Name 1, field Name 2, ... From table name 1, [table Name 2, ...] WHERE
Table name 1. field name = Table Name 2. field name [and ...];
Select Field Name 1, field Name 2, ... From table name 1, [table Name 2, ...] WHERE
Table name 1. field name = Table Name 2. Field name (+) [and ...];
field position with (+) number automatically fill empty value
The sort operation of the query result set, the default sort is ascending ASC, descending is desc
Select Field Name 1, field Name 2, ... From table name 1, [table Name 2, ...]
ORDER by Field name 1, field name 2 DESC;
Method of String Fuzzy comparison
INSTR (field name, ' string ') >0
Field name like ' String% ' ['% string% ']
Each table has an implied field rowid, which marks the uniqueness of the record.
Four. Data objects commonly used in Oracle (SCHEMA)
1. Indexing (Index)
CREATE index name on table name (field 1, [Field 2, ...];
ALTER index index name REBUILD;
The index of a table should not be more than three (except for special large tables), preferably with a single field index, combined with the analysis of SQL statement execution,
You can also create multi-field combined indexes and function-based indexes
ORACLE8.1.7 string can be indexed up to a maximum length of 1578 single bytes
ORACLE8.0.6 string can be indexed up to a maximum length of 758 single bytes
2. Views (view)
CREATE view name as SELECT .... From ...;
ALTER View name COMPILE;
A view is just a SQL query statement that can make complex relationships between tables simple.
3. Synonyms (synonmy)
CREATE synonym synonym name for table name;
CREATE synonym synonym name for table name @ database link name;
4. Database link
CREATE database link name connect to user name identified by password USING ' database connection string ';
The database connection string can be defined using NET8 Easy config or directly modifying the Tnsnames.ora.
Database parameter global_name=true requires the database link name to be the same as the remote database name
The database global name can be isolated with the following command
SELECT * from Global_name;
Querying tables in the remote database
SELECT ... From table name @ database link name;
Five. Rights Management (DCL) statements
1.GRANT Assign Permissions
The common set of system permissions has the following three:
Connect (basic connection), RESOURCE (program development), DBA (database management)
The common data object permissions are the following five:
All on data object name, SELECT on data object name, UPDATE on data object name,
DELETE on data object name, INSERT on data object name, ALTER on data object name
GRANT CONNECT, RESOURCE to user name;
GRANT SELECT on table name to user name;
GRANT SELECT, INSERT, DELETE on table name to user name 1, user name 2;
2.REVOKE Reclaim Permissions
REVOKE CONNECT, RESOURCE from user name;
REVOKE SELECT on table name from user name;
REVOKE SELECT, INSERT, DELETE on table name from user name 1, user name 2;
Querying the database for number 63rd errors:
Select Orgaddr,destaddr from sm_histable0116 where error_code= ' 63 ';
Query database for the maximum number of users to submit and maximum issued: select Msisdn,tcos,ocos from Ms_usertable;
Query the sum of the various error codes in the database:
Select Error_code,count (*) from sm_histable0513 GROUP by Error_code Order
by Error_code;
Query the report database for the type of word statistics.
Select SUM (successcount) from tbl_middlemt0411 where servicetype2=111
Select SUM (successcount), servicetype from tbl_middlemt0411 Group by servicetype

This article transferred from: http://www.cnblogs.com/jx270/archive/2013/03/10/2952584.html

Oracle Basic Syntax