Common Sqlplus commands

Common Sqlplus commands are as follows:
 
1. login system user:
Sqlplus and then enter the system user name and password
 
Log on to another user:
Conn user name/password;
 
 
2. Create a tablespace
 
Create tablespace space name
 
Datafile 'C: "Space name' size 15 M -- storage path of the tablespace, initial value: 15 M
 
AutoExtend on next 10 M -- the automatic increase of space is 10 M
 
Permanent online; -- permanent use
 
 
 
3. Create a user
 
Create user jun -- create username as jun
 
Identified by syj -- create a password as syj
 
Default tablespace name -- default tablespace name
 
Temporary tablespace temp -- temporary tablespace is temp
 
Profile default -- restricted by the profile file
 
Quota unlimited on tablespace name; -- unlimited table creation under the tablespace
 
 
 
4. Create a role
 
Create role name identified by password;
 
 
 
5. Authorize a role
 
Grant create session to role name; -- grant the role the permission to create a session
 
Grant role name to user name; -- grant role to user
 
 
 
6. grant permissions to users
 
Grant create session, resource to shi; -- grant all permissions to shi users
 
Grant create table to shi; -- grant the shi user the permission to create a table.
 
 
 
7. select table_name from user_tables; view all tables under the current user
 
 
 
8. select tablespace_name from user_tablespaces; view the tablespace of the current user
 
 
 
9. select username from dba_users; to view all user name commands, you must use sys as sysdba to log on. (well, this is the case on the Internet, but you can also view them using system .)
 
 
 
10. Create a table
 
Create table Name
(
Id int not null,
Name varchar2 (20) not null
) Tablespace name -- the tablespace to which the table belongs
Storage
(
Initial 64 K -- initial table values
Minextents 1 -- Minimum extension Value
Maxextents unlimited -- maximum extension Value
);
 
 
 
11. -- add primary keys and indexes to the usrs table
 
Alter table users
 
Add constraint pk primary key (ID );
 
 
 
12. Add Foreign keys to the created users table
 
Alter table users
 
Add constraint fk_roleid foreign key (roleid)
 
References role (role_id) on delete cascad; -- write the column in the base table below
 
On delete cascad is used to create cascading
 
 
 
 
13. connect two columns
 
Select concat (name, id) from table name; -- connect name and id
 
 
 
14. truncate a string
 
Select column (name, 'lil') from table name; -- remove 'lil' from name
 
 
 
 
15. You must write before running the transaction.
 
Set serveroutput on; -- open the input and output (if not written, no information is printed)
 
 
 
16. Application of while
 
Declare -- Declaration Part
 
Ccc number: = 1; -- resume employment
 
A number: = 0;
 
Begin -- start of a transaction
 
While ccc <= 100 loop -- loop
 
If (ccc mod 3) = 0) then -- Condition
 
Dbms_output.put_line (ccc | ','); -- print the display
 
A: = a + ccc;
 
End if; -- end if
 
Ccc: = ccc + 1;
 
End loop; -- end loop
 
Dbms_output.put_line ();
 
End; -- end the transaction
/
 
 
 
 
17. select into usage -- only one row of result set can be processed
 
Declare
 
Name varchar (30 );
 
Begin
 
Select username into name
 
From users
 
Where id = 2;
 
Dbms_output.put_line ('name: '| name );
 
End;
/
 
 
 
18. The % rowtype attribute can be used to easily declare and record variables and other structures at runtime.
 
Set serveroutput on;
 
Declare
 
Utype users % rowtype;
 
Begin
 
Select * into utype from users where id = 20;
 
Dbms_output.put_line ('name' | utype. username );
 
Dbms_output.put_line ('birthday' | utype. brithday );
 
End;
/-- % Rowtype: to copy a table
 
 
 
19. definition and use of cursors
 
Declare
 
Cursor ucur is select * from users; -- declares the Cursor
 
Us users % rowtype; -- defines the variable to be matched with the cursor
 
Begin
 
Open ucur; -- Open the cursor
 
Fetch ucur into us;
 
While ucur % found loop -- uses a loop to traverse the query results of the cursor
 
Dbms_output.put_line ('name: '| us. username | 'birthday' | us. brithday );
 
Fetch ucur into us;
 
End loop;
 
Close ucur; -- Close the cursor
 
End;
========================================================
 
 
% Found when the previous fetch statement corresponds to at least one row of the database, the % found attribute value is true; otherwise, the value is false;
 
% Notfound: when the previous fetch statement does not have a corresponding database row, the % notfound attribute value is true; otherwise, the value is false;
 
