If you are new to oracle, you can see that this is the foundation.
If you are new to oracle, you can see that this is the foundation.
I. Concepts
1. Database)
What is a database?
A database is a set of data that is organized according to a certain data model and stored in the second-level memory. This type of data set has the following features: it serves as many applications as possible in the best way as possible for a specific organization. Its data structure is independent of its application, the unified software manages and controls the addition, deletion, modification, and retrieval of data. From the development history, the database is an advanced stage of data management, which is developed by the file management system.
What is a database system?
A database system is a software system that provides data for the actual and operational storage, maintenance, and application systems. It is a collection of storage media, processing objects, and management systems. It is usually composed of software, databases, and data administrators. The software mainly includes the operating system, various host languages, utilities, and database management systems. Databases are centrally managed by the database management system. Data insertion, modification, and retrieval must be performed through the database management system. The Data Administrator is responsible for creating, monitoring, and maintaining the entire database so that the data can be effectively used by anyone with the right to use it. The database administrator is generally a person with high business level and deep qualifications.
Let's make a metaphor: the database is the data warehouse that stores the data. of course, warehouses must be organized in an orderly manner, which requires a set of management methods and management organizations. The combination of management methods and management organizations has become an organic system for managing warehouses.
2. Data Table space (Tablespace)
Data storage always requires space. Oracle divides a database into several spaces by function to store data. Of course, the data stored on the disk is ultimately in the form of files, so a disk and a data table space contain more than one physical file
3. Oracle users
Multiple users in a database create and manage their own data. each user has its own permissions and can share data with other users.
4. Data Tables
In the warehouse, we may have multiple houses, each with multiple shelves and multiple layers. We store data in databases and ultimately store and manage data tables.
5. Data Files
These concepts are logical, while data files are physical. That is to say, a data file is really "something you can see" and is displayed as a real file on the disk.
Ii. Create
(* If the oracle command is executed in sqlplus, run sqlplusw system/manager @ test-as mentioned in earlier installation)
1. After oracle8i is installed in the database, a database system is installed, with a default database. Of course, we can also create a new database.
2. Now we should create a data table space to store the goods. At least we should first build the house. It is the space for storing things. The tablespace stores the data.
Format: create tablespace table name datafile 'data filename 'size tablespace size;
Example:
Create tablespace data_test datafile 'e: \ oracle \ oradata \ test \ data_1.dbf' size 2000 M;
Create tablespace idx_test datafile 'e: \ oracle \ oradata \ test \ idx_1.dbf' size 2000 M;
(* The data file name contains the full path, and the tablespace size is 2000 MB, which is 2000 mb)
3. After tablespace is created, you can create a user.
Format: create user username identified by password default tablespace table;
Example:
Create user study identified by study default tablespace data_test;
(* Create a username named study and password named study. The tablespace named data_test is missing. This is created in step 2 .)
(* Saving the tablespace indicates that study's data in the future will be stored in data_test, that is, stored in the corresponding physical file e: \ oracle \ oradata \ test \ data_1.dbf)
4. authorize new users
Grant connect, resource to study;
-- Grant the connect and resource permissions to study users.
Grant dba to study;
-- Grant the dba permission to study
5. Create a data table
On the above page, we have built the User study. Now we enter this user
Sqlplusw study/study @ test
Then you can create a data table in study.
Format: name of the create table data table. For detailed parameters, search for "oracle" "create table" "Syntax" on the Internet. Too many parameters will not be included here.
The following is an example.
Create table test_user (
No number (5) not null, -- pk
Username varchar2 (30) not null, -- User Name
Passpord varchar2 (30) not null, -- Password
Constraint pk_connectdb primary key (no)
) Storage (initial 10 k next 10 k pctincrease 0 );
* The following describes the meaning of the above commands.
Create table test_user -- create a data table
No number (5) not null, -- pk
(Column name or field name) data type (Data Length) the data column cannot be blank, It is a delimiter between columns-the post content is a comment
Constraint pk_connectdb primary key (no)
(Constraint) constraint name (primary key) (column name) means that the no value of all rows in the test_user table cannot be the same (this is the meaning of the primary key)
Storage (initial 10 k next 10 k pctincrease 0); this is complicated. If a data table needs to store a large amount of data, set the values after initial and next to a larger value, otherwise, set a smaller value.
Since the table space is not specified in the table created above, the table is stored in the study Default table space data_test.
Create tablespace data_phonepos datefile 'd: \ install \ OracleXpdb \ datafilephonepos. dbf 'size 8000 M;
Create user phonepos identified by phonepos default tablespace data_phonepos;
Grant connect, resource to phonepos;
Grant dba to phonepos;
Permission Query
5. 1. query the object permissions granted by a user to other users in the current user mode.
Select * from user_tab_privs_made -- if the current user is WENZI, the query result is the permission record authorized by WENZI in WENZI mode.
5. 2. query the object permissions granted by a user to other users on the user mode object and other mode objects.
Select * from all_tab_privs_made -- if the current logon user is WENZI, the query result will be a record of all the permissions granted by WENZI.
Modify user
Alter user avyrros
IDENTIFIED EXTERNALLY
Default tablespace data_ts
Temporary tablespace temp_ts
QUOTA 100 m on data_ts
QUOTA 0 ON test_ts
PROFILE clerk;
Delete a user
The drop user username [CASCADE] -- CASECADE option deletes all objects in this USER mode. We recommend that you check whether other dependencies exist before deleting them.
Query user objects
Select owner, object_name, object_type, status from dba_objects where owner = 'wenzi'
5.3 query the permissions granted to a user on objects in other modes
Select * from user_tab_privs_recd -- if the current logon user is WENZI, the query result shows that WENZI has permissions on other schema objects.
5.4 query the permissions granted to a user on the user mode object and other mode objects
Select * from all_tab_privs_recd -- if the current user is wenzi, the query result is the wenzi object with permissions in the entire database.
Role management
Create a password file
Orapwd file = '.......... \ pwd {SID }. ora 'password = '*** (sys password) 'tntries = 10 (maximum number of users in the password file)
To allow a user to use the password file, the system automatically adds the user to the password file and must grant the SYSDBA permission to the user.
Grant sysdba to wenzi
When the SYSDBA permission is revoked, the system deletes the corresponding user from the password file.
Revoke sysdba from wenzi
View password file management users
Select * from v $ pwfile_users
