First day of Oracle Database learning, first day of oracle Database

First day of Oracle Database learning, first day of oracle Database

Crud addition, deletion, modification, and query create read update delete
1 -- oracle Database Installation
By default, the system creates two user-defined sys and system password.
Super administrator: sys
Administrator: system
Password 5615
2 -- start the Database Service
Service
Listener

3 -- access the database
Use the SQL plus client tool provided by oracle (Prerequisites: Add D: \ oracle \ app \ oracle \ product \ 11.2.0 \ server \ bin in path)
Open the Run window with the wind + R key
Input: sqlplus press ENTER
Enter the user name and password as prompted
Sys and password cannot directly log on to the database
The system and password can be used to log on to the database directly. Note: This method can only be used as a common user.
If you want to log on to the database using sys
Press wind + R and enter sqlplus sys/5615 as sysdba. Press enter.
View the login name of the current user
Show user; press ENTER

4 -- start and close the database
Close the database:
Shutdown immediate;
Start the database:
Startup open;
Sys can start and shut down Databases
System does not have this permission

5-database user operations
Sys User Creation:
Create user owen identified by 123;
After a user is created, the user is locked by default and no role is assigned. You need to unlock the user.
Alter user owen account unlock;
Sys assigns roles to users
Grant connect to owen;
Now you can use the owen user to log on to the database, but only have the permission to log on.
Conn owen/123;
Sys assigns a resource role to the user.
Grant resource to owen;
In this case, you can perform object operations under the current owen user.
Create table owen_record (login_time varchar (19 ));
To change a user to a super Administrator
Grant sysdba to owen;
Delete a user
Drop user owen;
Delete a user (delete all objects created by the user)
Drop user owen cascade;
Revoke sysdba roles from owen users
Revoke sysdba from owen;
Queries the names of all tables under the current user role.
Select table_name from user_tables;

Bytes ------------------------------------------------------------------------------------------------------------------------------

Table space
Is the largest Logical Structure in oracle databases.
Logically, the oracle database is composed of several tablespaces.
The tablespace is closely related to the physical structure of the database. It corresponds to several data files on the disk.
Physically, the database data is stored in the data file, logically, the data is stored in the tablespace.
A data file can belong to only one tablespace. A tablespace can have multiple data files.
Oracle data stores solution objects (tables, indexes, views, sequences) in tablespaces.

| -- Tablespace 1
| -- Tablespace 2
| -- Tablespace 3
| -- Data File 1
| -- Data file 2
| -- Disk 1
| -- Data file 3
| -- Disk 2
| -- Data file 4
| -- Disk 2


Create temporary tablespace
Create temporary tablespace ------ keywords for creating a tablespace File
Owen_temp ------ tablespace name
Tempfile 'd: \ oracle \ user \ owen_temp.dbf '------ the path must exist
Size: 100 MB ------ initial size
Autoextend on ------ enable auto Scaling
Next 10 m maxsize 1000 m ------ the size and maximum space of each extension

Create a data table space file statement
Create tablespace
Owen_data
Datafile 'd: \ oracle \ user \ owen_data.dbf'
Size 500 m
Autoextend on
Next 20 m maxsize 2000 m

Create a user and specify a tablespace
Create user owen identified by 123
Default tablespace owen_data
Temporary tablespace owen_temp;

Unlock this user
Alter user owen account unlock;

Authorize a user
Grant connect, resource to owen;

Query the tablespace of a specified user using the dba role (username must be in uppercase and must be in single quotes)
Select user_id, username, default_tablespace from dba_users where username = 'owen ';

Queries information about all tablespaces.
Select tablespace_name, status, allocation_type from dba_tablespaces;

Modify Table space name
Alter tablespace owen_data rename to new_owen_data;

Delete a tablespace. Only the tablespace records are deleted.
Drop tablespace owen_data;

Delete tablespace and tablespace files
Drop tablespace owen_data including contents and datafiles;

What are the permissions for objects (tables, indexes, views, and sequences?
Select, update, insert, alter, index, delete, all (all includes all permissions)

Sys creates a table t_test.
Sys grants the select t_test table permission to the user lp.
Grant select on t_test to lp;

Conn lp/1;
Query this table
Select * from sys. t_test;

Grant all operation permissions to this table
Grant all on t_test to lp;

Add record
Insert into sys. t_test (name) values ('owen ');