1. Previous section Review
2. Database administrator
3. Logical backup and recovery of the database
4. Data dictionary and Dynamic performance view
5. Managing table spaces and data files
1. Understand the basic responsibilities of Oracle Administrators
2. Learn how to back up and restore databases/tables
3. Understanding table space, data dictionary, Performance View Database administrator dBA, for a small database, a DBA is enough, but for a large database may require multiple DBAs to take different management responsibilities, a DBA's main work:
Duty:
(1) Installing and upgrading the Oracle database
(2) Build library, table space, table, view, index
(3) Develop and implement backup and recovery plans
(4) Database Rights Management, tuning, troubleshooting
(5) For advanced DBAs, who are required to participate in project development, write SQL statements, stored procedures, triggers, rules, constraints, packages
The user who manages the database is primarily SYS and System,sys is the chairman and system is the general manager
The main differences are as follows:
(1) The most important difference is the importance of the stored data in different SYS: all Oracle's data dictionaries and views are stored in the SYS user, these base tables and views are critical to the runtime of Oracle, maintained by the database itself, and cannot be changed manually by any user. SYS user has dba,sysdba,sysoper roles or permissions and is the most user with Oracle permissions
System: Used to store sub-level internal structures, such as some of the features or tools of Oracle
Management information, system users have DBA,SYSDAB roles or systems permissions
(2) Second difference, the authority of the different SYS user must be as SYSDBA (role) or as sysoper (role) Form login, can not log in the normal way database
If the system is logged on normally, it is actually a normal DBA user, but if
As Sysdba landed, the result was actually he was logged in as a sys user, from
We can see it in the login information.
SYS user-----------> Scenarios------------------> Base tables and dynamic views that hold Oracle database data dictionaries
Description: A user corresponds to a scenario, and then each scenario has its own base table and dynamic view
SYS has the following roles:
Dba Sysdba Sysoper
Each role has its own permissions, and then it has its own actions, and each role can have multiple permissions
System User----------------> Scenarios--------------> Secondary data
System has the following roles:
DBA SYSDBA
Conn sys/change_on_install;//landing method is wrong
Conn Sys/change_on_install as sysdba;//normal landing
Conn System/manager;//system User Login as a regular user
Conn System/manager as sysdba;//login as SYS user
Character Sysdba The permissions that sysoper have are different
Startup
Shutdown
ALTER DATABASE Open
Create a database Cannot create database
DBA Authority for users:
DBA user is a user with DBA role permissions
Permissions role Size comparison:
Sysdba > Sysoper > DBA
Conn System/manager as SYSDBA;
shutdown;//shutting down the database
startup;//starting the database
Summarize:
Two important users, three important user roles
Managing Initialization parameters
Show parameter;//display the management parameters of the database
Logical backup and Recovery of database (table)
Logical backup Use the Expport command to back up data objects, export to disk
Import and restore: Using Impport
Physical backups can be backed up in the open state of the database or under shutdown,
However, a logical backup can only be performed in the database open state
Export:
The export is divided into: Export table, export scheme, export database three ways
The export is done using the EXP command, which has the following common options:
UserID: Used to specify the user name, password, connection string to perform the export operation
Tables: Used to specify the table to perform the export operation
Owner: Specifies the scenario to perform the export operation
Full=y: Used to specify the database to perform the export operation
Inctype: Used to specify the increment type to perform the export operation
Rows: Used to specify whether the export operation is to export data from the table
File: Used to specify the export file name
Detailed Description:
Special Note:
When importing and exporting, go to Oracle's Bin directory, or else not.
To export a table:
(1) Export your own table
Exp Userid=scott/[email protected] tables= (tab1,tab2,...) file=d:\e1.dmp
Conn Scott/tiger;
Exp Userid=soctt/[email protected] table= (EMP) file=d:\emp.dmp
Note: The exported binary file, the export file suffix type does not affect the
Exp Userid=scott/[email protected] tbale= (emp,student) file=twotable.dmp
(2) Export tables for other scenarios
If a user wants to export a table for another scenario, they need DBA authority or
Exp_full_database's permissions, such as system, can export Scott's tables.
Exp Userid=system/[email protected] table= (scott.emp) file=d:\e2.dmp
Exp Userid=system/[email protected] tables= (scott.emp) file=d:\e3.dmp
(3) Structure of the exported table
Exp Userid=scott/[email protected] tables= (emp,dept) file=d:\e4.dmp rows=n
(4) Use direct Export method: Export Data faster
Exp Userid=scott/[email protected] tbales= (EMP) file=d:\e5.dmp direct=y
Export Scenario:
Export scenarios refer to exporting a scenario or multiple scenarios using the Export tool
All objects (tables, indexes, constraints: ) and data, and stored in the file
(1) Export your own solution
Exp Scott/[email protected] Owner=scott file=d:\scott.dmp
(2) Export other programs
If the user wants to export other scenarios, they need DBA authority or
Exp_full_database permissions, such as system users, can export any scenario
Exp System/[email protected] owner= (System,scott) file=d:\system.dmp
Export Database
Exporting a database refers to exporting objects and data from all databases using export.
Require the user to have DBA authority or exp_full_database permissions
Exp Userid=system/[email protected] full=y inctype=complete file=x.dmp
Import:
Import Table
(1) Import your own table
Imp Userid=scott/[email protected] tables= (EMP) file=d:\xx.dmp
(2) Importing tables to other users
Requires the user to have DBA authority, or Imp_full_database role
Imp Userid=system/[email protected] tables= (EMP) file=d:\emp.dmp
(3) Structure of the imported table
Import only the structure of a table without importing data
Imp Userid=scott/[email protected] tables= (EMP) file= ...
(4) Import data
If an object (such as a table) already exists, you can import only the table's data
Imp Userid=scott/[email protected] tables= (EMP) file=d:\xxx.dmp ignore=y
Import Database
By default, when you import a database, all object structures and data are imported, as in the following cases:
Imp Userid=system/manager full=y file=d:\xxx.dmp
Scott user------------"Scott's scenario--------" scenario has many things called data objects
Table
View
System User-------------------->system scenario----------"data Object---------" disk
Data dictionaries and dynamic performance views
The data dictionary is the most important part of the Oracle database, and it provides some system information for the database
Dynamic performance View records information about the start of a routine
A data dictionary is a data object that is placed in the system schema
Includes data base tables and Dynamic Data dictionary views
Base table: storing static data
Dynamic view: Storing Dynamic Data
Query table:
user_table;
Used to display all tables owned by the current user, he only returns all tables for the current user's schema
Select table_name from User_tables;
All_tables:
Used to display all tables that the current user can access, and it will not only return
All tables for the current user scenario, and also returns tables for other scenarios that the current user can access
Like what:
Select table_name from All_tables;
Dba_tables:
It shows all the database tables owned by the schema, but querying such a database dictionary view requires
User must be a DBA role or have select any table system permissions
For example: When the current system user queries the data dictionary view dba_table, it returns
System,sys,scott ... database tables corresponding to the scenario
User name, permissions, role
When a user is established, Oracle stores the user's information in a data dictionary, and when the user is granted a permission or role,
Oracle stores information about permissions and roles in a data dictionary,
Query Dba_users can display the details of all database users;
By querying the data dictionary view Dba_sys_privs, you can display the system permissions that the user has;
By querying the data dictionary view Dba_tab_privs, you can display the object permissions that the user has
By querying the data dictionary dba_col_privs, you can display the column permissions that the user has
Querying the database dictionary view Dba_role_privs can display the objects that the user has
Desc dba_users;
Select Username,password from Dba_users;
Permissions are divided into: System permissions and Object permissions
For example, to query the role Scott has, you can query Dba_role_privs:
Desc Dba_role_privs;
SELECT * from Dba_role_privs where grantee= ' Scott ';
About role Permissions:
Role: A role has a lot of permissions, the role is composed of permissions, the role assigned to a user, the user has
All the permissions of this role, a user can have multiple roles
Query all system permissions in Oracle, typically DBA, about 130 kinds
SELECT * from System_privilege_map order by name;
Querying all roles in Oracle, typically DBA
SELECT * from Dba_roles;
Querying all object permissions in Oracle, typically DBA, about 16 kinds
Select distinct privilege from Dba_tab_privs;
Querying the table space of a database
Select Tablespace_name from Dba_tablespaces;
Problem:
1. How do I query the permissions that a role contains?
1.1 What are the system permissions that a role contains?
SELECT * from Dba_sys_privs where grantee= ' DBA ';
To view the system permissions that the Connect role contains
SELECT * from Dba_sys_privs where grantee= ' connect ';
Or see the following methods:
SELECT * from Role_sys_privs where role= ' connect ';
1.2 The object permissions that a role contains?
SELECT * from Dba_tab_privs where grantee= ' connect ';
How many roles does 2.oracle have?
SELECT * from Dba_roles; Log in as a system user and query to 25 predefined roles in Oracle
3. How do I know which roles a certain user contains?
SELECT * from Dba_role_privs where grantee= ' Scott ';
4. Display all data dictionary views that the current user can access
SELECT * from dict where comments like '%grant% ';
5. Displays the full name of the current database
SELECT * from Global_name;
The same user can log on to multiple DB instances
Other instructions
:: The data dictionary records all system information for the Oracle database, and the following system information can be obtained by querying the data dictionary:
(1) Definition of objects
(2) The object occupies space size
(3) Column information
(4) Constraint information
。。。
However, because of this information, the dynamic performance view can be queried through the PL/SQL Developer tool Query: Used to record the activity information of the current routine, and when the Oracle server is started, a dynamic performance view is established when the Oracle server is stopped. The dynamic performance view is removed, and all of Oracle's dynamic performance views start with v_$, and Oracle provides synonyms for each dynamic performance view, with synonyms starting with v$, such as v_$datafile synonyms v$datafile; The owner of the dynamic performance view is SYS, which, in general, queries the dynamic performance view by a DBA or a privileged user. Because this is less in practice, it flies over the management table space and data Files-Introduction
Tablespace: A table space is a logical part of a database, from a physical perspective, database data
stored in a data file; Logically, the database is stored in a table space,
A tablespace consists of one or more data files
Tablespace---"Data file
Similar
BEIJING-----> Multi-block Land
Introduced:
The logical structure in Oracle includes table spaces, segments, extents, and blocks.
Explain that the database is made up of table space, and the table space is composed of segments,
And the section is composed by the district, and the area is composed of Oracle blocks such a
structure that can improve the efficiency of the database
The logical diagram is as follows:
Table space-------"section-------------------" District--------------"block
Block
。。。
Area
。。。
Paragraph
。。。
The Oracle database is so structured to better manage the database and how much of a database
There is no limit to the table space,
Table spaces are used to logically organize the data of a database, which is logically
One or more table spaces, the following functions can be achieved through a tablespace:
(1) Controlling the disk space occupied by the database
(2) DBAs can deploy different data types to different locations, which facilitates
Improves I/O performance while enabling management operations such as backup and recovery
Experienced database administrators typically put tables, views, triggers on different
In the table space,
To create a table space:
Creating a tablespace is done using the Create Tablespace command, and it is important to note that:
In general, creating a tablespace is performed by a privileged user or DBA, and if a table space is created with another user,
The user must have system permissions for create Tablespace
To create a data table space:
Must be available with DBA authority, and SYS user or system user
After the database is established, it is best to create your own tablespace for easy management of tables
Create tablespace data01 datafile ' D:\test\date01.dbf ' size 20m uniform size 128k;
The above statement establishes a table space, the tablespace name is DATA01, the corresponding database file is data01.dbf, the size of this file is 20M (that is, the segment size), the maximum can only be 500m, the size of the zone in 128K units
To create a database table, specify a table space:
CREATE TABLE Mypart (deptno number (4), dname varchar2 (+), loc varchar2 () tablespace sp001;
Managing table spaces and data files
Change the state of a table space
When the tablespace is established, the tablespace is online (online), at which time the table space
The tablespace is accessible, and the table space is read and write, that is, you can query the table space's data, and you can execute various statements in the tablespace, but
system maintenance or data maintenance, you may need to change the state of the table space, in general,
Operated by a privileged user or DBA
(1) Take the table space offline
Alter tablespace table space name offline;
(2) Bring the table space online
Alter tablespace table space name online;
(3) Read-only table space
When a tablespace is established, the tablespace can be read and written, if you do not want to execute it on that table space
Update,delete,insert operation, you can modify the tablespace to read-only
Alter Tablespace Query_data Read only;
For example:
Alter Tablespace sp001 Read only;
When the tablespace is read-only, you cannot insert data into the table.
To remove the readable properties of a table space
Alter Tablespace sp001 Read write;
1) know the tablespace name and show all tables included in the table space
SELECT * from all_tables where tablespace_name= ' table space name ';
2) know the table name and see which table space the table belongs to
Select Tablespace_name,table_name from user_tables where table_name= ' emp ';
To change the table space state:
To delete a table space:
Typically, a privileged user or DBA operates, and if it is another user operation,
Then require the user to have drop tablespace system permissions
For example:
Drop tablespace ' table space ' including contents and datafiles;
Description: Including contents to delete tablespace when table space is deleted
All database objects, and datafiles means that the database files are also deleted
To extend a table space:
Table space is composed of data files, the size of the table space is actually the size of the data file after the addition, then we can imagine, assume that the table emp
Storage to the DATA01 table space, the initial size is 2m, when the data is full of 2m space, if
Then insert the data into the employee table, which will show an insufficient space error
Case description
1. Set up a tablespace sp001
2. What to do when there is too much data inserted and there is not enough table space
Workaround:
Extending table Space
(1) Adding data files
Alter tablespace SP01 add datafile ' D:\test\sp01.dbf ' size 20m;
(2) Increase the size of the data file
Alter tablespace table space name ' D:\test\sp01.dbf ' resize 20m;
It is important to note that the size of the data file should not exceed 500m
(3) Set data file Auto-growth
Alter tablespace tablespace name ' D:\test\sp01.dbf ' autoextend on next 10m maxsize 500m;
To move a data file (migration tablespace):
Sometimes, if your data file is located on a disk that is damaged, the data file will no longer be used, in order to be able to
Re-use, you will need to move a copy of these files to another disk, and then restore:
The following is an example of moving data file sp001.dbf:
1) Determine the tablespace where the data file resides
Select Tablespace_name from Dba_data_files where file_name= ' D:\test\sp01.dbf ';
2) Take the table space offline so that it cannot be used
Ensure data file consistency, transforming tablespace to offline state
Alter tablespace SP01 offline;
3) Use the command to move the data file to the specified target location
Host Move D:\testsp01.dbf C:\TEST\SP01.DBF
4) Moving Data files
After you perform the alter tablespace command to physically move the data, you must also perform the ALTER TABLESPACE command on the database file
Logical modification:
Alter tablespace sp01 rename datafile ' D:\sp001.dbf ' to ' C:\sp001.dbf ';
5) Bring the table space online
After the data file has been moved, in order for the user to access the tablespace, you must
Turn it into online status
Alter tablespace DATA01 Online;
The above abbreviation for five parts
Display information for a table space
Query the data dictionary view dba_tablespaces to display information about the tablespace:
Select Tablespace_nae from Dba_tablespaces;
Show the data files that the tablespace contains
Query the data dictionary view Dba_data_files, which shows what the table space contains
Data files, as follows:
Select File_name,bytes from dba_data_files where tablespace_name= ' table space name ';
Table Space Summary:
1) Understanding the role of tablespaces and data files
2) Master the common table space, undo table space and the establishment of temporary table space method
3) Understand each state of a tablespace
The role of (Online,offline,read,write,read only), and
Ways to change the state of a tablespace
4) Understand the reasons for moving data files and use alter Tablespace
and alter DataTable command method for moving data files
Other table spaces
In addition to the most commonly used data table spaces, there are other types of table spaces
(1) Index Table space
(2) Undo table Space
(3) Temporary table space
(4) Table space for non-standard blocks
Building a table space on an index list increases the efficiency of data access
Number of initial transactions:
Maximum number of transactions:
The fourth day of learning to go to Oracle
