Oracle数据库操作命令

    首先，不管是Oracle、mySQL，还是其他数据库，一般都是使用的标准的SQL语言（结构化查询语言）。所以只要掌握了SQL语句，在其他数据库中大都是通用的。
    但是，每个数据库都有自己的特性。这就需要我们去掌握他们的特性。
    

Oracle数据库三种连接身份

“sysdba” ：数据库管理员。权限包括：打开数据库服务器、关闭数据库服务器、备份数据库、恢复数据库、日志归档、会话限制、管理功能和创建数据库。

数据库用sysdba登录的验证有两种方式，一种是通过os认证，一种是通过密码文件验证；登录方式有两种，一种是在数据库主机直接登录（用os认证的方式），一种是通过网络远程登录；需要设置的参数有两个，一个是SQLNET.AUTHENTICATION_SERVICES，一个是REMOTE_LOGIN_PASSWORDFILE。

“sysyoper”：数据库操作员。权限包括：打开数据库服务器、关闭数据库服务器、备份数据库、恢复数据库、日志归档和会话限制。
“normal”：普通用户。权限只有查询某些数据表的数据。

    SYS和SYSTEM是每个ORACLE 数据库系统缺省安装的两个帐户。SYS是所有内部数据库表、结构、过程包、等拥有者，此外它还拥有 V$和数据字典视图，并创建所有封装的数据库角色(DBA，CONNECT，RESOURCE)。 Sys是一个唯一能访问特定内部数据字典的用户。System也是在安装Oracle时创建的用户，用于 DBA任务的管理。
    

启动Oracle数据库的步骤

步骤：启动例程—>装载数据库—>打开数据库
    每启动一个数据库至少对应有一个例程，例程是Oracle用来管理数据库的一个实体。在服务器中，例程是由一组逻辑内存结构和一系列后台服务进程组成的。当启动数据库时，这些内存结构和服务进程到分配、初始化和启动。
注意：一个例程只能访问一个数据库，而一个数据库可以由多个例程同时访问。
打开数据库时各类文件的使用顺序：

Oracle常用操作命令

1.Oracle登录命令

C:\WINDOWS\system32>net start oracleserviceorcl   #启动服务
OracleServiceORCL 服务正在启动 ......
OracleServiceORCL 服务已经启动成功。
C:\WINDOWS\system32>lsnrctl start    #启动监听
...
TNS-01106: 使用名称LISTENER的监听程序已经启动
C:\WINDOWS\system32>sqlplus afei/123456 as sysdba   #让afei用户使用管理员身份登录
...
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> quit
C:\WINDOWS\system32>sqlplus /nolog    #直接进入SQLplus命令提示符
...
SQL> quit
C:\WINDOWS\system32>sqlplus / as sysdba   #以os身份连接
...
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn /as sysdba    #以os身份连接
已连接。
SQL> quit
C:\WINDOWS\system32>

    
配置使用默认账户scott/tiger登录：
    在Oracle 11g中，默认用户scott用户是被锁定的，先解锁才能使用scott用户登录。

C:\WINDOWS\system32>sqlplus system/Ltf123456   #使用默认账户system登录，密码是安装时设置的口令
...
SQL> conn scott/tiger
ERROR:
ORA-28000: the account is locked
警告: 您不再连接到 ORACLE。
SQL> conn / as sysdba
已连接。
SQL> show user
USER 为 "SYS"
SQL> alter user scott account unlock;    #解锁scott用户
用户已更改。
SQL> commit;   #提交
提交完成。
SQL> alter user scott identified by 123456;   #修改scott用户默认密码
用户已更改。
SQL> quit
C:\WINDOWS\system32>sqlplus scott/123456   #成功使用scott用户登录
...
SQL>

2.创建表空间和用户并授权

C:\WINDOWS\system32>sqlplus /nolog
...
SQL> conn /as sysdba   #以管理员登录
已连接。
SQL> show user   #查看当前用户
USER 为 "SYS"   #说明当前用户为管理员用户
SQL> select name from v$datafile;   #查看当前数据库文件存放位置
NAME
--------------------------------------------------------------------------------
E:\ORACLE\CANLANG\ORADATA\ORCL\SYSTEM01.DBF
E:\ORACLE\CANLANG\ORADATA\ORCL\SYSAUX01.DBF
E:\ORACLE\CANLANG\ORADATA\ORCL\UNDOTBS01.DBF
E:\ORACLE\CANLANG\ORADATA\ORCL\USERS01.DBF
E:\ORACLE\CANLANG\ORADATA\ORCL\EXAMPLE01.DBF
SQL> create tablespace afei datafile 'E:\ORACLE\CANLANG\ORADATA\ORCL\AFEI.DBF' size 300m;   #创建表空间名称为afei，并定义表空间位置和大小
表空间已创建。
SQL> create user afei identified by 123456 default tablespace AFEI;   #创建用户为afei并设置密码：123456，并设置默认表空间
用户已创建。
SQL> grant dba,connect to afei;   #给afei用户授权
授权成功。
SQL> quit
C:\WINDOWS\system32>

测试以创建的用户afei登录数据库：

C:\WINDOWS\system32>sqlplus afei/123456
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

连接成功
    

3.使用第三方工具Navicat连接
    首先，Navicat还不能直接连接，需要做相关配置：
将安装目录下的 E:\Oracle\canlang\product\11.2.0\dbhome_1\BIN\oci.dll文件导入到Navicat中


    
连接Oracle数据库：

    
3.其他常用命令

