Oracle Database Basics

Basic tutorial on Oracle database-general Linux technology-Linux technology and application information. For more information, see the following section. TableSpace


Tablespace:

One tablespace corresponds to multiple data files (physical dbf Files)


Create a tablespace using the syntax and log on to the table using sysdba:


-- Create a tablespace mytabs with a size of 10 MB:


Create tablespace mytabs datafile
'C: \ oracle \ oradata \ mydb \ mytabs1.dbf' size 10 M;
Alter user zgl default tablespace mytabs;
-- Use tabs as the default tablespace of zgl.
Grant unlimited tablespace to zgl;
-- Grant the tablespace operation permission to zgl.

Exception

Example:


Create or replace procedure
Pro_test_exception (vid in varchar2) is
UserName varchar2 (30 );
Begin
Select name into userName from t_user where id = vid;
Dbms_output.put_line (userName );
Exception
When no_data_found then
Dbms_output.put_line ('No data found! ');
When too_many_rows then
Dbms_output.put_line ('multiple rows of data are returned! ');
End pro_test_exception;

Security Management

Run the following statement to log on to sysdba:


User authorization:

Alter user zgl account lock; -- lock the account.

Alter user zgl identified by zgl11; -- modify the user password.

Alter user zgl account unlock; -- unlock the account.

Alter user zgl default tablespace tt; -- modify the default tablespace of user zgl to tt.

Create user qqq identified by qqq123 default tablespace tt; -- create a user.

Grant connect to qqq; -- grant the connect permission to qqq.

Grant execute on zgl. proc01 to test; -- grant the process zgl. proc01 to the user test.

Grant create user to zgl; -- grant zgl the permission to create a user.

Revoke create user from zgl; -- revoke zgl's permission to create a user.


Role authorization:

Create role myrole; -- create a role myrole

Grant connect to myrole; -- grant connect permission to myrole

Grant select on zgl. t_user to myrole; -- grant the permission to query zgl. t_user to myrole

Grant myrole to test; -- grant the role myrole to the test user

Profile (configuration file ):

Global settings: You can set the number of logins in the summary file. If this number is exceeded, the user is locked.


Synonym

Example of creating a synonym:


Create public synonym xxx for myuser. t_user
Create synonym t_user for myuser. t_user
Select * from dba_synonyms where table_name = 't_ user'

Cross-Database Query


Create database link dblinkzgl
Connect to myuser identified by a using 'mydb'
Select * From t_user @ dblinkzgl

Course example

Example 1:


Create or replace procedure pro_test_cursor is
UserRow t_user % rowtype;
Cursor userRows is
Select * from t_user;
Begin
For userRow in userRows loop
Dbms_output.put_line
(UserRow. Id | ',' | userRow. Name | ',' | userRows % rowcount );
End loop;
End pro_test_cursor;

Example 2:


Create or replace procedure
Pro_test_cursor_oneRow (vid in number) is
UserRow t_user % rowtype;
Cursor userCur is
Select * from t_user where id = vid;
Begin
Open userCur;
Fetch userCur into userRow;
If userCur % FOUND then
Dbms_output.put_line
(UserRow. id | ',' | userRow. Name );
End if;
Close userCur;
End pro_test_cursor_oneRow;

Record example


Create or replace
Procedure pro_test_record (vid in varchar2) is
Type userRow is record (
Id t_user.id % type,
Name t_user.name % type
);
RealRow userRow;
Begin
Select id, name
RealRow from t_user where id = vid;
Dbms_output.put_line
(RealRow. id | ',' | realRow. name );
End pro_test_record;

Rowtype example


Create or replace procedure
Pro_test_rowType (vid in varchar2) is
UserRow t_user % Rowtype;
Begin
Select * into userRow from t_user where id = vid;
Dbms_output.put_line
(UserRow. id | ',' | userRow. name );
End pro_test_rowType;

Article