Oracle总结

一、常用

1、常用sql

1、修改密码

alter user test identified by 123456;

Windows用cmd处理下密码过期：
sqlplus 用户名/密码@localhost/orcl;
alter user 用户名 identified by 密码;

特殊字符修改密码：alter user committee identified by "Sinosoft@876"

2、用户解锁

alter user test account unlock;

3、分页查询

单表：

SELECT * FROM (SELECT t.*,ROWNUM rn FROM TABLE t WHERE ROWNUM <= pageNumber*pageSize) WHERE rn >(pageNumber)*pageSize;

两张表：

SELECT * FROM (

SELECT ROWNUM RN,XX.* FROM (

SELECT 表名.字段名, 表名.字段名, 表名.字段名... FROM TABLE1 t1, TABLE2 t2 WHERE t1.字段=t2.字段

) XX WHERE ROWNUM<=pageSize*pageNumber

) WHERE RN >(pageNumber-1)*pageSize ;

4、查询用户的用户名、默认表空间、临时表空间

select username,default_tablespace,temporary_tablespace from dba_users where username='YNGW';

查询用户的表空间和所有表名：

select owner,table_name,tablespace_name from dba_tables where owner='YNGW';

5、Linux下Oracle的启动和关闭

1、登录服务器，切换到oracle用户，或者以oracle用户登录
su - oracle
2、打开监听服务（可以通过lsnrctl status命令查看Oracle监听器运行状况）
lsnrctl status
lsnrctl start
3.以SYS用户身份登录Oracle
sqlplus /nolog
conn /as sysdba
4.通过startup命令启动实例
startup
关闭数据库实例 shutdown
关闭监听器 lsnrctl stop
（4）启动数据库，命令：startup

（5）如果是关闭数据库，命令：shutdown immediate

（6）退出sqlplus控制台，命令：exit

（7）进入监听器控制台，命令：lsnrctl

（8）启动监听器，命令：start

（9）退出监听器控制台，命令：exit

6、创建dblink

create database link dblinkwd connect to CEN_INTERFACE identified by "123456"
using ' (DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) )
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)';
select * from 表名@dblinkwspt

7、建用户、导数据（数据库迁移）

建表空间：

create tablespace NBZCS datafile 'E:\ORADATA\NBZCS.DBF' size 1500M;

建用户：

create user NBZCS identified by "123" default tablespace NBZCS account unlock;

修改表空间：

ALTER TABLESPACE NBZCS ADD DATAFILE 'E:\ORADATA\NBZCS2.DBF' SIZE 800M autoextend on next 10m maxsize unlimited;

--- 查询oracle数据库表空间文件位置
select username,default_tablespace from dba_users where username='用户名';
Select * FROM DBA_DATA_FILES;

--- 给表空间扩容

alter database datafile 'E:\ORADATA\YNGW01.DBF' resize 50000m;

授权：

GRANT DBA,CONNECT,RESOURCE,IMP_FULL_DATABASE,EXP_FULL_DATABASE,SELECT ANY DICTIONARY TO NBZCS ;

创建目录：

将目录授权给用户：

grant read,write on directory DATA_BAK_DIR to NBZCS;

导出命令：

expdp NBZCS/123@orcl directory=DATA_BAK_DIR dumpfile=NBZCS.dmp logfile=NBZCS.log

导入命令：

impdp NBZCS/123@orcl directory=DATA_BAK_DIR dumpfile=NBZCS_20210623.DMP logfile=NBZCS.log schemas=NBZCS

----忽略表空间导入命令
impdp COMMITTEE/123456@orcl directory=DATA_BAK_DIR dumpfile=COMMITTEE_20210619.dmp TRANSFORM=segment_attributes:n logfile=committee.log schemas=committee

----忽略表空间，用户名更改，导入命令：
impdp ele/ele@orcl directory=DATA_BAK_DIR dumpfile=ELE.DMP REMAP_SCHEMA=olduser:newuser TRANSFORM=segment_attributes:n logfile=ele.log

普通导入导出（rows=n代表不要数据，只要结构）：

imp mtg_zb/embed@localhost/orcl file=C:\Users\Lenovo\Desktop\sing.dmp full=y ignore=y

exp yngw/yngw@localhost:1521/orcl file=D:\yngw.dmp rows=y owner=yngw

exp yngw/yngw@localhost:1521/orcl file=D:\yngw.dmp tables=(T_SYS_WD_YN_ORG)

exp jkfp_yngw/yngw@localhost:1521/gwqjdb file=D:\yngw.dmp tables=(PUB_BUSIRULEINFO,PUB_QUERYINPARAM)

Linux：

