sql 文件导入nactive都不成功_系统维护参考手册

2 准备工作

oracle 9i软件的操作系统安装用户名和密码(用户名一般为oracle，组一般为dba)。

oracle数据库的默认管理帐户和密码：

帐户system的默认密码为manager

帐户sys的默认密码为change_on_install。

(登陆方式为：

%sqlplus /nolog

SQL>conn sys/change_on_install as sysdba

此时拥有数据库的最最高级的权限)

3 常见系统配置、维护

3.1 oracle当前库及其监听的启动、关闭

以下讲的是一个服务器上只运行一个当前数据库的情况，多数据库实例情况参考 “FAQ 5.8”。

3.1.1 Oracle的启动

1) 以安装oracle软件的操作用户登录

2) ％sqlplus /nolog

3) SQL>connect /as sysdba

4) SQL>startup

5) SQL>exit

验证：

sqlplus system/manager

SQL>select count(*) from tab;

能正确查询出数据，即启动成功。

3.1.2 监听器的启动

1) 以安装oracle软件的操作用户登陆

2) %lsnrctl start

或者使用

3) %lsnrctl

4) LSNRCTL> start

5) LSNRCTL>exit

验证：

%lsnrctl status 查看监听器状态报告是否启动。(查看当前服务器提供的监听服务也使用该命令)

3.1.3 检查监听器服务

%lsnrctl status

3.1.4 Oracle的关闭

1) 以安装oracle软件的操作用户登陆

2) ％sqlplus /nolog

3) SQL>connect /as sysdba

4) SQL>shutdown immediate

5) SQL>exit

验证：

使用操作系统命令ps查看oracle后台进程无活动，以system帐户无法登录oracle执行查询操作：

查询oracle后台进程：%ps –ef | grep ora_

查看oracle内存：%ipcs

只要关闭了数据库，而系统还有ora_的后台进程和ipcs能查出oracle内存，则该数据库已不正常。需通知工程部进行处理。(仅适用单服务器单数据库实例情况)

登录验证与启动数据库验证一样。

3.1.5 监听器的关闭

1) 以安装oracle软件的操作用户登陆

2) %lsnrctl stop

3) 或者使用

4) %lsnrctl

5) LSNRCTL> stop

6) LSNRCTL>exit

验证：

与监听的启动验证一样。

3.1.6 自动启动和关闭Oracle

可以配置系统，使得系统每次启动时自动启动oracle数据库，每次shutdown时自动关闭oracle数据库；oracle数据库的自动启动是可选的，但是推荐系统每次shutdown时自动关闭ORACLE数据库，因为这样可以防止不正当的关闭ORACLE数据库。

ORACLE提供了两个shell文件来支持数据库的自动关闭和启动，他们是位于$ORACLE_HOME/bin的dbshut和dbstart。

dbshut和dbstart涉及了oratab文件的相同实体，所以不可能自动启动sid1、sid2、sid3而只自动关闭sid1、sid2，不过可以只用dbshut去自动关闭数据库而不用dbstart，

自动启动和关闭oracle数据库。

1． 编辑/var/opt/oracle/oratab文件

oratab文件中数据库实体为以下格式：

ORACLE_SID:ORACLE_HOME:{Y|N}

最后一个Y|N域指定是否自动关闭和启动数据库，找到要自动启动的数据库(即第一个域ORACLE_SID)，然后把最后一个域更改为Y。

即:在该文件末尾,有一行为如上格式的具体实例.把后面的参数N修改:Y:Y

2． 使用超级用户在/etc/init.d/目录中建立dbora文件

3． dbora文件的内容如下所示：

#!/bin/sh

# Set ORA_HOME to be equivalent to the ORACLE_HOME

# from which you wish to execute dbstart and

# dbshut

# set ORA_OWNER to the user id of the owner of the

# Oracle database in ORA_HOME

#使用真实的ORACLE_HOME代替如下路径.和安装用户.

ORA_HOME=//app/oracle/product/9.0.1.1

ORA_OWNER=oracle

if [! -f $ORA_HOME/bin/dbstart]

then

echo "Oracle startup: cannot start"

exit

fi

case "$1" in

’start’)

# Start the Oracle databases:

# The following command assumes that the oracle login will not

# prompt theuser for any values

su - $ORA_OWNER -c $ORA_HOME/bin/dbstart &

;;

’stop’)

# Stop the Oracle databases:

# The following command assumes that the oracle login will not

# prompt the

# user for any values

su - $ORA_OWNER -c $ORA_HOME/bin/dbshut &

;;

esac

4． 链接该文件使得系统自动启动和关闭数据库

# ln -s /etc/init.d/dbora /etc/rc0.d/K10dbora

# ln -s /etc/init.d/dbora /etc/rc2.d/S99dbora

5． 注意:在拷贝此教本的时候,注意编辑,否则执行不成功.

6． 在重启机器后,仍然需要启动监听.

验证：

使用reboot进行验证，与oracle的启动验证一样。

3.2 Oracle帐户管理

3.2.1 查询用户信息

SQL>select username,default_tablespace,temporary_tablespace,created,account_status from dba_users;

3.2.2 增加用户

1. % su – oracle，或以安装oracle软件的操作用户登陆

