Oracle 相关操作

***************************************ORA-27102: out of memory********************************************************
启用实例失败后
创建ora文件，修改内存位1G  1000*1024*1024  
create pfile from spfile;  
然后运行即可
startup pfile='D:\app\Administrator\product\11.2.0\dbhome_1\database\INITyanshi.ORA'
***************************************ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务********************************************************
检查OracleOraDb11g_home1TNSListener未开启导致  ORA-12541: TNS: 无监听程序   
检查实例服务OracleServiceORCL 

都没问题的话cmd 执行 sqlplus sys/sys as sysdba 是否能连接成功
***************************************解决连接oracle提示协议适配器错误********************************************************
数据库实例多的情况下，执行以下语句后
set ORACLE_SID=当前数据库的实例名称

***************************************解决oracle已连接到空闲例程，装载数据库并打开********************************************************
shutdown immediate; 这是关闭数据库，同时也就关闭了实例
shutdown abort;  这是关闭实例
找到oracle安装路径\app\admin\orcl\pfile下的文件
startup pfile='F:\app\admin\orcl\pfile\init.ora.311201915452'
使用pfile启动实例，也就启动了数据库

***************************************解决plsql工具乱码********************************************************
新建系统变量，设置变量名：NLS_LANG，变量值：SIMPLIFIED CHINESE_CHINA.ZHS16GBK

***************************************删除创建实例,配置监听服务********************************************************
database configuration assistant 对数据实例进行管理
database upgrade assistant    对数据实例进行升级

net configuration assistant 配置监听参数后会自动启动，在服务中能看到  
Net Manager配置监听的参数文件，需要手工重启监听服务，在服务中看不到

********************mstsc远程连接解决出现远程连接的时候出现身份验证错误要求的函数不受支持***********************
第一步：win+r 输入gpedit.msc 进入组策略。
第二步：在计算机配置下找到管理模板。
第三步：点开管理模板，找到系统。
第四步：再点开系统，找到凭据分配。
第五步：然后在右侧列表中找到加密Oracle修正，双击打开，选择已启用，保护级别选择易受攻击。
***************************************oracle 复制表结构********************************************************
create table test4 as select * from test2 where 1=2   //带where条件  只是复制表结构  不带where条件  复制表结构+数据

***************************************oracle 语法********************************************************
in() 函数: 范围内的选择，括号里只能查询单列值 不能用 * 查多列 
=        : 只能是一个值

*************************************oracle解决用户密码过期*****************************************************
// 查看用户密码的有效期设置
select * from dba_profiles s where s.profile ='DEFAULT' and s.resource_name ='PASSWORD_LIFE_TIME';  
 //将密码有效期由默认的180天修改成“无限制”
alter profile DEFAULT limit PASSWORD_LIFE_TIME UNLIMITED;  

*************************************oracle根据时间恢复删除表内的数据**********************************
第一种：
　(1)、查询当前系统时间
　　　　select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
　(2)、查询删除数据的时间点的数据
　　　　select * from 表名 as of timestamp to_timestamp('2013-05-29 15:29:00','yyyy-mm-dd hh24:mi:ss'); ?(如果不是，则继续缩小范围)
　(3)、恢复删除且已提交的数据
    先创建行移动功能
    alter table 表名 enable row movement;
　　　　flashback table 表名 to timestamp to_timestamp('2013-05-29 15:29:00','yyyy-mm-dd hh24:mi:ss');
第二种：
适合恢复当天的数据：

(1)、获得当前数据库的scn号
　　　　select current_scn from v$database; (切换到sys用户或system用户查询)?
　　　　查询到的scn号为：1499223
(2)、查询当前scn号之前的scn
　　　　select * from 表名 as of scn?1499220; (确定删除的数据是否存在，如果存在，则恢复数据；如果不是，则继续缩小scn号)
(3)、恢复删除且已提交的数据
    先创建行移动功能
    alter table 表名 enable row movement;
　　　　flashback table 表名 to scn?1499220;
第三种：
delete误删表，根据时间点查询出xlsb_chzh 这个表删除之前的数据，然后根据自己的方式进行导出还原
select * from xlsb_chzh as of timestamp to_timestamp('2018-10-31','yyyy-mm-dd')

