Oracle12C | 操作归纳

1. 修改

1.1 修改字段长度

alter table [表名] modify [字段名] [字段类型]([长度])  
eg: alter table student modify name varchar2(4000)

可以用如下语句查看表结构来查看修改是否生效

//查看表结构(表名、字段类型、字段长度...);注意区分表名大小写
SELECT * FROM user_tab_columns A where A.Table_Name = 'TEST_TABLE_NAME'; 

2. 查看

2.1 查看表结构

//查看表结构(表名、字段类型、字段长度等一系列信息会列出来);注意区分表名大小写！！！
SELECT * FROM user_tab_columns A where A.Table_Name = 'TEST_TABLE_NAME';

3. 新增

3.1 新增视图

CREATE OR REPLACE VIEW TEST_VIEW_NAME AS (SELECT [字段] FROM [表名])
//从 student 表中查询 name，classname，age结果作为 v_student 展示出来。
eg：create or replace view v_student as (select name，classname, age from student)

4. 删除

暂无

5. 备份

5.1 exp/imp 方式备份

5.1.1 数据库导出、导入

导出数据库

exp username/password@ip地址/实例 file=e:\test.dmp owner=username 

导入数据库：

imp username/password@ip地址/实例 file=e:\test.dmp  fromuser=导出用户名 touser=导入用户名  IGNORE=y COMPILE=y log=c:\imp.log

eg：如果导出的用户是DBA权限，而导入的用户(touser) 不是DBA 时，可能会提示权限不对，这时只需把 username/password 换成 具有DBA 权限的用户(比如 SYSTEM)即可，touser 保持不变。

5.1.2 表导出、导入

导出表：

exp username/password@ip地址/实例 file=路径及文件名 tables=(表名1，表名2，…)

导入表：

imp username/password@ip地址/实例 file=e:\table.dmp grants=y commit=y full=y ignore=y

另一种导入表方式：

imp username/password@ip地址/实例 fromuser=导出用户名 touser=导入用户名file=e:\table.dmp commit=y full=y ignore=y tables=(表名)

5.2 expdp/impdp 方式备份

服务端备份多采用这种方式（又称为数据泵备份;更快,更强!!!）

导出：

create directory dp_dir as '/home/oracle/test'; //创建导出文件夹
grant read,write on directory dp_dir to hners; //授权导出文件夹的读写权限给用户 hners
grant exp_full_database, imp_full_database to hners; //授权导出、导入权限给hners用户
expdp username/password@ip地址/实例 schemas=username dumpfile=test.dmp directory=dp_dir logfile=expdb.log （可在后面加版本控制 version=12.1.0.2.0）

导入(本地为linux)：

create directory dp_dir as '/home/oracle/test'; //创建导入文件夹,将备份好的文件放入该文件夹;
grant read,write on directory dp_dir to yners; //授权导入文件夹的读写权限给用户yners
grant exp_full_database,imp_full_database to yners;//授权导出、导入的权限给yners用户
//下面语句中 REMAP_SCHEMA = hners:yners 是重点, hners是导出用户 yners是导入用户
impdp username/password@ip地址/实例 REMAP_SCHEMA = hners:yners table_exists_action = replace directory = dp_dir dumpfile = test.dmp logfile = impdp.log TRANSFORM=segment_attributes:n 

导入(本地为windows)

//导入（本地 window）
create directory dp_dir as 'D:\dbbkup';
grant read,write on directory dp_dir to yners; //授权导入文件夹的读写权限给用户yners
grant exp_full_database,imp_full_database to yners;//授权导出、导入的权限给yners用户
//下面语句中 REMAP_SCHEMA = hners:yners 是重点, hners是导出用户 yners是导入用户
impdp username/password@ip地址/实例 REMAP_SCHEMA = hners:yners table_exists_action = replace directory = dp_dir dumpfile = test.dmp logfile = impdp.log TRANSFORM=segment_attributes:n 

5.3 快速备份表

create table BA_HGSH_BK20200318 as select * from BA_HGSH

6. 更新

6.1 更新关联表数据

6.1.1 关联表数据一对一

-- 将表 TEMPF_201018 中的 num 刷到表TB_LOG 的 num 上,条件是两表的 code 值相同
UPDATE TB_LOG tl set tl.num = (SELECT num FROM TEMPF_201018 tf WHERE tf.code = tl.code)

6.2 更新时间

--报错信息,举个栗子
-- ORA-01830: 日期格式图片在转换整个输入字符串之前结束
--以下语句更新不成功
UPDATE TB_SOMER set createddate = to_date('2020-04-24 20:32:31.000000','yyyy-mm-dd hh24:mi:ss') WHERE id = 'd33f8c09-0a5e-4abe-a5de-49a1eb0a002f';
-- 以下正确更新时间
UPDATE TB_SOMER set createddate = to_date('2020-04-24 20:32:31','yyyy-mm-dd hh24:mi:ss') WHERE id = 'd33f8c09-0a5e-4abe-a5de-49a1eb0a002f';

6.3 更新主从表数据

主从表数据库更新一定要使用 where 条件

UPDATE TB_SOMERDET tsd set tsd.supplierName = (SELECT ts.supplierName FROM TB_SOMER ts WHERE ts.id = tsd.so and ts.tsType = '03') WHERE
tsd.id in
-- 这里不加条件限制,则更新的是 TB_SOMERDET 表的全部数据
(
	SELECT 
	tsd.id
	FROM 
	TB_SOMERDET tsd
	left join TB_SOMER ts on ts.id = tsd.so
	WHERE ts.tsType = '03'
)

6.4 更新大量数据

采用快速游标法

BEGIN
FOR cur IN (
	SELECT 
		s.id sid
	FROM TB_SOMERDET s
	) loop
 
   UPDATE TB_SOMERDET s SET s.gerCode = '*',s.sunCode = '*',s.lunCode = '*' WHERE s.id = cur.sid;
 END loop;
END;

7. 用户操作

7.1 修改密码

> sqlplus /nolog
SQL> conn /as sysdba
--查看用户
SQL> select username,password from dba_users;
--修改用户口令 格式 alter user 用户名 identified by 新密码;
SQL> alter user system identified by manager;

8. 模式操作

8.1 新增模式

-- 查看当前容器
SQL> select sys_context ('USERENV', 'CON_NAME') from dual;
-- 查看所有容器
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
-- 切换容器
SQL> alter session set container=ORCLPDB;
-- 创建用户
SQL> create user fsyml identified by fsyml;
-- 用户授权
SQL> grant connect, resource, dba to fsyml;

Oracle 新增模式必须新增一个用户，换句话说就是创建用户之后会自动地新增一个模式，而这个模式名和用户名是相同的。

9. 表操作

9.1 误删表恢复

Oracle10g 以上的数据库版本支持从数据库回收站中恢复表，具体步骤如下。

• 查看数据库回收站中表

select object_name,original_name,partition_name,type,ts_name,createtime,droptime from recyclebin;

• 恢复表

# 方式一
SQL>flashback table 表名 to before drop;

# 方式二
SQL>flashback table "BIN$b+XkkO1RS5K10uKo9BfmuA==$0" to before drop;