2. 更改ORACLE_SID环境变量为要增加用户的数据库名

3. SQL> connect /as sysdba

4. SQL>create user user_name identified by user_passwd

default tablespace default_tablespace

temporary tablespace temp_tablespace

quota unlimited on default_tablespace;

建立用户usern_ame，密码为user_password,选择缺省表空间和临时表空间,一般缺省表空间自己建立,最差情况使用USERS表空间,临时表空间使用TEMP.

5. SQL> grant connect,resource to user_name;，为user_name分配权限

验证：

使用该创建了的用户登录，并进行验证中文字符的方法进行验证，如果成功，则创建成功。

3.2.3 修改用户密码

1.以oracle用户登录.

2.%sqlplus /nolog

SQL>connect /as sysdba

SQL>alter user USER_NAME identified by USER_PASSWD;

验证：

使用该用户与修改了的密码登录，登录成功即修改密码成功。

3.2.4 删除用户

删除用户时候,会从数据字典中删除该用户及其相关的模式,同时,如果在该模式中包含任何模式对象,也将立即删除.使用cascade选项删除该用户以及所有相关的对象和依赖于该用户的外键:

1.以oracle用户登录.

2.%sqlplus /nolog

SQL>connect /as sysdba

SQL>drop user user_name cascade;

验证：

在删除前用该用户登录成功，在删除后，再使用该用户登录进行验证，如果失败即表示删除成功或用户已被lock。

或者使用SQL>select username from dba_users where username=’被删除用户名大写’没有查询出记录即可。

3.3 服务命名配置

用Oracle Ne manager配置服务命名:

1. 在$ORACL_HOME/bin目录下运行netmgr 启动Oracle Net manager.

2. 在导航窗格，展开Local>Service Naming.(服务命名)

3. 在工具条里单击+。网络服务名称向导启动。

4. 在网络服务名称域里输入名称(任何用户想设定的名称,一般设置为与目标数据库相同名称)，然后单击Next.

5. 选择客户端将要监听的协议(该协议必须已安装在客户端上)。单击Next.

6. 在提供的域里为所选择的协议输入适当的协议参数，然后单击呢Next.

7. 输入目的服务(主机名选择客户端的IP,端口号选择对方启动的监听端口号)，然后单击Next.

8. 输入对方数据库服务名,设置为对方数据库名.

9. 单击Test,验证网络服务名称能否正常工作，然后单击Next.

如果测试不成功：

确认目标数据库和监听者正在运行，然后单击Test.

单击Chang Login来改变连接的用户名称和密码，然后单击Test.

10. 击Close关闭连接测试对话框。

11. 单击Finish保存配置，并关闭服务名称向导。

验证：

使用该服务名进行连接进行测试，如果成功，则表示配置成功：

SQL>sqlplususername/passwd@net_service_name

SQL>select count(*) from tab;等操作如果成功，即表示配置成功。

3.4 修改内存结构

如果在创建数据库时,没有对内存进行设置,可在安装完成后,按需要对数据库的一些内存等参数进行修改:

修改方法: 可通过alter system set语句修改; 也可通过创建pfile转化为spfile修改.

Alter system set 语句:alter system set 参数表达式 scope=spfile;

Pfile转化为spfile:

SQL>create spfile=”$ORACLE_BASE/oradata/ORACLE_SID/spfile/ORACLE_SID.ora” from pfile=” $ORACLE_BASE/oradata/ORACLE_SID /init ORACLE_SID.ora”

需修改选项和修改内容为如下:

#SGA_MAX_SIZE一般设置为物理内存的2/3.此处设置为4G

*.SGA_MAX_SIZE=4294967296

*.db_cache_size=524288000

*.db_file_multiblock_read_count=8

*.db_files=160

*.fast_start_mttr_target=900

*.java_pool_size='786432000'

*.large_pool_size='10485760'

*.log_buffer=4194304

*.log_checkpoint_interval=50000

*.open_cursors=900

*.parallel_max_servers=8

*.processes=200

*.shared_pool_size=786432000

*.sort_area_size=5242880

验证：

使用如下命令，查找相关内存配置是否满足配置要求：

SQL>show parameters

3.5 Oracle最大链接数两种修改办法

1. 修改init ORACLE_SID.ora参数文件,但是必须带参数启动数据库才有效。修改$ORACLE_HOME/dbs/init ORACLE_SID.ora文件中的processes参数的值，然后带参数重新启动数据库即可(SQL>startup pfile=/$ORACLE_HOME/oradata/ ORACLE_SID/init ORACLE_SID.ora)。(修改的数值可以参考$ORACLE_HOME/dbs/init.ora模板文件的提示)

2. 使用oracle 9i的新特性,使用spfile.

Alter system set processes=数值 scope=spfile;

重启数据库即可.

如果不习惯使用spfile,可在安装后把$ORACLE_HOME/dbs/spfile ORACLE_SID.ora删除即可.此时数据库将使用pfile,即原来的init ORACLE_SID.ora文件.

验证：

与“修改内存结构”的验证方法一样。

3.6 导入导出数据

整个数据库导入导出:

导出: exp dbauser/dbapasswd@ ORACLE_SID file=exp_filename log=exp_logfile full=y

