dblink

1
2
3
4
SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM DBA_TABLESPACES;
SQL> DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
SQL> SELECT USERNAME FROM DBA_USERS;
SQL> DROP USER user_name CASCADE;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create tablespace TS_PUB
logging
datafile '/data/oracle/oradata/orcl2/ts_pub.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local; 

create user pub identified by pub default tablespace TS_PUB;

grant connect, resource to pub;
grant create session to pub;
grant create procedure to pub;
grant create database link to pub;
grant create view to pub;
grant create table to pub;
grant unlimited tablespace to pub;
grant select any table to pub;
grant create trigger to pub;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table USERS
(
    USERS_ID varchar(20) not null,
    USERS_NAME varchar(20) not null,
    USERS_MOBILE varchar(20) null,
    USERS_PASSWORD varchar(50) not null
)

ALTER TABLE USERS  
ADD CONSTRAINT PK_USERS PRIMARY KEY (USERS_ID);

--创建物化日志
CREATE MATERIALIZED VIEW LOG ON PUB.USERS WITH PRIMARY KEY;

INSERT INTO users (users_id, users_name, users_mobile,users_password) values('1','zhangsan','13987765656','123');

SELECT * FROM PUB.users;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
create tablespace TS_ENT001
logging
datafile '/data/oracle/oradata/orcl2/ts_ent001.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local; 

create user ent001 identified by 123 default tablespace TS_ENT001;

grant connect, resource to ent001;
grant create session to ent001;
grant create procedure to ent001;
grant create database link to ent001;
grant create view to ent001;
grant create table to ent001;
grant unlimited tablespace to ent001;
grant select any table to ent001;
grant create trigger to ent001;
GRANT CREATE ANY MATERIALIZED VIEW TO ent001;
grant insert on pub.users to ent001;
grant update on pub.users to ent001;
grant delete on pub.users to ent001;

1
2
3
4
5
6
7
8
9
create database link TO_PUB
connect to pub identified by pub
using '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.52.148)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )';

1
2
3
4
5
6
7
8
create materialized view users
build immediate
refresh fast with primary key
on demand
start with sysdate
next sysdate + 10/(60*24)
as
SELECT * FROM users@TO_PUB;