Summary of Oracle Operational user and table spaces

1. Operation Process of Oracle database

First we need to figure out the entire operational flow of the Oracle database, as shown in.  
650) this.width=650; "title=" "style=" border:0px;vertical-align:middle;margin:0px;padding:0px;font-size:15px; background:transparent; "alt=" Oracle Operation Flow "src=" http://img.blog.csdn.net/20170426130132754?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqvcxffmzyxotkxoty=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Center "/> 
The next steps to the table space and the user's operations are to be based on the following three-step:

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

• 2nd step: Enter the command: Sqlplus/nolog, enter the Oracle console.

• 3rd Step: Enter the conn username/password Sysdba Enter in the DBA role to indicate that the connection was successful. (Note: The user must have DBA authority here, such as: SYS)

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

2. Operating table Space 2.1 Creating a table space

Create tablespace dweblogging datafile ' C:\Program files\oracle\inventory\dweb.dbf ' size 50m autoextend on next 50m maxsiz E 20480m Extent Management local;1234567

2.2 Deleting a table space

Drop tablespace ackj including contents and datafiles;1

2.3 Viewing tablespace usage

select  a.tablespace_name  table space name        ,total  table space size         ,free  table space remaining size        , (Total-free)   tablespace usage size       &nbsp, (total/(1024*1024*1024))  as  table space size g        ,free /  (1024 * 1024 * 1024)   table space remaining size G        , (Total - free)  /  (1024 * 1024 *  1024)   tablespace use 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&Nbsp;tablespace_name, sum (bytes)  total           From dba_data_files         group by tablespace_ Name)  b where a.tablespace_name = b.tablespace_name;123456789101112131415

3. Operating User 3.1 Creating a user

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

Create user DWeb identified by dweb default Tablespace dweb;1

3.2 Deleting a user

Drop User DWeb Cascade;1

3.3 Change Password

Alter user DWeb identified by 123456;1

3.4 Viewing the list of users

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 Actions

--View the tablespace to which the user belongs (user name must be capitalized) Select username,default_tablespace from dba_users where username = ' DWeb ';---View user's tablespace (username must be capitalized) select * from dba_sys_privs where grantee= ' DWeb ';-- Oracle removes all tables from the specified user (username must be capitalized) select  ' drop table  ' | | table_name| | '; '  from all_tableswhere owner= ' DWeb ';--get all the 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 to enable foreign KEY constraints alter table table_name enable  constraint constraint_name; --command to disable foreign KEY constraints Alter table table_name disable constraint  constraint_name;--using SQL to isolate the constraint name of a key outside 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 enable 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.v_sql; Exception when others then dbms_output.put_line (SQLERRM);  end;end loop;end;/  commit;12345678910111213141516171819202122

--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.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;

This article is from the "Richie Space" blog, so be sure to keep this source http://richieboy.blog.51cto.com/12852819/1919710

Summary of Oracle Operational user and table spaces