导入： imp dbauser/dbapasswd@ ORACLE_SID full=y ignore=y file=exp_filename log=exp_logfile

按用户导入导出:

导出:exp dbauser/dbapasswd@ ORACLE_SID file=exp_filename log=exp_logfile wner=exp_username

导入:imp dbauser/dbapasswd@ ORACLE_SID file=exp_file_name log=exp_logfile fromuser=exp_user touser=imp_user

其中方括号内的内容规定如下：

dbauser：具有DBA权限的Oracle用户名

dbapasswd：dbauser的用户密码

sid：Oracle服务名

exp_file_name：导出数据文件的文件名

exp_logfile:导出日志

exp_user：导出数据文件的用户名。

Imp_user：准备要将数据导入的用户名。

验证：

导出验证：使用在本机进行导入和在bin模式下FTP到其他机器进行相关导入验证。

导入验证：more log文件查询导入是否成功。

3.7 Oracle联机在线日志文件管理

3.7.1 查询联机在线日志文件信息

SQL> select * from v$log;

SQL>select * from v$logfile;

3.7.2 修改数据库联机在线日志文件

如果没有在创建数据库时把redo日志文件大小设置成5M,和分为5个组,每组两个redo日志文件.则需要使用如下方法进行设置.

一般默认安装后,的redo日志文件都为三组三个.

则增加4组和5组,每组两个日志文件,且大小为5M,命名为redo04.log,redo14.log和redo05.log,redo15.log.

使用日志切换,把当前活动日志切换到4组.则把1,2,3组drop掉.再用手工删除该对应的redo日志文件.

创建1,2,3组,文件命名分别为: redo01.log,redo11.log和redo02.log,redo12.log和redo03.log,redo13log.大小都为5M.

创建日志组,且一起创建日志文件,使用如下命令:

SQL>alter database add logfile group X (‘$ORACLE_BASE/oradata/ ORACLE_SID/redo0X.log’,’ $ORACLE_BASE/oradata/ORACLE_SID/redo1X.log’) size 5M;

查看当前日志状态和日志切换使用如下命令:

SQL>select * from v$log;

SQL> alter system switch logfile;

删除旧的日志文件使用如下命令:

SQL> alter database drop logfile group X;

手动删除对应的日志文件使用系统删除命令.

(如果文件已经损坏,则可使用如下方法(保守方法: alter database clear logfile group X;):alter database clear unarchived logfile group X;清除,再drop.不进行归档,此时需要进行做一个备份,否则数据库将不能使用其进行恢复.)

验证：

使用SQL命令查询修改结果是否符合修改要求。

SQL>select name from v$log;

3.8 Oracle归档日志管理

3.8.1 检查归档配置

%sqlplus /nolog

SQL>archive log list

信息如下(有可能以中文显示)：

SQL> archive log list

Database log mode Archive Mode //说明是否为归档模式，此处是归档模式

Automatic archival Enabled //自动归档进程是否启动，此处是自动启动

Archive destination /usr5/archive //说明第一归档目标，此处是/usr5/archive

Oldest online log sequence 51402 //说明就的联机在线日志序号

Next log sequence to archive 51404

Current log sequence 51404

SQL>

若要查看是否设置了其他归档目标，则使用如下命令检查：

SQL>show parameters log_archive_dest

3.8.2 设置归档模式和取消归档模式

数据库运行模式分为归档和不归档模式.一般在确认有足够的空间存放归档日志文件才使用归档模式设置数据库(推荐：至少有用户数据的4倍以上的空闲空间，才使用归档模式)

3.8.2.1 设置归档模式

归档模式的修改,可使用oracle 9i的新特性,使用spfile, 通过alter system set语句完成.

配置归档模式:

配置好归档属性

把数据库放入归档模式

检查归档是否满足要求

1) 配置好归档属性

a) 设置归档目标

% su – oracle，或以oracle用户登陆

更改ORACLE_SID环境变量为要增加用户的数据库名

SQL> connect /as sysdba

假如需要放入归档文件的路径为$ORACLE_ARCH_1和$ORACLE_ARCH_2,则进行如下设置归档目标:

SQL>alter system set log_archive_dest_1=”Location=/$ORACLE_ARCH_1” scope=spfile;

SQL>alter system set log_archive_dest_2=”Location=/$ORACLE_ARCH_2” scope=spfile;

b) 设置归档进程

启动oracle自动归档的进程,进行如下设置:

SQL>alter system set log_archive_start=ture scope=spfile;

c) 设置归档文件名称

设置好归档文件的名称,一般需要带上SN号,可配置如下:

SQL>alter system set log_archive_format=”SID_T%TS%S.ORA”

2) 把数据库放入归档模式

正常关闭数据库shutdown

放入归档模式

SQL>startup mount

SQL>alter database archivelog

打开数据库

SQL>alter database open;

3) 检查归档属性

SQL>archive log list

查看归档目标是否设置正确,归档进程是否已经启动.数据库是否已经处于归档状态

验证：

使用检查归档属性的方法进行验证，从报告中即可得出是否配置成功。

3.8.2.2 取消归档模式

先检查数据库是否已经放入归档模式运行，如果已经是归档模式运行，则按照如下方法取消归档模式：

