Oracle11g创建表空间、创建用户、角色授权、导入导出表以及中文字符乱码问题

[转载]原文地址：https://www.cnblogs.com/bjh1117/p/6605037.html

 

前提：本机已经安装了Oracle11g数据库。

需求：使用PL SQL数据库连接工具操作Oracle数据库

一、创建表空间和用户

     想要操作数据库，首先需要创建用户并给用户授予权限；在创建用户时需要指定表空间并指定用户在表空间中能够使用的大小。因此，想要创建用户，首先需要创建数据库表空间。

     1、采用sqlplus工具创建用户及表空间：

     （1）打开sqlplus工具：开始——》所有程序——》 Oracle --OraDb11g_home1——》应用程序开发——》SQL Plus

     （2）创建表空间：

       以用户system连接数据库，密码为你安装数据库时设置全局数据库的窗口设置的管理口令，SYSTEM、SYS、SYSMAN、DBSNMP的初始密码都是这个密码，用户SCOTT的初始口令为tiger。

       创建表空间的语句：datafile2.dbf为新创建的表空间数据文件，注意：存放表空间数据文件的目录（这里是D:\OracleFiles\OracleData）必须已经存在，否则会报下面的错误，该目录是自己定义的，不一定就和下图中的一样。

 

修改数据文件位置后，创建表空间成功：

这里采用的是本地化管理方式创建的，dbsp_2是表空间的名称，size 10m 表示表空间的大小，extent management local autoallocate是设置当表空间大小已满时，用自动管理的方式扩展表空间。还有另一种扩展方式是每次扩展相同的大小，语句为：

extent management local uniform size 256K；其中，256k是根据实际需求设置的。

查找表空间大小语句：

 

（3）创建用户：

第一个testUser为用户名，第二个testUser为用户密码，default tablespace是默认表空间，这里设置成上面创建的表空间，temporary tablespace是临时表空间，quote设置用户在表空间上占用的空间大小。如果不指定默认表空间，Oracle会将SYSTEM表空间指定为用户的默认表空间；如果没有用quota子句，用户在特定表空间的配额为0，用户不能在该表空间上创建数据对象。

创建用户后，当用户分配的空间不够时，可以扩展用户的空间：

alter user testUser quota 20m on dbsp_2;将用户的空间扩展为20m

给用户指定两个表空间，并且可使用大小不限制：

alter user userName
quota unlimited on tablespace1
quota unlimited on tablespace2;

注意：在实际应用中建议表数据和表索引分两个表空间，这时就需要给用户在两个表空间分配可使用空间，在创建用户时还是按照上面提到的方法，只指定一个默认表空间，这样如果在创建表时没有指定表空间的情况下，会默认将表创建在默认表空间里（即default tablespace），如果想给数据库表数据和索引指定不同的表空间，则在给用户分配可使用空间后，在创建数据库表和索引时直接指定即可。

修改用户密码：

alter user testUser identified by 123456;将用户的密码设置为123456。

有时用户会处于锁定状态，解锁用户：

alter user testUser account unlock;

（3）为用户设置权限

      初始建立的用户没有任何权限，不能执行任何数据库操作，因此必须为用户设置权限或者角色。被赋予了某个角色的用户将拥有该角色所具备的权限，常被用到的系统预定义角色：CONNECT、RESOURCE、DBA、EXP_FULL_DATABASE、IMP_FULL_DATABASE。其中，CONNECT、RESOURCE、DBA主要用于数据库管理，数据库管理员需要被授予这三个角色。一般的数据库开发人员，需要被授予CONNECT、RESOURCE角色即可。EXP_FULL_DATABASE、IMP_FULL_DATABASE角色分别用于操作数据库导出、导入相关的操作。为用户授予角色：

          

（4）创建用户并为用户授权的另一种方法：用PL SQL工具

  双击运行PLSQL Developer软件，连接到本地的oracle数据库服务器

   

 

在“对象”下，找到users，右击选择“新建”

 

 在弹出的“创建用户”窗口中，输入新用户的名称、口令，默认表空间、临时表空间等

 

 

 

赋予新用户权限，赋予其角色权限：connect、resource，这样用户才能登录操作数据库

 

 

 （5）创建数据库用户完成后，就可以用该用户名、密码登录PL SQL工具操作数据库了。

 （6）删除用户语句：

　　drop user 用户名 cascade;

