Common ORACLE commands and oracle commands

Common ORACLE commands and oracle commands

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-oracle

A. Start the ORACLE System
Oracle> svrmgrl
SVRMGR> connectinternal
SVRMGR> startup
SVRMGR> quit

B. Disable the ORACLE system.
Oracle> svrmgrl
SVRMGR> connectinternal
SVRMGR> shutdown
SVRMGR> quit

Run the following command to start the oracle9i database:
$ Sqlplus/nolog

SQL * Plus: Release9.2.0.1.0-ProductiononFriOct3113: 53: 532003

Copyright (c) 1982,2002, OracleCorporation. Allrightsreserved.

SQL> connect/assysdba
Connectedtoanidleinstance.
SQL> startup ^ C

SQL> startup
ORACLEinstancestarted.

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-root

A. Start the ORACLE System
Hareg-yoracle

B. Disable the ORACLE system.
Hareg-noracle

Oracle Database startup Methods

Note:

There are several startup methods:
1. startupnomount
Non-installation startup. In this mode, you can execute: re-build the control file and re-build the database.

Read the init. ora file and start the instance, that is, start the SGA and background processes. To start the process, you only need the init. ora file.

2. startupmountdbname
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. startupopendbname
Run "nomount" first, then "mount", and then open all database files including Redolog files,
In this way, you can access data in the database.

4. startup, which is equal to the following three commands
Startupnomount
Alterdatabasemount
Alterdatabaseopen

5. startuprestrict
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 RESTRICTEDSESSION Permissions

6. startupforce
Force start Mode
When the database cannot be closed, you can use startupforce to close the database.
Shut down the database first, and then execute the normal database startup command

7. startuppfile = 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: startuppfile = E: Oracleadminoradbpfileinit. ora

8. startupEXCLUSIVE
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

To query index-related data dictionaries, use the following SQL statement:

