Oracle日常维护命令

程序员必备：Oracle日常维护命令
博客分类： Oracle

Oracle 命令 数据库 SQL dba .

测试环境：Oracle 10g



一、启动、关闭数据库


1、启动实例的各种模式

1）启动实例，装载并打开数据库

这是最普通的数据库操作，意味着某个实例已经启动并且数据库也已经装载并打开。这种模式允许任何一个有效的用户连接到数据库，并执行典型的数据访问操作。启动实例接着从默认的服务器参数文件位置读取初始化参数，然后通过STARTUP命令来装载和打开数据库：

SQL> STARTUP



2）启动实例，不装载数据库

启动实例而不用装载数据库，通常只在整个数据库创建过程中使用该模式：

SQL> STARTUP NOMOUNT



3）启动实例，并装载数据库

可以启动实例并装载数据库，但不打开数据库。该模式允许执行特定的维护操作，例如在下面的任务中必须装载数据库而不打开数据库。

* 重命名数据文件

* 添加取消或重命名重做日志文件

* 允许和禁止重做日志存档选项

* 执行完整的数据为恢复操作

SQL> STARTUP MOUNT



4）在启动过程中限制访问数据库

可以在受限制的模式下启动实例并装载和打开数据库，以便只有管理人员而不是一般的数据库用户可以利用数据库。当需要完成以下一种任务时，使用这种数据库启动模式：

* 执行数据库数据的导入或导出操作

* 执行数据库装载操作用SQL*Loader

* 暂时阻止一般的用户使用数据

* 在某个移植过程和升级操作过程中

SQL> STARTUP RESTRICT



5）迫使实例启动

在一些特殊环境下，可能会在启动数据库实例的时候遇到一些问题。一般不要迫使数据库启动，除非存在以下情形：

* 用SHUTDOWN NORMAL、SHUTDOWN IMMEDIATE、SHUTDOWN TRANSACTIONAL命令不能关闭当前的实例时。

* 在启动实例的时候遇到一些问题时。

SQL> STARTUP FORCE



6）启动实例，装载数据库，并启动完整的介质恢复过程

如果知道需要介质恢复过程，就可以启动实例并为其装载数据库，以及通过使用带RECOVER选项的STARTUP命令的方法来自动启动恢复过程。

STARTUP OPEN RECOVER



2、改变数据库的可用性

1）为实例装载数据库

SQL> ALTER DATABASE MOUNT;



2）打开一个关闭的数据库

SQL> ALTER DATABASE OPEN;



3）以只读模式打开数据库

SQL> ALTER DATABASE OPEN READ ONLY;

以读写模式打开数据库

SQL> ALTER DATABASE OPEN READ WRITE;



3、关闭数据库

1）以正常模式关闭数据库


不允许新的连接、等待会话结束、等待事务结束、做一个检查点并关闭数据文件。启动时不需要实例恢复。

SQL> SHUTDOWN NORMAL



2）以立即模式关闭数据库

不允许新的连接、不等待会话结束、不等待事务结束、做一个检查点并关闭数据文件。没有结束的事务是自动rollback的。启动时不需要实例恢复。

SQL> SHUTDOWN IMMEDIATE



3）以事务模式关闭数据库

不允许新的连接、不等待会话结束、等待事务结束、做一个检查点并关闭数据文件。启动时不需要实例恢复。

SQL> SHUTDOWN TRANSACTIONAL



4）以终止模式关闭数据库


不允许新的连接、不等待会话结束、不等待事务结束、不做检查点且没有关闭数据文件。启动时自动进行实例恢复。

SQL> SHUTDOWN ABORT





二、用户登录


1、以没有连接数据库的方式启动SQL*Plus

# sqlplus /nolog



2、在命令提示符环境中以SYSDBA角色登录，并启动SQL*Plus

1）

# sqlplus /nolog

SQL> connect / as sysdba

2）

# sqlplus / as sysdba



3、以某用户身份登录，并启动SQL*Plus


1）

