Common SQL instruction sets of oralce

Turn: http://www.javaeye.com/topic/431907

 

1. Default users of the system

 

1. Sys; // System Administrator with high Permissions
2. System; // local administrator with the next high permission
3. Scott; // common user. The default password is Tiger and is not unlocked by default.

2. Login

1. Sqlplus/As sysdba; // log on to the sys account
2. Sqlplus sys as sysdba; // same as above
3. Sqlplus Scott/tiger; // log on to normal user Scott

3. Manage Users

1. Create user zhangsan; // under the Administrator account, create the user zhangsan
2. Alert user Scott identified by tiger; // modify the password

4. Grant Permissions
1. The default normal user Scott is not unlocked by default and cannot be used. The new user does not have any permissions and must be granted permissions.

1. /* Administrator authorization */
2. Grant create session to zhangsan; // grant the zhangsan user the permission to create a session, that is, the login permission.
3. Grant unlimited session to zhangsan; // grant the zhangsan user the permission to use the tablespace.
4. Grant create table to zhangsan; // grant the table creation permission
5. Grante drop table to zhangsan; // grant the table deletion permission
6. Grant Insert table to zhangsan; // insert table permission
7. Grant update table to zhangsan; // Modify Table Permissions
8. Grant all to public; // This is important. Grant all permissions to all users (public)

2. oralce is more rigorous in permission management. Common users cannot access each other by default and must be authorized to each other.

1. /* Oralce strictly manages permissions, and common users cannot access each other by default */
2. Grant select on tablename to zhangsan; // grant the zhangsan user the permission to view the specified table.
3. Grant drop on tablename to zhangsan; // grant the table deletion permission.
4. Grant insert on tablename to zhangsan; // grant the insert permission
5. Grant update on tablename to zhangsan; // grant the table modification permission.
6. Grant insert (ID) on tablename to zhangsan;
7. Grant Update (ID) on tablename to zhangsan; // grant the insert and modify permissions for specific fields in the specified table. Note that only insert and update are allowed.
8. Grant alert all table to zhangsan; // grant permissions to any table of the zhangsan user alert

5. revoke permissions

1. The basic syntax is the same as grant. The keyword is revoke.

6. View Permissions

1. Select * From user_sys_privs; // view all permissions of the current user
2. Select * From user_tab_privs; // view the table permissions of the users used

VII. User table of the operation table

1. /* Add the user name before the table name, as shown below */
2. Select * From zhangsan. tablename

8. Permission Transfer
That is, user a grants permissions to user B, and user B can grant operation permissions to user C. The command is as follows:

1. Grant alert table on tablename to zhangsan with admin option; // keyword with admin Option
2. Grant alert table on tablename to zhangsan with grant option; // The keyword with grant option is similar to admin.

9. Roles
A role is a set of permissions. You can assign a role to a user.

1. Create role myrole; // create a role
2. Grant create session to myrole; // grant the create session permission to myrole
3. Grant myrole to zhangsan; // grant the role of myrole to the zhangsan user
4. Drop role myrole; delete a role
5. /* However, some permissions cannot be granted to the role, such as the unlimited tablespace and any keywords */

 

 

 

 

Search all fields in the database for the corresponding table
Select C. column_name, C. table_name from dba_tab_columns C where owner =''

Check the top three scro class scores for each subject
Select ID, name, class, scro
From (select row_number () over (partition by class order by scro DESC) CNT,
ID,
Name,
Class,
Scro
From student)
Where a. CNT <= 3;

Search for the first three sorted rows
Select *
From (select RW. *, rownum
From (select *
From student d
Where D. Class = 'B'
Order by D. scro DESC) RW
Where RW. ID> = 1
Order by RW. Class DESC) N
Where rownum <= 3

Table Replication
Insert into table_a (ID, name, age) Select B. ID, B. Name, B. Age from table_ B;

-- Trigger for deleting table data
Create or replace procedure delete_data
Is
Begin
Delete from test;
Commit;
Exception
When no_data_found
Then
NULL;
When others
Then
Raise;
End delete_data;

-- Regularly Delete the plan executed every 5 minutes
Declare
X number;
Begin
SYS. dbms_job.submit
(Job => X
, What => 'delete _ data ;'
, Next_date => to_date ('2016/25/08 00:00:00 ', 'dd/mm/yyyy hh24: MI: ss ')
, Interval => 'sysdate + 1/24/12'
, No_parse => false
);
SYS. dbms_output.put_line ('job number is: '| to_char (x ));
Commit;
End;

-- View the current Oracle plan
Select job, next_date, next_sec, failures, broken from user_jobs;

-- Delete a job
Begin
Dbms_job.remove (46); -- 46 is the job number.
End;

-- Insert all data of fields corresponding to the old table to the new table
Insert into AAA (ID, name) Select B. ID, B. name from BBB;

Article Search: [Click to package this article]

Select * from V $ version; view the server version
Select * From all_users; # view all users
Select name from V $ database; # view the current database
Database test; # enter the Test Database
Select * from V $ instance; # view all database instances
Shutdown immediate # Shut down the database
Alter user SYS identified by new_password; # change the User Password
Select username, password from dba_users; # view the user and password in the instance
Show parameter control_files; # view the control file;
Select member from V $ logfile; # view log files
Show parameter; # view database Parameters
Select * From user_role_privs; # view the role of the current user
Select username, default_tablespace from user_users; # view the default tablespace of the current user
Alter user system identified by [Password] # modify a user's password
Alter user "Scott" account unlock # unlock a Scott user
Show parameter Processes; # view the maximum number of sessions

