Oracle learning knowledge point (1)

One login sqlplus
Sqlplus username/password @ database instance as login role;
For example, if user SYS (password: 123) logs on to the database as sysdba, you can enter sqlplus sys/123 @ l as sysdba;
This logon method directly exposes the password. If you want to hide the password, you can omit the password input here, for example, sqlplus sys @ l as sysdba. After you press enter, Oracle will prompt you to enter the password.
If you want to switch to another user after logon, you can directly use the connect command, such as connect user2/password @ l as sysdba. Similarly, you can separate the passwords.

Four Common query statements for paging and sorting in Oracle
1. query the first 10 records

Select * From testtable where rownum <= 10

2. query 11th to 20th records

Select * from (select testtable. *, rownum Ro from testtable where rownum <= 20) where Ro> 10

3. The first 10 records sorted by name field in ascending order

Select * from (select * From testtable ordery by name ASC) Where rownum <= 10

4. 11th to 20th records sorted by name field in ascending order

Select * from (select TT. *, rownum Ro from (select * From testtable order by name ASC) TT where rownum <= 20) where Ro> 10
Ii. Table operations
1. Modify the data type or column name of a column (data is not allowed)
SQL> ALTER TABLE table name modify (column name and column data type );
2. Add a field
SQL> ALTER TABLE table name Add (column name column data type );
3. delete a field
SQL> ALTER TABLE Table Name drop column name;
4. Modify the table name
SQL> rename old table name to new table name;
5. Insert Date Field
The default format in Oracle is "DD-mm-yy", for example, '20-August 7-87 '. The month must be Chinese characters;
Change the default format of the date: Alter session nls_date_format = 'yyyy-mm-dd ';
6. Insert null values
Insert into student (XH, XM, sex, birthday) values ('20140901', 'xiaoming ', null)
Select * from student where birthday is (not) null when querying;
7. Modify NULL data
Update student set sex = 'female 'Where birthday is null;
8. delete data
Delete from Table Name: delete all records. The table structure is still in progress, logs can be written, and the deletion speed is slow;
Drop table name; Delete table structure and data;
Truncate Table Name: delete all records. The table structure is still in progress and no logs are written. The deleted records cannot be retrieved, which is fast.
Iii. Complex Table query
1. Summary of group queries
A. Grouping functions can only appear in the selection list, having, and order by clauses.
B. If select statements contain group by, having, and order by statements, their order is group by, having, and order.
C. If there are columns, expressions, and grouping functions in the selection column, one of these columns and expressions must appear in the group by clause; otherwise, an error occurs. For example, select deptno, AVG (SAL), max (SAL) from EMP group by deptno having AVG (SAL) <2000; here, deptno must appear in group.
2. subquery
Single Row subquery: returns the subquery Statement of only one row of data.
Multi-row subquery: A subquery that returns multi-row data.
The SQL statement is scanned from right to left. The rightmost first scan.
Use the all operator in multi-row subqueries
Select ename, Sal, DEPT from EMP where SAL> All (select Sal from EMP where deptno = 0 );
In multi-row subqueries, use the any operator select ename, Sal, DEPT from EMP where SAL> Any (select Sal from EMP where deptno = 0 );
Multi-column subquery is a subquery that returns data from multiple columns.
3. Use subquery in from
It should be noted that when a subquery is used in the from clause, the subquery will be treated as a view. Therefore, it is called an embedded view, when using a subquery in the from clause, you must specify an alias for the subquery. the alias for the table name cannot be added.
4. Paging Query
There are three paging methods for Oracle
Retrieve by employee ID in ascending order
1) rownum Paging
Select * from (select A1. *, rownum rn from (select * from EMP) A1 where rownum <= 10) Where rn> = 6;
2) by Analysis Function
Select * from (select T. *, row_number () over (order by cid desc) rk from
T_xiaoxi t) Where rk <10000 and rk> 9980;
3. Divide by rowid
Select * From t_xiaoxi where rowid in (select rid from (select rownum RN, rid from (select rowid RID, CID from t_xiaoxi order by cid desc) Where
Rownum <10000) Where rn> 9980 order by cid desc;
Among them, 1 is the best, 3 is the second, and 2 is the worst.
5. Create a new table using the query results
Create Table mytable (ID, name, Sal, job, deptno)
As select empno, ename, Sal, job, deptno from EMP;
6. Merge Query
Sometimes, in practice, the Union, Union all, intersect, and minus operators can be used to merge the results of multiple select statements.
1) The union operation is used to obtain the union of two result sets. When this operator is used, repeated rows in the result are automatically removed;
2) Union all
This operation is similar to union, but it does not cancel duplicate rows and does not sort.
3) intersect uses this operator to obtain the intersection of two result sets.
4) minus uses this operation to obtain the difference set of two result sets. It is only displayed in the first set, but does not exist in the second set.
Iv. Database Operations
1. Use subqueries to insert a large amount of data
When a values clause is used, a subquery can insert a row of data. When a subquery is used to insert data, an insert statement can insert a large amount of data, when processing row migration or loading external table data to the database, you can use subqueries to insert data.
2. When using the update statement to update data, you can use an expression or data to directly modify the data, or use a subquery to modify the data.
Update EMP set (job, Sal, comm) = (select job, Sal, comm from EMP where ename = 'Smith ') Where ename = 'Scott'
V. Transactions
A transaction is used to ensure data consistency. It consists of a group of related DML statements. The DML statements in this group are either all successful or all failed.
6. Database Administrator
1. Basic functions of the Oracle administrator:
1) install and upgrade the Oracle database
2) database creation, tablespace, table, view, and index
3) Develop and implement backup and recovery plans
4) database permission management, tuning, and troubleshooting
5) for senior DBAs, they must be able to participate in project development and write stored procedures, triggers, rules, constraints, and packages.
2. Database Administrator
1) The difference between sys and system is that the importance of data storage is different.
SYS: The base tables and views of all ORACLE data dictionaries are stored in SYS users. These base tables and views are crucial for Oracle operation and are maintained by the database, no user can change it manually. The sys user has the role or permission of DBA, sysdba, and sysoper, and is the user with the highest Oracle permissions.
System: used to store secondary internal data, such as the Management Information of Oracle features or tools. system users have dBA, sysdba roles, or system permissions.
2) The difference is that permissions are different.
Sys users must Log On As sysdba or as sysoper, and cannot log on to the database as normal.
If the system is logged on normally, it is actually a common DBA user, but if it is logged on as sysdba, the result is actually logged on as sys user, we can see from the login information.
DBA users refer to databases with DBA perspectives. Privileged users can perform special operations such as starting and disabling instances. DBA users can only perform various management work after the database instance is started.
3. Manage initialization parameters
1) how to modify parameters
To modify the initialization parameters, go to the file path... Modify it in the/Oracle/admin/myoral/pfile/init. ora file. To modify the name of an instance
4. logical and physical backup and recovery of databases
Logical backup refers to the process of exporting the structure and data of data objects to a file using the export tool, logical recovery refers to the process of using the import tool to import database objects to the database when database objects are damaged due to misoperation.
Physical backup can be performed when the database is open or after the database is closed, but logical backup and recovery can only be performed when the database is open.
1) Export
You can export tables, export schemes, and export databases.
When importing and exporting data, go to the bin directory of the Oracle directory (execute the exp command to enter the exp under this path in the CMD command mode)
Exp command description:
A) export your own table: EXP userid = Scott/tiger @ orcl tables = (EMP, DEPT...) file = D:/a. dmp; (storage path)
B) Export tables of other solutions: this user must have DBA permissions or exp_full_database permissions.
Exp userid = system/manager @ orcl tables = (Scott. EMP, Scott. Dept) file = D:/B. dmp; (storage path)
Tables
Owner: used to specify the scheme for executing the export operation full = Y: used to specify the scheme for executing the export operation.
Inctype: Specifies the incremental type of the export operation.
Rows: used to specify whether to export data in the table during the export operation.
File: Specifies the exported file name.
Export table structure: EXP userid = Scott/tiger @ orcl tables = (EMP) file = D:/C. dmp rows = N
Use Direct Export
Exp userid = Scott/tiger @ orcl tables = (EMP) file = D:/d. DMP direct = y is faster than the conventional method by default. When the selected data volume is large, you can consider using this method (in this case, the character set of the database must be exactly the same as that of the client, otherwise, an error is reported ..)
Export Scheme: Use the export tool to export all objects and data in one or more schemes and store them in files.
C) Export your own solution
Exp Scott/tiger @ orcl owner = Scott file = D:/Scott. dmp;
D) export other schemes: If you want to export other schemes, you need DBA or exp_full_database permissions, for example:
Exp system/manager @ orcl owner = (system, Scott) file = D:/system. dmp;
E) export the database
This user is required to have DBA or exp_full_database Permissions
Exp userid = system/manager @ orcl full = y inctype = complete file = D:/X. dmp;
2) Import
A) import your own table
IMP userid = Scott/tiger @ orcl tables = (Dept, EMP) file = D:/XX. dmp;
B) import the table to other users.
This user is required to have DBA permissions or imp_full_database
IMP userid = system/manager @ orcl tables = (Dept, EMP) file = D:/XX. dmp touser = Scott
C) Structure of the import table
Only import the table structure without importing data
IMP userid = Scott/tiger @ rocl tables = (EMP) file = D:/XX. dmp rows = N;
D) import data
If the object already exists, you can only import table data.
IMP userid = Scott/tiger @ orcl tables = (EMP) file = D:/XX. dmp ignore = y;
4. Import Solution
A) import your own solution
IMP userid = Scott/Tigger @ orcl file = D:/XX. dmp;
B) import other solutions
This user is required to have DBA Permissions
IMP userid = system/manager file = D:/xxx. dmp fromuser = system touser = Scott;
5. Import Database
By default, all object structures and data will be imported during data import.
IMP userid = system/manager full = y file = D:/xxx. dmp;
V. Data Dictionary and view
1. Data Dictionary
The data dictionary records the system information of the database. It is a collection of read-only tables and views. The owner of the data dictionary is the Sys user.
Users can only perform query operations on the data dictionary, and maintenance and modification are automatically completed by the system.
The data dictionary includes the data dictionary base table and data dictionary view. The base table stores the basic information of the database. The data dictionary view is a view created based on the data dictionary base table, common users can obtain system information by querying the data dictionary view. The data dictionary includes three types: user_xxx, all_xxx, and dba_xxx.
2. Dynamic Performance View
User_tables: used to display all tables owned by the current user. It only returns the table of the user's corresponding solution.
All_tables: displays all tables accessible to the current user. It not only returns all tables of the current user's solution, but also returns tables of other schemes accessible to the current user.
Dba_tables: displays the database tables owned by all solutions. However, to query this database dictionary view, you must be a DBA role or have select any table system permissions.
Vi. User Name, permission, and role
1. When a user is created, Oracle stores the user information in the data dictionary. When a user is granted the permission or role, Oracle stores the permission and role information in the data dictionary.
By querying dba_users, you can view detailed information of all database users;
You can query dba_sys_privs to view the system permissions of a user;
You can query dba_tab_privs to display the object permissions of a user;
You can query dba_col_privs to view the permissions of columns owned by the user.
You can query dba_role_privs to display the role of the user.
2. Relationship between roles and permissions
A role contains one or more permissions. The permissions are divided into two types: system permissions and object permissions.
How many roles does oracle have:
Select * From dba_roles;
How many permissions does a role have?
A) system permissions contained in a role
Select * From dba_sys_privs where grantee = 'connection' (role name)
You can also view
Select * From role_sys_privs where role = 'connect '(role name)
B) object permissions contained in a role
Select * From dba_tab_privs where grantee = 'connection' (role name)
Queries the number of system permissions in the Oracle System
Select * From system_privilege_map order by name;
Query the permissions of objects in the Oracle System
Select distinct privilege from dba_tab_privs;
If you want to see what role a user has?
Select * From dba_role_privs where grantee = 'username ';
3. display all data dictionary views accessible to the current user
Select * From dict where comments like '% grant % ';
Display the full name of the current database
Select * From global_name;
4. Other Instructions
The data dictionary records all system information of the Oracle database. You can obtain the following system information by querying the data dictionary:
Object definition, space occupied by the object, column information, and constraint information...
To obtain this information, you can query it using the PL/SQL developer tool.
Dynamic Performance view: used to record the activity information of the current routine. When ORACLE Server is started, the system will create a dynamic performance view. When ORACLE Server is stopped, the system will delete the dynamic performance view. All the dynamic performance views of Oracle start with V _ $, and Oracle provides a synonym for each dynamic performance view, and the synonym starts with V $, for example, the synonym for V _ $ datafile is V $ datafile, and the owner of the dynamic performance view is sys. Generally, DBA or privileged users can query the dynamic performance view.