第四种：
恢复表：

-- 删除t1表后，该表的block还在表空间中，查询回收站可以看到被删除的对象
select * from recyclebin;
-- 闪回表到删除之前
flashback table t1 to before drop;

/* 闪回数据库 */
-- 需要有sysdba权限,才能使用flashback database命令，必须以独占模式装载数据库，但不打开数据库。
-- 同时将数据库置于闪回模式，执行闪回。
startup mount exclusive;
alter database archivelog;
alter database flashback on;
alter database open;
-- 查看闪回模式是否开启
select current_scn, flashback_on from v$database;
shutdown;
startup mount exclusive;
-- 闪回数据库到1小时之前
flashback database to timestamp sysdate-1/24;
-- 闪回成功后，打开数据库，同时resetlogs开启对数据库的写访问权限
alter database open resetlogs;
startup mount exclusive;
alter database flashback off;
alter database open;

***********************************oracle实例+表空间和用户*******************************************   
oracle添加实例   打开Database Configuration Assistant  一步一步安装即可

--rem PW表空间
CREATE TABLESPACE PW
DATAFILE 
'd:/gwdb/PW.dbf'  size 60M  reuse
 AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED; 

--REM 创建PW用户
-- Create the user 
create user PW
  identified by "密码"
  default tablespace 表空间
  temporary tablespace TEMP  --临时表空间
  profile DEFAULT;
-- Grant/Revoke role privileges 
grant connect to PW;
grant exp_full_database to PW;
grant imp_full_database to PW;
grant resource to PW;
-- Grant/Revoke system privileges 
grant unlimited tablespace to PW;

查询出删除表所有表的语句
select 'drop table '||table_name||';' 
from cat 
where table_type='TABLE'

——————————————————解决导入dmp文件以及修改字符集————————————————————

1)解决oracle11g导出空表问题
用sys或sysdba进入执行脚本
show parameter deferred_segment_creation；返回结果如果是true则执行以下脚本：
alter system set deferred_segment_creation=false;
2) 查看字符集SELECT USERENV('LANGUAGE') NLS_LANG FROM DUAL;
oracle修改字符集utf8
先用sqlplus 系统用户登录：
sqlplus / as sysdba;
然后执行下面的命令即可：
shutdown immediate;     //关闭数据库服务器
startup mount;
alter session set sql_trace=true;
alter system enable restricted session;
alter system set job_queue_processes=0;
alter system set aq_tm_processes=0;
alter database open;
alter database character set internal_use utf8;
alter session set sql_trace=false;
shutdown immediate;
startup;

3)    执行创建表空间和用户脚本tablespace_user.sql
使用dba账户登录数据库
Sqlplus sys/systm@192.168.88.204:1521/SZDT as sysdba
成功连接数据库后，执行 “@D:\ tablespace_user.sql”创建用户
4)    整体导入数据库
修改” 三合一数据导入.bat”中数据库连接和文件路径，在oracle11g客户端执行批处理操作，导入数据库。在plsql用JAVAFRAME登录数据库，打开各用户的存储过程 触发器等是否有效，无效的重新编译。

*******************************************oracle解决用户问题************************************

sqlplus PW/PW@47.92.114.182/test   连接数据库执行sql，语句  
dmp文件直接打开cmd DOS命令执行即可还原 imp PW/PW@TKYPTGL file='e:\接受QQ文件\bak20180620\PW.dmp'  full=y ignore=y
备份： 
账号/密码@ip地址:端口/实例名  file'保存dmp文件的路径'  log'保存log日志的路径' owner= 账号
使用cmd指令解锁oracle用户：

1.sqlplus/nolog                 连接oracle数据库
2.connect sys/sys as sysdba;          以DBA角色进入
3.select username from dba_users;       查看所有用户列表
4.alter user system account unlock;     为system用户解锁
5.commit;                  提交一下

使用cmd指令修改账户：

