oracle常用操作命令，希望对大家有用

xkmj · 发表于 2010-3-14 00:33:02

连接数据库
C:>SQLPLUS /NOLOG

SQL>CONN / AS SYSDBA

1.Oracle 关闭
SQL>SHUTDOWN (ABORT|IMMEDIATE|NORMAL)

2.Oracle 启动
SQL>STARTUP (REMOUNT|MOUNT|OPENT)

3.SQL> HELP SHOW

SHOW
----

Shows the value of a SQL*Plus system variable, or the
current SQL*Plus environment.

SHO[W] option

where option represents one of the following terms or clauses:
   system_variable
   ALL
   BTI[TLE]
   ERR[ORS] [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER
      | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS} [schema.]name]
   LNO
   PARAMETERS [parameter_name]
   PNO
   REL[EASE]
   REPF[OOTER]
   REPH[EADER]
   SGA
   SPOO[L]
   SQLCODE
   TT[ITLE]
   USER

SHOW SPOOL is not available in iSQL*Plus

4.TNSPING
TNS连接问题检测
TNSNAME.ORA

5.执行SQL Script
SQL>@C:\RUN.SQL

6.查看执行结果（开启/关闭）
SQL>SET ECHO ON(OFF)

7.常用配置
SQL>SET LINESIZE 1000
SQL>ECHO ON
SQL>SET TIMING ON
SQL>SPOOL C:\TEST.LOG
SQL>SELECT * FROM XXXXX
SQL>SPOOL OFF

8.授权
建立连接 GRANT CREATE SESSION TO USERNAME
移除连接 REVOKE CREATE SESSION TO USERNAME
其他授权
GRANT SELECT,INSERT,DELETE,UPDATE ON SCOTT.EMP TO USERNAME
确认更改
SQL>COMMIT

9.更改密码
Alter user username identified by password

10.察看Datafile
SQL>SELECT * FROM V$DATAFILE

11察看结构
SQL>DESC V$DATAFILE

12,察看DB File
SQL>SELECT * FROM V$DBFILE

13.察看Tablespace
SQL>SELECT * FROM V$TABLESPACE

14.察看SGA大小
SQL> SHOW PARAMETER SGA

15.更改 SGA大小，
SQL>ALTER SYSTEM SET SHARED_POOL_SIZE=..M

16.察看CONTROLFILE
SQL> SELECT * FROM V$CONTROLFILE

17察看REDO LOG
SQL> SELECT * FROM V$CONTROLFILE

18.察看所有的用户
SQL> SELECT USERNAME FROM DBA_USERS

19.在Offline状态下搬迁数据库文件
关闭数据库
SQL>SHUTDOWN IMMEDIATE
搬迁数据文件
连接数据库
SQL>CONN / AS SYSDBA
SQL>STARTUP MOUNT
SQL>Alter database rename file ‘e:\.......’ to ‘c:\.......’
SQL>ALTER DATABASE OPEN
SQL>SHUTDOWN
删除旧的数据文件

20.更改ONLINE REDO LOG
SQL>ALTER SYSTEM SWITCH LOGFILE;