SQL> select * from all_users;   #查看系统拥有那些用户
USERNAME                          USER_ID CREATED
------------------------------ ---------- --------------
AFEI                                   91 06-11月-19
BI                                     90 05-11月-19
PM                                     89 05-11月-19
...
SQL> drop user afei;   #删除用户afei
用户已删除。
SQL> select name from v$database;   #查看当前数据库名
NAME   
---------
ORCL
SQL> shutdown immediate;   #关闭数据库
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup   #启动数据库例程
ORACLE 例程已经启动。
Total System Global Area 3373858816 bytes
Fixed Size                  2180424 bytes
Variable Size            1845496504 bytes
Database Buffers         1509949440 bytes
Redo Buffers               16232448 bytes
数据库装载完毕。
数据库已经打开。
SQL> select * from user_role_privs;   #查看当前用户角色
USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
SYS                            ADM_PARALLEL_EXECUTE_TASK      YES YES NO
SYS                            APEX_ADMINISTRATOR_ROLE        YES YES NO
...
SQL> select * from dba_data_files;   #查看数据库文件
FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
E:\ORACLE\CANLANG\ORADATA\ORCL\USERS01.DBF
         4 USERS                             5242880        640 AVAILABLE
           4 YES 3.4360E+10    4194302          160    4194304         512
ONLINE
...

查看数据库是否为归档模式：

SQL> select name,log_mode from v$database;   
NAME      LOG_MODE
--------- ------------
ORCL      NOARCHIVELOG
SQL> archive log list;
数据库日志模式       非存档模式
自动存档             禁用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     5
当前日志序列           7
SQL> select archiver from v$instance;
ARCHIVE
-------
STOPPED
SQL>

开启数据库的归档模式（只能在mount模式开启）：

SQL> shutdown immediate;
SQL> startup mount;  #启动mount例程
ORACLE 例程已经启动。
...
SQL> alter database archivelog;
数据库已更改。
SQL> alter database open;
数据库已更改。
SQL> alter system archive log start;
系统已更改。
SQL> archive log list;
数据库日志模式       存档模式
自动存档             启用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     5
下一个存档日志序列   7
当前日志序列           7
SQL>

关闭归档（同样需要在mount状态下操作）：

SQL> shutdown immediate;
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。
SQL> alter database noarchivelog;
数据库已更改。
SQL> archive log list;
数据库日志模式       非存档模式
自动存档             禁用
...
SQL>

启动模式说明：

    

4.表空间操作

SQL> select * from v$tablespace;   #查看当前存在的表空间
       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         1 SYSAUX                         YES NO  YES
...
SQL> shutdown immediate;
SQL> startup
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;   #查看表空间使用情况，单位M
TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------ --------------------
SYSAUX                                          490
USERS                                             5
AFEI                                            300
SYSTEM                                          690
EXAMPLE                                         100
已选择6行。
SQL> select username,default_tablespace from dba_users;  #查看用户默认表空间

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYS                            SYSTEM
SYSTEM                         SYSTEM
...

    
5.查询修改数据库最大连接数

SQL> select value from v$parameter where name = 'processes';   #查询数据库最大连接数
VALUE
--------------------------------------------------------------------------------
150
SQL> alter system set processes = 1000 scope=spfile;   #修改数据库最大连接数
系统已更改。  #需要重启数据库才能生效
SQL> shutdown immediate;
SQL> startup
SQL> select value from v$parameter where name = 'processes';
VALUE
--------------------------------------------------------------------------------
1000
SQL>

    
6.其他查询命令

SQL> select * from v$version;  #查看数据库版本
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> select name from v$controlfile;   #查看控制文件路径
NAME
--------------------------------------------------------------------------------
E:\ORACLE\CANLANG\ORADATA\ORCL\CONTROL01.CTL
E:\ORACLE\CANLANG\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL
SQL> select member from v$logfile;    #查看日志文件路径
MEMBER
--------------------------------------------------------------------------------
E:\ORACLE\CANLANG\ORADATA\ORCL\REDO03.LOG
E:\ORACLE\CANLANG\ORADATA\ORCL\REDO02.LOG
E:\ORACLE\CANLANG\ORADATA\ORCL\REDO01.LOG
SQL>

    
7.Oracle数据库报错问题

问题1：当执行删除用户语句“drop user 用户名”后，手动到Oracle安装目录下删除表空间文件，这会对Oracle数据库产生影响，其实在Oracle数据库中表空间文件记录还存在。

原因：ORA-01109: 数据库未打开。
解决办法：删除表空间，重启数据库。

C:\WINDOWS\system32>sqlplus / as sysdba
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
E:\ORACLE\CANLANG\ORADATA\ORCL\AFEI.DBF   #表空间记录依然存在
SQL> alter database datafile 'E:\ORACLE\CANLANG\ORADATA\ORCL\AFEI.DBF' offline drop;    #更改文件，下线
数据库已更改。
SQL> drop tablespace afei including contents;   #删除表空间
表空间已删除。
SQL> select name from v$datafile; 
NAME
--------------------------------------------------------------------------------
E:\ORACLE\CANLANG\ORADATA\ORCL\SYSTEM01.DBF
...
E:\ORACLE\CANLANG\ORADATA\ORCL\EXAMPLE01.DBF   #AFEI.DBF表空间成功删除
SQL> shutdown immediate
SQL> startup   #重启生效

    
8.SHOW命令的基本功能