Oracle 应用总结

oracle应用总结

1 层次树型查询

对具有层次树型结构的数据按照层次关系输出显示：

select

from table_name

where condition

start with column='value'

connect by prior 父主键=子外键

1.start with 子句标识一行作为层次树型查询中的根行，该行必须满足由此子句指定的条件。如果省略此子句，Oracle将表中所有行作为根行。start with条件可以包括子查询。

2. connect by 子句指定在层次树型查询中父行和子行之间的关系，此子句包含了定义父子关系的条件，条件的一部分必须用PRIOR操作符来表示父行。PRIOR操作符可出现在“=”的左边，也可出现在其右边。connect by子句可以包括条件来过滤出的行，但不能使用子查询。

3.可使用where子句剪除满足条件的节点，但该节点的子节点仍然出现在结果树种。在connect by子句中使用条件，可剪除满足条件的节点分支。

selectLPAD('',2*(LEVEL-1))||ename org_chart,empno,mgr,job

from EMP

start withjob='PRESIDENT'

connect by priorempno=mgr;

使用where条件子句去掉一个节点，但其子节点仍在查询结果中

selectLPAD('',2*(LEVEL-1))||ename org_chart,empno,mgr,job

from EMP

where ename !=''FORD'

start with mgr is null

connect by priorempno=mgr;

使用connect by 子句条件去掉一个分支

selectLPAD('',2*(LEVEL-1))||ename org_chart,empno,mgr,job

from EMP

start with mgr is null

connect by priorempno=mgr and ename!='FORD'

测试脚本如下：

CREATE TABLE T_TREE_BAK (ID NUMBER PRIMARYKEY, FATHER_ID NUMBER, NAME VARCHAR2(30));
 SELECT * FROM T_TREE_BAK FOR UPDATE;
 INSERT INTO T_TREE_BAK VALUES (1, 0,'A');
 INSERT INTO T_TREE_BAK VALUES (2, 1,'B');
 INSERT INTO T_TREE_BAK VALUES (3, 1,'C');
 INSERT INTO T_TREE_BAK VALUES (4, 2,'D');
 INSERT INTO T_TREE_BAK VALUES (6, 2,'E');
 INSERT INTO T_TREE_BAK VALUES (5, 3,'F');
 INSERT INTO T_TREE_BAK VALUES (7, 3,'G');
 INSERT INTO T_TREE_BAK VALUES (8, 4,'H');
 INSERT INTO T_TREE_BAK VALUES (9, 4,'I');

 

查询实例：

SELECT ID, FATHER_ID, NAME, CONNECT_BY_ISLEAFleaf, level lev,
CONNECT_BY_ROOT ID,
SYS_CONNECT_BY_PATH(ID,'/')
FROM T_TREE_BAK
CONNECT BY PRIOR ID = FATHER_ID
start with id=1
order by level 

2 JDBC连接数据库的三种方式

格式一: Oracle JDBC Thin using aServiceName:

jdbc:oracle:thin:@//<host>:<port>/<service_name>

Example:jdbc:oracle:thin:@//192.168.2.1:1521/XE

注意这里的格式，@后面有//, 这是与使用SID的主要区别。

这种格式是Oracle 推荐的格式，因为对于集群来说，每个节点的SID 是不一样的，但是SERVICE_NAME 确可以包含所有节点。

 格式二: Oracle JDBC Thin using anSID:

jdbc:oracle:thin:@<host>:<port>:<SID>

Example:jdbc:oracle:thin:@192.168.2.1:1521:X01A

Note:

Support for SID is being phased out. Oraclerecommends that users switch over to usingservice names.

 格式三：Oracle JDBC Thin using a TNSName:

jdbc:oracle:thin:@<TNSName>

Example: jdbc:oracle:thin:@GL

Note:

Support for TNSNames was added in thedriver release 10.2.0.1

3 数据库迁移后新老数据库记录比对

