ORACLE基本命令使用介绍

 

连接数据库

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;