【ORACLE】常用命令整理，专注于DOS命令的程序猿。（笔记）

1、登录

-- 不用密码登录
sqlplus/nolog
-- 使用账号密码登录，as sysdba 使用管理员权限登录
sqlplus 【用户名】/【密码】 as sysdba

2、查看基本信息

2.0 查询表中数据

-- 查询前十条信息
 1) select * from tbname where rownum < 11;
 2) select * from (select * from tbname order by id desc ) where rownum<=10;

2.1 查看当前用户

-- 查看自己的是哪个用户
SHOW USER;
-- 查询所有用户
SELECT * FROM user_users;
-- 查看当前用户详细信息
select * from user_users;

2.2 查看角色

2.2.1 查看所有角色

-- 查看所有角色
SELECT * FROM dba_roles;

2.2.2 查看当前用户所拥有的角色

SELECT * FROM user_role_prives

2.2.3 查询当前会话所拥有的角色（一个会话以一个命令窗口为准）

SELECT * FROM session_roles;

2.3 查看表空间名称及大小

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; 

2.4 查看表结构

DESC 表名

DESC tableName;

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

SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space 
FROM dba_data_files 
ORDER BY tablespace_name; 

2.6 查询某个用户拥有的权限（只有dba能查）

2.7 查看当前数据库的一些详细参数（比如查询字符编码）

-- 查询数据库详细信息
SELECT * FROM NLS_DATABASE_PARAMETERS; 
-- 查询字符编码
SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET'
-- 查询数据库版本及其他信息
SELECT * FROM v$version
SELECT * FROM PRODUCT_COMPONENT_VERSION;

2.8 通过rowid查询某一行所在的数据文件

SELECT file_name, file_id 
FROM dba_data_files 
WHERE file_id IN (
	SELECT DISTINCT dbms_rowid.rowid_relative_fno(rowid) 
	FROM sofa.demovo
);

2.9 查看回滚段名称及大小

SELECT segment_name, tablespace_name, r.status, (initial_extent / 1024) initialextent, 
(next_extent / 1024) nextextent, max_extents, v.curext curextent 
FROM dba_rollback_segs r, v$rollstat v 
WHERE r.segment_id = v.usn(+) 
ORDER BY segment_name; 

2.10 查看表空间的使用情况

SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name 
FROM dba_free_space 
GROUP BY tablespace_name; 

SELECT a.tablespace_name, a.bytes total, b.bytes used, c.bytes free, 
	(b.bytes * 100) / a.bytes "% USED ", (c.bytes * 100) / a.bytes "% FREE " 
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c 
WHERE a.tablespace_name = b.tablespace_name 
	AND a.tablespace_name = c.tablespace_name; 

2.11 查看数据库的创建日期和归档方式

SELECT created, log_mode, log_mode FROM v$database; 
--1G=1024MB 
--1M=1024KB 
--1K=1024Bytes 
--1M=11048576Bytes 
--1G=1024*11048576Bytes=11313741824Bytes 
SELECT a.tablespace_name "表空间名", 
total "表空间大小", 
free "表空间剩余大小", 
(total - free) "表空间使用大小", 
total / (1024 * 1024 * 1024) "表空间大小(G)", 
free / (1024 * 1024 * 1024) "表空间剩余大小(G)", 
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", 
round((total - free) / total, 4) * 100 "使用率 %" 
FROM (
		SELECT tablespace_name, SUM(bytes) free 
		FROM dba_free_space 
		GROUP BY tablespace_name
	) a, (
		SELECT tablespace_name, SUM(bytes) total 
		FROM dba_data_files 
		GROUP BY tablespace_name
	) b 
WHERE a.tablespace_name = b.tablespace_name 

3、切换用户

conn useName/password 
conn useName/password as sysdba

4、创建表空间、创建用户并授权

       想知道怎么创建表空间、创建用户并授权，尝试点一下这里

5、修改信息

5.0 修改一条信息

语法：UPDATE 表名 SET 列名1=值，列名2=值…… WHERE 条件

UPDATE INFOS SET CLASSNO='1002',STUADDRESS='山东莱芜' WHERE STUNAME='阮小二';

5.1 修改表空间状态

表空间的状态属性主要有在线（online），离线（offline），只读（read only）和读写（read write）四种，
其中只读与读写状态属于在线状态的特殊情况

-- 当表空间的状态为online时，才允许访问该表空间中的数据。
ALTER TABLESPACE tablespace_name online;

当表空间的状态为offline时，不允许访问该表空间中的数据。此时向表空间中创建表或者读取表空间的表数据操作
都将无法进行，这时可以对表空间进行脱机备份，也可以对应用程序进行升级和维护等

--parameter表示将表空间切换为offline状态时可以使用的参数。
alter tablespace tablespace_name offline parameter; 
--其中parameter参数主要有：
-- normal
-- temporary
-- immediate
-- for recover

	当表空间的状态为read only时，虽然可以访问表空间的数据，但范文仅仅仅限于阅读，而不能进行任何的
更新和删除操作，目的是为了保证表空间的数据安全。
    如果表空间不是read only状态的，可以使用ater tablespace语句将其状态修改为read only
不过，将表空间的状态修改为read only之前，需要注意如下的事项：
1.表空间必须处于online状态
2.表空间不能包含任何事务的回退段
3.表空间不能正处于在线的数据库备份期间

alter tablespace tablespace_name read only;

当表空间的状态为read write时，可以对表空间进行正常访问，包括对表空间中的数据进行查询，更新和操作。
如果表空间不是read write状态的，可以使用alter tablespace语句将其状态修改为read write
修改表空间的状态为read write，也需要保证表空间处于online状态。

