ORACLE常用命令

1.     用SQLPLUS连接ORACLE需修改的文件：

    F:\oracle\product\10.2.0\db_3\NETWORK\ADMIN下的tnsnames.ora

 

2.     用命令登录数据库：

sqlplus用户名/密码@sid，如：sqlplus napsdev/napsdev@rslocal

3.     创建表空间：

SQL> create smallfile tablespace ngis datafile'd:\oracle\oradata\orcl\ngis.dbf' size 100m autoextend on next 20m maxsizeunlimited logging extent management  localsegment space management auto;

   表空间已创建。

 

4.     删除表空间

drop tablespace ngis including contents and datafiles;

5.     用户相关：

(1)      Oracle 11g 默认用户名和密码

安装ORACLE时，若没有为下列用户重设密码，则其默认密码如下：

用户名 /密码                     登录身份                             说明

sys/change_on_install       SYSDBA 或 SYSOPER       不能以 NORMAL 登录，可作为默认的系统管理员

system/manager              SYSDBA或 NORMAL        不能以 SYSOPER 登录，可作为默认的系统管理员

sysman/oem_temp            sysman                           为 oms 的用户名

scott/tiger                       NORMAL                           普通用户

aqadm /aqadm               SYSDBA或 NORMAL       高级队列管理员

Dbsnmp/dbsnmp          SYSDBA 或 NORMAL          复制管理员

(2)      创建用户

在表空间NGIS上创建用户testusername/ testpassword

SQL> create user testusernameprofile default identified bytestpassword default tablespace NGIS temporary tablespace temp account unlock;

(3)      用户授权

SQL> grant create session,dba to ngis;

(4)      删除用户（以及其下面的表和视图）

SQL> drop user btop cascade;

注：oracle中用户删除不了，提示“无法删除当前已连接的用户”

1)查看用户的连接状况(注意username的值一定要大写,否则查不出来!)

select username,sid,serial# fromv$session where username=’ 'WUZHQ'’

------------------------------------------

如下结果：

username         sid               serial#

---------------------------------------- 
WUZHQ             532             4562

 

2)找到要删除用户的sid,和serial，并删除

-------------------------------------------

如：你要删除用户'WUZHQ',可以这样做：

alter system kill session'532,4562'

(5)      密码命令

1)           修改密码

把用户naps的密码修改为111111

SQL> alter user naps identified by111111;

用户已更改。

2)           密码过期

【原因/触发因素】

确定是由于oracle11g中默认在default概要文件中设置了“PASSWORD_LIFE_TIME=180天”所导致。

【影响和风险】

   密码过期后，业务进程一旦重启会提示连接失败。

【解决方案】

   按照如下步骤进行操作：

    <1>查看用户的proifle是哪个，一般是default：

   sql>SELECT username,PROFILE FROM dba_users;

    <2>查看指定概要文件（如default）的密码有效期设置：

   sql>SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' ANDresource_name='PASSWORD_LIFE_TIME';

    <3>将密码有效期由默认的180天修改成“无限制”：

   sql>ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

   修改之后不需要重启动数据库，会立即生效。

    <4>修改后，还没有被提示ORA-28002警告的帐户不会再碰到同样的提示；

   已经被提示的帐户必须再改一次密码，举例如下：

   $sqlplus / as sysdba

   sql> alter user smsc identified by <原来的密码> ----不用换新密码

   oracle11g启动参数resource_limit无论设置为false还是true，密码有效期都是生效的，所以必须通过以上方式进行修改。以上的帐户名请根据实际使用的帐户名更改。

3)           账号被锁定

【问题描述】

   近日H局点反馈，使用sqlplus登陆短消息oracle数据库时提示“ORA-28000:帐号被锁定”。

【原因/触发因素】

   确定是由于oracle11g中默认在default概要文件中设置了“FAILED_LOGIN_ATTEMPTS=10次”，当输入密码错误次数达到设置值将导致此问题。

