Oracle 创建表空间，用户

最新推荐文章于 2024-11-22 09:01:14 发布

最新推荐文章于 2024-11-22 09:01:14 发布 · 87 阅读

文章标签：

#Oracle

Oracle 专栏收录该内容

7 篇文章

订阅专栏

本文介绍了Oracle数据库中表空间的创建与删除操作，包括主表空间及索引表空间的管理，并展示了如何创建用户及其权限分配，同时涉及了数据库链接（DBLINK）、物化视图的创建等高级主题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

---删除表空间
--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;