取消归档模式

% su – oracle，或以oracle用户登陆

更改ORACLE_SID环境变量为要增加用户的数据库名

SQL> connect /as sysdba

检查是否处于归档模式

SQL>archive log list

正常关闭数据库

SQL>shutdown

把数据库放入非归档模式

SQL>start mount

SQ.L>alter database noarchivelog

打开数据库

SQL>alter database open;

停止归档进程

SQL>archive log stop

取消归档进程自动启动

SQL>alter system set log_archive_start=false

Scope=spfile;

验证：

使用检查归档属性的方法进行验证，从报告中即可得出是否配置成功。

3.9 Oracle存储信息

3.9.1 查询存储信息

SQL>select * from dba_tablespaces; //检查逻辑空间信息

SQL>select * from dba_data_files; //检查逻辑空间与物理空间的信息

SQL>select * from v$datafile; //检查物理存储文件的信息

3.9.2 创建表空间

以oracle用户登录,执行如下命令创建表空间和该表空间的数据文件

%sqlplus /nolog

%connect /as sysdba

SQL>create tablespace tablespace_name

datafile ‘/$ORACLE_BASE/oradata/ORACLE_SID/datafile_name01.dbf’

size 1000M

autoextend on next 1000M maxsize unlimited

minimum extent 25M

default storage ( initial 50M

next 50M

minextents 1

maxextents unlimited

pctincrease 0);

-----------黑体部分为可修改部分,其中,最好只修改tablespace_name,和数据文件路径和数据文件名称(最好与其他数据文件放在一起)，除了名称与路径，最好不要做其他修改。

给表空间添加数据文件：

SQL> alter tablespace tablespace_name add

datafile ‘/$ORACLE_BASE/oradata/ORACLE_SID/datafile_name01.dbf’

size 1000M;

验证：

使用SQL命令查询是否已经创建成功。

SQL>select tablespace_name from v$tablespaces;

3.10 修改某用户缺省和临时表空间

如果在创建用户时，没有指定缺省和临时表空间，则默认都为SYSTEM表空间。

修改用户缺省表空间：

SQL>alter user system default tabspace USERS;

修改用户临时表空间：

SQL>alter user system temporary tabspace TEMP;

验证：

使用SQL>select * from dba_users where username=’被修改用户名大写’;查看即可。

3.11 Oracle数据库的汉字显示问题

注意

在oracle的使用过程中,如果字符集出现错误.版本在oracle7以下的,则允许用以下方法修改;如果是oracle8版本,则需要使用其他的命令修改,且原来设置的语言必须为美国英语;对于9版本.则必须重新创建数据库.在创建数据库的过程中设置正确的字符集.

Oracle7版本字符集修改办法

在SQL*Plus中insert进的都是中文的，为什么一存入服务器后，再select出的就是? ? ?了？

错误现象：

1、 有的时候，服务器数据先导出，重装服务器，再导入数据，结果，发生数据查询是出现的是? ? ?。

2、 有时，服务器设置就有问题，字符集设成单字节了。

错误原因：

一般这种问题产生的原因是因为字符集设置不对造成的。

解决方法：

1、检查服务器上Oracle数据库的字符集，检查的方法如下：

SQL> connect /as sysdba

连接成功.

SQL> desc props$

列名 可空值否 类型

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

NAME NOT NULL VARCHAR2(30)

VALUE$ VARCHAR2(2000)

COMMENT$ VARCHAR2(2000)

SQL> col value$ format a40

SQL> select name,value$ from props$;

NAME VALUE$

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

DICT.BASE 2

NLS_LANGUAGE AMERICAN

NLS_TERRITORY AMERICA

NLS_CURRENCY $

NLS_ISO_CURRENCY AMERICA

NLS_NUMERIC_CHARACTERS .,

NLS_DATE_FORMAT DD-MON-YY

NLS_DATE_LANGUAGE AMERICAN

NLS_CHARACTERSET ZHS16GBK

NLS_SORT BINARY

NLS_CALENDAR GREGORIAN

NLS_RDBMS_VERSION 7.3.4.0.0

GLOBAL_DB_NAME ORACLE.WORLD

EXPORT_VIEWS_VERSION 3

查询出记录.

NLS_CHARACTERSET这个参数应该是ZHS16GBK，如不是，需要修改成此值，修改的方法如下，

SQL*Plus中修改方法：

SQL> update props$ set value$='新字符集' where name='NLS_CHARACTERSET';

操作系统中修改方法：

connect /as sysdba

alter database SID character set ZHS16GBK;

alter database SID national character set ZHS16GBK;

注意修改数据库字符集后需要重启数据库。

2、检查操作系统WINDOWS中Oracle汉字显示的字符集，检查方法如下：

运行regedit，定位到：

HKEY_LOCAL_MACHINESOFTWAREORACLE

找到以下字符串：

NLS_LANG

检查是否以下内容，如不是，改之，修改方法如下：

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

注意修改数据库字符集后需要重启数据库。

3.12 oracle卸载方法

3.12.1 UNIX

a) 使用安装向导工具卸载

b) 删除安装目录

c) 删除/var/opt/目录下的oracle目录

d) 卸载完成