1.sqlplus/nolog                        连接数据库
2.connect sys/system@DEV as sysdba;              以DEV数据库的DBA角色进入
3.select user#,name from user$;             如：user#等于66
4.update user$ set name='新的用户名称' where user#=66;  系统会提示：1 row updated.说明修改成功，
5.commit；                           （回车）提交所作修改

使用cmd指令修改账户的密码：

1.sqlplus/nolog                  连接数据库
2.connect sys/sys as sysdba;           以SYSDBA角色进入
3.alter user 用户名 identified by 密码;   修改密码
4.commit;                    提交一下

使用cmd指令删除账户：
1.sqlplus/nolog
2.connect sys/sys as sysdba; 
3.drop user trackpad;
4.commit;

使用cmd指令创建账户：
1.sqlplus/nolog
2.connect sys/sys as sysdba; 
3.create user 账户 identified by "密码";
4.commit;
5.grant create session to 用户;  给用户登录权限
6.grant create table to 用户;  给用户创建表权限
6.revoke create table from 用户; 删除用户的创建表权限

注：cmd命令后面的（分号）是必不可少的。
*******************************************************************

如果pl/sql中遇到不能复制粘贴的情况，关闭pl/sql，请在后台程序里找到 rdpclip.exe剪贴板监视程序  关闭
然后window+R 运行rdpclip.exe  再次打开pl/sql即可。

***********************************修改全部表空间名称和用户授权**************************************************

查询所有表空间 select * from dba_tablespaces  
查询数据库下的所有用户名：select username from dba_users
查询当前用户的所有权限：select * from session_privs
查看用户对象权限：select * from dba_tab_privs
查询用户的所有角色：SELECT * FROM USER_ROLE_PRIVS;
查找用户：select  *　from dba_users;
查找工作空间的路径： select * from dba_data_files; 
删除用户： drop user 用户名称 cascade;
删除表空间：drop tablespace 表空间名称 including contents and datafiles cascade constraint;

修改所有表的表空间：alter tablespace  原来的表空间名称 rename to 新的表空间名称; 

给数据库登陆用户授权：grant connect,resource,dba to 用户名
给所有表和试图授权： grant select any table,create view to TKYPTGL

connect 基本授权 增删改查等操作
resource授予开发人员  在方案中建表，序列，试图等 
dba     授权系统管理员，拥有所有的系统权限。

grant all privileges to javagwinterface; 给用户配置系统权限，类似于system权限
*********************user lacks CREATE SESSION privilege; logon denied***************************

给表空间用户赋予权限：grant create session,resource to 用户名

****************************************oracle创建同义词以及赋权限****************************************************

创建： 就是创建目的地用户的表.同义词  和 本地用户的表.同义词    全部同义词的表来自本地用户  但是赋权限的时候要在目的地用户上执行
只是查询出创建语句，还要拷贝执行一次：
select 'create or replace synonym  TKYPTGL.'|| table_name ||' for PW.'|| table_name ||';'from all_tables where owner ='PW'

赋权： 授权所需要的功能 从本地用户.同义词表  给目的地 全部同义词的表来自本地用户  但是赋权限的时候要在本地用户上执行
只是查询出赋权语句，还要拷贝执行一次：
select 'grant select, insert, update, delete on PW.'|| table_name ||' to TKYPTGL;' from all_tables where owner ='PW'

DROP synonym  t_sys_token  删除同义词

授权一个表  把你的表授权给他  就要在你的表下执行命令
grant select on PW.Gpsinfo to TKYPTGL  用到级联授权的时候加上 with grant option
grant select on TKYPTGL.Gpsinfo to GIS
级联：
GIS想要访问PW用户的表  需要授权，但是他们没有关系，要通过TRACKPAD用户当介绍人。  PW—>TRACKPAD—>GIS  
 

============================================语法+用法===========================================================

查找父类窗口iframe的id为iframe38CA5A66-C993-4410-AF95-50489B22939C的当前页面中的方法

 window.parent.document.getElementById('Iframe的ID').contentWindow.Closed();

  上层页面的id
$.currentWindow = function () {
    var iframeId = top.$(".NFine_iframe:visible").attr("id");
    return top.frames[iframeId];
}