create table tb_table_record(
tab_name varchar2(100),
tab_count number(10),
record_time date default sysdate
);
declare
 v_sql varchar2(1000);
 type t_ref is REF CURSOR; --列表游标
  c1t_ref;
 c_row tb_table_record%rowtype;
 v_count number;
begin
 v_sql := 'truncate table tb_table_record ';
 execute immediate v_sql;
 v_sql := 'insert into tb_table_record(tab_name )select table_name fromuser_tables t ';
 execute immediate v_sql;
 v_sql :='select tab_name,tab_count,record_time from tb_table_record';
 OPEN c1 FOR v_sql;    
   loop            
     fetch c1 into c_row;              
     v_sql :='select count(1) from '||c_row.tab_name;
     execute immediate v_sql into v_count;
     v_sql :='update tb_table_record t set t.tab_count = '||v_count||' wheret.tab_name = '''||c_row.tab_name||'''';
     execute immediate v_sql;
     exit when c1%notfound;
    endloop;       --关闭游标     
 close c1;
 commit;
end;
select * from tb_table_record;
--比较记录数
select * from src_user.tb_table_record@link_new a ,tb_table_record b where a.tab_name =b.tab_name and a.tab_count<> b.tab_count;
注：src_user是源数据库的用户 link_new是dblink

4 Oracle表空间相关查询与操作语句

select usernamefrom dba_users;--查询所有用户
--查看表空间使用情况
SELECT a.tablespace_name "表空间名",
       total/1024/1024表空间大小,
       free/1024/1024表空间剩余大小,
       (total - free)/1024/1024表空间使用大小,
       ROUND((total- free) / total,4) *100"使用率 %"
  FROM (SELECT tablespace_name,SUM(bytes)free
          FROMDBA_FREE_SPACE
         GROUPBY tablespace_name) a,
       (SELECTtablespace_name,SUM(bytes) total
          FROMDBA_DATA_FILES
         GROUPBY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name ;

--创建新用户且未指定表空间时默认使用users表空间
createuser hadoopidentifiedby hadoopdefaulttablespace test_data;
--查看当前用户的缺省表空间
select username,default_tablespacefromuser_users;
--查看当前用户的角色
select *from user_role_privs;
--查看当前用户的系统权限和表级权限
select *from user_sys_privs;
select *from user_tab_privs;
--显示当前会话所具有的权限
select *fromsession_privs;
--显示指定用户所具有的系统权限
select *fromdba_sys_privswhere grantee='GAME';
--查看表空间的名字及大小:
select t.tablespace_name,round(sum(bytes/(1024*1024)),0) ts_size_mb
from dba_tablespaces t, dba_data_files dwhere t.tablespace_name = d.tablespace_name  
groupby t.tablespace_name;
----查看表空间的大小；
SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 MBFROM DBA_FREE_SPACE GROUPBY TABLESPACE_NAME;
--修改限额
ALTERUSER "SPACEDBA"QUOTAUNLIMITEDON SPACE_DATA;
--Oracle查看表空间及修改数据文件大小
--第一步：查看所有表空间及表空间大小：
select tablespace_name ,sum(bytes)/1024 /1024as MBfrom dba_data_filesgroupbytablespace_name;
--第二步：查看所有表空间对应的数据文件：
select tablespace_name,file_namefromdba_data_files;
--第三步：修改数据文件大小
alterdatabasedatafile'H:\APP\ROBINJUN\PRODUCT\11.2.0\DBHOME_1\ORADATA\TESTSERVER\TEST_DATA01.DBF'RESIZE32M;
alterdatabasedatafile'H:\APP\ROBINJUN\PRODUCT\11.2.0\DBHOME_1\ORADATA\TESTSERVER\TEST_DATA01.DBF'OFFLINE;
--删除表空间
DROPTABLESPACE test_dataINCLUDINGCONTENTSANDDATAFILES;
--更改ONLINE_STATUS
altertablespace test_dataonline;
--查看表空间
SELECT *from dba_tablespaces t,dba_data_files dwhere t.tablespace_name =d.tablespace_name ;
--查看表空间使用的文件
SELECT *FROM Dba_Data_Files aFORUPDATE;
SELECT *FROM dba_segments aWHERE a.tablespace_name='TEST_DATA'
--查看锁定对象
select b.object_name,session_id,process,locked_mode
from v$locked_object a, dba_objects bwherea.object_id=b.object_id;
--建立DBLINK
createdatabaselink link_nameconnectto HADOOPidentifiedby HADOOPusing'HADOOP';
createdatabaselink link_name_testconnecttoLINKidentifiedby HADOOPusing'HADOOP';--LINK_NAME_TEST
select *fromdual@link_name_test;
--修改密码
ALTERUSERHADOOPIDENTIFIEDBY HADOOP;
--删除database link
DROP databaselinklink_name_test;
--测试连接是否成功
select *from dual@link_name;
select *fromdual@link_name_test;
--在本地数据库中查询已经建立的远程连接名 
select owner,object_namefromdba_objectswhere object_type='DATABASE LINK';
select *from dba_objectswhere object_type='DATABASELINK';

5. 查询表空间

5.1 查询一般表空间

SELECTa.tablespace_name "表空间名",
      total 表空间大小,
      free 表空间剩余大小,
      (total - free) 表空间使用大小,
      ROUND((total - free) / total, 4) * 100 "使用率 %"
  FROM (SELECTtablespace_name, SUM(bytes) free
         FROM DBA_FREE_SPACE
        GROUP BY tablespace_name) a,
      (SELECT tablespace_name, SUM(bytes) total
         FROM DBA_DATA_FILES
        GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
   and a.tablespace_name = '查询的表空间名称' 

5.2 查询临时表空间

SELECT a.tablespace_name "表空间名",
       total 表空间大小,
      total-used 表空间剩余大小,
       used 表空间使用大小,
      ROUND(used / total, 4) * 100 "使用率 %"
  FROM (SELECT tablespace_name,SUM(bytes_used) used
         FROM v$temp_space_header
        GROUP BY tablespace_name) a,
      (SELECT tablespace_name, SUM(bytes) total
         FROM dba_temp_files
        GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
   and a.tablespace_name = '查询的临时表空间名称' 

6 创建表空间

//创建表空间 
create tablespace test 
datafile 'c:\oracle\oradata\orcl9\test.dbf'size 50M 
default storage (initial 500K  
Next 500K 
minextents 1 
maxextents unlimited 
pctincrease 0);
 //创建用户 
create user lxg identified by lxg defaulttablespace test; 
 //授权　 
grant resource,connect,dba to test;
 //删除表空间  
drop tablespace "空间名" including contents and datafiles
删除用户
drop user"lxg" cascade
增加表空间
alter tablespacechinawater add datafile 'c:\oracle\oradata\orcl9\ADDCHINAWATER.dbf' size 200M
创建用户
create user userNameidentified by password;
创建用户userName,密码为password

----------------------------------------------------------------------------------------------------------------------------------------------------------------

//创建临时表空间
create temporary tablespace test_temp   tempfile'E:\oracle\product\10.2.0\oradata\testserver\test_temp01.dbf'   size 32m  autoextend on   next 32m maxsize2048m   extent management local;
 //创建数据表空间
create tablespace test_data   logging  datafile 'E:\oracle\product\10.2.0\oradata\testserver\test_data01.dbf'   size 32m  autoextend on   next 32m maxsize2048m   extent management local;
//创建用户并指定表空间
create user username identified bypassword   default tablespacetest_data   temporary tablespace test_temp;
//给用户授予权限
grant connect,resourceto username;  //以后以该用户登录，创建的任何数据库对象都属于test_temp 和test_data表空间，这就不用在每创建一个对象给其指定表空间了。
--select * from dba_data_files wherefile_name like '%SPACE%';
--select * from dba_temp_files wherefile_name like '%SPACE%';
 --drop user emp_mgr10 CASCADE;
--drop tablespace xxxx including contentsand datafiles;
--drop tablespace xxxx including contentscascade constraints;
 --创建缺省表空间，系统支持表、公用数据表
create bigfile tablespace SPACE_DATAdatafile 'E:\OracleData\SPACE\DataFiles\SPACE_data.dbf' size 256m autoextendon;
 --创建临时表空间
create bigfile temporary tablespaceSPACE_TEMP tempfile 'E:\OracleData\SPACE\DataFiles\SPACE_temp.dbf' size 256mautoextend on;
 --篮球表空间
create bigfile tablespace BASKETBALLdatafile 'E:\OracleData\SPACE\DataFiles\basketball.dbf' size 256m autoextendon;
 --销售表空间
create bigfile tablespace LOTTERY_SALEdatafile 'E:\OracleData\SPACE\DataFiles\sale.dbf' size 10240m autoextend on;
 --业务日志
create bigfile tablespace SPACE_LOGdatafile 'E:\OracleData\SPACE\DataFiles\log.dbf' size 256m autoextend on;
 用户包括该用户下的表，试图，同义词，过程，索引，及相关的一切都被删除然后新建一个同名用户，赋予相应权限
 --drop user SPACEDba cascade;
 --创建管理员用户
create user SPACEDba identified by qweasd defaulttablespace SPACE_data temporary tablespace SPACE_temp;
 --授权
grant connect,dba to SPACEdba;
 --修改限额
ALTER USER "SPACEDBA" QUOTAUNLIMITED ON SPACE_DATA;
 修改用户默认表空间
alter user test1 default tablespaceuser_tbs

7给用户授权

grant dba to lxg;--授予DBA权限 
grant unlimited tablespace to lxg;--授予不限制的表空间 
grant select any table to lxg;--授予查询任何表 
grant select any dictionary to lxg;--授予 查询 任何字典
grant dba to lxg; 
grant unlimited tablespace to lxg; 
grant select any table to lxg; 
grant select any dictionary to lxg;
grantresource,CONNECTto hadoop;
grantcreateanytypeto hadoop;
(GRANTCREATEdatabaselinkTO hadoop;)--与上面重复
GRANT EXP_FULL_DATABASETO hadoop;--exp_full_database权限
GRANT imp_full_databaseTO hadoop;--imp_full_database权限
GRANTDROPdatabaselinkTO hadoop; 

8创建目录

--删除directory
drop directory pump_dir;
 --创建pump_dir目录（pump_dir需要手工创建）linux机器
CREATE DIRECTORY pump_dir AS '/u01/oracle/oradata/pump_dir';
GRANT READ ON DIRECTORY pump_dir TO PUBLIC;
 --本机数据库测试（windows环境）
--查看目录：
select * from dba_directories;
--指定dump_dir目录：（目录维护命令:）
create directory pump_dir as 'H:\app\ROBINJUN\oradata\pump_dir';--建立
Drop directory pump_dir;--删除
--为用户对该目录操作授权：
grant read,write on directory pump_dir to hadoop;

9 impdp命令

--impdp加载数据命令格式

impdp rman/rman network_link=dmp_link

remp_schema=source_schema:target:shcema 

remap_tablespace=source_tablespace:target:tablespace

注明：network_link 指的是在目标库创建的源库db_link且涉及的连接的用户必须具备EXP_FULL_DATABASE

--加载数据

impdp hadoop/hadoop directory=dump_dirdumpfile=mytest.dmp network_link=link_name_test schemas=link remap_schema=link:hadoop remap_tablespace=users:test_data

impdp hadoop/hadoop network_link=link_name_test schemas=link remap_schema=link:hadoop remap_tablespace=users:test_data

10 Oracle查询删除创建表语句拼接

select 'drop table ' || table_name||';'||chr(13)||chr(10) from user_tables;

select 'drop table ' || table_name||'|'||table_name||';'||chr(13)||chr(10) from user_tables;

select ' create table ' || table_name ||'as select * from '||table_name||'@dblink;'||chr(13)||chr(10) from user_tables;

注明：10 表示换行 13表示回车