# sqlplus jsam/jsam123

2）

# sqlplus jsam/jsam123@orcl

3）

# sqlplus /nolog

SQL> conn jsam/jsam123

4）

# sqlplus /nolog

SQL> conn jsam/jsam123@orcl

等等...





三、安全管理


1、用户管理

1）创建用户账号名为jsam，密码为jsam123，采用数据库认证方式。默认表空间为users，临时表空间为temp，其中对表空间users的使用限额为500K，表示最多可以使用500K的表空间：


SQL> create user jsam identified by jsam123

default tablespace users

temporary tablespace temp

quota 500k on users;



应该为每一个用户分配一个默认表空间。如果在创建用户时不指定表空间，系统表空间将被作为默认表空间。系统表空间包含数据字典，并且经常被Oracle使用。在同一表空间放置多个用户对象会由于磁盘竞争而导致数据库系统性能退化。

如果不限制用户使用表空间的限额，可以不加quota 500k on users。



一般创建用户之后会同时进行授权，通常可授权用户连接数据库、创建表/序列/过程/包/函数/视图等权限：

SQL> grant connect,resource,create view to jsam;



2）修改密码

SQL> alter user jsam identified by "123456";



3）删除用户

* 删除用户jsam

SQL> drop user jsam;



* 如果在删除用户的同时，还要删除用户所拥有的数据库对象（如表、索引、簇、视图等），则可使用带cascade的drop语句

SQL> drop user jsam cascade;




4）锁定和解锁用户账号

* 锁定用户账号

SQL> alter user jsam account lock;



* 解锁用户账号

SQL> alter user jsam account unlock;



5）查看当前用户身份


SQL> show user



6）查询用户信息

* 查看当前用户可以访问的所有账号信息、用户ID及创建时间：

SQL> select * from all_users;



* 查看当前数据库所有的用户账号

信息、配置文件及状态：

SQL> select username, profile, account_status from dba_users;



* 查询某用户的表空间使用限额情况（如果没指定限额，将返回no rows selected）：

SQL> select * from dba_ts_quotas where username = 'JSAM';




7）查看用户状态


SQL> select username,account_status from dba_users;



8）用户配置文件

* 查看所有配置情况

SQL> select * from dba_profiles;



* 查看默认配置文件参数情况

SQL> select * from dba_profiles where profile='DEFAULT';



* 查看密码有效期配置

SQL> select * from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';



* 查看引起一个用户被锁定的连续登录失败的次数

SQL> select * from dba_profiles where profile='DEFAULT' and resource_name='FAILED_LOGIN_ATTEMPTS';



* 设置密码有效期为"无限期"

SQL> alter profile default limit password_life_time unlimited;




* 修改连续登录失败上锁次数限制

SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS 20;





2、授予和撤销系统权限

1）授予用户多个系统权限

SQL> grant create session,create table,create view,create any index,create sequence,create type to appdeveloper1;



2）如果想要某个用户代为管理某个特定的权限，可在授予系统权限时指定with grant option选项。

该用户具有以下操作能力：

* 可以向数据库里的任何其他用户授予或撤销该系统权限；

* 可以在向其他用户授予系统权限的时候使用with admin option选项。

SQL> grant create table to tableAdmin with admin option;



3）授予用户所有系统权限（除了select any dictionary）

SQL> grant all privileges to admin;



4）授予用户DBA权限

SQL> grant dba to admin;



5）撤销已授予用户appdeveloper1的create type和create sequence系统权限

SQL> revoke create type, create sequence from appdeveloper1;



3、授予和撤销对象权限

1）授予表的全部对象权限

SQL> grant all privileges on bookinfo to booksystemdeveloper;



2）授予指定的对象权限

SQL> grant insert,update,delete,select on authorinfo to booksystemdeveloper;



3）如果授予对象权限的时候使用了with grant option，那么被授权者就能够把获得的对象权限再去授予其他用户。

SQL> grant all privileges on bookinfo to booksystemdeveloper with grant option;



