Common OraclePL/SQL commands

1. Use OraclePLSQL 1. database object permission (DataBaseSystemPrivilege) Orac

1. Oracle PL/SQL usage 1. database object permission (DataBaseSystem Privilege) Orac

I. Oracle PL/SQL usage
1. User Management
Database System permission (DataBaseSystem Privilege)

Database Object permission (DataBaseSystem Privilege)

Oracle internal users Sys and System

Create user: create user user_test identified by user_test123;

Modify user: alter user user_test identified by user_test123;

Delete user: drop user user_test;

Delete a user and its object: drop user user_testCASCADE;

User authorized connection: grant connect, resource to user_test;

Use the following command in sqlplus to check the list of user tables:

Sqlplus scott/tiger -- use sqlplus to log on to the oracle database

Col table_name format a30; -- specify the column width of table_name

Col table_type format a10; -- specify the column width of table_type

Select * from cat; -- list user data tables

2. role management
1) Connect Role: a temporary user who does not need to create a table.

2) Resource Role: more reliable and formal database users.

3) Dba Role (database administrator Role): Has all system permissions.

Operation:

Grant (authorization) command: grant connect, resource to user_test;

Revoke (UNDO) command: revoke connect, resource to user_test;

Create role: In addition to the three standard roles provided by the system, you can create your own role.

Create role user_test_role;

Role authorization: grant select on t_service to user_test_role;

Note: All users with the student role have the select permission on the t_service table.

Delete a role: droprole user_test_role;

View the roles of a user:

Select grant_role fromdba_role_privs where grantee = 'Scott ';

View the permissions of a user:

Select privilege from dba_sys_privs wheregrantee = 'Scott ';

Select privilege fromdba_sys_privs where grantee = 'connect ';

Default User: Super User in Sys: oralce, which is mainly used to maintain system information and manage instances.

System: the default System administrator in oracle with dba permissions. Users, permissions, and storage of oracle databases are usually managed.

Scott: A demo account of the oracle database, which is created during database installation.

User authorization: grant permission [on Object Name] to user name [with grant option];

Grant select on scott. emp to user_test with grant option;

Grant create session to user_test;

3. permission management
Grant the user_test user the permission to view emp table data.

1. Verify user_test's permission to query scott's emp table.

SQL> select * from scott. emp;

2. Grant the emp Table query permission to the user user_test.

SQL> conns cott/scott @ test;

SQL> grant select on scott. emp to user_test;

3. log on to the user_test account and query the table emp information of scott.

SQL> conn user_test/u01 @ test;

SQL> select * from scott. emp;

Revoke permission: revoke permission [on Object Name] from User Name

SQL> revoke select on scott. emp from user_test;

Change user Password: alter user Username identified by new password;

Permission-related tables:

1. dba_sys_privs (all system permissions)

2. user_sys_privs (user's system permissions)

3. user_col_privs (object permissions owned by the user)

Common system permission assignment statements:

SQL> grant create session to user_test;

SQL> grant create table to user_test;

SQL> grant unlimited tablespace to user_test;

SQL> grant create session to public;

Grant and revoke object permissions:

SQL> grant select on mytable to user_test;

SQL> grant all on mytable to user_test;

SQL> revoke select on mytable from user_test;

SQL> revoke all on mytable from user_test;

In addition to granting corresponding permissions to table objects, Oracle can also control permissions to the columns of tables:

SQL> grant update (name) on mytable to user_test;

SQL> grant insert (id) on mytable to user_test;

4. Password Management
Use profile to manage user passwords

Profile is a set of password restrictions and resource management commands. When a database is created, Oracle automatically creates a profile named "default. If the profile option is not specified for the created user, the Oroacle assigns the default option to the user.

Keyword list:

SQL> create profile configuration file name limit failed_login_attempts number of attempts password_lock_time Number of days locked;

SQL> alter user name profile configuration file name;

For example, if you specify a tea user, you can only log on three times. The lock time is 2 days.

SQL> create profile lock_account limit failed_login_attempts 3 password_lock_time 2;

SQL> alter user tea profile lock_account;

Unlock an account

SQL> alter user user_test account unlock;

For example, you can use the termination password to change the password periodically.

SQL> create profile myprofile limit password_life_time 10 password_grace_time 2;

Unlock a user

SQL> alter user user_testprofile myprofile;

Delete profile

SQL> drop profile myprofile [cascade];

5. sequence
Create a primary key auto-increment table

1) Create Table t_test

Create table t_test (

Userid number (10) not null primary key,/* primary key, automatically added */

Username varchar2 (20)

);

2) create an automatic growth sequence

CREATESEQUENCE t_test_increase_sequence

INCREMENTBY 1 -- add a few

Start with 1 -- count from 1

NOMAXVALUE -- if the maximum value is not set, set the maximum value: maxvalue 9999

NOCYCLE -- always accumulate without repeating

CACHE 10;

Example: create sequence t_service_sequence INCREMENT BY1 start with 1 nomaxvalue nocycle cache 10;

3) create a trigger (add a client tool)

Create trigger t_service_triggerBEFORE

Insert ON t_service for each row/* checks whether each row is triggered */

Begin

Select t_service_sequence.nextvalinto: New. userid from dual;

End;

Example:

Create orreplacetrigger t_service_trigger

Before insert on t_service

For eachrow

Declare

-- Local variables here

Begin

Select t_service_sequence.nextvalinto: New. idfrom dual;

End t_service_trigger;

4) Submit

Commit;

5) test

Insert into test_increase (Username) values ('test ');

6. remote connection
Set the listener before connection and run the following command:

Sqlplus usr/pwd @/host: port/sid

Ii. SpringJDBC Configuration
# Flow config jdbcfor oracle

Jdbc. driver = oracle. jdbc. driver. OracleDriver

Jdbc. url = jdbc: oracle: thin: @ 127.0.0.1: 1521: orcl

Jdbc. username = user_test

Jdbc. password = user_test123

Configure the Dialect in xml:

Org. hibernate. dialect. OracleDialect

True

True

False

True

Update

Related reading:

Rlwrap

SQLPLUS spool to dynamic Log File Name

Oracle SQLPLUS prompt settings

Accelerate SQL return by setting SQLPLUS ARRAYSIZE (row prefetch)