Oracle11gR2创建表空间、用户

为什么80%的码农都做不了架构师？>>>   

1、创建数据库表空间：sys账户用SYSDBA登录后，sql窗口执行：

    1.1、创建临时表空间

       create temporary tablespace oa_cfwsj_temp
             tempfile 'D:\app\Administrator\oradata\oa\oa_cfwsj_temp.dbf' 
             size 50m  
             autoextend on  
             next 50m maxsize 20480m  
             extent management local; 

1.2、创建数据表空间

        create tablespace oa_cfwsj
            datafile 'D:\app\Administrator\oradata\oa\oa_cfwsj.dbf'
            size 50m 
            autoextend on 
            next 50m maxsize 20480m 
            extent management local; 

2、创建用户：sys用户SYSDBA登录

      2.1、选择Users，右键，新建：

            

            

            

    2.2、创建命令语句：

                  创建用户： 

        create user ezoffice identified by 13572468;

        grant connect,resource,dba to ezoffice;

                    -- Create the user
                    create user EZOFFICE
                          default tablespace OA_CFWSJ
                          temporary tablespace OA_CFWSJ_TEMP
                          profile DEFAULT
                          password expire;
                    -- Grant/Revoke role privileges
                    grant connect to EZOFFICE;
                    grant dba to EZOFFICE;
                    grant resource to EZOFFICE;
                    -- Grant/Revoke system privileges
                    grant unlimited tablespace to EZOFFICE;

    2.3、查看用户属于哪个表空间：

                select username,default_tablespace from dba_users;

    2.4、查看表空间物理位置：

                SELECT file_name, tablespace_name FROM dba_data_files;

                修改表空间名称：

                ALTER TABLESPACE tablespacename RENAME TO newtablespacename;

                tablespacename 和newtablespacename 分别对应原来的表空间名字和更改后的表空间名字。

转载于:https://my.oschina.net/pmos/blog/668003