4）撤销已授予用户的update、delete对象权限：

SQL> revoke update,delete on authorinfo from booksystemdeveloper;



4、查看权限信息

1）查询用户所拥有的系统权限

SQL> select privilege, admin_option from dba_sys_privs where grantee = 'JSAM';



查看当前用户的系统权限

SQL> select * from user_sys_privs;



2）查询

用户拥有的对象权限

SQL> select owner || '.' || table_name object_name, privilege, grantable

from dba_tab_privs

where grantee = 'JSAM'

order by owner, table_name, privilege;




查看当前用户的对象权限

SQL> select * from user_tab_privs;



3）查询当前会话可以使用的权限

SQL> select * from session_privs;




5、角色管理

角色是具有名称的一组系统权限和对象权限的集合。

1）将connect, resource两个角色授予用户

SQL> grant connect, resource to jsam;



2）查看当前会话启用的角色列表

SQL> select * from session_roles;



3）查看当前用户的角色列表

SQL> select * from user_role_privs;



4）查看数据库所有角色

SQL> select * from dba_roles;



5）查看某个用户所拥有的角色

SQL> select granted_role, admin_option

from dba_role_privs

where grantee = 'JSAM';



6）查看角色CONNECT拥有的系统权限

SQL> select role, privilege, admin_option

from role_sys_privs

where role = 'CONNECT';



7）查看角色拥有的对象权限

SQL> select owner || '.' || table_name object_name, privilege, grantable

from role_tab_privs

where role = 'CONNECT'

order by owner, table_name, privilege;






四、事务处理

1、事务提交

1）打开自动提交

SQL> set autocommit on;



2）关闭自动提交

SQL> set autocommit off;



3）显示提交命令

SQL> commit;



2、事务回滚

1）保存存储点

SQL> savepoint 存储点名称



2）回滚到某个存储点

SQL> rollback to 存储点名称



3）回滚整个事务

SQL> rollback






五、对象管理


1、显示一个表的结构

SQL> desc table_name;

SQL> describe table_name;




2、查看对象及状态

查看当前用户的函数与存储过程及状态：

SQL> select object_name,status from user_objects where object_type='FUNCTION';

SQL> select object_name,status from user_objects where object_type='PROCEDURE';



object_type可以是：


SEQUENCE

PROCEDURE

LOB

PACKAGE

PACKAGE BODY

TRIGGER

INDEX

TABLE

VIEW

FUNCTION

JAVA CLASS

JAVA SOURCE

TYPE

...



3、查看对象的源代码

可用于查看存储过程、函数、包等源代码

SQL> select text from all_source where owner=user and name=upper('&plsql_name');




4、查看当前用户所有的表

SQL> select * from user_tables;

SQL> select table_name from user_tables;

tabs是user_tables的同义词，所以可以直接使用tabs更加简洁。



5、编译对象


SQL> alter function FUNCTION_NAME compile;

SQL> alter procedure PROCEDURE_NAME compile;

SQL> alter trigger TRIGGER_NAME compile;


...



6、锁对象


1）当前所有被锁的对象信息

SQL> select * from v$locked_object;




2）查询出被锁对象

、锁的模式及所属用户

SQL> select b.owner, b.object_name, a.session_id, a.locked_mode

from v$locked_object a, dba_objects b

where b.object_id = a.object_id;




3）被锁对象的会话信息

SQL> select https://www.doczj.com/doc/273965285.html,ername, b.sid, b.serial#, logon_time

from v$locked_object a, v$session b

where a.session_id = b.sid

order by b.logon_time;




有些锁可能等待很久都没释放，此时可能需要强行关闭：


语法：alter system kill session 'sid,serial#';

SQL> alter system kill session '104,1894';




4）查询发生锁对应的语句

a）

SQL> select sql_text

from v$sql

where hash_value in

(select sql_hash_value

from v$session

where sid in (select session_id from v$locked_object));



b）

SQL> select distinct sql_text

from v$sql

where hash_value in