//关闭方法
function Closed() {

    $(".operate").animate({ "left": '-100.1%' }, 200);
}

$.currentWindow.Closed();

 ++++++++++++++++++++++连接字符串 ++++++++++++++++++++++

connectionString="server=服务器名称;database=数据库名称;Trusted_Connection=SSPI   为true 或 SSPI  就是windows身份验证登陆    如果为false 就要输入账号密码登陆 ;"
 providerName = "System.Data.SqlClient"   将要链接的数据库是sql server类型数据库   可以省略 
 

<add name="applicationServices"

connectionString="Data Source=.\ppp;Initial Catalog=zjJunHaoCarChain;Trusted_Connection=SSPI;" providerName="System.Data.SqlClient"/>

————————————用sql server2014  还原dmf文件的数据库——————————————————————

sp_attach_db   将数据库附加到服务器。
@dbname        要附加到服务器的数据库的名称。该名称必须是唯一的。dbname   的数据类型为   sysname，默认值为  NULL。 
@filename      包含的物理文件路径和 文件日志路径

EXEC  sp_attach_db  @dbname  =  '你的数据库名',      

@filename1  =  'mdf文件路径（包缀名）',     

@filename2  =  'Ldf文件路径（包缀名）'

局域网内不能连接本地数据库时，1.找到管理配置器 2.打开sql server 网络配置下的协议，全部启用 3，重启本地数据库服务

————————————别人无法连接自己的oracle服务数据库时 或监听程序找不到符合协议堆栈要求的可用处理程序 —————————————

关闭普通防火墙，重启oracle数据库试试
show parameter processes; 查询参数值
 alter system set processes=1000 scope=spfile; 修改参数值大点 
create pfile from spfile;  如果没空间要创建spfile
最后重启自己的oracle服务

————————————oracle导入Excel数据和文本数据 使用cmd命令执行——————————————————————

因为Excel数据表格最大限制是1048576行，所以要文本打开，文本无限制！

sqlldr userid=TKYPTGL/TKYPTGL@TKYPTGL control=e:/ctl.ctl log=e:/sqlldr3.log
         // 连接数据库 登录账号密码     control = 打开执行文件的位置      log = 是打印日志的位置 （可以不写）

这是执行ctl.ctl文件的内容：

load data                            准备读取数据
infile 'C:\Users\Administrator\Desktop\1.csv'        外部数据的文件   可指定多个数据文件 多写几个infile
append into table GIS_TEST                    向表中追加数据
fields terminated by ','                  外部文件的数据以'，'分隔    OPTIONALLY ENCLOSED BY ','  部分字段可以用双引号包起来
TRAILING NULLCOLS                      表中的字段没有对应的值时填充空值       
(                            下面是数据库中的字段名，文本中的数据要对应数据库里的字段，顺序要一致
ID,
LINECODE,
LINENANE,
POINTX,
POINTY,
MILE,
行别
)

————————————windows服务 cmd指令  以管理员身份运行——————————————————————
第一种：
1、安装服务
sc create 服务名 binPath= "C:\Users\Administrator\Desktop\win32srvDemo\win32srvdemo\Debug\win32srvDemo.exe"
2、配置服务
sc config 服务名 start=AUTO
        start=AUTO  (自动)

            start=DEMAND (手动)
    
              start=DISABLED  (禁用)
3、开启服务
net start 服务名
4、关闭服务
net stop 服务名
5、删除服务
sc delete 服务名

第二种：
cd C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319 回车 进入这个文件夹
InstallUtil D:\Soft\WindowsService1\bin\Debug\WindowsService1.exe 即InstallUtil + 服务.exe文件的地址 回车。这时服务就安装完毕了。

InstallUtil D:\Soft\WindowsService1\bin\Debug\WindowsService1.exe -u 即"InstallUtil + 服务.exe文件的地址 - u" 回车。这时服务就卸载完毕了。

————————————windows服务 bat 批处理1  以管理员身份运行——————————————————————

1、创建、配置、开启服务

