1. Operation flow of Oracle database

First, we need to understand the whole process of the Oracle database, as shown in the following figure. 
 
Following is an introduction to tablespaces and user actions based on the following three steps:

• Step 1: Use the cmd command to open the DOS window.

• Step 2: Enter the command sqlplus/nolog to enter the oracle console.

• Step 3: Enter the conn username/password sysdba to enter as a DBA and prompt for a successful connection.(Note: Users here must have DBA privileges, such as sys)

• Note: clear SCR can be used to clear the screen during operation

2. Operational tablespaces

2.1 Create a tablespace

create tablespace dweb
logging 
datafile 'C:\Program Files\Oracle\Inventory\dweb.dbf' size 50m 
autoextend on next 50m maxsize 20480m 
extent management local;1234567

2.2 Delete tablespace

drop tablespace ackj including contents and datafiles;1

2.3 View tablespace usage

SELECT  a.tablespace_name Tablespace name
       ,total Table space size
       ,free Table space remaining size
       ,(total-free) Table space usage size
       ,(total/(1024*1024*1024)) as Table space size G
       ,free / (1024 * 1024 * 1024) Table space remaining size G
       ,(total - free) / (1024 * 1024 * 1024) Table space usage size G
       ,round((total - free) / total, 4) * 100 Usage  FROM (SELECT tablespace_name, SUM(bytes) free          FROM dba_free_space         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) total          FROM dba_data_files         GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name;123456789101112131415

3. Operating users

3.1 Create Users

In practice, a user is generally responsible for one tablespace, so when creating a user, it needs to be given the tablespace to which it belongs.

create user dweb identified by dweb default tablespace dweb;1

3.2 Delete Users

drop user dweb cascade;1

3.3 Change Password

alter user dweb identified by 123456;1

3.4 View User List

select username from dba_users;select * from all_users;12

4. User Authorization

4.1 Permission Description

4.2 User Authorization

grant connect,resource,dba to dweb;grant create any sequence to dweb;grant create any table to dweb;grant delete any table to dweb;grant insert any table to dweb;grant select any table to dweb;grant unlimited tablespace to dweb;grant execute any procedure to dweb;grant update any table to dweb;grant create any view to dweb;12345678910

5. Related Operations

--View the tablespace the user belongs to (user name must be uppercase) select username,default_tablespace from dba_users where username='DWEB'; --View the tablespace the user has (user name must be uppercase) select * from dba_sys_privs where grane='DWEB'; --Oracle's method of deleting all tables for the specified user (user name must be uppercase)Capitalized) select'Drop table'||table_name|';'from all_tableswhere owner='DWEB'; - get all tables under the current user select table_name from user_tables; - delete all table data under a user select'truncate table'|| table_name from user_tables; - command alter with foreign key constraints enabledTable_name enable constraint_name; --command to disable foreign key constraints alter table_name disable constraint_name; --use SQL to find out the constraint name of the foreign key in the database select'alter table'||table_name|'enable constraint'||constraint_name|'; from user_cOnstraints where constraint_type='R'; select'alter table'||table_name|'disable constraint'||constraint_name|';'from user_constraints where constraint_type='R'; 12345678910111214151618192122222425

--ORACLE Enable foreign keys and triggers SET SERVEROUTPUT ON SIZE 1000000BEGINfor c in (select 'ALTER TABLE '||TABLE_NAME||' ENABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);begin
 EXECUTE IMMEDIATE c.v_sql;
 exception when others then
 dbms_output.put_line(sqlerrm); end;end loop; for c in (select 'ALTER TABLE '||TNAME||' ENABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop
 dbms_output.put_line(c.v_sql);
 begin
 execute immediate c.v_sql;exception when others then
 dbms_output.put_line(sqlerrm); end;end loop;end;/ 
commit;12345678910111213141516171819202122

--Disable scripts SET SERVEROUTPUT ON SIZE 1000000BEGINfor c in (select 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);begin
 EXECUTE IMMEDIATE c.v_sql;
 exception when others then
 dbms_output.put_line(sqlerrm); end;end loop; for c in (select 'ALTER TABLE '||TNAME||' DISABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop
 dbms_output.put_line(c.v_sql);
 begin
 execute immediate c.v_sql;exception when others then
 dbms_output.put_line(sqlerrm); end;
 end loop;
 end;
 / commit;