(select sql_hash_value

from v$session

where sid in (select session_id from v$locked_object));




c）

SQL> select sql_text,count(*)

from v$sql

where hash_value in

(select sql_hash_value

from v$session

where sid in (select session_id from v$locked_object)) group by sql_text;





六、表空间

1、查看一个数据库内所有表空间的名称和默认存储参数

SQL> select tablespace_name "tablespace",

initial_extent "initial_ext",

next_extent "next_ext",

min_extents "min_ext",

max_extents "max_ext",

pct_increase

from dba_tablespaces;



2、创建表空间

1）创建表空间myspace，大小为100m


SQL> create tablespace myspace datafile '/home/oracle/oracle/oradata/ora10/myspace01.dbf' size 100m;



2）创建表空间myspace，大小为100m，空间不足时按128K自动扩展，最大为200m

SQL> create tablespace myspace datafile '/home/oracle/oracle/oradata/ora10/myspace01.dbf' size 100m autoextend on next 128K maxsize 200m;



3、增加表空间

1）通过增加表空间数据文件大小增加表空间

SQL> alter database datafile '/home/oracle/oracle/oradata/ora10/myspace01.dbf' resize 120m;



2）通过增加数据文件增加表空间

SQL> alter tablespace myspace add datafile '/home/oracle/oracle/oradata/ora10/myspace02.dbf' size 50M;



4、修改表空间


1）开启数据文件自动扩展

SQL> alter database datafile '/home/oracle/oracle/oradata/ora10/myspace01.dbf' autoextend on;



2）关闭数据文件为自动扩展

SQL> alter database datafile '/home/oracle/oracle/oradata/ora10/myspace01.dbf' autoextend off;



5、删除表空间

SQL> drop tablespace myspace;




6、查看用户的缺省表空间

1）查看当前用户的缺省表空间

SQL> select username,default_tablespace,temporary_tablespace from user_users;



2）查看某用户的缺省表空间

SQL> select username,default_t

ablespace from dba_users where lower(username) = 'jsam';




查看所有用户及默认表空间关系

SQL> select username,default_tablespace from dba_users;




3）查看一个表所在表空间

SQL> select tablespace_name from all_tables where table_name='COMPANY';

SQL> select tablespace_name from user_tables where table_name='COMPANY';



7、查看表空间物理文件的名称及大小


1）查看一个数据库内所有数据文件和相关的表空间




SQL> select file_name, blocks, tablespace_name from dba_data_files;

SQL> select file_name, blocks, tablespace_name, autoextensible from dba_data_files;




2）查看表空间的名称及大小

SQL> select t.tablespace_name, round(sum(bytes / (1024 * 1024)), 0) ts_size


from dba_tablespaces t, dba_data_files d

where t.tablespace_name = d.tablespace_name

group by t.tablespace_name;



3）查看表空间物理文件的名称及大小

SQL> select tablespace_name,


file_id,

file_name,

round(bytes / (1024 * 1024), 0) total_space

from dba_data_files

order by tablespace_name;



4）查看表空间物理文件的名称及大小（常用）

格式：FILE_NAME TABLESPACE TOTAL(M) USED(M) %USED

语句：


SQL> select b.file_name,

b.tablespace_name,

b.bytes / 1024 / 1024 "TOTAL(M)",

(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 "USED(M)",

substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) "%USED"

from dba_free_space a, dba_data_files b

where a.file_id(+) = b.file_id

group by b.tablespace_name, b.file_name, b.bytes

order by b.tablespace_name;



5）查询空闲的表空间


SQL> select sum(bytes) / (1024 * 1024) as free_space, tablespace_name

from dba_free_space

group by tablespace_name;



6）查询空闲的表空间


SQL> select tablespace_name,

count(*) as extends,

round(sum(bytes) / 1024 / 1024, 2) as "Free(MB)",

sum(blocks) as blocks

from dba_free_space

group by tablespace_name;






七、查看参数


1、查看初始化参数