@echo.服务启动......  
@echo off  
@sc create 服务名 binPath= "C:\Users\Administrator\Desktop\win32srvdemo\win32srvdemo\Debug\win32srvdemo.exe"  
@net start 服务名  
@sc config 服务名 start= AUTO  
@echo off  
@echo.启动完毕！  
@pause

2、关闭服务

@echo.服务关闭  
@echo off  
@net stop 服务名  
@echo off  
@echo.关闭结束！  
@pause

3、删除服务(删除前要关闭服务)

@echo.服务删除  
@echo off  
@sc delete 服务名 
@echo off  
@echo.删除结束！  
@pause
————————————windows服务 bat 批处理2  以管理员身份运行——————————————————————
安装install
%SystemRoot%\Microsoft.NET\Framework\v4.0.30319\installutil.exe "D:\t\WWWWWWWWWWWWW.exe"
Net Start 11
sc config 11 start= auto
pause

卸载install
%SystemRoot%\Microsoft.NET\Framework\v4.0.30319\installutil.exe /u "D:\t\WWWWWWWWWWWWW.exe"
pause

————————————oracle 内存被占  被锁 清空进程——————————————————————
1.下面的语句用来查询哪些对象被锁：

select object_name,machine,s.sid,s.serial# 
from v$locked_object l,dba_objects o ,v$session s
where l.object_id　=　o.object_id and l.session_id=s.sid;

2.下面的语句用来杀死一个进程：
alter system kill session '24,111'; (其中24,111分别是上面查询出的sid,serial#)

【注】以上两步，可以通过Oracle的管理控制台来执行。

3.如果利用上面的命令杀死一个进程后，进程状态被置为"killed"，但是锁定的资源很长时间没有被释放，那么可以在os一级再杀死相应的进程（线程），首先执行下面的语句获得进程（线程）号：
select spid, osuser, s.program 
from v$session s,v$process p
where s.paddr=p.addr and s.sid=24 （24是上面的sid）

4.在OS上杀死这个进程（线程）：
1)在unix上，用root身份执行命令: 
#kill -9 12345（即第3步查询出的spid）
2)在windows（unix也适用）用orakill杀死线程，orakill是oracle提供的一个可执行命令，语法为：
orakill sid thread
其中：
sid：表示要杀死的进程属于的实例名
thread：是要杀掉的线程号，即第3步查询出的spid。
例：c:>orakill orcl 12345

————————————cmd 指令 把sql文件 导入oracle ——————————————————————

不管导入一个还是多个sql数据文件 最后一行加入commit即可。
如果为多个sql文件把所有文件里的数据放在一个sql文件里，系统会自动把数据添加到相应的表里。

sqlplus TKYPTGL/TKYPTGL@47.92.114.182/test
@D:\test.sql >log.txt
————————————bat脚本批量 把sql文件 导入oracle ——————————————————————
如果要导入a.sql  和 b.sql  新建个c.sql 里面的内容为： 
@@D:\a.sql
@@D:\b.sql
在bat里执行c.sql文件就行了。

@echo off
sqlplus TKYPTGL/TKYPTGL@47.92.114.182/test @D:\c.sql > log.txt
exit

************************************oracle存储过程与数据的增删该查*************************************************
存储过程：添加数据
插入数据（有参）存储过程
create or replace procedure emp_insert_para_proc
(myempno NUMBER,
 myename VARCHAR2,
 myjob VARCHAR2,
 mymgr NUMBER,
 mydate DATE,
 mysal NUMBER,
 mycomm NUMBER,
 mydeptno NUMBER)
is
begin
  INSERT INTO emp VALUES(myempno,myename,myjob,mymgr,mydate,mysal,mycomm,mydeptno);
  dbms_output.put_line('插入数据的id：'||myempno);
end emp_insert_para_proc;
 
-- 调用插入数据有参存储过程
begin 
  emp_insert_para_proc(111,'三九','坑爹',7689,SYSDATE,1800,4000,30);                       
end;

插入数据（无参）存储过程
create or replace procedure emp_insert_proc 
is
begin
  INSERT INTO emp VALUES(7779,'Hello Java','SALESMAN',7839,SYSDATE,1800,4000,30); 
  dbms_output.put_line('添加数据的id： '||'7779');