3.12.2 WIN

e) 使用安装向导工具卸载

f) 用Control Panel(控制面板)中的Services应用程序停止所有ORACLE服务。

g) 运行regedit，选择HKEY_LOCAL_MACHINESOFTWAREORACLE，按del键删除这个入口。

h) 选择HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServices，滚动这个列表，并删除所有ORACLE入口

i) 从桌面上、STARTUP组中删除所有有关ORACLE的组和图标

j) 删除与ORACLE有关的文件，选择ORACLE所在的缺省目录c:orant，删除这个入口目录及所有子目录，

k) 并从WINDOWS NT目录(一般为c:winnt)下删除以下文件

ORACLE.INI ORADIM80.INI

l) WIN.INI文件中若有[ORACLE]的标记段，删除该段

4 常见开发操作

4.1 常见表字段名操作

4.1.1 查看表字段

SQL>desc 表名

如：

SQL> show user

USER is "dbuser"

SQL> desc dbuser2.xtbm

Name Null? Type

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

XTBM NOT NULL VARCHAR2(20)

XTXX VARCHAR2(50)

THETYPE VARCHAR2(10)

DESCRIPTION VARCHAR2(200)

REMARK VARCHAR2(100)

SQL>

4.1.2 增加表字段

例：

SQL>alter table ne_pcf add (adminstate integer,alarmstatus integer);

(此操作不会修改原有字段的数据，新添加字段数据为空)

4.1.3 删除表字段

例：

SQL> alter table table_name drop (system_title, net_title,obj_id,ne_dn);

(此操作不会影响其他字段的数据，直接删除所列字段名及所有数据)

4.1.4 修改表字段类型

例：

SQL> alter table ne_aaa modify (sw_version varchar2(255));

注意：

修改字段类型为不同的数据类型，如从char变到int，则需要该字段的所有数据为空。

? 同类类型修改，如从char(10)变为char(20)，char(30) 到varchar2(30)等变化，不会影响已有数据。

4.1.5 修改表字段名

可行的办法：

create table 表名(字段名,字段名,字段名…..) select 字段名,字段名,字段名…. From 表名

使用上述语句创建需要的新表，检查符合要求，数据无丢失后，drop掉原来的表，再把新表改名为原来的表名即可。

相关操作语句本章查找。

4.2 表操作

4.2.1 查询表信息

? 查询当前用户所拥有的表名、表所使用的表空间、表创建时间、表大小

SQL>select s.segment_name,s.tablespace_name,o.created,s.bytes/1024/1024 as table_size_M

from user_segments s,user_objects o

where o.object_type = 'TABLE'

and s.segment_type = 'TABLE'

and o.object_name = s.segment_name;

(查询当前用户的表的信息)

4.2.2 创建表

例：

SQL> create table t2 (en varchar(100),intr int) tablespace dbmonitor;

(创建表的时候请指定表空间)

4.2.3 删除表数据(重要)

删除表数据的方法：

删除表所有数据的最快速方法

SQL> truncate table 表名;

(此方法系统不记录日志，一但删除，将不可恢复)

删除满足指定条件的大表的数据

对于大数据量的表，比如告警表，采集原始表，最好编写一procedure进行删除，小批量删除提交后再进行小批量删除提交，直到满足要求。

Procedure范例如下(在SQL>提示符号下，先运行SQL>set serveroutput on，以便存储过程输出处理结果)：

v_table_name：被删除的表名

v_once_delete_row_num：一次删除的行数

v_condition：删除表的where 条件

create or replace procedure delete_table

(v_table_name varchar2,

v_once_delete_row_num varchar2,

v_condition varchar2)

as

pragma autonomous_transaction;

v_delete number:=0;

begin

while 1=1 loop

EXECUTE IMMEDIATE

'delete from '||v_table_name||' where '||v_condition|| ‘ and rownum <= :cnt’

USING v_once_delete_row_num;

if SQL%NOTFOUND then

exit;

else

v_delete:=v_delete + SQL%ROWCOUNT;

end if;

commit;

end loop;

commit;

DBMS_OUTPUT.PUT_LINE('删除操作结束!');

DBMS_OUTPUT.PUT_LINE('一共删除了 '||to_char(v_delete)||' 条记录');

end;

/

例如：

SQL> set timing on

SQL> exec delete_table('alarminfo_bk','1000','alarmnumber = 0000191105786406');

删除操作结束！

一共删除了1条记录

PL/SQL 过程已成功完成。

已用时间: 00: 03: 26.41

SQL>

删除时间虽然还是比较长，但可以保证部分大批量无控制删除时的出错和消耗UNDO等的性能消费。

4.2.4 删除表

SQL>drop table 表名;

4.2.5 重命名表

SQL>rename 表1 to 表2;

4.2.6 备份表

备份成其他名称的表

SQL>create table 表名1 as select * from 表名2;

备份成数据文件

%exp 表所属用户名/密码@服务名 file=定义文件名称.dat log=定义日志文件名称 tables=表名

备份表结构

没办法，要么：

1) 使用如TOAD等工具进行导出备份。

