Http://blog.csdn.net/starnight_cbj/article/details/6792364Oracle establish tablespace and user. Category: Oracle 2011-09-20 08:58 80909 people read reviews (11 ) Collection report Oracletableuserfileinsertdeleteoracle establish tablespace and user [SQL] view plaincopyprint?01. Steps to establish tablespaces and users: 02. User 03. Create: Create User User name identified by "password"; 04. Authorization: Grant create session to username; Grant CREATE table to user name; . Grant create tablespace to user name; . Grant create view to username; Steps to establish a tablespace and User: User creation: Create user username identified by "password"; authorization: Grant create session to username; Grant CREATE table to user name; Grant create tablespace to user name; Grant CREATE view to user name; [SQL] View plaincopyprint?01. Table Space 02. Create a tablespace (typically a table space and an index space that has n stored data): 03.create tablespace tablespace name 04.datafile ' path (to build the path first) \***. DBF ' size *m 05.tempfile ' path \***.dbf ' size *m 06.autoextend on-auto-grow 07.--There are also some commands for defining the size, see need 08. Default storage (initial 100K, next 100k, 11.); Tablespace establishes table space (typically a table space with n stored data and an index space): Create tablespace tablespace name datafile ' path (to build the path first) \***.dbf ' size *mtempfile ' path \***.dbf ' size *m Autoextend on-auto-grow-there are some commands for defining the size of the default Storage (initial 100K, next 100k,); [SQL] View plaincopyprint?01. Example: Create Tablespace 02.create tablespace demospace 03.datafile ' E:/oracle_tablespaces/demospace_ TBSPACE.DBF ' 04.size 1500M 05.autoextend on next 5M maxsize 3000M; 06. Delete Table Space 07.drop tablespace demospace including contents and Datafiles example: creating a table Space create tablespace demospace datafile ' e:/or acle_tablespaces/demospace_tbspace.dbf ' size 1500M autoextend on next 5M maxsize 3000M; Delete tablespace drop Tablespace demospace Inc Luding contents and Datafiles[sql] View plaincopyprint?01. User Rights 02. Grant users permission to use tablespace: 03.alter user username quota unlimited on table space; 04. Or alter user username quota *m on table space; User Rights grant users permission to use tablespace: Alter user username quota unlimited on table space, or alter user username quota *m on table space; full example: [SQL] View plaincopyprint?01. --Table Space 02.CREATE tablespace SDT 03.DATAFILE ' F:\tablespace\demo ' size 800M 04. EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; 05.--Index Table Space 06.CREATE tablespace sdt_index 07.DATAFILE ' F:\tablespace\demo ' size 512M 08. EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; 09.10.--2 Users 11.create user demo identified by demo 12.default Tablespace demo; 13.14.--3. Empowering 15.grant Connect,resource to demo; 16.grant Create any sequence to demo; 17.grant Create any table to demo; 18.grant Delete any table to demo; 19.grant Insert any table to demo; 20.grant Select any table to demo; 21.grant Unlimited tablespace to demo; 22.grant execute any procedure to demo; 23.grant Update any table to demo; 24.grant Create any view to demo; --Table space Create tablespace sdtdatafile ' F:\tablespace\demo ' size 800M EXTENT MANAGEMENT LOCAL SEGMENT space MANAGEMENT AUTO; --Index tablespace create tablespace sdt_indexdatafile ' F:\tablespace\demo ' size 512M EXTENT MANAGEMENT LOCAL SEGMENT space Managemen T AUTO; --2. Create user demo identified by demo default Tablespace demo; --3. Empowering Grant Connect,resource to demo;grant create any sequence to demo;grant create any table to demo;grant delete any tab Le to demo;grant Insert no table to demo;grant Select any table to Demo;grant UnlimiTed Tablespace to Demo;grant execute any procedure to demo;grant update any table to demo;grant create any view to demo; [SQL] View plaincopyprint?01.--Import Export command 02.ip Export method: Exp DEMO/[EMAIL PROTECTED]:1521/ORCL file=f:/f.dmp full=y 03.exp Demo/[email protected] file=f:/f.dmp full=y 04.imp demo/[email protected] file=f:/f.dmp full=y ignore=y
Reference Oracle establishes tablespace and user