oracle 数据结构部署，-优快云博客

本文提供Oracle数据库中关于数据文件管理、表空间操作、用户创建及权限授予等方面的实用SQL语句，帮助读者掌握基本的数据库管理技能。

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

Oracle 数据结构部署

用例：

- 1、查询DBF文件位置

  select tablespace_name, file_id,file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;
  select * from dba_data_files;
  --/home/oracle/app/oracle/oradata/orcl/users01.dbf

- 2、删除用户表空间

  drop user ecom cascade;
  drop tablespace ERPDATA including contents and datafiles;

- 3、创建表空间

  create tablespace ERPDATA datafile 'D:/test/ecom_00.dbf' size 200M;

- 4、授权表空间大小自增长

  alter database datafile 'D:/test/ecom_00.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 3000M;
  alter tablespace ERPDATA add datafile '/data/oracle/data/orcl/ecom_01.dbf' size 200M;
  alter database datafile '/data/oracle/data/orcl/ecom_01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 3000M;

- 5、扩展空间，将数据文件扩大至5000MB

  alter database datafile '/data/oracle/data/orcl/ecom_01.dbf' resize 5000m;

- 6、创建用户

  create user ecom identified by "passwd111" account unlock default tablespace ERPDATA;

- 7、给用户授权

  grant "CONNECT" TO ecom;
  grant resource to ecom;
  grant "JAVASYSPRIV" TO ecom;
  grant "JAVAUSERPRIV" TO ecom;
  grant create view to ecom;
  grant create synonym to ecom;

操作点：

--当前用户有多少张表

select count(table_name) from user_tables ;

--用户在表空间所分配的空间没有限制

alter user sourcedata quota unlimited on SRC_DATA;

/* 无法删除当前连接用户

select username ,sid,serial# from v$session
alter system kill session '687,4263';
alter system kill session '491,34491';

--查询用户权限

select * from dba_sys_privs d where d.GRANTEE = 'ECOM';

--查询用户对表的权限

select 'grant '||u.privilege||' on '||u.TABLE_NAME||' to '||u.GRANTEE||' with grant option;'
from user_tab_privs u where u.table_name not like '%==%';
--撤销授权不级联
with admin option;
--撤销授权级联
with grant option;

转载于:https://www.cnblogs.com/fuhaha/p/9223534.html