2) 使用exp 的方法导出数据，再使用imp的方式导入数据库，只是此时一定要在imp语句末尾加上 show =y，并且写入日志文件。这样才可以在日志文件中观察表结构(导出的表结构不能直接使用，需要整理)。

如：

%expdbuser2/dbuser2@userdbfile=a.dat tables=ne_msc

% impdbuser2/dbuser2@userdbfile=a.dat log=a.log full=y show=y

4.3 索引操作

4.3.1 查询索引信息

查询某表所有索引及每个索引使用的字段

SQL>select index_name,column_name from user_ind_columns

where table_name = '表名大写' order by index_name;

查询某索引所使用的表空间、索引大小

通过从上面查询到的索引名称，可继续查询索引详细信息

SQL>select segment_name,tablespace_name,bytes/1024/1024 as index_size_M from user_segments

where segment_type = 'INDEX'

and segment_name = '索引名称大写';

(查询当前用户的表的索引信息)

4.3.2 创建索引

(注意：

1) 虽然主键仅是一种唯一、非空的约束而已，但创建(删除)方法与创建一般约束有所不同。

2) 只要索引名称不重复，索引字段完全一样，就允许创建无穷多个索引)

创建主键

例：

SQL>alter table tabname_router add primary key(ne_id,port_id,start_time,stop_time);

创建唯一索引

例：

SQL>create unique index inx_o_tabname_vlr_temp on o_tabname_vlr_temp(ne_id, start_time, stop_time) tablespace rperfdbs;

创建非唯一索引

例：create unique index idx_bb on t2(a is not null,b);

(可在创建的语句中指定索引使用某个表空间)

4.3.3 删除索引

删除主键

例：

SQL>alter table tabname_router drop primary key;

创建(非)唯一索引

例：

SQL> drop index idx_bb;

4.3.4 重建索引

一般重建方法

SQL> alter index 索引名称 rebuild;

(如果这样重建失败，则采用下面的方法)

其他重建方法

1)记录该索引的属性(如是否主键，是否非空，是否唯一等)。

2) drop 该索引。

3) create 该索引。

(具体语句的语法请参考本章)

4.4 SQL语句及存储过程优化

4.4.1 SQL语句是否使用索引

使用执行该SQL语句的用户，执行sqlplus的方式登陆数据库

SQL>

执行如下语句：

SQL> set autotrace traceonly explain

SP2-0613: Unable to verify PLAN_TABLE format or existence

SP2-0611: Error enabling EXPLAIN report

如果遇到如上错误，则执行如下语句(如没遇到错误，则不需要执行如下语句)：

SQL> @${ORACLE_HOME}/rdbms/admin/utlxplan

此时输入欲查询是否使用索引的SQL语句，如：

SQL> set autotrace traceonly explain;

SQL> select count(*) from SERVICE_MONTHLY_REPORT2;

Execution Plan

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

0 SELECT STATEMENT ptimizer=CHOOSE

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (FULL) OF 'SERVICE_MONTHLY_REPORT2'

SQL>

如通过FULL 标识，我们知道查询该表时没有使用索引。

又如：

SQL> select ne_id from o_tabname_stp;

Execution Plan

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

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=18)

1 0 INDEX (FULL SCAN) OF 'INX_O_tabname_STP' (UNIQUE)

通过FULL SCAN知道是索引扫描，而且使用的是INX_O_tabname_STP索引。

4.4.2 SQL语句是否使用了最恰当的索引

通过“4.4.1”，判断是否关键被判断的字段，和查询的字段都在索引里，如果是，则一般是使用了恰当的索引。

4.4.3 怎么提示SQL语句使用固定的索引

/*+ INDEX(tabname_CELLCIRCUIT) */

4.4.4 怎么提示SQL语句固定使用全表扫描

/*+ FULL(o_tabname_stp) */

例：

通过如下两个执行计划可知道提示生效

SQL> select ne_id from o_tabname_stp;

Execution Plan

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

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=18)

1 0 INDEX (FULL SCAN) OF 'INX_O_tabname_STP' (UNIQUE)

SQL>

SQL> select /*+ FULL(o_tabname_stp) */ ne_id from o_tabname_stp;

Execution Plan

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

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=18)

1 0 TABLE ACCESS (FULL) OF 'O_tabname_STP' (Cost=2 Card=1 Bytes

=18)

4.4.5 存储过程是否可以执行更少的、有效的SQL语句

1) 确保所有应该使用索引的SQL语句都使用了索引。

2) 通过设计判断，减少SQL语句的执行次数。

3) 努力减少执行次数多的SQL语句。

4.5 操作阻塞,锁情况

由于锁情况比较复杂，此处只讲已知某个在操作的用户出现锁的情况。

通过如下语句，查询出当前在被锁的表，表所属用户，操作系统用户名，通过这些信息，判断找出制造死锁的会话sid。

SQL>select o.owner,o.object_name,l.session_id,l.os_user_name

from v$locked_object l,dba_objects o

where o.object_id = l.object_id;

通过sid，使用如下语句，查找出数据库服务器上操作系统的后台连接进程或直接使用SQL语句杀死该会话。

1) 查找数据库服务器后台进程：

SQL> select spid from v$process where addr =

(select paddr from v$session where sid= &sid);

