Oracle to create user and datasheet methods _oracle

first, the concept
1. Databases (database)
What is a database?
The database is organized according to some data model and holds the data collection in level two memory. This data set has the following characteristics: As far as possible without duplication, in the best way for a specific organization of a variety of applications, the data structure is independent of the application of its applications, the increase, deletion, modification and retrieval by the unified software management and control. From the history of development, database is the advanced stage of data management, it is developed by file management system.
What is a database system?
The database system is a software system which can provide data for storage, maintenance and application system, and is a collection of storage medium, processing object and management system. It is usually made up of software, databases, and data administrators. Its software mainly includes operating system, various host languages, utilities and database management system. Database management system is unified management, data insertion, modification and retrieval through the database management system. The Data Administrator is responsible for creating, monitoring, and maintaining the entire database so that it can be used effectively by anyone who has the right to use it. A database administrator is typically a person with a higher level of business and a deeper seniority.

It's a metaphor: the library is the warehouse where the data is stored. Of course, warehouses have to be organized, this requires a set of management methods and management organizations, management methods and management of the Organization to become a management of the warehouse organism-system.
2. Datasheet space (tablespace)
Storing data always requires space, and Oracle divides a database into several spaces by function to hold data. Of course the data stored in the disk is ultimately in file form, so a disk of data table space contains more than one physical file
3. Oracle Users
A database with multiple users to create and manage their own data, each user has its own permissions, and can share data with other users
4. Data table
In the warehouse, we may have a number of houses, each house has more than one shelf, each with multiple layers. We store and manage data in a database, which is ultimately a unit of data tables.
5. Data files
The above concepts are logically, and the data files are physical. That is, the data file is really "something to see", which is embodied in a real file on disk.

Second, create
(* Where Oracle commands are executed in Sqlplus, that is, run SQLPLUSW system/manager@test-This is the previous installation)
1. Database when we install the oracle8i, a database system is installed, which has a default database, of course, we can also create a new database.
2. Now we should build the data table space, if we want to store the goods, at least first have to build the house well. Is the space to store things. Table space is the space where the data is stored.
Format: Create tablespace table name datafile ' data file name ' size table space;
Example
Create tablespace data_test datafile ' e:\oracle\oradata\test\data_1.dbf ' size 2000M;
Create tablespace idx_test datafile ' e:\oracle\oradata\test\idx_1.dbf ' size 2000M;
(* Data file name contains full path, table space size 2000M table is 2000 megabytes)
3. Build a good tablespace, you can build users
Format: Create user username identified by password default Tablespace table space table;
Example
Create user study identified by study default tablespace data_test;
(* We create a user name of study, the password is study, the lack of tablespace is data_test-this is done in the second step.)
(* The Province table space to indicate that the user study future data, if not specifically pointed out, its data is stored in data_test, that is, stored in the corresponding physical file e:\oracle\oradata\test\data_1.dbf)
4. Authorize to new users
Grant Connect,resource to study;
----to grant Connect,resource permissions to study users
Grant DBA to study;
--Means to grant DBA authority to study
5. Create data tables
On top, we've built the user study we're now entering that user
SQLPLUSW Study/study@test
You can then create the data table in user study.
Format: CREATE table datasheet name, followed by the detailed parameters, please search the Internet "Oracle" "CREATE TABLE" "syntax." Too much, I will not be attached here.
Here is an example of how you feel.
CREATE TABLE Test_user (
No number (5) NOT NULL,--PK
Username VARCHAR2 NOT null--user name
Passpord varchar2 NOT NULL,--Password
Constraint Pk_connectdb primary KEY (NO)
) Storage (initial 10k next 10k pctincrease 0);
* Explain the meaning of the above command in the following ways
CREATE TABLE Test_user--Creating a datasheet
No number (5) NOT NULL,--PK
(column or field name) data type (data length) The data column cannot be empty, it is a separator between columns--the following is a comment
Constraint Pk_connectdb primary KEY (NO)
(constraint) constraint name (primary key) (column name) means that all row data in Datasheet Test_user cannot have the same value (which is what the primary key means)
Storage (initial 10k next 10k pctincrease 0); This is more complicated to say, anyway, if a data table to store a large amount of data, the initial and next value set a little bit, otherwise set a small point.
Since the table space is not specifically specified above in the Create data table, the table is stored in the Study default table space Data_test.
Create tablespace data_phonepos datefile ' d:\install\OracleXpdb\datafilephonepos.dbf ' size 8000M;
Create user Phonepos identified by phonepos default Tablespace data_phonepos;
Grant Connect, resource to Phonepos;
Grant DBA to Phonepos;

Query for permissions
5. 1 Querying a user to grant other users object permissions in the current user mode
SELECT * FROM User_tab_privs_made-if the current user is Wenzi, then the query result is authorized by Wenzi, and the permission record in Wenzi mode
5. 2 Query a user to grant other users object permissions on this user-mode object and other mode objects
SELECT * FROM All_tab_privs_made--if the current logged-on user is Wenzi, the query result is a record of all permissions granted by Wenzi

Modify User
ALTER USER Avyrros
Identified externally
DEFAULT tablespace Data_ts
Temporary tablespace temp_ts
QUOTA 100M on Data_ts
QUOTA 0 on Test_ts
Profile Clerk;
Delete User
The drop user username [CASCADE]--casecade option deletes all objects in that user mode, and recommends that you confirm that there are other dependencies before you delete them.
Querying objects that belong to a user
Select Owner,object_name,object_type,status from dba_objects where owner= ' Wenzi '
5.3 Permissions granted to a user by a query on other schema objects
SELECT * FROM USER_TAB_PRIVS_RECD--if the current logged-on user is Wenzi, then the query result is Wenzi permissions on other schema objects
5.4 Queries are granted to a user for permissions on that user-mode object and other schema objects
SELECT * FROM ALL_TAB_PRIVS_RECD--if the current user is Wenzi, the result of the query is Wenzi object with permissions in the entire database

Role management
Create password file
Orapwd file= ' ... \pwd{sid}.ora ' password= ' * * * (sys password) ' tntries=10 (maximum number of password file users)
To make a password file available to a user, you must grant SYSDBA permission to it, and the system will automatically add it to the password file.
Grant SYSDBA to Wenzi
When the SYSDBA permission is retracted, the system deletes the corresponding user from the password file.
Revoke SYSDBA from Wenzi
View Password file management users
SELECT * FROM V$pwfile_users