% When the cursor is opened, the % isopen attribute value is true; otherwise, the value is false;
 
% Rowcount displays the number of rows retrieved from the display cursor so far
 
 
 
 
 
20. Delete

Drop tablespace space name including contents; -- delete the tablespace and content in it
 
Drop table name -- delete a table
 
Drop user Username -- delete a user

-------------------------------------

Oracle users
 
# Su-oracle
 
Create a tablespace-> Create a new user-> authorize a user
 
1. Create a tablespace
 
SQL> CREATE TABLESPACE test01 DATAFILE '/data/oracle/oradata/db/test01.dbf' SIZE 1024 M UNIFORM SIZE 128 k;
 
1) DATAFILE: path for storing tablespace data files
 
2) SIZE: Set it to 1 GB at first.
 
3) UNIFORM: the size of the specified area is 128 k. If not specified, the default area size is 64 k.
 
4) The space name test01 is not required to be the same as the data file name test01.dbf.
 
Alter database to modify the size of a space extension
 
 
SQL> ALTER DATABASE DATAFILE '/data/oracle/oradata/db/test01.dbf'' AUTOEXTEND ON;
 
 
1) autoextend on: In Oracle10g, You need to manually start the automatic partition expansion function.
 
 
 
 
2. Create a new user
 
SQL> CREATE USER test IDENTIFIED BY 123456 DEFAULT TABLESPACE test01;
 
1) create a user to allocate table space at the same time
 
 
 
 
Three-user authorization
 
1) User Role authorization
 
SQL> CRANT CONNECT, RESOURCE TO test;
 
A) Here I will assign the CONNECT and RESOURCE roles to the new user test, and test will have operation permissions for these two roles.
 
 
2) direct authorization
 
Multiple users
 
SQL> CRANT SELECT, INSERT, UPDATE, DELETE, ON USERS TO test, test1;
 
 
 
 
The following two methods can be used to log on to Oracle db:
 
One SQLPLUS
 
1) Local Login
 
SQL> sqlplus test/123456
 
 
2) Remote Logon
 
Assume that the database is on another server.
 
SQL> sqlplus test/123456 @ db
 
 
 
2. PLSQL Developer
 
As described in the previous article, enter the account password.
 
 
 
 
The following is related to the above and will be used in the work.
 
1. delete a user
 
SQL> DROP USER test CASCADE;
 
 
2. Delete tablespaces
 
SQL> DROP TABLESPACE test01 INCLUDING CONTENTS AND DATAFILES;
 
1) Delete the tablespace content and data files.
 
2) Generally, invalid tablespaces occupy disk space, so this should be very common.
 
 
 
 
View User Role Permissions
 
Select * from user_role_privs;
 
View User table Permissions
 
Select * from user_tab_privs;
 
-------
 
/* SQL * Plus: Release 10.2.0.1.0-Production on Sunday March 4 13:54:32 2012 Copyright (c) 1982,200 5, Oracle. All rights reserved .*/

Create tablespace sini_data
DATAFILE 'd: \ oracle \ oradata \ sini_data.dbf' SIZE 500 m
Autoextend on next 1 m MAXSIZE UNLIMITED
DEFAULT STORAGE
(
Initialize 128 k
NEXT 1 m
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
);
/

Create tablespace sini_index DATAFILE
'D: \ oracle \ oradata \ sini_index.dbf 'size 200 m
Autoextend on next 1 m MAXSIZE UNLIMITED
DEFAULT STORAGE
(
Initialize 128 k
NEXT 1 m
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
);
/

Create tablespace sini_temp DATAFILE
'D: \ oracle \ oradata \ sini_temp.dbf 'size 200 m
Autoextend on next 1 m MAXSIZE UNLIMITED
DEFAULT STORAGE
(
Initialize 128 k
NEXT 1 m
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
);
/
 
Alter tablespace sini_temp TEMPORARY;
/
 
Create user bingo identified by bingo default tablespace sini_data temporary tablespace sini_temp;
/
 
Grant connect, resource to bingo;
/
 
Grant create table to bingo;
/
 
Alter user bingo quota unlimited on sini_data;
/
 
Alter user bingo quota unlimited on sini_index;
/
 
Alter user bingo quota unlimited on sini_temp;
/
 
 
The following two statements 1. Generate a tablespace named TbsUserData. 2. authorize user User1 to use the tablespace without restrictions.

Create tablespace TbsUserData
DATAFILE 'd: \ data \ UserData. dbf'50 M
Default storage (INITIAL 2 M
NEXT 2 M
PCTINCREASE 10)
/

Alter user User1 Quota Unlimited on TbsUserData
/