　　   删除空的表空间，但是不包含物理文件
　　drop tablespace tablespace_name;
　　　删除非空表空间，但是不包含物理文件
　　drop tablespace tablespace_name including contents;
　　　删除空表空间，包含物理文件
　　drop tablespace tablespace_name including datafiles; 
　　　删除非空表空间，包含物理文件
　　drop tablespace tablespace_name including contents and datafiles;
　　　如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

二、数据库数据的导入导出

     数据导出：PL SQL登录成功后，点击工具--》导出表--》选中要导出的表--》插入SQL--》选中删掉表选项、去掉包括存储前面的对号，选择输出文件的位置--》点击导出按钮--》等待导出完成。

 

 

 数据导入：点击PL SQL左上角的新建--》命令窗口

 

 输入@+sql文件位置，点击回车，即可将表导入数据库。

 

 

      导入数据库的过程中，可能会报表空间不足的错误“unable to create INITIAL extent for segment in tablespace”

      修改表空间数据文件大小：

     alter database datafile 'xxx.dbf' autoextend on next 10m [maxsize 500m];

     每次扩展10m，最大大小为500m。后续表空间不够，还可以修改maxsize的值。

三、Oracle数据库乱码问题：

    1、 导入导出的过程中，可能会发现导出的sql文件中或者PL SQL显示的中文汉字乱码、显示问号：

 

 

     可能是因为ORACLE数据库的编码格式不支持中文汉字，修改方法如下：

     （1） 开始-运行-输入regedit-回车进入注册表，依次单击HKEY_LOCAL_MACHINE--->SOFTWARE ---> ORACLE--->KEY_OraDb11g_home1（不同版本的Oracle显示的都不太一样，但都会包含home这个单词），找到“NLS_LANG”，查看数值数据是否为：“SIMPLIFIED CHINESE_CHINA.ZHS16GBK”，如果不是就将它设置为“SIMPLIFIED CHINESE_CHINA.ZHS16GBK。”

 

     （2）设置完注册表后，接下来设置我们的环境变量，计算机（右键） --->属性--->高级系统设置--->高级--->环境变量--->新建，个人建议新建用户变量，变量名输入：“NLS_LANG”，变量值输入：“SIMPLIFIED CHINESE_CHINA.ZHS16GBK”。点击确定即可，到此我们就设置完了。

 

 （3）这样设置过后，sql文件和PL SQL中应该可以显示中文了。

     2、经过上面的步骤后，可能会出现部分表可以显示中文、部分表中文仍然乱码的情况，登录PL SQL工具时提示：“数据库字符集和客户端字符集是不同的，字符转化可能会造成不可预期的结果“

     原因：这是因为ORACLE服务器和客户端字符编码格式设置的不同，即刚刚改过的注册表中的编码格式与Oracle数据库中的编码格式不同。

     解决办法：打开sqlplus窗口，查看当前编码格式：

     

我的数据库已经配置过了，和上面注册表中的字符集是相同的，如果不同，需要更改字符集，方法如下：

      sql> conn / as sysdba;

　　sql> shutdown immediate;

　　database closed.

　　database dismounted.

　　oracle instance shut down.

　　sql> startup mount;

　　oracle instance started.

　　total system global area  135337420 bytes

　　fixed size                          452044 bytes

　　variable size                     109051904 bytes

　　database buffers              25165824 bytes

　　redo buffers                      667648 bytes

　　database mounted.

　　sql> alter system enable restricted session;

　　system altered.

　　sql> alter system set job_queue_processes=0;

　　system altered.

　　sql> alter system set aq_tm_processes=0;

　　system altered.

　　sql> alter database open;

　　database altered.

　　sql> alter database character set internal_use ZHS16GBK

　　sql> shutdown immediate;

　　sql> startup;

      经过上面的步骤，数据库服务器和客户端的字符集一致了，应该不会出现中文汉字乱码的问题了。

      以上是今天操作数据库遇到的问题，现在应该可以正常使用本地Oracle11g数据库了。

     

      参考文章:

       图像化方法创建用户：http://jingyan.baidu.com/article/375c8e1997522e25f2a229c3.html

       修改Oracle注册表和环境变量字符集：http://www.2cto.com/database/201407/313317.html

       修改Oracle字符集：http://blog.sina.com.cn/s/blog_4b055d6b0100sssg.html

　　 删除表空间：http://blog.csdn.net/gbj890229/article/details/6623996

　　虽然有参考其他文章，但整理、实践、补充很用心，转载请注明出处。