Oracle establishes tablespaces and users

[SQL]View PlainCopy

2. To establish the tablespace and the user's steps:
4. User
6. Create:Create user username identified by "password";
8. Authorization:Grant create session to user name;
10. Grant create table to user name;
12. Grant create tablespace to user name;
14. Grant Create view to user name;

[SQL]View PlainCopy

1. Table Space
2. Create a tablespace (typically a table space with n stored data and an index space):
3. Create tablespace table space name
4. DataFile ' Path (to build the path first) \***.dbf ' size *m
5. Tempfile ' path \***.dbf ' size *m
6. Autoextend on -automatic growth
7. --There are some commands to define the size, see the need
8. Default Storage (
9. Initial 100K,
10. Next 100k,
11. );

[SQL]View PlainCopy

1. Example: creating a table space
2. Create Tablespace Demospace
3. DataFile ' e:/oracle_tablespaces/demospace_tbspace.dbf '
4. Size 1500M
5. Autoextend on next 5M maxsize 3000M;
6. Delete Table space
7. Drop tablespace demospace including contents and datafiles

[SQL]View PlainCopy

1. User Rights
2. To grant a user permission to use the tablespace:
3. Alter user username quota Unlimited on table space;
4. or alter user username quota *m on table space;

Complete Example:

[SQL]View PlainCopy

2. --Table space
4. CREATE tablespace SDT
6. DataFile ' F:\tablespace\demo ' size 800M
8. EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
10. --Index Table space
12. CREATE tablespace Sdt_index
14. DataFile ' F:\tablespace\demo ' size 512M
16. EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
20. --2. Build Users
22. Create User demo identified by demo
24. Default Tablespace demo;
28. --3. Empowering
30. Grant Connect,resource to demo;
32. Grant create any sequence to demo;
34. Grant create any table to demo;
36. Grant Delete any table to demo;
38. Grant insert any table to demo;
40. Grant select any table to demo;
42. Grant Unlimited tablespace to demo;
44. Grant execute any procedure to demo;
46. Grant update any table to demo;
48. Grant Create any view to demo;

[SQL]View PlainCopy

1. --Import and export commands
2. IP Export mode: Exp demo/[email protected]:1521/orcl file=f:/f.dmp full=y
3. Exp Demo/[email protected] file=f:/f.dmp full=y
4. Imp demo/[email protected] file=f:/f.dmp full=y ignore=y

Oracle establishes tablespaces and users