SQL> select * fromdictionarywhereinstr (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> selectcolumn_name, commentsfromdict_columnswheretable_name = 'user _ INDEXES ';

You can easily know the detailed name and explanation of the data dictionary without viewing other ORACLE documents.

The following describes how to use common data dictionaries for ORACLE users by category.

1. User

View the default tablespace of the current user
SQL> selectusername, default_tablespacefromuser_users;

View the role of the current user
SQL> select * fromuser_role_privs;

View the system and table-level permissions of the current user
SQL> select * fromuser_sys_privs;
SQL> select * fromuser_tab_privs;

2. Tables

View All tables under a user
SQL> select * fromuser_tables;

View tables whose names contain log characters
SQL> selectobject_name, object_idfromuser_objects
Whereinstr (object_name, 'log')> 0;

View the creation time of a table
SQL> selectobject_name, createdfromuser_objectswhereobject_name = upper ('& table_name ');

View the size of a table
SQL> selectsum (bytes)/(1024*1024) as "size (M)" fromuser_segments
Wheresegment_name = upper ('& table_name ');

View the table in the ORACLE memory Partition
SQL> selecttable_name, cachefromuser_tableswhereinstr (cache, 'y')> 0;

3. Index

View the number and category of Indexes
SQL> selectindex_name, index_type, table_namefromuser_indexesorderbytable_name;

View indexed fields
SQL> select * fromuser_ind_columnswhereindex_name = upper ('& index_name ');

View index size
SQL> selectsum (bytes)/(1024*1024) as "size (M)" fromuser_segments
Wheresegment_name = upper ('& index_name ');

4. Serial number

View the serial number. last_number is the current value.
SQL> select * fromuser_sequences;

5. View

View view name
SQL> selectview_namefromuser_views;

View the select statement for creating a view
SQL> setview_name, text_lengthfromuser_views;
SQL> setlong2000; Description: You can set the size of setlong Based on the text_length value of the view.
SQL> selecttextfromuser_viewswhereview_name = upper ('& view_name ');

6. Synonyms

View synonym name
SQL> select * fromuser_synonyms;

7. Constraints

View the constraints of a table
SQL> selectconstraint_name, constraint_type, search_condition, r_constraint_name
Fromuser_constraintswheretable_name = upper ('& table_name ');

SQL> selectc. constraint_name, c. constraint_type, cc. column_name
Fromuser_constraintsc, user_cons_columnscc
Wherec. owner = upper ('& table_owner') andc. table_name = upper ('& table_name ')
Andc. owner = cc. ownerandc. constraint_name = cc. constraint_name
Orderbycc. position;

8. storage functions and processes

View the status of functions and processes
SQL> selectobject_name, statusfromuser_objectswhereobject_type = 'function ';
SQL> selectobject_name, statusfromuser_objectswhereobject_type = 'processed ';

View the source code of functions and processes
SQL> selecttextfromall_sourcewhereowner = userandname = upper ('& plsql_name ');

Iii. View Database SQL
1. view the table space name and size.

Selectt. tablespace_name, round (sum (bytes/(1024*1024), 0) ts_size
Fromdba_tablespacest, dba_data_filesd
Wheret. tablespace_name = d. tablespace_name
Groupbyt. tablespace_name;

2. view the name and size of the tablespace physical file.

Selecttablespace_name, file_id, file_name,
Round (bytes/(1024*1024), 0) total_space
Fromdba_data_files
Orderbytablespace_name;

3. Check the rollback segment name and size.

Selectsegment_name, tablespace_name, r. status,
(Initial_extent/1024) InitialExtent, (next_extent/1024) NextExtent,
Max_extents, v. curextCurExtent
Fromdba_rollback_segsr, v $ rollstatv
Wherer. segment_id = v. usn (+)
Orderbysegment_name;

4. View Control Files

Selectnamefromv $ controlfile;

5. view log files

Selectmemberfromv $ logfile;

6. View table space usage

Selectsum (bytes)/(1024*1024) asfree_space, tablespace_name
Fromdba_free_space
Groupbytablespace_name;

SELECTA. TABLESPACE_NAME, A. BYTESTOTAL, B. BYTESUSED, C. BYTESFREE,
(B. BYTES * 100)/A. BYTES "% USED", (C. BYTES * 100)/A. BYTES "% FREE"
FROMSYS. SM $ TS_AVAILA, SYS. SM $ TS_USEDB, SYS. SM $ TS_FREEC
WHEREA. TABLESPACE_NAME = B. TABLESPACE_NAMEANDA.TABLESPACE_NAME = C. TABLESPACE_NAME;

7. view database objects

Selectowner, object_type, status, count (*) count # fromall_objectsgroupbyowner, object_type, status;

8. view the database version

SelectversionFROMProduct_component_version
WhereSUBSTR (PRODUCT, 1, 6) = 'oracle ';

9. view the database creation date and archiving method

SelectCreated, Log_Mode, Log_ModeFromV $ Database;
Iv. ORACLE user connection management

Use the system administrator to check whether the current database has several user connections:

SQL> selectusername, sid, serial # fromv $ session;

If you want to stop a connection

SQL> altersystemkillsession 'sid, serial #';

If this command does not work, find the number of UNIX processes.

SQL> selectpro. spidfromv $ sessionses, v $ processprowhereses. sid = 21andses. paddr = pro. addr;

Note: 21 is the number of sid of a connection.

Then run the kill command to kill the process number.

V. use SQL * PLUS
A. Near-entry 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
Lists information about a specific command.
SQL> help command name

C. DESCRIBE
SQL> DESC table name

D. Edit commands in 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

You can use the INPUT command to add one or more rows in the SQL Buffer.
SQL> I
SQL> input 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 = vi

F. Run the command file
SQL> STARTtest
SQL> @ test

Common SQL * Plus statements
A. create, modify, and delete tables
The command format for creating a table is as follows:
Createtable table name (column description list );

The command to add a new column to the base table is as follows:
ALTERTABLE table name ADD (column description list)
For example, add an Age column to the test table to store the Age.
SQL> altertabletest
Add (Agenumber (3 ));

The command to modify the definition of a base table column is as follows:
ALTERTABLE 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> alteratbletest
Modify (Countychar (10 ));

B. The statement format for deleting a table is as follows:
DORPTABLE table name;
For example, deleting a table deletes both the table data and table definitions.
SQL> droptabletest

C. Create and delete tablespaces

Vi. SH file for ORACLE logical backup

Full backup SH file: exp_comp.sh

Rq = 'date + "% m % d "'

Su-oracle-c "expsystem/managerfull = yinctype = completefile =/oracle/export/db_comp $ rq. dmp"

Cumulative backup SH file: exp_cumu.sh

Rq = 'date + "% m % d "'

Su-oracle-c "expsystem/managerfull = yinctype = cumulativefile =/oracle/export/db_cumu $ rq. dmp"

SH file for Incremental Backup: exp_incr.sh

Rq = 'date + "% m % d "'

Su-oracle-c "expsystem/managerfull = yinctype = incrementalfile =/oracle/export/db_incr $ rq. dmp"

Root User crontab file
/Var/spool/cron/crontabs/root Add the following content

021 **/oracle/exp_comp.sh

302 ** 0-5/oracle/exp_incr.sh

452 ** 6/oracle/exp_cumu.sh

Of course, this timetable can be changed according to different requirements. This is just an example.

VII. Common SQL syntax and Data Objects in ORACLE

I. Data Control Statement (DML)

1. INSERT (INSERT the record statement into the data table)

INSERTINTO table name (field name 1, field name 2 ,......) VALUES (value 1, value 2 ,......);
INSERTINTO table name (field name 1, field name 2 ,......) SELECT (field name 1, field name 2 ,......) FROM another table name;

Field Values of the string type must be enclosed in single quotes, for example, 'goodday'
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 best to verify the length before insertion.

The field value of the date field can use the system time SYSDATE of the current database, accurate to seconds
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 ORACLEDOC.
Year-month-day hour: minute: Second format YYYY-MM-DDHH24: MI: SS

The maximum length of a string that can be operated during INSERT is less than or equal to 4000 single bytes. To INSERT a longer string, use the CLOB type for the field,
Use the DBMS_LOB package that comes with ORACLE.

If you want to use a serial number that automatically increases from 1 during INSERT, you should first create a serial number
CREATESEQUENCE serial number name (preferably table name + Serial number mark) INCREMENTBY1STARTWITH1
MAXVALUE99999CYCLENOCACHE;
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: name of the serial number. NEXTVAL

2. DELETE (DELETE the statements recorded in the data table)

WHERE efrom 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.
TRUNCATETABLE table name;
This operation cannot be rolled back.

3. UPDATE (modify the statements 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 a value 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.
To recall these operations, run the ROLLBACK command to restore them.

B. Estimate the range of records that may be operated before running INSERT, DELETE, and UPDATE statements,
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)

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
DATE type

When creating a table, you need to put a small field that is not empty in front of the field that may be empty in the back

You can use a Chinese field name when creating a table, but it is best to use an English field name.

When creating a table, you can add the default value to the field, such as defasyssysdate.
In this way, the operation time can be obtained every time you insert or modify this field without any program operation.

When creating a table, you can add constraints to fields.
For example, duplicate UNIQUE is not allowed, and the key is PRIMARYKEY.

2. ALTER (change tables, indexes, views, etc)

Change table name
ALTERTABLE table name 1TO table name 2;

Add a field to the end of the table.
ALTERTABLE table name ADD field Name field name description;

Modify the definition description of fields in the table
ALTERTABLE table name MODIFY Field Name field name description;

Add constraints to fields in the table
ALTERTABLE table name ADDCONSTRAINT constraint name PRIMARYKEY (field name );
ALTERTABLE table name ADDCONSTRAINT constraint name UNIQUE (field name );

Place the table in or out the memory area of the database
ALTERTABLE table name CACHE;
ALTERTABLE table name NOCACHE;

3. DROP (Delete tables, indexes, views, synonyms, processes, functions, database links, etc)

Delete a table and all its constraints
DROPTABLE table name CASCADECONSTRAINTS;

4. TRUNCATE (clear all records in the table and keep the table structure)

TRUNCATE table name;

Iii. SELECT

SELECT field name 1, field name 2 ,...... FROM table name 1, [Table name 2,...] WHERE condition;

Field names can be imported into functions.
For example, COUNT (*), MIN (field name), MAX (field name), AVG (field name), DISTINCT (field name ),
TO_CHAR (DATE Field name, 'yyyy-MM-DDHH24: MI: ss ')

NVL (EXPR1, EXPR2) Function
Explanation:
IFEXPR1 = NULL
RETURNEXPR2
ELSE
RETURNEXPR1

DECODE (AA, V1, R1, V2, R2....) Function
Explanation:
IFAA = V1THENRETURNR1
IFAA = V2THENRETURNR2
.....
ELSE
RETURNNULL

LPAD (char1, n, char2) Functions
Explanation:
Character char1 is displayed based on the specified number of digits n. The reserved number of digits is replaced by the reserved number on the left using the char2 string.

Arithmetic Operations can be performed between Field Names
Example: (field name 1 * field name 1)/3

The query statement 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 (do not remove duplicate records), difference set MINUS, intersection INTERSECT

Group Query
SELECT field name 1, field name 2 ,...... FROM table name 1, [Table name 2,...] GROUPBY field name 1
[HAVING condition];

Query connections 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 is automatically null.

Sort the query result set. The default sorting is ASC in ascending order and DESC in descending order.

SELECT field name 1, field name 2 ,...... FROM table name 1, [Table name 2,...]
ORDERBY field name 1, field name 2 DESC;

Fuzzy string comparison

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)

CREATEINDEX index name ON table name (Field 1, [Field 2,…]);
ALTERINDEX index name REBUILD;

It is recommended that a table have no more than three indexes (except for a special large table). It is recommended that you use a single-field index to analyze the 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)