1）查看所有初始化参数

SQL> show parameters;



2）如果只想查询一个具体的参数值，则使用命令：show parameters [参数名称]。

例如查询控制文件信息：

SQL> show parameters control_files;



2、查询字符集

1）查询当前数据库字符集

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



2）查看服务器端字符集配置

SQL> select * from v$nls_parameters;

SQL> select * from nls_database_parameters;

第二个语句的查询结果比第一个语句多了一个数据库版本的参数NLS_RDBMS_VERSION。



3）客户端字符集

SQL> select * from nls_instance_parameters;





八、导入导出


EXP和IMP既可以在客户端使用，也可以在服务端使用。

EXPDP和IMPD

P只能在ORACLE服务端使用，不能在客户端使用。



1、导出

1）完全导出数据库

使用DBA用户完全导出数据库

SQL> exp system/pass123@ora10 file=./jsam.dmp full=y;



2）导出用户jsam的表

SQL> exp jsam/jsam123@ora10 file=./jsam.dmp compress=n log=jsam.log;


compress=n：表明导出来的文件不需要压缩放在一个数据块中

log=jsam.log：指定日志文件



2、导入

1）导入数据库

SQL> imp jsam/jsam123@ora10 file=./jsam.dmp;



2）当导出与导入使用的是不同的用户名时，需要指定fromuser、touser

SQL> imp jsam/jsam123@ora10 file=./jsam.dmp fromuser=jsam_test touser=jsam ignore=y;

ignore=y：表明忽略表创建的过程，只是将表中的数据导入表中。例如表已经存在，使用该参数可忽略表的创建，而将数据导入到数据库中。



3、数据泵导入导出


1）创建文件导出目录

# mkdir /home/oracle/dbbackup/tempdump/



2）数据库创建逻辑导出目录并授权给jsam

SQL> create or replace directory tempdump as '/home/oracle/dbbackup/tempdump/';

SQL> grant read,write on directory tempdump to jsam;



3）导出数据库(导出文件保存在/home/oracle/dbbackup/tempdump)

# expdp jsam/jsam123@ora10 dumpfile=jsam_dp.dmp directory=tempdump



4）导入数据库

# impdp jsam/jsam123@ora10 dumpfile=jsam_dp.dmp directory=tempdump logfile=jsam_dp.log remap_schema="jsam":"新的用户名" remap_tablespace="users":"新的表空间名称"



说明：

remap_schema：该选项用于将源方案的所有对象装载到目标方案中。当源、目标用户名称不同时需要使用

remap_tablespace：将源表空间的所有对象导入到目标表空间。当源、目标表空间名称不同时需要使用



如果需要导入到其他服务器的数据库中，需要根据情况创建用户、表空间，同样执行1）、2），将导出的文件放到相应的目录（此处目录可以与导出的时候不同）下，再执行导入操作。







九、用户连接管理

1、用系统管理员登录，查看当前数据库有几个用户连接

SQL> select username,sid,serial# from v$session;

SQL> select username,sid,serial# from v$session where username=upper('jsam');



2、关闭某个连接

语法：alter system kill session 'sid,serial#';

SQL> alter system kill session '104,1894';




3、查询Oracle支持的最大连接数

SQL> show parameter processes;



4、修改最大连接数

1）修改最大连接数

SQL> alter system set processes=300 scope = spfile;



2）创建pfile

SQL> create pfile from spfile;



3）重启数据库



5、查看连接数

1）查询当前连接数


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



2）查询当前并发连接数

SQL> select count(*) from v$session where status='ACTIVE';



3）查询不同

用户的连接数


SQL> select count(*) from v$session where username is not null;

SQL> select username, count(username)

from v$session

where username is not null

group by username;




4）查看当前数据库建立的会话情况

SQL> select sid, serial#, username, program, machine, status from v$session;





十、其他命令


1、tnsping命令

1）验证名字解析（name resolution，当然是oracle自己的网络服务名）

2）检查远程的listener是否启动（用法：tnsping