end emp_insert_proc ;
 
--执行存储过程 emp_insert_proc
begin
 emp_insert_proc;
end;

存储过程：查询数据
--创建存储过程：按照id查询单条数据
create or replace procedure emp_selectnamebyid_proc
(myempno in number,myname out varchar2) 
is
begin
  SELECT ename into myname FROM emp WHERE empno=myempno;
  --dbms_output.put_line('被查询的ename='||myname); 
end emp_selectnamebyid_proc;
 
-- 调用 查询存储过程emp_selectnamebyid_proc
declare 
  -- Local variables here
  myname VARCHAR2(50);  
begin
  -- Test statements here
  emp_selectnamebyid_proc(123,myname);
end;
    

-- 创建存储过程：查询所有的结果（多行用游标显示）
create or replace procedure emp_selectempAll_proc   
is
  CURSOR sel_emp IS SELECT * FROM emp;--定义游标,该游标指向查询结果
  rowresult emp%ROWTYPE;
begin
  OPEN sel_emp;--打开游标
  LOOP FETCH sel_emp INTO rowresult;--将游标中的值赋给rowresult
       EXIT WHEN sel_emp%NOTFOUND;--判断：游标不存在时跳出循环
       dbms_output.put_line('员工名ename='||rowresult.ename||', 工资sal='||rowresult.sal); 
  END LOOP;
  CLOSE sel_emp;--关闭游标           
end emp_selectempAll_proc  ;
 
 
-- 调用存储过程：查询所有的结果（多行用游标显示）
begin 
  emp_selectempall_proc;
end;

存储过程：修改数据
--更新数据存储过程
--按照id修改名字
create or replace procedure emp_update_proc
(myempno IN NUMBER,
 myename IN VARCHAR2
 ) 
is
begin
  UPDATE emp SET ename=myename WHERE empno=myempno; 
  dbms_output.put_line('被修改的数据的myempno='||myempno); 
end emp_update_proc;
 
--调用修改数据存储过程
--按照id修改名字存储过程
begin
  emp_update_proc(111,'改后名');
  commit;
end;
    
    
存储过程：删除数据
--创建存储过程：按照id删除记录
create or replace procedure emp_del_proc(myemp NUMBER) 
is
begin
  DELETE FROM emp WHERE empno=myemp;
  dbms_output.put_line('被删除的记录的empno='||myemp); 
end emp_del_proc;
 
-- 调用存储过程
begin
  -- Test statements here
  emp_del_proc(111);
  commit;
end;        
    

添加存储过程：
create or replace procedure pri(a in number default 1,b in number default 1) 
as 
begin 
dbms_output.put_line(‘a:’||a||’—-b:’||b); 
end pri;

查询存储过程：

select * from user_source where name=‘PRI’ 

修改存储过程：
create or replace procedure pri(a in number default 1,b in number default 1) 
as 
begin 
dbms_output.put_line(‘a:’||a||’—-b:’||b); 
end pri;
    
    
删除存储过程：
        
drop procedure PRI;

***************************************************oracle索引*******************************

创建索引:    
CREATE [UNIQUE] | [BITMAP] INDEX index_name  --unique表示唯一索引
ON table_name([column1 [ASC|DESC],column2    --bitmap，创建位图索引
[ASC|DESC],…] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]                                 --指定索引在数据块中空闲空间
[STORAGE (INITIAL n2)]
[NOLOGGING]                                  --表示创建和重建索引时允许对表做DML操作，默认情况下不应该使用
[NOLINE]
[NOSORT];                                    --表示创建索引时不进行排序，默认不适用，如果数据已经是按照该索引顺序排列的可以使用

修改索引:
1）重命名索引：alter index index_sno rename to bitmap_index;
2)合并索引（表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较低）
   alter index index_sno coalesce;
3)重建索引
方式一：删除原来的索引，重新建立索引
方式二：alter index index_sno rebuild;

删除索引:
drop index index_sno;

查看索引:
select index_name,index-type, tablespace_name, uniqueness from all_indexes where table_name ='tablename';
create index index_sno on student('name');
select * from all_indexes where table_name='student';