alter user 用户名 identified by 新密码；

-- 创建名为"TEST_DATA"表空间
CREATE TABLESPACE idms
-- 数据存放的位置
DATAFILE 'D:\oracle\oradata\ORCL\idms.dbf' 
-- 初始空间50M
size 50M
-- 每次扩大50M
AUTOEXTEND ON next 50M
-- 最大可以扩大到 20280M 如果想扩大至无限：unlimited 
maxsize 20480M
extent management local;


create user idms identified by idms default tablespace idms  ;
--创建用户
grant dba to username;
--给用户赋权，初学可以给dba
alter user username account unlock;
--解锁
grant select any table to username;
--给用户赋予查询所有表的权限（一般不用这一步如果遇到查询权限不足时使用）

grant connect,resource to idms;  










-- 登录 21的服务器 
su -  oracle 
sqlplus /nolog
conn /as sysdba
grant  dba to upgrade
--  将游标 设置为3000
alter system set open_cursors=3000 scope=both;
-- 查看游标数
show parameter open_cursors;



ALTER TABLE idms_ebloan_pre_apply RENAME TO idms_ebloan_pre_apply_bak;
ALTER TABLE idms_ebloan_pre_draft RENAME TO idms_ebloan_pre_draft_bak;



-- 查询数据库 当前进程的连接数
select count(*) from v$process;
-- -- 查询数据库 当前回话的连接数
select count(*) from v$session;
-- -- 查询数据库 并发的连接数
select count(*) from v$session  where  status='ACTIVE';
-- -- 查询数据库 允许的最大的连接数
SELECT VALUE from  v$parameter where name='processes'

--   构造 树形结构 层级

select t.*,LEVEL from SYS_CITY t

start with t.id=1
connect by prior t.id = t.pid
1qaz@4321

导入导出 数据泵方式

##  导出表   表的位置： /home/oracle/app/dptemp


expdp upgrade/upgrade directory=dptemp  dumpfile=biao20211214.dmp  NOLOGFILE=Y tables=sys_test,sys_test1,sys_balance
###  导入表
impdp upgrade/upgrade DIRECTORY=dptemp DUMPFILE=biao20211214.dmp LOGFILE=biao.dmp.log  REMAP_SCHEMA=upgrade:upgrade TABLE_EXISTS_ACTION=truncate
####
####ORA-01000: maximum open cursors exceeded
#### ORA-01000: 超出打开游标的最大数
解决方案
sqlplus /nolog
conn /as sysdba
grant  dba to upgrade
alter system set open_cursors=10000 scope=both;
show parameter open_cursors;


alter system set open_cursors=3000 scope=both;

oracle 处理锁表问题

##  查询锁表
   SELECT ao.object_name, s.sid, s.serial#, s.username, s.osuser, s.machine
     FROM all_objects ao, v$session s, v$locked_object lo
    WHERE ao.object_id = lo.object_id
      AND lo.session_id = s.sid;

-- 杀掉锁表进程(SID和serial，对应上面语句中的查询列的值)
alter system kill session '132,192';

物化视图01

create table t_user(
 id number primary key,
 ename varchar2(100),
 deptno number
);
create table t_dept(
 id number primary key,
 dname varchar2(100)
 
);


insert into t_dept(id,dname) values(1,'研发部');
insert into t_dept(id,dname) values(2,'销售部');
insert into t_dept(id,dname) values(3,'财务部');


insert into t_user(id,ename,deptno) values(1,'zs',1);
insert into t_user(id,ename,deptno) values(2,'zs01',2);
insert into t_user(id,ename,deptno) values(3,'zs02',2);
insert into t_user(id,ename,deptno) values(4,'zs03',2);
insert into t_user(id,ename,deptno) values(5,'zs04',1);
commit;
-- 删除物化视图
drop MATERIALIZED view view_user;
-- 创建物化视图  并立即初始化数据    事务提交时刷新
CREATE MATERIALIZED VIEW view_user
BUILD IMMEDIATE
refresh FORCE
ON COMMIT
as 
select u.*,t.dname from t_user u, t_dept t

where u.deptno=t.id(+)
;

物化视图02

--  统计 部门的人数

insert into t_user(id,ename,deptno) values(6,'zs05',3);
delete from t_user where id=6;
commit;
--  快速全量刷新     必须创建 物化视图日志  
create MATERIALIZED view log on t_user;
drop MATERIALIZED view log on t_user;
-- create MATERIALIZED view log on sit.t_user;
DROP  MATERIALIZED VIEW  vw_user_co;
CREATE MATERIALIZED VIEW vw_user_co
BUILD IMMEDIATE
refresh COMPLETE   -- FORCE COMPLETE  
ON COMMIT
as 
select t.deptno,count(1) dept_counts from  t_user t group by t.deptno;

select  * from vw_user_co;

达梦数据库 物化视图 oracle11g 立即刷新 不能使用union

-- 物化视图02ALTER
-- 保理交易
create table tx_baoli(
id NUMBER PRIMARY key,
contract_code VARCHAR2(100),
amount decimal(22,4)
);
-- 自营贷款交易
create table tx_zy(
id NUMBER PRIMARY key,
contract_code VARCHAR2(100),
amount decimal(22,4)
);

insert into tx_baoli(id,contract_code,amount) VALUES(1,'bl_001',1000.00);
insert into tx_baoli(id,contract_code,amount) VALUES(2,'bl_002',2000.00);
insert into tx_baoli(id,contract_code,amount) VALUES(3,'bl_003',3000.00);

insert into tx_zy(id,contract_code,amount) VALUES(1,'zy_001',3000.00);
insert into tx_zy(id,contract_code,amount) VALUES(2,'zy_002',3000.00);
insert into tx_zy(id,contract_code,amount) VALUES(3,'zy_003',3000.00);
insert into tx_zy(id,contract_code,amount) VALUES(4,'zy_004',4000.00);
COMMIT;

create MATERIALIZED view log on tx_baoli;
create MATERIALIZED view log on tx_zy;
-- 删除物化视图   ·
DROP  MATERIALIZED VIEW  vw_tx
--  创建物化视图 全量刷新
CREATE MATERIALIZED VIEW vw_tx
BUILD IMMEDIATE
refresh COMPLETE   -- FORCE COMPLETE  
ON COMMIT
as 
select contract_code,amount from tx_baoli
union 
select contract_code,amount from tx_zy;


select  * from vw_tx;

分页

     SELECT *
     FROM (
         SELECT e.*, ROWNUM rn
         FROM employees e
         WHERE ROWNUM <= 10
     )
     WHERE rn >= 1;
--------------------------
     SELECT *
     FROM (
         SELECT e.*, ROWNUM rn
         FROM employees e
         WHERE ROWNUM <= page_number * page_size
     )
     WHERE rn > (page_number - 1)*page_size;