1. Oracle startup and Shutdown
1. In a single-host environment
To start or shut down the Oracle system, you must first switch to the Oracle user, as shown below:
Su-oraclea: Start the Oracle System
Oracle> svrmgrl
Svrmgr> connect internal
Svrmgr> startup
Svrmgr> quitb. Disable the Oracle System
Oracle> svrmgrl
Svrmgr> connect internal
Svrmgr> Shutdown
Svrmgr> quit:
$ Sqlplus/nologsql * Plus: Release 9.2.0.1.0-production on Fri Oct 31 13:53:53 2003 copyright (c) 1982,200 2, Oracle Corporation. All Rights Reserved. SQL> connect/As sysdba
Connected to an idle instance.
SQL> startup ^ csql> startup
Oracle instance started. 2. In a dual-host environment
To start or shut down the Oracle system, you must first switch to the root user, as shown below:
Su-roota: Start the Oracle System
Hareg-y oracleb, disable ORACLE System
Which of the following startup methods are available for the Hareg-N oracleoracle database:
1. startup nomount
For non-installation startup, execute re-build the control file and re-build the database to read init. ora file, start the instance, that is, start the SGA and background processes. ora file. 2. startup Mount dbname
Install and start. In this mode, run:
Database Log archiving,
Database media recovery,
Online or offline data files,
Locate the data file and redo the log file. Execute "nomount", open the control file, and confirm the location of the data file and the on-line log file,
However, data files and log files are not verified at this time. 3. startup open dbname
Run "nomount" first, then "Mount", and then open all database files including the redo log file,
In this way, you can access data in the database. 4. startup, which is equal to the following three commands
Startup nomount
Alter database Mount
Alter database open5 and startup restrict
Constraint-based startup
This method can start the database, but only allow access by users with certain privileges
When a non-authorized user accesses the service, the following prompt is displayed:
Error:
ORA-01035: Oracle only allows users with restricted session permissions to use 6, startup force
Force start Mode
When the database cannot be closed, you can use startup force to close the database.
Shut down the database first, and then execute the normal startup DATABASE Command 7. startup pfile = parameter file name
Startup method with initialization parameter file
Read the parameter file first, and then start the database according to the settings in the parameter file.
Example: startup pfile = E: oracleadminoradbpfileinit. ora8, startup exclusive 2. How can users effectively use data dictionaries?
Oracle Data dictionary is an important part of a database. It is generated with the database and changes with the database changes,
It is reflected in some tables and Views under the Sys user. The data dictionary name is an uppercase English character. The data dictionary contains user information, user permission information, all data object information, table constraints, and views of the statistical analysis database.
We cannot manually modify the information in the data dictionary. Generally, oracle users do not know how to use it effectively. The name and interpretation of all data dictionary tables. It has a synonym, dict.
Dict_column field names and explanations in all data dictionary tables if you want to query the index-related data dictionary, you can use the following SQL statement: SQL> select * From dictionary where instr (comments, 'index')> 0; if you want to know the detailed meaning of each field name in the user_indexes table, you can use the following SQL statement: SQL> select column_name, comments from dict_columns where table_name = 'user _ indexes '; so far, you can easily know the detailed name and interpretation of the data dictionary without viewing other Oracle documents. The following describes how to use common data dictionaries for oracle users by category. 1. Check the default tablespace of 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 the system and table permissions of the current user
SQL> select * From user_sys_privs;
SQL> select * From user_tab_privs; 2. view all tables under the user
SQL> select * From user_tables; View tables whose names contain log characters
SQL> select object_name, object_id from user_objects
Where instr (object_name, 'log')> 0; 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 the table in the Oracle memory Partition
SQL> select table_name, cache from user_tables where instr (cache, 'y')> 0; 3. Check the number and category of indexes.
SQL> select index_name, index_type, table_name from user_indexes order by table_name; view the indexed Field
SQL> select * From user_ind_columns where index_name = upper ('& index_name'); view the index size
SQL> select sum (bytes)/(1024*1024) as "size (m)" from user_segments
Where segment_name = upper ('& index_name'); 4. view the serial number. last_number is the current value.
SQL> select * From user_sequences; 5. view name
SQL> select view_name from user_views; view the SELECT statement for creating a view
SQL> set view_name, text_length from user_views;
SQL> set long 2000; Description: You can set the size of set long based on the text_length value of the view.
SQL> select text from user_views where view_name = upper ('& view_name'); 6. View Synonyms
SQL> select * From user_synonyms; 7. View constraints of 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. view the status of functions and processes in stored 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 the source code of the function and Process
SQL> select text from all_source where owner = user and name = upper ('& plsql_name'); 3. view the database SQL
1. Check the tablespace name and size. 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 name and size of the tablespace physical file. 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 the control file select name from V $ controlfile; 5. view the log file select Member from V $ logfile; 6. view the table space usage 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. tablespace_name = B. tablespace_name and. tablespace_name = C. tablespace_name; 7. view database objects select owner, object_type, status, count (*) Count # From all_objects group by owner, object_type, status; 8. view the database version select version from product_component_version
Where substr (product,) = 'oracle '; 9. view the database creation date and archiving method select created, log_mode, log_mode from V $ database; 4. Administrator of the Oracle user connection management system. check whether there are several user connections in the current database: SQL> select username, Sid, serial # from V $ session; if you want to stop a connection, use SQL> alter system kill session 'sid, serial # '; if this command does not work, find its UNIX process count SQL> select pro. spid from V $ session SES, V $ process pro where SES. SID = 21 and SES. paddr = pro. ADDR; Note: 21 is the number of SID of a connection, and then kill the process number with the kill command. V. use SQL * Plus
A. Near-entry SQL * Plus
$ Sqlplus user name/password exit SQL * Plus
SQL> exitb. Get help information under sqlplus
List all SQL commands and SQL * Plus commands
SQL> help
Lists information about a specific command.
SQL> HELP command name c. display table structure command describe
SQL> edit command in DESC table name d and SQL * Plus
Display SQL Buffer commands
SQL> L modify SQL commands
First, change the row to be corrected to the current row.
SQL> N
Use the change command to modify the content
SQL> C/old/new
Confirm whether it is correct
SQL> l use the input command to add one or more rows in the SQL Buffer
SQL> I
SQL> enter Content E, call 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. In the login. SQL file, define the following line:
Define_editor = VIF. Run the command file
SQL> Start test
SQL> @ test common SQL * Plus statements
A. create, modify, and delete tables
The command format for creating a table is as follows:
Create Table Name (column description list). The command to add a new column to the base table is as follows:
Alter table table name Add (column description list)
For example, add an age column to the test table to store the age.
SQL> ALTER TABLE Test
Add (age Number (3); the command to modify the definition of the base table column is as follows:
Alter table table name
Modify (column name data type)
For example, the width of the count column in the test table is extended to 10 characters.
SQL> alter atble Test
Modify (county char (10); B. The statement format for deleting a table is as follows:
Dorp table name;
For example, deleting a table deletes both the table data and table definitions.
SQL> drop table testc, create and delete table spaces VI. Sh files backed up by Oracle logical backup. Sh files backed up completely: exp_comp.shrq = 'date + "% m % d" 'Su-Oracle-c "exp system/manager full = y inctype = complete file =/Oracle/export/db_comp $ RQ. DMP "accumulative backup sh file: exp_cumu.shrq = 'date + "% m % d" 'Su-Oracle-c "exp system/manager full = y inctype = cumulative file =/Oracle/export/db_cumu $ RQ. DMP "Incremental Backup sh file: exp_incr.shrq = '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.sh30 2 ** 0-5/Oracle/exp_incr.sh45 2 ** 6/Oracle/exp_cumu.sh of course, this schedule can be changed according to different requirements, this is just an example. VII. Common Oracle SQL syntax and Data Object 1. data Control Statement (DML) Part 1. insert (statement for inserting records 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; string field values must be enclosed in single quotes, for example, 'good Day'
If the field value contains single quotes and requires String Conversion, we replace it with two single quotes ''.
An error occurs when the value of a string type field exceeds the defined length. It is recommended that you perform length verification before insertion. The field value of the date field can be accurate to seconds using the system time sysdate of the current database.
Or use a string to convert to the date type function to_date ('2017-08-01 ', 'yyyy-MM-DD ')
To_date () has many date formats. For more information, see Oracle Doc.
Year-month-day hour: minute: Second format YYYY-MM-DD hh24: MI: ssinsert maximum operable string length less than or equal to 4000 single byte, if you want to insert a longer string, please consider using the clob type for fields,
To use the dbms_lob package. Insert in Oracle, if you want to use the serial number that automatically increases from 1, you should first create a serial number.
Create sequence serial number name (preferably table name + Serial number mark) increment by 1 start with 1
Maxvalue 99999 cycle nocache;
The maximum value is determined by the length of the field. If the defined auto-increment serial number (6), the maximum value is 999999.
Insert statement insert this field value: the name of the serial number. nextval2.delete (statement used to delete records in a data table) delete from table name where condition; Note: deleting a record does not release the occupied data block tablespace in Oracle. it only marks the deleted data blocks as unused. to delete all records in a large table, run the truncate command to release the occupied data block tablespace.
Truncate table name;
This operation cannot be rolled back. 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, the original record content will be cleared to null. It is best to perform non-null verification before modification;
An error occurs when the value of N exceeds the defined length. It is best to verify the length before insertion .. note:
A. The preceding SQL statements have row-level locks applied to tables,
After confirmation, you must add the Transaction Processing Command commit to take effect,
Otherwise, the changes may not be written into the database.
If you want to recall these operations, you can use the rollback command to restore. B. Before running the insert, delete, and update statements, it is best to estimate the range of records that may be operated,
It should be limited to a small range (10 thousand records). Otherwise, Oracle will use a large rollback segment to process this transaction.
Slow program response or even loss of response. If more than 100,000 of these operations are recorded, you can complete these SQL statements in multiple parts,
In the meantime, commit is added to confirm transaction processing. II. data Definition (DDL) Part 1. create (create a table, index, view, synonym, process, function, database link, etc.) Common Oracle field types include
Char fixed-length string
Varchar2 variable-length string
Number (m, n) numeric type M is the total length of digits, n is the length of decimal places
When creating a table of the date type, you must put a small field that is not empty in front of the field that may be empty in the back. When creating a table, you can use a Chinese field name, however, it is better to add the default value to the field when creating a table using the field name in English, for example, default sysdate.
In this way, when you insert and modify this field, you do not need to perform any program operation to obtain the action time. When creating a table, you can add constraints to the field.
For example, duplicate unique is not allowed, and the key word primary key2.alter (alter table, index, view, etc.) is used to change the table name.
Alter table table name 1 to table name 2; add a field after the table
Alter table table name add field Name Description; modify the field definition description in the table
Alter table table name Modify Field Name field Name Description; add constraints to fields in the table
Alter table table name Add constraint name primary key (field name );
Alter table table name Add constraint name unique (field name); put the table in or out the memory zone of the database
Alter table table name cache;
Alter table table name nocache; 3. Drop (delete table, index, view, synonym, process, function, database link, etc.) Delete the table and all its constraints
Drop table name cascade constraints; 4. truncate (clear all records in the table and keep the table structure) truncate table name; 3. select Statement (select) Part select field name 1, field name 2 ,...... From table name 1, [Table name 2,...] Where condition; field names can be included in Functions
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
Explanation:
If expr1 = NULL
Return expr2
Else
Return expr1decode (AA, V1, R1, V2, R2....) Function
Explanation:
If AA = V1 then return r1
If AA = v2 then return r2
.....
Else
Return nulllpad (char1, N, char2) Function
Explanation:
Character char1 is displayed based on the specified number of digits (N). When the number of digits is insufficient, the reserved field names on the Left can be replaced by the char2 string for arithmetic operation.
For example: (field name 1 * field name 1)/3 query statements can be nested
Example: select ...... From
(Select ...... From table name 1, [Table name 2,...] Where condition) Where condition 2; the results of two query statements can be set.
Example: Union (remove duplicate records), Union all (do not remove duplicate records), difference set minus, intersection intersect grouping Query
Select field name 1, field name 2 ,...... From table name 1, [Table name 2,...] Group by field name 1
[Having condition]; join query between two or more 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…] ; The position of a field with a (+) number automatically fills in the sorting operation of the null query result set. The default sorting is ASC in ascending order, descselect field name 1 in descending order, field name 2 ,...... From table name 1, [Table name 2,...]
Order by field name 1, field name 2 DESC; string fuzzy comparison method instr (field name, 'string')> 0
Field name like 'string % '[' % string % '] Each table has an implicit field rowid, which indicates the uniqueness of the record. 4. common Data Objects (schema) in Oracle 1. index create Index name on table name (Field 1, [Field 2,…] );
Alter Index name rebuild; It is recommended that there be no more than three indexes for a table (except for a special large table). It is best to use a single field index, combined with the analysis and execution of SQL statements,
You can also create a multi-field composite index and a function-based index oracle8.1.7. the maximum length of a string that can be indexed is 1578 bytes.
The maximum length of an oracle8.0.6 string that can be indexed is 758 bytes. 2. View (View) Create view name as select .... From .....;
Alter view name: compile; view is only an SQL query statement, which can simplify the complex relationship between tables. 3. Synonym (synonmy)
Create synonym name for table name;
Create synonym name for table name @ Database Link name; 4. Database Link)
Create database link database connection name connect to user name identified by password using 'database connection string'; database connection string can be net8 easy config or tnsnames can be directly modified.. when the database parameter global_name is set to true, the Database Link name must be the same as the remote database name. You can run the following command to check the global name of the database:
Select * From global_name; query the tables in the remote database
Select ...... From table name @ Database Link name; 5. permission management (DCL) Statement 1. Grant Permissions
Common system permission sets include the following:
Connect (basic connection), Resource (Program Development), DBA (Database Management)
Common Data Objects have the following permissions:
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 username 1, username 2; 2. Revoke reclaim permission revoke connect, resource from username;
Revoke select on table name from user name;
Revoke select, insert, delete on table name from username 1, username 2; error 63rd in database query:
Select orgaddr, destaddr from sm_histable0116 where error_code = '63'; query the maximum submission and delivery numbers of account users in the database: Select msisdn, TCOs, ocos from ms_usertable; query the total number of error codes in the database:
Select error_code, count (*) from sm_histable0513 group by error_code order
By error_code; query the statement statistical types in the report database.
Select sum (successcount) from tbl_middlemt0411 where servicetype2 = 111
Select sum (successcount), servicetype from tbl_middlemt0411 group by servicetype