【影响和风险】

   帐号被锁定后，业务进程一旦重启会提示连接失败。

【解决方案】

   按照如下步骤进行操作：

    <1>查看用户的proifle是哪个，一般是default：

   sql>SELECT username,PROFILE FROM dba_users;

    <2>查看指定概要文件（如default）的密码有效期设置：

   sql>SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' ANDresource_name='FAILED_LOGIN_ATTEMPTS';

    <3>将尝试登录失败次数由默认的10次修改成“无限制”：

   sql>ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

    <4>修改后，还没有被提示ORA-28000警告的用户不会再碰到同样的提示；

   已经被锁定的帐户必须解除锁定，举例如下：

   $sqlplus / as sysdba

   sql> alter user smsc identified by oracle account unlock;

    <5>修改后defaultprofile应该如下：

   sql> select * from dba_profiles WHERE dba_profiles.profile='DEFAULT';

   PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT

   ------------------------------ -------------------------------- -----------------------------------

   DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED

   DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED

   修改之后不需要重启动数据库，会立即生效。

   如果出现ORA-28000the account is locked.错误

   alter user用户名 account unlock;

6.     查看数据库服务器字符集：

selectuserenv('language') from dual;

7.     修改服务器字符集（用于10g）：

SQL> connect system as sysdba

输入口令:

已连接。

SQL> shutdown immediate;

数据库已经关闭。

已经卸载数据库。

ORACLE例程已经关闭。

SQL> startup mount;

ORACLE例程已经启动。

Total System Global Area 612368384 bytes

Fixed Size                 1250428 bytes

Variable Size            176163716 bytes

Database Buffers         427819008 bytes

Redo Buffers               7135232 bytes

数据库装载完毕。

SQL> alter system enable restricted session;

系统已更改。

SQL> alter system set JOB_QUEUE_PROCESSES=0;

系统已更改。

SQL> alter system set AQ_TM_PROCESSES=0;

系统已更改。

SQL> alter database open;

数据库已更改。

SQL> alter database character set internal_use UTF8;

数据库已更改。

SQL> shutdown immediate;

数据库已经关闭。

已经卸载数据库。

ORACLE例程已经关闭。

SQL> startupmount;

     ORACLE例程已经启动。

Total SystemGlobal Area 612368384 bytes

     FixedSize                 1250428 bytes

     VariableSize            176163716 bytes

    DatabaseBuffers         427819008 bytes

RedoBuffers               7135232 bytes

  数据库装载完毕。

SQL> alter database open;

数据库已更改。

SQL>select userenv('language') from dual;

USERENV('LANGUAGE')

----------------------------------------------------

SIMPLIFIEDCHINESE_CHINA.ZHS16GBK

 

8.     修改客户端字符集：

E:\xjrccbSqlScript\utf8\database_struts>setnls_lang=SIMPLIFIED CHINESE_CHINA.AL32UTF8

注：生效范围当前DOS窗

9.     ORACLE常用数据类型

（1.）字符型

CHAR型：定长字符串，短则用空格填充，长则出错。

VARCHAR2型：变长字符串。字段长度根据实际字符串长度自动调整，不用空格填充。

（2.）数值型

NUMBER(PRECISION,SCALE)型：精度PRECISION指定整个数字的位数，范围SCALE指定小数的位数，两个参数均是可选的。如果插入的字段数据超过指定位数，将自动四舍五入。

（3.）日期时间数据类型

DATE型：可以存储日期和时间的组合数据。ORACLE默认的日期格式是 DD-MON-YY。

（4.）LOB数据类型

用于大型的、未被结构化的数据，如二进制文件、图片文件等。LOB数据类型又分为BLOB、CLOB和BFILE三种。

BLOB类型：用于存储二进制对象。如图像、音频、视频。

CLOB类型：用于存储字符格式的大型对象。Oracle将数据转换成Unicode格式。