CREATEVIEW view name ASSELECT .... FROM .....;
ALTERVIEW name: COMPILE;

A view is only an SQL query statement that simplifies the complex relationships between tables.

3. SYNONMY)
CREATESYNONYM synonym name FOR table name;
CREATESYNONYM synonym name FOR table name @ Database Link name;

4. Database Connection (DATABASELINK)
CREATEDATABASELINK database connection name CONNECTTO username IDENTIFIEDBY password USING 'database connection string ';

You can use NET8EASYCONFIG to change the database connection string or directly modify the definition in TNSNAMES. ORA.

When the database parameter global_name = true, the Database Link name must be the same as the remote database name.

You can use the following command to check the global name of the database:
SELECT * FROMGLOBAL_NAME;

Query tables in a remote database
SELECT ...... FROM table name @ Database Link name;

V. permission management (DCL) Statements

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:
ALLON Data Object Name, SELECTON Data Object Name, UPDATEON Data Object Name,
DELETEON Data Object Name, INSERTON Data Object Name, ALTERON Data Object Name

GRANTCONNECT, RESOURCETO user name;
GRANTSELECTON table name TO user name;
GRANTSELECT, INSERT, DELETEON table name TO username 1, username 2;

2. REVOKE permissions from REVOKE

REVOKECONNECT, RESOURCEFROM user name;
REVOKESELECTON table name FROM user name;
REVOKESELECT, INSERT, DELETEON table name FROM username 1, username 2;

