oracle11gR2创建用户和表空间记录

1、使用system登录PL/SQL Developer或sysdba用户登录sqlplus

2、查询表空间
SQL> select * from v$tablespace;

       TS# NAME                           INCLUDED_IN_DATABASE_BACKUP BIGFILE FLASHBACK_ON ENCRYPT_IN_BACKUP
---------- ------------------------------ --------------------------- ------- ------------ -----------------
         0 SYSTEM                         YES                         NO      YES         
         1 SYSAUX                         YES                         NO      YES         
         2 UNDOTBS1                       YES                         NO      YES         
         4 USERS                          YES                         NO      YES         
         3 TEMP                           NO                          NO      YES         
         6 EXAMPLE                        YES                         NO      YES         

6 rows selected

3、查询表空间文件
SQL> select * from v$datafile;

     FILE# CREATION_CHANGE# CREATION_TIME        TS#     RFILE# STATUS  ENABLED    CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME   OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME      BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME                                                                             PLUGGED_IN BLOCK1_OFFSET AUX_NAME                                                                         FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIME FOREIGN_DBID FOREIGN_CREATION_CHANGE# FOREIGN_CREATION_TIME PLUGGED_READONLY PLUGIN_CHANGE# PLUGIN_RESETLOGS_CHANGE# PLUGIN_RESETLOGS_TIME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ------------------ --------------- --------------------- ------------------ ------------ ----------- --------------- -------------- ----------- ---------- ---------- ------------ ---------- -------------------------------------------------------------------------------- ---------- ------------- -------------------------------------------------------------------------------- ------------------- -------------------- ------------ ------------------------ --------------------- ---------------- -------------- ------------------------ ---------------------
         1                7 2009/8/15 0:1          0          1 SYSTEM  READ WRITE            1073988 2015/7/13 22:00                     0                                                      945183         945184 2015/7/13 3  713031680      87040            0       8192 /u01/app/oracle/oradata/secooler/system01.dbf                                             0          8192 NONE                                                                                               0                                 0                        0                       NO                            0                        0
         2             2140 2009/8/15 0:1          1          2 ONLINE  READ WRITE            1073988 2015/7/13 22:00                     0                                                      945183         945184 2015/7/13 3  545259520      66560            0       8192 /u01/app/oracle/oradata/secooler/sysaux01.dbf                                             0          8192 NONE                                                                                               0                                 0                        0                       NO                            0                        0
         3           942603 2009/8/15 0:5          2          3 ONLINE  READ WRITE            1073988 2015/7/13 22:00                     0                                                      945183         945184 2015/7/13 3  110100480      13440            0       8192 /u01/app/oracle/oradata/secooler/undotbs01.dbf                                            0          8192 NONE                                                                                               0                                 0                        0                       NO                            0                        0
         4            17993 2009/8/15 0:1          4          4 ONLINE  READ WRITE            1073988 2015/7/13 22:00                     0                                                      945183         945184 2015/7/13 3    5242880        640            0       8192 /u01/app/oracle/oradata/secooler/users01.dbf                                              0          8192 NONE                                                                                               0                                 0                        0                       NO                            0                        0
         5           974128 2015/7/13 3:0          6          5 ONLINE  READ WRITE            1073988 2015/7/13 22:00                     0                                                      974128         974133 2015/7/13 3  104857600      12800    104857600       8192 /u01/app/oracle/oradata/secooler/example01.dbf                                            0          8192 NONE                                                                                               0                        4023594539                   945509 2009/8/15 0:54:01     NO                       965812                   945184 2015/7/13 3:01:38

4、级联删除用户
SQL> drop user jison cascade;

User dropped

5、创建临时表空间
SQL> create temporary tablespace jison_temp tempfile '/u01/app/oracle/oradata/secooler/jison_temp.dbf' size 64m autoextend on next 64m maxsize 2048m extent management local;

Tablespace created

6、创建表空间
SQL> create tablespace jison_blog logging datafile '/u01/app/oracle/oradata/secooler/jison_blog.dbf' size 64m autoextend on next 64m maxsize 2048m extent management local;

Tablespace created

创建临时表空间和表空间使用的dbf文件路径参考3查询表空间文件里的路径，若是win-server服务器，则路径类似C:\app\jison\oradata\orcl\jison_blog.dbf

7、创建用户
SQL> create user jison identified by jison default tablespace jison_blog temporary tablespace jison_temp;

User created

8、授权
SQL> grant connect, resource, unlimited tablespace to jison;

Grant succeeded

9、oracle对普通用户hr解锁并改密码
SQL> alter user hr account unlock;

User altered

by后面为密码，以将密码改为“oracle”为例
SQL> alter user hr identified by oracle;

User altered

10、对用户加锁
SQL> alter user hr account lock;

User altered