Shulou(Shulou.com)06/01 Report--

1. Operation flow of Oracle database

First of all, we need to understand the whole operation flow of the Oracle database, as shown in the following figure.

Next, the introduction of the tablespace and the operation of the user needs to be 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 the DBA role to indicate that the connection is successful. (note: the user must have dba permission here, such as sys)

Note: you can use clear SCR to clear the screen during operation.

two。 Operation tablespace 2.1Create tablespace create tablespace dweblogging datafile'C:\ Program Files\ Oracle\ Inventory\ dweb.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;12345672.2 delete tablespace drop tablespace ackj including contents and datafiles 12.3 View tablespace using SELECT a.tablespace_name tablespace name, total tablespace size, free tablespace remaining size, (total-free) tablespace usage size, (total/ (1024 "1024)) as tablespace size G, free / (1024" 1024) tablespace remaining size G (total-free) / (1024 * 1024 * 1024) tablespace 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 1234567891011121314153. Action user 3.1 create user

In practice, a user is generally responsible for corresponding a tablespace, so when creating a user, it is necessary to give the tablespace to which it belongs.

Create user dweb identified by dweb default tablespace dweb;13.2 delete user drop user dweb cascade;13.3 change password alter user dweb identified by 123456X 13.4 view user list select username from dba_users;select * from all_users;124. User Authorization 4.1user 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;123456789105. Related actions-View the tablespace to which the user belongs (user name must be uppercase) select username,default_tablespace from dba_users where username='DWEB';-- to view the tablespace the user has (user name must be uppercase) select * from dba_sys_privs where grantee='DWEB';--Oracle method to delete all tables of the specified user (username must be uppercase) select 'Drop table' | | table_name |'; 'from all_tableswhere owner='DWEB' -- getting all tables under the current user select table_name from user_tables;-- deleting all table data under a user select 'truncate table' | | table_name from user_tables;-- commands enabling foreign key constraints alter table table_name enable constraint constraint_name;-- disabling foreign key constraints command alter table table_name disable constraint constraint_name;-- uses SQL to find out the constraint names of all foreign keys 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';12345678910111213141516171819202122232425--ORACLE enables foreign key and trigger 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') loopDBMS_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.vaccounsqlterexception when others then dbms_output.put_line (sqlerrm); end;end loop;end;/ commit 12345678910111213141516171819202122 ALTER TABLE-disable script 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') loopDBMS_OUTPUT.PUT_LINE (C.V_SQL); begin EXECUTE IMMEDIATE c.vroomsql; 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.vaccounsqlash exception when others then dbms_output.put_line (sqlerrm); end; end loop; end; / commit

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.