Error 63rd in database query:
Selectorgaddr, destaddrfromsm_histable01_whereerror_code = '63 ';

Query the maximum number of submissions and outgoing mails of the account owner in the database: selectMSISDN, TCOS, OCOSfromms_usertable;

Query the total number of error codes in the database:
Selecterror_code, count (*) fromsm_histable0513groupbyerror_codeorder
Byerror_code;

Query the statement statistical types in the report database.
Selectsum (Successcount) fromtbl_MiddleMt0411whereServiceType2 = 111
Selectsum (successcount), servicetypefromtbl_middlemt0411groupbyservicetype

Common commands for oracle databases

1. su-oracle is not required. It is suitable for use without a DBA password. You do not need a password to enter the sqlplus interface.
2. sqlplus/nolog, sqlplus system/manager, or./sqlplus system/manager @ ora9i;
3. SQL> connect/as sysdba; (as sysoper) or
Connect internal/oracle as sysdba; (scott/tiger)
Conn sys/change_on_install as sysdba;
4. SQL> startup; start the database instance
5. view all current databases: select * from v $ database;
Select name from v $ database;
Desc v $ databases; view database structure Fields
7. How to check which users have SYSDBA and SYSOPER permissions:
SQL> select * from V _ $ PWFILE_USERS;
Show user; view the current database connection user
8. Enter the test database: database test;
9. View All database instances: select * from v $ instance;
For example, ora9i
10. view all data tables of the current database:
SQL> select TABLE_NAME from all_tables;
Select * from all_tables;
SQL> select table_name from all_tables where table_name like 'U ';
TABLE_NAME --------------- default_auditing_options
11. view the table structure: desc all_tables;
12. display the structure of all fields of CQI. T_BBS_XUSER:
Desc CQI. T_BBS_XUSER;
13. Obtain records in the CQI. T_BBS_XUSER table:
Select * from CQI. T_BBS_XUSER;
14. Add database users: (test11/test)
Create user test11 identified by test default tablespace users Temporary TABLESPACE Temp;
15. user authorization:
Grant connect, resource, dba to test11;
Grant sysdba to test11;
Commit;
16. change the password of the Database User: (change the password of sys and system to test .)
Alter user sys indentified by test;
Alter user system indentified by test;
 
Common oracle commands

Baike.baidu.com/view/1239908.htm

SQL Plus commands are almost all on the above page.

Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mysql |
| Sqldoc |
| Test |
+ -------------------- +
4 rows in set (0.00 sec)

An Oracle instance is a database, so there is no corresponding show databases statement

Mysql> show tables
->;
+ ---------------- +
| Tables_in_test |
+ ---------------- +
| Sale_report |
| Test_dysql |
| Union_tab_1 |
| Union_tab_2 |
| V_sale_report |
+ ---------------- +
5 rows in set (0.00 sec)

Oracle uses the following statement to implement

SQL> SELECT
2 table_name
3 FROM
4 all_tables
5 WHERE
6 ROWNUM <10;

TABLE_NAME
------------------------------------------------------------
ICOL $
CON $
UNDO $
PROXY_ROLE_DATA $
FILE $
UET $
IND $
SEG $
COL $

9 rows selected.

Mysql> desc sale_report;
+ ------------ + --------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------------ + --------------- + ------ + ----- + --------- + ------- +
| SALE_DATE | datetime | NO | NULL |
| SALE_ITEM | varchar (2) | NO | NULL |
| SALE_MONEY | decimal (10, 2) | NO | NULL |
+ ------------ + --------------- + ------ + ----- + --------- + ------- +
3 rows in set (0.00 sec)

Oracle

SQL> desc all_tab_columns
Name ...... remaining full text>