exp sing/embed@localhost:1521/orcl file=/home/oracle/222.dmp rows=y tables='('INDEX_STATISTICS_SOURCE')'

exp yngw/yngw@localhost/orcl tables='('EHR_HIGHRISK_ATTR,EHR_DISEASE_HISTORY,EHR_HISTORY_SHOUSHU,EHR_HISTORY_SHUXUE,EHR_HISTORY_WAISHANG,EHR_FEESOURCE_ATTR')' query=\"where flag=\'dls\'\" file=/bak/bkup/dls20190611.dmp log=/bak/bkup/dls611.log

注意：如果不使用 ignore=y 参数在进行imp 导入时，就会对已经存在的表就不会进行导入，

如果加上ignore=y 参数就会对已经存在的表中没有的记录进行更新，但对已经存在记录不会进行覆盖修改。

8、删除某个用户、表空间下面的

drop user xxx必须是这个用户下面没有任何对象，这样才可以使用这个命令，否则就会报错；

如果用户下面有对象，就得用drop user xxx cascade来删除这个用户以及这个用户下的所有对象了。

select owner, segment_name from dba_segments where tablespace_name = 'EMR_EHR'

drop user EMR_EHR cascade;

既删除数据文件表空间等表或视图的记录又删除了物理文件：

drop tablespace EMR_EHR including contents and datafiles;

9、根据逗号查询多行：

select p.*,sys_guid() uuiddd from (
select distinct * from (
select regexp_substr(q.nums, '[^,]+', 1, Level,'i') order_num, names
from (
select '1,2,3' nums, '张三' names from dual
union all
select '4,5' nums, '李四' names from dual
union all
select '5,6' nums, '王五' names from dual
) q
connect by Level <= LENGTH(q.nums) - LENGTH(REGEXP_REPLACE(q.nums, ',', '')) + 1) order by order_num
) p

10、用存储过程造大量数据

create or replace procedure insert_sjbj(num1 in number) is
i number;
begin
i:=0;
loop
i:=i+1;
if i>num1 then
exit;
end if;
insert into students (id,name,sex,age,class,Addr) VALUES (sys_guid(),CONCAT('test',i),'女','28','三年二班','北京市海淀区');

end loop;
end insert_sjbj;

调用存储过程：

MySQL：call insert_sjbj();

Oracle：

declare
num1 integer;
begin
--调用存储过程---
num1:=100;
insert_sjbj(num1);
dbms_output.put_line('输出结果：'|| num1 );
end;

删除存储过程：

drop procedure if exists insert_sjbj;

11、查询当前用户所有表

SELECT * FROM USER_TABLES where num_rows!=0 order by table_name

1）、查看所有表空间及表空间大小：

select tablespace_name ,sum(bytes) / 1024 / 1024 as MB　from dba_data_files group by tablespace_name;
2）、查看所有表空间对应的数据文件：

select tablespace_name,file_name from dba_data_files;
3）、修改数据文件大小：

alter database datafile 'H:\ORACLE\PRODUCT\10.1.0\ORADATA\ORACLE\USERS01.DBF' RESIZE 10240M;

4）、查询表名以及表的数据条数
select u.TABLE_NAME,u.NUM_ROWS from user_tables u

12、查询系统时间

--当前时间减去7分钟的时间.
select sysdate,sysdate - interval '7' MINUTE from dual ;
--当前时间减去7小时的时间
select sysdate - interval '7' hour from dual ;
--当前时间减去7天的时间
select sysdate - interval '7' day from dual ;
--当前时间减去7月的时间
select sysdate,sysdate - interval '7' month from dual ;
--当前时间减去7年的时间
select sysdate,sysdate - interval '7' year from dual ;
--时间间隔乘以一个数字
select sysdate,sysdate - 8 *interval '2' hour from dual ;

13、exp导出时候，导出空表

（1）、执行下面sql

select 'alter table '|| a.table_name ||' allocate extent;'
from user_tables a where a.table_name not in (select segment_name from user_segments where segment_type = 'TABLE');

（2）、复制上面查询结果的sql，去执行这些sql

（3）、用exp就能导出空表

2、外键约束

1、查询所有表的外键：
select table_name, constraint_name from user_constraints where constraint_type = 'R';

2、禁用所有外键约束, 使用下面的sql生成对应sql脚本:
---------constraint_type：为 'R'代表外键、为P代表主键、为U代表唯一约束性;
select 'alter table ' || table_name || ' disable constraint ' || constraint_name || ';'
from user_constraints
where constraint_type = 'R';

