oracle笔记

1、创建表空间

使用system登录，system/manager sysdba

执行

 

CREATE TABLESPACE ts1  
DATAFILE 'F:\tablespace\ts1' size 100M  
         EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE ts2  
DATAFILE 'F:\tablespace\ts2' size 100M  
         EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

 

会发现F:\tablespace文件夹下生成两个新文件TS1和TS2

若要删除表空间

执行

 

DROP TABLESPACE ts1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE ts2 INCLUDING CONTENTS AND DATAFILES;

2、创建用户

 

 

create user user1 identified by user1   
default tablespace ts1;
create user user2 identified by user2   
default tablespace ts2;

创建两个新用户，用户user1使用表空间tablespace1，用户user2使用表空间tablespace2

 

3、赋权

 

grant connect,resource to user1;  
grant create any sequence to user1;  
grant create any table to user1;  
grant delete any table to user1;  
grant insert any table to user1;  
grant select any table to user1;  
grant unlimited tablespace to user1;  
grant execute any procedure to user1;  
grant update any table to user1;  
grant create any view to user1;

对用户user2的赋权同理
4、建表

 

connect user1/user1，切换成user1用户

执行

 

create table table1
(
  id VARCHAR2(32) not null,
  code NUMBER,
  name VARCHAR2(500)
)
comment on column table1.id
  is '主键ID';
comment on column table1.code
  is '编码';
comment on column table1.name
  is '名称';

connect user2/user12，切换成user2用户

 

执行

 

create table table2
(
  id VARCHAR2(32) not null,
  code NUMBER,
  name VARCHAR2(500)
)
comment on column table2.id
  is '主键ID';
comment on column table2.code
  is '编码';
comment on column table2.name
  is '名称';

至此，在oracle数据库里，就创建了两个用户，对应两个表空间。若要实现两个用户管理下的表之间的透明性，即可以数据互查。可以通过视图或同义词。

 

5、创建视图

 

create view V_USER1_TABLE1 as
select * from USER1.TABLE1

则user2用户可以使用select * from V_USER1_TABLE1来查询到user1用户管理下的table1表
6、创建同义词

 

创建同义词之前，要先创建数据源连接database link

connect system/manager，切换成system用户

执行

 

create public database link conn_to_user1
  connect to user1 identified by user1
  using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) )';

public表示该数据库源连接的所有者为共有的

 

创建完数据源连接后，就可以创建同义词了

 

create or replace public synonym USER2_TABLE1
  for USER1.TABLE1@CONN_TO_USER1;

表示同义词USER2_TABLE1指向USER1下的TABLE1表，可以使用USER2_TABLE1来查询USER1下的TABLE1表
同创建数据库源连接一下，有一个public关键字，表示该同义词是公有的，即所有用户都可以使用。若要对此权限进行限制可以使用下面的方式。

 

connect system/manager，切换成system用户

执行

 

grant create synonym to user2

给USER2用户赋予创建同义词的权限

 

connect user2/user2，切换成user2用户
执行

 

create or replace synonym USER2_TABLE1
  for USER1.TABLE1@CONN_TO_USER1;

则该同义词的所有者为当前用户user2(数据库源连接同理)，则user2用户可以使用select * from USER2_TABLE1来查询到user1用户管理下的table1表

 

行转列

with a as (select '2,1,6'|| ',' showname from dual) ,
          b as (select regexp_substr(showname,'[^,]+',1,rownum)  showname from a connect by rownum <= length(regexp_replace(showname,'[^,]+')))
          select * from b
SELECT * FROM(
SELECT t.*,row_number() OVER(partition by grop order  by id) rn FROM testt) WHERE rn=1

with a as (select 'ABC,AA,AD,ABD,JI,CC,ALSKD,ALDKDJ' id from dual)
select regexp_substr(id,'[^,]+',1,rownum) id from a
connect by rownum <= length(regexp_replace(id,'[^,]+'))