Oracle数据库新建用户及分配表空间

我不是女神ヾ 2023-02-20 07:23 24阅读 0赞

# 新建表空间、用户 #

## 1、新建临时表空间 ##

create temporary tablespace VEHICLEUSER_TMP      tempfile '/oradata/orcl/VEHICLEUSER_TMP.dbf'             --路径根据实际情况任意存储     
    size 50m autoextend on next 50m maxsize 10240m extent management local;

## 2、新建表空间 ##

create tablespace VEHICLEUSER_FILE logging datafile '/oradata/orcl/VEHICLEUSER01.dbf'
     size 50m autoextend on next 50m maxsize 20480m extent management local;

## 3、创建用户 ##

create user VEHICLEUSER_01 identified by veh123#    -- ‘VEHICLEUSER_01’ 为创建的用户名  ；‘veh123#’为创建的新用户登录密码 
    default tablespace VEHICLEUSER_FILE         temporary tablespace VEHICLEUSER_TMP;

## 4、授予权限 ##

grant connect,resource,dba to VEHICLEUSER_01;

# 表空间查询 #

## 1、查看表空间名称和大小（单位MB） ##

SELECT t.tablespace_name, round(SUM(bytes/(1024*1024)),0) ts_size FROM dba_tablespaces t, dba_data_files d WHERE t.tablespace_name = d.tablespace_name 
    GROUP BY t.tablespace_name;

## 2、查看表空间物理文件和大小 ##

SELECT tablespace_name, 
    file_id, 
    file_name, 
    round(bytes / (1024 * 1024), 0) total_space 
    FROM dba_data_files 
    ORDER BY tablespace_name;

## 3、查看表空间使用情况 ##

SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name 
    FROM dba_free_space 
    GROUP BY tablespace_name; 
    SELECT a.tablespace_name, 
    a.bytes total, 
    b.bytes used, 
    c.bytes free, 
    (b.bytes * 100) / a.bytes "% USED ", 
    (c.bytes * 100) / a.bytes "% FREE " 
    FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c 
    WHERE a.tablespace_name = b.tablespace_name 
    AND a.tablespace_name = c.tablespace_name;