Vi. Table space and data files
Tablespaces are logical components of databases. Physically, data is stored in data files. Logically, databases are stored in tablespaces, a tablespace consists of one or more data files;
The logical structure of Oracle includes tablespaces, segments, partitions, and blocks.
The database is composed of tablespaces, while the tablespace is composed of segments, and the segments are composed of partitions. the partitions are composed of Oracle blocks, which can improve the efficiency of the database.
Table space can serve the following purposes:
1) control the disk space occupied by the database
2) DBA can deploy different data types to different locations, which improves I/O performance and facilitates management operations such as backup and recovery.
Create a tablespace
Create tablespace AA (tablespace name) datafile 'd:/test. dbf' (tablespace storage path) Size 20 m (tablespace size) uniform size 128 K (partition size)
Use tablespace
Create Table mypart (deptno number (4), dname varchar2 (14) tablespace AA;
Change the tablespace status
When a tablespace is created, the tablespace is online. In this case, the tablespace is accessible and can be read and written. That is, the tablespace data can be queried, in addition, you can execute various statements in the tablespace. However, during system maintenance or data maintenance, you may need to change the tablespace status. Generally, the operation is performed by a privileged user or DBA.
1) take the tablespace offline
Alter tablespace name offline;
2) Bring the tablespace online
Alter tablespace name online;
3) read-only tablespace
When a tablespace is created, the tablespace can be read and written. If you do not want to perform the update, delete, and insert operations on the tablespace, you can change the tablespace to read-only.
Alter tablespace query_data read only;
4. Know the tablespace name and display all tables included in the tablespace.
Select * From all_table where tablespace_name = 'tablespace name'
5. Know the table name and view which tablespace the table belongs.
Select tablespace_name, table_name from user_tables where table_name = 'table name ';
6. delete a tablespace
Generally, the operation is performed by a privileged user or DBA. If the operation is performed by another user, the user must have the system permission to drop tablespace.
Drop tablespace 'tablespaces 'including contents and datafiles;
Note: Including contents indicates that all database objects in the tablespace are deleted when the tablespace is deleted, while datafiles indicates that the database files are also deleted;
7. Extended tablespace
There are three ways to add more storage space for it:
1) add data files
Alter tablespace name Add datafile 'd:/BB. dbf' size 20 m;
2) increase the data file size
Alter tablespace name 'd:/B. dbf' resize 20 m;
Note that the data file size should not exceed 500 mb.
3) set Automatic File Growth
Alter tablespace name 'd:/B. dbf'autoextend on next 10 m maxsize 500 m;
8. Move data files
1) determine the tablespace where the data file is located
Select tablespace_name from dba_data_files where file_name = 'd:/yy. DBF ';
2) use tablespace offline
Ensure data file consistency and change the tablespace to Offline state.
Alter tablespace YY offline;
3) use commands to move data files to the specified target location
Host move D:/yy. dbf c:/yy. DBF;
4) execute alter tablespace
After physically moving data, you must execute the alter tablespace command to modify the logic of the database file.
Alter tablespace YY rename datafile 'd:/yy. DBF 'to 'C:/yy. dbf ';
5) Bring the tablespace online
After a data file is moved, you must change it to the online State alter tablespace YY on line to make it accessible.
9. query related tablespace Information
1) display tablespace Information
Select tablespace_name from dba_tablespaces;
2) display the data files contained in the tablespace
Select file_name, bytes from dba_data_files where tablespace_name = 'tablespace name'
7. maintain data integrity
Data integrity can be achieved using three methods: Constraints, triggers, and applications (processes and functions ).
Among the three methods, constraints are easy to maintain and have the best performance, so they are the first choice to maintain data integrity.
Constraints include: not null (if the column defines not null, data must be provided for the column when data is inserted ), unique (this column value cannot be repeated but can be null), primary key (a table can only be one primary key but can have multiple unqiue constraints, this column cannot be repeated and cannot be null), foreign key (defines the relationship between the master table and the slave table, after the foreign key constraint is defined, the foreign key column data must exist in the primary table's key column or be null. Check (conditions required for mandatory row data)
If you forget to create necessary constraints when creating a table, you can use the alter table command to add constraints to the table after creating the table. Note that you must use the modify option to add not null constraints, add the other four constraints to use the Add option.
Delete constraint: alter table Table Name drop constraint name;
If the constraints to be deleted have a master-slave relationship, cascade must be added when you delete the primary key constraint of the master table.
Query the constraints of the current user:
Select constraint_name, constraint_type, status, validated from user_constraints where table_name = 'table name'
Show constraint Columns
Select colunmn_name, position from user_cons_columns where constraint_name = 'constraint name'
Column-Level Definition and table-Level Definition
Column-level definition defines constraints while defining Columns
Table-level definition defines constraints after all columns are defined. Not null constraints can only be defined on column-level definitions.
8. Index
An index is a data object used to accelerate data access. Using an index properly can greatly reduce the number of I/O operations and improve data access performance.
1. Single Column Index
A single column index is an index created based on a single column.
Create Index name on table name (column name)
2. Composite Index
The index is based on two or more columns. The same table can have multiple indexes, but the combination of columns must be different.
For example, create index emp_idx1 on EMP (ename, job );
Create index emp_idx1 on EMP (job, ename );
3. indexing principles
1) creating an index on a large table makes sense;
2) create an index on the column frequently referenced in the where subname or connection condition.
3) The index level should not exceed four layers;
4. Index disadvantage Analysis
1) To create an index, the system needs to occupy about 1.2 times the hard disk and internal space of the table to store the index.
2) When updating data, the system must have additional time to update the index at the same time to maintain data and index consistency.
Practices show that improper indexes not only do not affect the system performance, but also reduce the system performance, because a large number of indexes take more time to insert, modify, and delete than no indexes.
For example, it is inappropriate to create an index in the following fields;
1) Few or never reference fields
2) For logical fields, improving query efficiency is at the cost of consuming a certain amount of system resources. indexes cannot be established blindly. This is an important indicator to test whether DBAs are excellent.
5. Other Indexes
Data storage can be divided into B * trees (based on columns with few duplicate values), reverse indexes, and bitmap indexes (based on relatively fixed columns with many duplicate values and different values );
The indexes can be classified into single-column indexes and composite indexes based on the number of index columns;
Based on the uniqueness of the index column value, it can be divided into unique and non-unique indexes;
In addition, there are function indexes, global indexes, and partition indexes...
6. Display index information
Select index_name, index_type from user_indexes where table_name = 'table name ';
Show index Columns
Select table_name, column_name from user_ind_columns where index_name = 'ind _ ename ';
9. manage permissions and Roles
A user that has just been created does not have any permissions and cannot perform any operations. To perform certain database operations, the user must be granted system permissions; if you want to access objects in other schemes, you must grant them the object permissions. Roles can be used to simplify permission management.
A) system Permissions
1. System Permissions
System permission refers to the permission to execute specific types of SQL commands. It is used to control one or more level-1 Database Operations that a user can perform. For example, when a user has the CREATE TABLE permission, you can create tables in the solution. When you have the create any table permission, you can create tables in any solution. The Oracle system provides more than 100 system permissions.
Show System Permissions
Select * From system_privilege_map order by name;
2. Grant System Permissions
Generally, the DBA grants the system permission. If other users grant the system permission, the user must have the system permission grant any privilege, the with admin option can be included when granting permissions, so that the authorized user or role can also grant the system permission to other users or roles.
For example, grant create session, create table to Ken with admin option;
3. Revoke system Permissions
Generally, DBA revokes system permissions. If other users revoke system permissions, the user must have the corresponding system permissions and grant system permissions to the user. Revoke system permissions using revoke. For example, revoke create Session from Ken;
The user's system permissions are not cascaded revocation, and the permissions of other users remain unchanged after revocation.
B) object permissions
The permission to access objects in other schemes. You can directly access objects in your own schemes. However, if you want to access objects in other schemes, you must have the permission to access objects in other schemes. For example, if a smith user wants to access Scott's table Scott. EMP (SCOTT: solution, EMP: GE)
Commonly used: Alter, delete, select, insert, update, index, references, execute
1. Display object permissions
Select distinct privilege from dba_tab_privs;
Select grantor, owner, table_name, privilege from dba_tab_privs where grantee = 'bucke ';
2. Grant object permissions
Object permissions can be granted to users, roles, and public. When granting permissions, if you have the with grant option, you can grant this permission to other users. However, you must note that the with grant option cannot be assigned to a role. If you only need to grant the object permission for a field
Grant update/select on EMP (SAL) to Uu;
3. revoke permissions
After revoking permissions, you cannot execute related SQL commands. However, you must note whether the object permissions will be revoked in cascade mode. The answer is: it will be withdrawn by cascade.
C) Role
A role is a command set for related permissions. The main purpose of a role is to simplify permission management.
Roles are divided into predefined and custom roles.
A predefined role is a role provided by Oracle. Each role is used to perform certain management tasks.
1. The CONNECT role has most of the permissions required by general application developers. When a user name is set up, you only need to grant the connect and resource roles to the user in most cases.
2. The resource role has other permissions required by application developers, such as creating stored procedures and triggers. Note that the resource role implies the unlimited tablespace system permissions.
3. the DBA role has all system permissions and the with admin option. By default, DBA users of users are sys and system. They can grant any system permission to other users. The DBA role does not have the privileges of sysdba and sysoper (START and close the database)