3、启用所有外键约束, 使用下面的sql生成对应sql脚本:
---------constraint_type：为 'R'代表外键、为P代表主键、为U代表唯一约束性;
select 'alter table ' || table_name || ' enable constraint ' || constraint_name || ';'
from user_constraints
where constraint_type = 'R';
4、删除所有外键约束, 使用下面的sql生成对应sql脚本:
---------constraint_type：为 'R'代表外键、为P代表主键、为U代表唯一约束性;
select 'alter table ' || table_name || ' drop constraint ' || constraint_name || ';'
from user_constraints
where constraint_type = 'R';

3、触发器启用、禁用

1、禁用 table_name 表的所有 trigger
alter table table_name disable all triggers;
2、启用 table_name 表的所有 trigger
alter table table_name enable all triggers;
3、禁用指定 trigger
alter trigger trigger_name disable;
4、启用指定 trigger
alter trigger trigger_name enable;

二、Oracle物化视图

先看简单创建语句：
create materialized view mv_materialized_test refresh force on demand start with sysdate next
to_date(concat(to_char( sysdate+1,'dd-mm-yyyy'),'10:25:00'),'dd-mm-yyyy hh24:mi:ss') as

select * from user_info; --这个物化视图在每天10：25进行刷新

物化视图也是种视图。Oracle的物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。
物化视图可以查询表，视图和其它的物化视图。

特点：

(1) 物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表)，这通过其可以被user_tables查询出来，而得到确认；
(2) 物化视图也是一种段(segment)，所以其有自己的物理存储属性；
(3) 物化视图会占用数据库磁盘空间，这点从user_segment的查询结果，可以得到佐证；
创建语句：create materialized view mv_name as select * from table_name
因为物化视图由于是物理真实存在的，故可以创建索引。

创建时生成数据：

分为两种： build immediate 和 build deferred，
build immediate是在创建物化视图的时候就生成数据。
build deferred则在创建时不生成数据，以后根据需要在生成数据。
如果不指定，则默认为 build immediate。

刷新模式：

物化视图有二种刷新模式：
在创建时refresh mode是 on demand 还是 on commit。
on demand 顾名思义，仅在该物化视图“需要”被刷新了，才进行刷新(REFRESH)，即更新物化视图，以保证和基表数据的一致性;
on commit 提交触发，一旦基表有了commit，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。一般用这种方法在操作基表时速度会比较慢。
创建物化视图时未作指定，则Oracle按 on demand 模式来创建。

上面说的是刷新的模式，针对于如何刷新，则有三种刷新方法：

完全刷新（COMPLETE）：会删除表中所有的记录（如果是单表刷新，可能会采用TRUNCATE的方式），然后根据物化视图中查询语句的定义重新生成物化视图。
快速刷新（FAST）：采用增量刷新的机制，只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。FAST必须创建基于主表的视图日志。对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。
FORCE方式：这是默认的数据刷新方式。Oracle会自动判断是否满足快速刷新的条件，如果满足则进行快速刷新，否则进行完全刷新。

关于快速刷新：Oracle物化视图的快速刷新机制是通过物化视图日志完成的。Oracle通过一个物化视图日志还可以支持多个物化视图的快速刷新。物化视图日志根据不同物化视图的快速刷新的需要，可以建立为ROWID或PRIMARY KEY类型的。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。

查询重写（QueryRewrite）：

包括 enable query rewrite 和 disable query rewrite 两种。
分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时，oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。
默认为 disable query rewrite。

语法：

create  materialized  view   view_name
refresh  [ fast|complete|force ]
[
on [commit|demand ]   |
start  with  (start_time)  next  (next_time)
]
AS subquery;

具体操作

创建物化视图需要的权限：
grant create materialized view to user_name;

在源表建立物化视图日志：
create materialized view log on test_table
tablespace test_space -- 日志空间
with primary key;     -- 指定为主键类型

在目标数据库上创建MATERIALIZED VIEW：
create materialized view mv_materialized_test refresh force on demand start with sysdate next
to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),'10:25:00'),'dd-mm-yyyy hh24:mi:ss') asselect * from user_info; --这个物化视图在每天10：25进行刷新

修改刷新时间：
alter materialized view mv_materialized_test refresh force on demand start with sysdate next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 23:00:00'),'dd-mm-yyyy hh24:mi:ss');
或
alter materialized view mv_materialized_test refresh force on demand start with sysdate next trunc(sysdate,'dd')+1+1/24; -- 每天1点刷新
建立索引：
create index IDX_MMT_IU_TEST
on mv_materialized_test(ID,UNAME)
tablespace test_space;
删除物化视图及日志：

drop materialized view log on test_table;    --删除物化视图日志：
drop materialized view mv_materialized_test; --删除物化视图

posted @ 2021-08-23 17:48 宇宙小咖阅读(95) 评论(0) 收藏举报

刷新页面返回顶部