如上语句，输入 session的sid ，即可查找相应的数据库服务器后台进程，直接使用%kill -9命令杀掉该进程即可。

2) 使用SQL语句杀掉该会话：

SQL> alter system kill session 'sid,serial#';

Sid,serial#可以从v$session视图查出。

5 FAQ

5.1 操作表数据提示数据文件错误

通过检查，判断数据文件状态：

数据文件状态不是ONLINE或SYSTEM

请提交工程部或数据库组处理。

检查文件状态方法可通过dbMonitor查看，也可通过 “3.9.1”小节方法查看。

数据文件状态是ONLINE

1) 验证该操作表数据的语句错误再次出现。

2) 使用set autotrace traceonly explain的方法检查该语句是否使用索引。

3) 若使用了索引：则使用 /*+ FULL(表名) */ 的提示方法，选择表表操作判断是否问题依然出现。若问题不出现，则drop 该操作使用的索引，重新创建该索引；若问题出现，则exp表数据，drop条，重新创建表。

(若是同时操作多个表，则一个表一个表的使用/*+ table=表名 index=索引名 */ 的提示方法指定表单独使用索引，逐个排查)

使用set autotrace traceonly explain的方法和使用表提示的方法，请参考“4.4.1”。

5.2 从数据库服务器进程运行中，优化最消耗CPU的SQL语句

%prstat

获取PROCESS行为oracle字样，CPU超过1%的行的PID，此时使用

%sqlplus “/as sysdba” ----登陆数据库服务器

SQL> ---------执行如下SQL语句

SELECT /*+ ORDERED */ sql_text FROM v$sqltext a WHERE (a.hash_value, a.address)

IN (SELECT DECODE (sql_hash_value,0, prev_hash_value,sql_hash_value),

DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)

FROM v$session b

WHERE b.paddr =

(SELECT addr FROM v$process c WHERE c.spid = '&pid')) ORDER BY piece ASC;

系统会提示你输入PID的值： ---输入刚才准备的PID值

SQL> -----此时就会查出正在消耗CPU的语句。

使用SQL语句优化的方法优化此SQL语句。

5.3 导数据到新数据库指定用户的缺省表空间

我们常常从某台服务器的一个数据库exp导数据到另外一台服务器的一个数据库，发现imp后，很有可能没有使用新数据库的导入用户的缺省表空间。

使用语句：

SQL> select table_name,tablespace_name from user_tables;

可以查出表使用的表空间。

那是因为我们创建用户的时候，没有回收被创建用户对表空间的unlimited tablespace 权限，我们使用语句

SQL>revoke unlimited tablespace from 用户名;

收回用户的unlimited tablespace 权限，此时再在默认缺省表空间上分配该权限：

SQL>alter user 用户名 quota unlimited on 缺省表空间名;

再导入测试，如果还不行，发现总是导入到某个表空间ABC上，则使用如下语句：

SQL>alter user 用户名 quota 0 on ABC;

回收用户在该表空间的配额分配权限，重新导入，则一定可以矫正过来。

5.4 正确安装数据库软件，但无法创建库

现象：

在UNIX正确安装oracle 9i后，提示安装成功，但不能使用，提示OS错误

分析：

由于操作系统自己和硬件的原因，导致oracle的使用受到限制。

处理：

可通过如下两种方法解决：

1. 把启动参数processes参数修改的更底(降低一倍)。

2. 增加/etc/system文件中SEMMSL & SEMMNI的参数值(增加一倍)。Reboot系统再启动数据库。

5.5 打开数据库报告关闭错误，关闭数据库报告打开错误

现象：

数据库不可用，ORACLE关闭时报告数据库已经打开打开，打开数据库时报告数据库已经关闭。

分析：

一般这种情况是因为非正常关闭数据库造成，即一般使用abort 参数。还有就是实际用户定义的$ORACLE_SID与实际创建时候的SID不一样，这样系统找不到当前SID为启动SID，报告错误。

处理：

A) 检查.cshrc 中配置的$ORACLE_SID与用户在用户已经创建了的数据库SID范围内。

B) 把需要启动的数据库SID设置成当前系统默认SID，使用 setenv ORACLE_SID SID，这样只在当前窗口有效。关闭该窗口即无效。

C) 使用ipcs查看当前内存等中是否有残留内存等信息，如果有，使用ipcrm –m清除共享内存，使用ipcrm –s清除下一个。保证清除干净。

D) 检查后台进程，如果有在运行，直接使用kill -9 杀掉即可。查看：ps –ef |grep ora_

E) 此时再去打开数据库即可。

5.6 用户不能在数据库中创建对象

现象：

用户以前能正确在数据库中创建对象，突然不能创建用户对象。

分析：

此问题是因为在创建用户的时候没有分配配额，用户使用到一定时候后，就不能再创建对象了。

处理：

设置用户配额为无限制。

SQL>alter user user_name quota unlimited on user_default_tablespace;

5.7 导入导出错误

现象：

导入导出错误为:imp16,type 852 to 850等字符集错误。

分析：

用户当前配置字符集与安装时候的配置字符集不一致。

处理：

查看用户环境变量的NLS_LANG设置是否与与导出的时候一致，要求一致。其次，要求props$中对语言的设置与原来一致。可参考安装成功后对该.cshrc文件的备份。