Select username, Sid, serial # from V $ session; view the current session connection

Select * From all_tables; # view all data tables of the current database
 

View the table structure: DESC all_tables;

Create a user and grant permissions
### ---------------------------- Create a user and grant permissions ------------------------------------####-
Create user mpss
Identified by "mpss12"
Default tablespace ts_mpss_data
Temporary tablespace temp;

Grant permissions to users
Grant connect to mpss;
Grant resource, create session to mpss; development role
Grant create procedure to dbuser; # these permissions are sufficient for development and production environments.

Authorize a user
Grant DBA to spms; -- Grant DBA Permissions
Grant unlimited tablespace to LXG; -- Grant unlimited tablespace
Grant select any table to LXG; -- Grant the permission to query any table
Grant select any dictionary to LXG; -- Grant query to any dictionary

Delete a user
Drop user mpss cascade;

Create a tablespace

Create tablespace "ts_mpss_data"
Logging
Datafile '/mpss/data/ts_mpss_data.dbf' size 1024 m
Extent management local segment space management auto

Create temporary tablespace

Create
Temporary tablespace "swvip" tempfile '/APP/Oracle/oradata/
Sworacle/swvip. dbf'size 5 m extent management Local Uniform
Size 1 m

View tablespaces
### ---------------------------- View the tablespace size ------------------------------------####-
Select D. tablespace_name "name ",
To_char (. bytes-Decode (F. bytes, null, 0, F. bytes)/1024/1024)/(. bytes/1024/1024) * 100, '000000') "used (% )",
To_char (decode (F. bytes, null, 0, F. bytes)/1024/1024), '20140901') "free (m )"
From SYS. dba_tablespaces D, SYS. sm $ ts_avail A, SYS. sm $ ts_free F
Where D. tablespace_name = A. tablespace_name
And F. tablespace_name (+) = D. tablespace_name;
### Region ###--------------------------------------------------------------------------------------####-

Select D. tablespace_name, space "sum_space (m)", blocks Samsung, SPACE-NVL (free_space, 0) "used_space (m )",
Round (1-nvl (free_space, 0)/space) * 100,2) "used_rate (%)", free_space "free_space (m )"
From
(Select tablespace_name, round (sum (bytes)/(1024*1024), 2) space, sum (blocks) Blocks
From dba_data_files
Group by tablespace_name) d,
(Select tablespace_name, round (sum (bytes)/(1024*1024), 2) free_space
From dba_free_space
Group by tablespace_name) f
Where D. tablespace_name = f. tablespace_name (+)
Union all -- if have tempfile
Select D. tablespace_name, space "sum_space (m)", blocks sum_blocks,
Used_space "used_space (m)", round (nvl (used_space, 0)/Space * 100,2) "used_rate (% )",
Nvl (free_space, 0) "free_space (m )"
From
(Select tablespace_name, round (sum (bytes)/(1024*1024), 2) space, sum (blocks) Blocks
From dba_temp_files
Group by tablespace_name) d,
(Select tablespace_name, round (sum (bytes_used)/(1024*1024), 2) used_space,
Round (sum (bytes_free)/(1024*1024), 2) free_space
From v $ temp_space_header
Group by tablespace_name) f
Where D. tablespace_name = f. tablespace_name (+ );

View the name and size of the tablespace physical file;

### ------------------ Name and size of a tablespace physical file ------------------------####-
Select tablespace_name, file_id, file_name,
Round (Bytes/(1024*1024), 0) total_space
From dba_data_files
Order by tablespace_name;
### Region ###------------------------------------------------------------------------------------####-

View data file placement path
### Region ###------------------------------------------------------------------------------------####-

SQL> Col file_name format A50
SQL> select tablespace_name, file_id, Bytes/1024/1024, file_name from dba_data_files order by file_id;
### Region ###------------------------------------------------------------------------------------####-

View database objects

Select owner, object_type, status, count (*) Count # From all_objects group by owner, object_type, status;

Expand table space
### Region ###------------------------------------------------------------------------------------####-
Alter tablespace g000 add datafile '/dev/vgbilling/rg000_lv03 'size 7500 m;
Add a 7500m logical volume to the g000 table '/dev/vgbilling/rg000_lv03'
### Region ###------------------------------------------------------------------------------------####-

Check objects locked for a long time
### Region ###------------------------------------------------------------------------------------####-

SQL> select. session_id,. process,. locked_mode, B. object_name, B. object_type, B. status from V $ locked_object A, dba_objects B where. object_id = B. object_id;
### Region ###------------------------------------------------------------------------------------####-

-- Create a view for hy user (use Leon user)

 

Create or replace view view_tablename

Select column... From table;

 

-- Grant/Revoke object privileges

Grant select, update on res_xim_card to hy;

 

-- Create a synonym

Create synonym res_xim_card for yy. res_xim_card;

Sys User Login
Create a tablespace:
SQL> Create tablespace lmsstemp datafile 'f:/oradata/lmss/lmsstemp01.dbf' size 1
024 M extent management local;

Add data files to the tablespace
Alter tablespace lmsstemp add datafile 'f:/oradata/lmss/lmsstemp02.dbf' size 1
024 m;

Change tablespace to auto Scaling
SQL> alter database datafile 'f:/oradata/lmss/lmsstemp01.dbf' autoextend on;

View tablespace Information
SQL> select file_name, tablespace_name, autoextensible from dba_data_files;