Custom roles: Generally, they are created by DBAs. Other users have the create role system permission. When creating a role, you can specify the authentication method (no verification, database verification, and so on)
1) create a role (not verified)
If a role is a public role, you can create a role without verification.
Create role name not identified;
2) create a role (Database verification)
In this way, the role name and password are stored in the database. When activating this role, you must provide a password. When creating this role, you must provide it with a password.
Create role name identified by password
3) Role authorization
When a role is created, the role does not have any permissions. In order for the role to complete a specific task, it must be granted the corresponding system and object permissions.
There is no much difference between authorizing a role and authorizing a user. However, you must note that the unlimited tablespace of the system permission and the with grant option of the object permission cannot be granted to the role.
Grant create session to role name with admin Option
Grant select on Scott. EMP to role name
Grant insert, update, delete on Scott. EMP to role name
4) assign a role
Generally, roles are assigned by the DBA. To assign roles as other users, the user must have the system permission to grant any role.
5) delete a role
Generally, drop role is executed by DBA. If other users are required to have the system permission to drop any role. Drop role name. After the role is deleted, the user assigned the role does not have the corresponding permissions.
6) display role information
Show all roles
Select * From dba_roles;
Displays the system permissions of a role.
Select privilege, admin_option from role_sys_privs where role = 'Role name'
7) display the object permissions of the role
You can query the data dictionary view dba_tab_privs to view the object permissions or column permissions of the role.
8) display the roles and default roles of the user
When you connect to the database as a user, Oracle automatically activates the default role. by querying the data dictionary view dba_role_privs, you can view all roles of a user and the current default roles.
Select granted_role, default_role from dba_role_privs where grantee = 'username'
9) Fine Access Control
I will not describe it here because it is rarely used.