5.8 不同版本的数据库数据导入导出

现象：

直接从一个版本中导出的数据，无法导入到不是同一个版本中的数据库。

分析：

由oracle自己的软件机制所决定。

处理：

使用与需要导入的目标数据库同样版本的客户端进行连接，然后在该客户端进行导出操作即可。

5.9 数据库挂起问题

现象：

已经进行连接的用户能够继续对数据库操作，未连接或曾经连接但是已经断掉的用户，如果再想连接，就会长时间等待。

关闭数据库，重新启动。暂时解决。但是过了三天左右，又开始出现这个问题。

分析：

1) 有可能是数据库处于归档模式，而又没有启动归档进程或者磁盘没有空间。

2) 进程产生死锁。

3) 其他原因

处理：

1) 归档进程问题：启动归档进程；磁盘空间问题：清理磁盘空间。

2) 死锁问题，重启数据库即可。

目前一个处理方法：

5.10 多个数据库操作方法:不同实例间转换.

现象：

在用户创建了多个数据库的时候，需要分别对不同的数据库进行管理。

分析：

在用户登录的时候，启动用户环境变量.cshrc，而在.cshrc中已经设置了当前数据库SID，所以要启动其他SID数据库，需要把他变为当前数据库SID。

处理：

只要把需要操作的数据库SID变为当前SID进行操作即可。有如下两种方法修改SID为SID。

比如有两个数据库SID分别为sdh和 sp，在.cshrc文件中定义的当前数据库为sdh，今需要对sp进行启动关闭等操作。

1) 修改.cshrc 文件中的setenv ORACLE_SID sdh项，修改为希望操作的数据库SID，即为setenv ORACLE_SID sp。然后使用source 命令使其生效即可把它变为当前SID。

2) 使用命令修改。执行%setenv ORACLE_SID sp即可。

此时再使用%sqlplus /nolog

SQL>conn /as sysdba连接的即为修改了的SID数据库了。

5.11 查询错误号信息

现象：

在数据库使用过程中，数据库会报告一些有如ora-XXXX,imp-XXXX等等错误号码。

分析：

该号码是发生错误，或者oracle系统提示时报告用户的一个标识。

处理：

使用如下方法获取该错误号码对应的报告信息：

比如报告ora-0042

%oerr ora 0042

5.12 查看oracle系统的版本

SQL> select banner from sys.v_$version;

5.13 查看oracle系统安装了哪些选项

SQL> col PARAMETER format a60

SQL> col VALUE format a10

SQL> select * from sys.v_$option;

5.14 查看oracle软件是几位数据库

%cd $ORACLE_HOME/bin

%file oracle

5.15 数据库起不来，报告ORA-01157和ORA-01110错误

现象：

数据库不能启动正常运行，报告ORA-01157和ORA-01110错误。

分析：

数据库datafile文件发生损坏或者用户误操作删除数据库datafile文件。

处理：

1) 如果用户有备份，利用上次的热备份进行恢复。

2) 如果没有备份，且再需要改数据文件，则：

执行一次冷备份，并

%sqlplus /nolog

SQL>connect /as sysdba

SQL>startup mount

SQL>alter database datafile ‘$ORACLE_BASE/oradata/$ORACLE_SID/datafile_name’ offline drop；

//执行该命令后，以后该数据文件不可恢复。

SQL>alter database open

执行一次全库备份。

问题：如果误删了redo log文件，数据库起不来怎么办？

解决：

如果删掉的日志文件为Inactive

sqldba>startup mount

sqldba>alter database drop logfile filename

sqldba>alter database add logfile filename

recover database until cancel

CONTINUE

CONTINUE

alter database open reset logs

如果删掉的日志文件为current 或active, 联系oracle 的技术支持部门，然后采取相应的措施

问题：如何修改数据库的名字？

解决：

sqldba>alter database backup controfile to trace;

在?/rdbms/log目录下，找到最新生成的trace文件ora_nnnn.trc, nnnn表示一个数字

Edit该trace文件，找到create controlfile命令 ,存于 一个文件ccf.sql

修改旧命令为： create controlfile set database newdbname resetlogs

CONTINUE

CONTINUE

备份所有旧的control file

编辑initsid.ora, 修改db_name=newdbname

sqldba>startup nomount

sqldba>@ccf

sqldba>alter database open

5.16 Oracle目录文件维护

Oracle以下目录的下的相应文件可以直接删除而不会影响数据库运行

(注意，bdump,cdump,udump,audit目录是不可以删除的，否则数据库出错！！！)：

${ORACLE_BASE}/admin/${ORACLE_SID}/bdump目录

*.trc文件

*.log文件

${ORACLE_BASE}/admin/${ORACLE_SID}/cdump目录

core_*目录

${ORACLE_BASE}/admin/${ORACLE_SID}/udump目录

*.trc文件

${ORACLE_HOME}/rdbms/audit目录

*.aud文件

以上文件需要按时维护(直接删除，或备份后再删除)，他们会随着时间的增长，文件会不断的变大，或文件数量会不断的增多，这些文件记录了数据库的一些运行信息，在数据库故障或需要做一些数据库性能等分析的时候需要使用这些文件