alter tablespace tablespace_name read write;

5.2 修改表状态

Oracle 11g推出了一个新特性，用户可以将表置于READ_ONLY(只读)状态，处于该状态下的表不能执行DML和DDL操作。
在Oracle 11g之前为了使某张表处于READ_ONLY状态，只能通过将整个表空间或者数据库置于READ_ONLY状态。

ALTER TABLE TABLENAME READ ONLY; 

5.3 修改表中列名和列类型

6、删除信息

语法： DELETE FROM 表名 WHERE 条件
DELETE命令是DML命令，删除后的数据可以通过日志文件恢复

7、导入、导出

7.1 导出

exp 用户名/密码@网络服务名 file=xxx.dmp tables=(表名);

7.1.1 实例数据完全导出

导出指定实例下的所有数据

--  将数据库TEST完全导出,用户名system 密码manager 导出到D:\test.dmp中
exp username/password@127.0.0.1/orcl file=d:\test.dmp full=y

7.1.2、用户数据导出

导出指定实例下指定用户的数据，如下为导出用户myoracle1和用户myoracle2的数据

-- 将数据库中myoracle1用户与myoracle2用户的表导出
exp username/password@127.0.0.1/orcl file=d:\test.dmp owner=(myoracle1,myoracle2);

7.1.3、具体表数据导出

导出指定实例下指定用户下的具体表的数据，如下即为导出实例orcl，用户username下的t_user表和t_role表的数据

-- 将数据库中的表t_user、t_role导出
exp username/password@127.0.0.1/orcl file=d:\data\test.dmp tables=(t_user,t_role)

7.2 导入

imp 用户名/密码@网络服务名 file=xxx.dmp full=y;

-- 将D:\daochu.dmp 中的数据导入 TEST数据库中。
imp username/password@127.0.0.1/orcl file=d:\test.dmp full=y;
-- 将d:\daochu.dmp中的表table1 导入
imp username/password@127.0.0.1/orcl file=d:\test.dmp tables=(table1)

附录：

8、导入数据库步骤：

第一步：创建表空间

create tablespace farmdoab datafile 'D:\dzzwpt.dbf' size 100M autoextend on next 50m;

第二步：创建目录

create or replace directory acs_dir as 'D:\DMP';

    PS:把要导入的DMP文件放入目录下。

第三步：创建用户，并指定默认的表空间

create user cxdb identified by root default tablespace farmdoab;

--》》（删除用户：drop user iteye cascade;）

第四步：授权

4.1：授权dba权限：grant connect, dba to cxdb;

4.2：授权用户cxdb读写目录文件： grant read, write on directory dp_dir to cxdb;

第五步：导入DMP文件

impdp acs/acs@ORCL directory=acs_dir dumpfile=PAACS.dmp  full=y;
    PS：lodfile可以不加，不加系统自动创建。

expdp acs/acs@orcl 
dumpfile=paacs.dmp 
logfile=full.log 
directory=data_pump_dir 
tablespaces=ACS,ACS_IDX;

9、Oracle常用数据字典

9.1、Oracle数据字典的名称由前缀和后缀组成，使用_连接，含义说明如下：

dba_:包含数据库实例的所有对象信息
v$_:当前实例的动态视图，包含系统管理和系统优化等所使用的视图
user_:记录用户的对象信息
gv_：分布式环境下所有实例的动态视图，包括系统管理和系统优化使用的视图
all_:记录用户的对象信息机被授权访问的对象信息  

9.2、基本数组字典

描述逻辑存储结构和物理存储结构的数据表，还包括描述其他数据对象信息的表：

数据字典名称	说明
dba_tablespaces	关于表空间的信息
dba_ts_quotas	所有用户表空间限额
dba_free_space	所有表空间中的自由分区
dba_segments	描述数据库中所有段的存储空间
dba_extents	数据库中所有分区的信息
dba_tables	数据库中所有数据表的描述
dba_tab_columns	所有表、视图以及簇的列
dba_views	数据库中所有视图的信息
dba_synonyms	关于同义词的信息
dba_sequences	所有用户序列信息
dba_constraints	所有用户表的约束信息
dba_indexs	数据表中所有索引的描述
dba_ind_columns	所有表及簇上压缩索引的列
dba_triggers	所有用户的触发器信息
dba_source	所有用户存储过程信息
dba_data_files	查询关于数据库文件的信息
dba_tab_grants/privs	查询关于对象授权的信息
dba_objects	数据库所有对象
dba_users	关于数据库中所有用户的信息

9.3、常用动态性能视图

提供了关于内存和磁盘的运行情况，用户只能进行只读而不能修改它们

数据字典名称	说明
v$database	描述关于数据库的相关信息
v$datafile	数据库使用的数据文件信息
v$log	从控制文件中提取有关重做日志组的信息
v$logfile	有关实例重置日志组文件名及其位置的信息
v$archived_log	记录归档日志文件的基本信息
v$archived_dest	记录归档日志文件的路径信息
v$controlfile	描述控制文件的相关信息
v$instance	记录实例的基本信息
v$system_parameter	显示实例当前有效的参数信息
v$sga	显示实例的SGA区大小
v$sgastat	统计SGA使用情况的信息
v$parameter	记录初始化参数文件中所有项的值
v$lock	通过访问数据库会话，设置对象锁的所有信息
v$session	有个会话的信息
v$sql	记录SQL语句的详细信息
v$sqltext	记录SQL语句的语句信息
v$bgprocess	显示后台进程信息
v$process	当前进程的信息