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_BAKCONNECT BY PRIOR ID = FATHER_IDstart with id=1order 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) bWHERE 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_mbfrom 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_STATUSaltertablespace 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_modefrom v$locked_object a, dba_objects bwherea.object_id=b.object_id;--建立DBLINKcreatedatabaselink link_nameconnectto HADOOPidentifiedby HADOOPusing'HADOOP';createdatabaselink link_name_testconnecttoLINKidentifiedby HADOOPusing'HADOOP';--LINK_NAME_TESTselect *fromdual@link_name_test;--修改密码ALTERUSERHADOOPIDENTIFIEDBY HADOOP;--删除database linkDROP 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) bWHERE 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) bWHERE 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创建目录

--删除directorydrop 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表示回车