BFILE类型：将二进制文件作为操作系统文件存储在数据库外部，BFILE类型的字段仅保存二进制文件的指针。

（5.）ROWID类型

亦称伪列类型，用于保存表中每条记录的物理地址。每条记录都有唯一的rowid。ORACLE自动为每个表建立名称为ROWID的字段。可以对该字段进行查询。

rowid确定了每条记录属于哪一个数据对象、数据文件、块、行。是基于64位编码的18个字符显示。

10.            将SQLPLUS执行日志输出到文件：

(1)    在本地创建文件output_log.sql

(2)    内容如下：

--日志输出路径

spool E:\xjrccbSqlScript\utf8\log.log;

--写上要执行SQL文件的路径（需修改）

@E:\xjrccbSqlScript\utf8\database_struts\create_db_cnaps2db_all_20121023.sql;

--将缓存的日志全部输出

           spool off;

(3)    执行output_log.sql：

SQL> @E:\xjrccbSqlScript\utf8\output_log.sql;

11.            备份还原数据库

（1）      全库

<1>备份：

C:\>set nls_lang=SIMPLIFIEDCHINESE_CHINA.AL32UTF8

C:\>expnapsdev/napsdev@xjrccb file=E:\db_backup\xjrccb.dmp log=E:\db_backup\exp.log full=y 

<2>还原：

C:\>set nls_lang=SIMPLIFIEDCHINESE_CHINA.AL32UTF8

C:\>imp napsdev/napsdev@xjrccb file=E:\db_backup\xjrccb.dmp full=y ignore=ylog= E:\db_backup\exp.log

  @xjrccb是sid，file后是备份文件路径,ignore=y是输出警告 ，full=y全库

（2）      某个用户下的数据

<1>备份：

C:\>set nls_lang=SIMPLIFIEDCHINESE_CHINA.AL32UTF8

C:\>expnapsdev/napsdev@xjrccb file=E:\db_backup\xjrccb.dmp log=E:\db_backup\exp.log full=n

<2>还原：

C:\>set nls_lang=SIMPLIFIEDCHINESE_CHINA.AL32UTF8

C:\>imp napstest/napstest@xjrccb fromuser=napsdev touser=napstest file= E:\db_backup\xjrccb.dmp

可选条件:ignore=y buffer=5000000 log= E:\xjrccb_db\db_backup\imp.log

注:用户名/密码@xjrccb是sid，file后是备份文件路径,ignore警告 ，full=n当前用户库

 

（3）      处理数据中的特殊字符：

为了避免ORACLE将数据中的特殊字符（如:&等），识别成自变量产生异常，所以关闭自变量设置，然后再导入数据就不会报异常了。

SQL>set define off;

12.            oracle11修改最大连接数

(1)    查看当前会话数

SQL>conn / as sysdba

已连接。

SQL>select count(*) from v＄session;

COUNT(*)

----------

45

注：session值=processes值*1.1+5

(2)    查看数据库最大进程数

SQL> show parameterprocesses

NAME TYPE VALUE

--------------------------------------------- ------------

aq_tm_processesinteger 0

db_writer_processesinteger 1

gcs_server_processesinteger 0

job_queue_processesinteger 10

log_archive_max_processesinteger 2

processesinteger 50

(3)    查看数据库最大会话数

SQL>show parameter sessions

NAME TYPEVALUE

--------------------------------------------- ------------

java_max_sessionspace_sizeinteger 0

java_soft_sessionspace_limitinteger 0

license_max_sessionsinteger 0

license_sessions_warninginteger 0

logmnr_max_persistent_sessionsinteger 1

sessionsinteger 60

shared_server_sessionsinteger

 

(4)    修改processes和sessions值

SQL> altersystem set processes=300 scope=spfile;

系统已更改。

SQL> altersystem set sessions=300 scope=spfile;

系统已更改。

注：一般只执行第一个命令就可以，session自动更改