Oracle 创建表空间，用户

---删除表空间
--drop tablespace t1 including contents;

 

--查询表空间路径

select t1.name, t2.name

  from v$tablespace t1, v$datafile t2

 where t1.ts# = t2.ts#;

---创建表空间
CREATE TABLESPACE t1
  DATAFILE '/opt/oracle/oradata/ora11/t1.dbf' SIZE 100M REUSE
    AUTOEXTEND ON NEXT 2M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;


---删除索引表空间
--drop tablespace t1_index including contents;


---创建索引表空间
--CREATE TABLESPACE t1_index
--DATAFILE '/opt/oracle/oradata/ora11/t1_index.dbf' SIZE 100M REUSE
--AUTOEXTEND ON NEXT 2M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;


--- 创建用户
create user u1 identified by u1 default tablespace t1 temporary tablespace TEMP;



---赋予用户权限
grant connect,resource,create any table,drop any table,create sequence,
     alter any trigger, create any trigger, drop any trigger,
     alter any type, create any type, drop any type,
     create public database link,drop public database link,
     create any view, drop any view,    
     create procedure,
     create any directory ,
     create any materialized view,
     create database link,
     create synonym,

     debug any procedure,

     debug connect session,
     create session to u1;

 

    
---创建DB LINK
create database link DB_LINK
  connect to "u1" identified by "u1"
  using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ora11)
    )
  )';
 
---创建物化视图
create materialized view t_mater refresh
         start with sysdate next sysdate + 5/1440
         with rowid
         as
         select * from t_tab;
 
commit;