2020-12-21 更新bug
PLSQL 登录oracle数据库出现 “无法解析指定的连接标识符”错误
解决办法：
问题就是安装完client后，出在tnsnames.oRA，你需要把tnsnames.oRA删掉，然后复制oracle服务器安装里的tnsnames.ora。
将
F:\Oracle\Client\product\11.2.0\client_1\network\admin\sample
下的 tnsnames.oRA 删除后，将
F:\Oracle\Server\product\11.2.0\dbhome_1\NETWORK\ADMIN
下的 tnsnames.oRA 复制到
F:\Oracle\Client\product\11.2.0\client_1\network\admin
里面，重新登陆plsql即可。
在SQL窗口中执行以下SQL代码。
1、创建表空间
-- Create the TABLESPACE
CREATE TABLESPACE FJFEDW
LOGGING
DATAFILE 'F:\app\administrator\oradata\orcl\FJFEDW.DBF' SIZE 32M
AUTOEXTEND
ON NEXT 2000K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
2、创建临时表空间，这里也可不创建临时表空间，使用默认的temp也可以。
CREATE TEMPORARY TABLESPACE FJFEDW_TEMP
TEMPFILE 'F:\app\Administrator\oradata\orcl\FJFEDW_TEMP.DBF'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;
方法一：执行sql语句
1、创建用户
-- Create the user
create user test-- 用户名
identified by test-- 口令密码
default tablespace FJFEDW--默认表空间
temporary tablespace TEMP --临时表空间
（没有创建临时表空间的执行上一句，创建的执行此句：
temporary tablespace FJFEDW_TEMP）
profile DEFAULT
password expire;
2、给用户赋予权限
-- Grant/Revoke role privileges
grant connect to test with admin option;
grant dba to test with admin option;
-- Grant/Revoke system privileges
grant unlimited tablespace to test with admin option;
方法二：图形界面设置
1、在新建中找到用户选项。
2、填写用户名密码，选择之前创建的表空间。概要文件选择默认的。
3、设置角色权限。
4、设置系统权限。
5、限额设置。
1 使用profile管理用户口令
概述：profile是口令限制，资源限制的命令集合，当建立数据库时，oracle会自动建立名称为default的profile。
当建立用户没有指定profile选项时，那么oracle就会将default分配给用户。
2 账户锁定
概述：指定该账户(用户)登陆时最多可以输入密码的次数，也可以指定用户锁定的时间(天)一般用dba的身份去执行该命令。
例子：指定scott这个用户最多只能尝试3次登陆，锁定时间为2天，创建profile文件
create profile lock_account limit failed_login_attempts 3 password_lock_time 2;
alter user scott profile lock_account;
3 给账户(用户)解锁
alter user scott account unlock;
4 终止口令
为了让用户定期修改密码可以使用终止口令的指令来完成，同样这个命令也需要dba的身份来操作。
例子：给前面创建的用户test创建一个profile文件，要求该用户每隔10天要修改自己的登陆密码，宽限期为2天。看看怎么做。
create profile myprofile limit password_life_time 10 password_grace_time 2;
alter user test profile myprofile;
5 口令历史
概述：如果希望用户在修改密码时，不能使用以前使用过的密码，可使用口令历史，这样oracle就会将口令修改的信息存放到数据字典中，这样当用户修改密码时，oracle就会对新旧密码进行比较，当发现新旧密码一样时，就提示用户重新输入密码。
例子：
create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10
//password_reuse_time指定口令可重用时间即10天后就可以重用
alter user test profile password_history;
drop profile password_history 【casade】
注意：文件删除后，用这个文件去约束的那些用户通通也都被释放了。
加了casade，就会把级联的相关东西也给删除掉。
6 Oracle数据类型
常用的数据类型
字符类 char 长度固定，最多容纳2000个字符。
例子：char(10) ‘小韩’前四个字符放‘小韩’，后添6个空格补全，如‘小韩 ’ varchar2(20) 长度可变，最多容纳4000个字符。
例子：varchar2（10） ‘小韩’ oracle分配四个字符。
这样可以节省空间。clob(character large object) 字符型大对象，最多容纳4g char 查询的速度极快浪费空间，适合查询比较频繁的数据字段。varchar 节省空间
数字型 number范围-10的38次方到10的38次方，可以表示整数，也可以表示小数 number(5,2)表示一位小数有5位有效数，2位小数；
范围：-999.99 到999.99 number(5)表示一个5位整数；范围99999到-99999
日期类型 date 包含年月日和时分秒 oracle默认格式1-1月-1999 timestamp 这是oracle9i对date数据类型的扩展。可以精确到毫秒。
图片 blob 二进制数据，可以存放图片/声音4g；
一般来讲，在真实项目中是不会把图片和声音真的往数据库里存放，一般存放图片、视频的路径，如果安全需要比较高的话，则放入数据库。
在菜单栏选择session下拉选项选中log on 进行登录。
oracle11g数据库的导入/导出，就是我们通常所说的oracle数据的还原/备份。
方法：
一、传统方法：
通用命令:
exp(imp) username/password@SERVICENAME:1521 file="e:\temp.dmp" full = y;
exp:导出命令，导出时必写。
imp:导入命令，导入时必写,每次操作，二者只能选择一个执行。
username:导出数据的用户名，必写;
password:导出数据的密码，必写;
@:地址符号，必写;
SERVICENAME : Oracle的服务名，必写;
1521:端口号，1521是默认的可以不写,非默认要写;
file=“e:\temp.dmp” : 文件存放路径地址，必写;
full=y :表示全库导出。可以不写，则默认为no,则只导出用户下的对象;
数据库导出举例:
exp test/123456@orcl file="e:\temp.dmp" full = y;
1.完全导入导出：
exp(imp) username/password@SERVICENAME:1521 file="e:\temp.dmp" full = y;
2.部分用户表table导入导出：
exp(imp) username/password@SERVICENAME:1521 file="e:\temp.dmp" tabels= (table1,table2,table3,...);
3.表空间tablespaces导入导出：
(一个数据库实例可以有N个表空间(tablespace)，一个表空间下可以有N张表(table)。)
exp(imp) username/password@SERVICENAME:1521 file="e:\temp.dmp" tablespaces= (tablespace1,tablespace2,tablespace3,...);
4.用户名username对象导入导出：
exp(imp) username/password@SERVICENAME:1521 file="e:\temp.dmp" owner(username1,username2,username3);
二、数据泵方法：
创建directory:
expdp(impdp) username/password@SERVICENAME:1521 schemas=username dumpfile=file1.dmp logfile=file1.log directory=testdata1 remap_schema=test:test;
expdp: 导出命令，导出时必写。
impdp: 导入命令，导入时必写,每次操作，二者只能选择一个执行。
username:导出数据的用户名，必写;
password:导出数据的密码，必写;
@:地址符号，必写;
SERVICENAME:Oracle的服务名，必写;
1521:端口号，1521是默认的可以不写,非默认要写;
schemas：导出操作的用户名;
dumpfile：导出的文件;
logfile:导出的日志文件,可以不写；
directory:创建的文件夹名称;
remap_schema=源数据库用户名:目标数据库用户名,二者不同时必写，相同可以省略;
数据库导出举例:
expdp test/123456@orcl:1521 schemas=test dumpfile=test.dmp
logfile=test.log directory=testdata1;
1.查看表空间：
select ＊ from dba_tablespaces;
2.查看管理理员目录(同时查看操作系统是否存在，因为Oracle并不关心该目录是否存在，如果不存在，则出错)。
select ＊ from dba_directories;
3.创建逻辑目录，该命令不会在操作系统创建真正的目录，最好以system等管理员创建。
create directory testdata1 as 'd:\test\dump';
4.给test用户赋予在指定目录的操作权限，最好以system等管理员赋予。
//test是用户名(123456是用户密码)
grant read,write on directory testdata1 to test;
5.导出数据
1)按用户导
expdp test/123456@orcl schemas=test dumpfile=expdp.dmp directory=testdata1;
2)并行进程
parallel expdp test/123456@orcl directory=testdata1 dumpfile=test.dmp parallel=40 job_name=test
3)按表名导
expdp test/123456@orcl tables=emp,dept dumpfile=expdp.dmp directory=testdata1;
4)按查询条件导
expdp test/123456@orcl directory=testdata1 dumpfile=expdp.dmp tables=emp query='WHERE deptno=20';
5)按表空间导
expdp system/manager directory=testdata1 dumpfile=tablespace.dmp tablespaces=temp,example;
6)导整个数据库
expdp system/manager directory=testdata1 dumpfile=full.dmp FULL=y;
6.还原数据
1)导到指定用户下
impdp test/123456 directory=testdata1 dumpfile=expdp.dmp schemas=test;
2)改变表的owner
impdp system/manager directory=testdata1 dumpfile=expdp.dmp tables=test.dept remap_schema =test:system;
3)导入表空间
impdp system/manager directory=testdata1 dumpfile=tablespace.dmp tablespaces=example;
4)导入数据库
impdb system/manager directory=dump_dir dumpfile=full.dmp FULL=y;
5)追加数据
impdp system/manager directory=testdata1 dumpfile=expdp.dmp schemas=system table_exists_action;
三、PLSQL方法：
登录plsql工具，所使用用户为源数据库有导出权限（exp_full_database,dba等）的用户。
1.导出建表语句（包括存储结构）
导出步骤tools ->export user object，选择要导出的对象.
导出.sql格式文件并等待导出完成：
2.导出数据文件 ;
1）导出为dmp格式
导出步骤tools ->export tables，选择要导出的表及导出的格式进行导出。
2）导出为sql格式
3）导出为pde格式
提示说明：采用第三方工具导出导入整个数据库的话，耗时较长，一定要有足够的时间来操作(数据量大的话需要好几个小时)
3.导入建表语句
导入步骤tools->import tables->SQL Inserts 导入.sql文件
4.导入数据；
tools->import talbes，然后再根据导出的数据格式选择导入dmp文件，或者sql文件，或者pde文件。
【注】导入之前最好把以前的表删除，当然导入另外数据库除外。
另外导入时当发现进度条一直卡在一个点，而且导出的文件不再增大时，甚至是提示程序未响应，千万不要以为程序卡死了，这个导入导出就是比较缓慢，只要没有提示报错，或者导入完成就不要停止程序。
SELECT table_name FROM user_tables;
select user,table_name from all_tables;
select table_name,column_name from user_tab_columns;
select user,table_name,column_name from all_tab_columns;
select * from user_sequences;
SELECT * FROM user_objects;
select table_name,comments from user_tab_comments;
select table_name,column_name,comments from user_col_comments;
COMMENT ON TABLE aa10 IS '系统参数表';
COMMENT ON COLUMN aa10.aaa100 IS '参数类别';
DESC aa10;
SELECT table_name,COLUMN_ID,column_name,data_type,data_length,DATA_PRECISION,NULLABLE
FROM user_tab_columns
ORDER BY table_name,COLUMN_ID;
selecttablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name
selecttablespace_name,sum(bytes)/1024/1024 from dba_free_space group bytablespace_name;
SELECT * FROM user_segments;
CREATE TABLE table_count(table_name VARCHAR2(50),columns NUMBER(20));
select
'insert into table_count values('''||table_name||''',
(select count(1)from '||table_name||')
);//' ||comments from user_tab_comments;
SELECT * FROM table_count;
1 、建表
Create table 表名（
字段1 数据类型 [default 默认值],
字段2 数据类型 [default 默认值],
...
字段n 数据类型 [default 默认值]
）;
【例】
创建person表
create table person(
pid number(10),
name varchar2(10),
gender number(1) default 1,
birthday date
);
insert into person(pid, name, gender, birthday)
values(1, '张三', 1, to_date('1999-12-22', 'yyyy-MM-dd'));
2、删除表
DROP TABLE 表名;
3、修改表
添加语法：
ALTER TABLE 表名称 ADD(列名1 类型 [DEFAULT 默认值]，列名1 类型 [DEFAULT 默认值]…)
修改语法：
ALTER TABLE 表名称 MODIFY(列名1 类型 [DEFAULT 默认值]，列名1 类型 [DEFAULT 默认值]…)
修改列名:
ALTER TABLE 表名称 RENAME COLUMN 列名1 TO 列名2
添加一个字段
alter table student add (classid number(2));
修改一个字段的长度
alter table student modify (xm varchar2(30));
修改字段的类型或是名字（不能有数据） 不建议做
alter table student modify (xm char(30));
删除一个字段 不建议做(删了之后，顺序就变了。加就没问题，应该是加在后面)
alter table student drop column sal;
修改表的名字 很少有这种需求
rename student to stu;
【例】
在person表中增加列address
alter table person add(address varchar2(10));
把person表的address列的长度修改成20长度
alter table person modify(address varchar2(20));
4、 截断表
在person表中使用delete语句删除数据，则可以使用rollback来回滚，如果想要清空一张表的数据，同时不想回滚可以立即释放资源需要使用截断表的语法
TRUNCATE TABLE 表名
【例】
截断person表
truncate table person;
5、表数据更新
1 INSERT
INSERT INTO表名[(列名1，列名2，…)]VALUES(值1，值2，…)
insert into person(pid,name,gender,birthday,addrress)
values(1,'张三',1,'9-5月-1981','北京');
想要将从其它表中选择数据并将其插入另一个表中。要做到这一点，可使用Oracle INSERT INTO SELECT语句，如下所示：
INSERT INTO target_table (col1, col2, col3)
SELECT col1,
col2,
col3
FROM source_table
WHERE condition;
Oracle INSERT INTO SELECT语句要求源表和目标表匹配，有时我们可能希望将多行插入到一个或多个表中。
在这种情况下，就可以使用Oracle INSERT ALL语句，该语句也被称为多项式插入语句。
Oracle提供了两种类型的多项式插入语句：无条件的和有条件的。
无条件的Oracle INSERT ALL语句
将多行插入到表中，要将多行插入到表中，请使用以下Oracle INSERT ALL语句：
INSERT ALL
INTO table_name(col1,col2,col3) VALUES(val1,val2, val3)
INTO table_name(col1,col2,col3) VALUES(val4,val5, val6)
INTO table_name(col1,col2,col3) VALUES(val7,val8, val9)
Subquery;
在这个语句中，每个值表达式值：val1，val2或val3必须引用由子查询的选择列表返回的列对应的值。
如果要使用文字值而不是子查询返回的值，请使用以下子查询：
SELECT * FROM dual;
条件多项插入语句允许根据指定的条件将行插入到表中。
以下显示了条件多项插入语句的语法：
INSERT [ ALL | FIRST ]
WHEN condition1 THEN
INTO table_1 (column_list ) VALUES (value_list)
WHEN condition2 THEN
INTO table_2(column_list ) VALUES (value_list)
ELSE
INTO table_3(column_list ) VALUES (value_list)
Subquery
如果指定了ALL关键字，则Oracle将在WHEN子句中评估每个条件。如果条件评估/计算为true，则Oracle执行相应的INTO子句。
但是，当指定FIRST关键字时，对于由子查询返回的每一行，Oracle都会从WHEN子句的上下方向评估每个条件。
如果Oracle发现条件的计算结果为true，则执行相应的INTO子句并跳过给定行的后续WHEN子句。
请注意，单条件多项式插入语句最多可以有127个WHEN子句。
Oracle多表插入语句受以下主要限制：
2 事务
因为oracle的事务对数据库的变更的处理，我们必须做提交事务才能让数据真正的插入到数据库中，在同样在执行完数据库变更的操作后还可以把事务进行回滚，这样就不会插入到数据库。
如果事务提交后则不可以再回滚。
提交：commit
回滚：rollback
savepoint 保存点名字
示例：
savepoint a1; --设置一个保存点 a1;
update person set name='张三' where pid='101' --修改一条数据
savepoint a2; --设置一个保存点 a1;
update tmp set name='李四' where id='102 --再修改一条数据
rollback to a2; --回滚到a2 保存点。 此时在 a2 保存点之后的所有数据修改视为无效。
rollback to a1; --这里可以再从a2保存点再回滚到a1 保存点。 此时在 a1 保存点之后的所有数据修改视为无效。
rollback; --回滚全部。即撤消至上一次提交事务后的所有数据修改。
commit; --提交事务 将回滚后的事务提交，并会删除所有保存点。
3 UPDATE
全部修改：
UPDATE 表名 SET 列名1=值1，列名2=值2，…
局部修改：
UPDATE 表名 SET 列名1=值1，列名2=值2，…WHERE 修改条件
4 DELETE
DELETE FROM 表名 WHERE 删除条件;
在删除语句中如果不指定删除条件的话就会删除所有的数据
现实应用中，经常从与其他表中的行具有外键关系的表中来删除行记录。
例如，想要从orders表中删除id为1的销售订单，并从order_items表中删除与订单id是1关联的所有行项目。
通常可以发出两个DELETE语句，如下所示：
-- 第一条
DELETE FROM
orders
WHERE
order_id = 1;
-- 第二条
DELETE FROM
order_items
WHERE
order_id = 1;
COMMIT WORK;
COMMIT WORK语句确保两个DELETE语句以全部或全部方式执行，以防第一条语句成功，但第二个DELETE语句失败时在order_items表中的孤行。
但是，如果知道如何正确设置表的约束，那么这是不必要的。
在这种情况下，当创建order_items表时，可以使用DELETE CASCADE选项定义一个外键约束
CREATE TABLE order_items
(
order_id NUMBER( 12, 0 ) ,
-- other columns
-- ...
CONSTRAINT fk_order_items_orders
FOREIGN KEY( order_id )
REFERENCES orders( order_id )
ON DELETE CASCADE
);
通过这样做，每当从orders表中删除一行时
DELETE
FROM
orders
WHERE
order_id = 1;
order_items表中order_id为1的所有行也被数据库系统自动删除。
Truncate table实现数据删除
比较truncat与delete实现数据删除？
delete删除的数据可以rollback,也可以闪回
delete删除可能产生碎片，并且不释放空间
truncate是先摧毁表结构，再重构表结构
5 Merge语句
Oracle MERGE语句从一个或多个源表中选择数据并更新或将其插入到目标表中。
MERGE语句可指定一个条件来确定是更新数据还是将数据插入到目标表中。
MERGE INTO target_table
USING source_table
ON search_condition
WHEN MATCHED THEN
UPDATE SET col1 = value1, col2 = value2,...
WHERE <update_condition>
[DELETE WHERE <delete_condition>]
WHEN NOT MATCHED THEN
INSERT (col1,col2,...)
values(value1,value2,...)
WHERE <insert_condition>;
首先，指定要在INTO子句中更新或插入的目标表(target_table)。
其次，指定要更新或插入USING子句中的数据源(source_table)。
第三，指定合并操作在ON子句中更新或插入的搜索条件。
对于目标表中的每一行，Oracle都会评估搜索条件：
如果结果为true，则Oracle使用源表(source_table)中的相应数据更新该行。
如果任何行的结果为false，则Oracle将源表(source_table)中相应的行插入到目标表(target_table)中。
当想要在单个操作中组合多个INSERT，UPDATE和DELETE语句时，MERGE语句变得很方便。
因为MERGE是确定性语句，所以不能在同一个MERGE语句中多次更新目标表的同一行。
可以将一个可选的DELETE WHERE子句添加到MATCHED子句中，以在合并操作之后进行清理。 DELETE子句只删除目标表中与ON和DELETE WHERE子句匹配的行。
要执行MERGE语句，必须在源表上具有INSERT和UPDATE对象权限。 如果使用DELETE子句，则还必须在目标表上具有DELETE对象特权。
【扩展】
DDL : 数据定义语言 create alter drop truncate
DML : 数据操纵语言 insert update delete
DCL : 数据控制语言 安全 授权 grant revoke
DQL : 数据查询语言 select from子句 where子句
欢迎点赞、关注和评论、一键三连哦！
如果你也是技术宅，如果你也对技术渴望提升，那么我们是同一类人，欢迎大家关注我的个人公众号，我们一起进步吧！
如果对你有帮助，赞赏也是对我最大的支持。
