oracle新建表空间和用户并赋权限

1. 查看表空间路径

SELECT
    a.file#,
    a.name,
    a.bytes / 1024 / 1024                          currentmb,
    ceil(hwm * a.block_size / 1024 / 1024)         resizeto,
    ( a.bytes - hwm * a.block_size ) / 1024 / 1024 releasemb,
    'alter database datafile '''
    || a.name
    || ''' resize '
    || ceil(hwm * a.block_size / 1024 / 1024)
    || 'M;'                                        resizecmd
FROM
    v$datafile a,
    (
        SELECT
            file_id,
            MAX(block_id + blocks - 1) hwm
        FROM
            dba_extents
        GROUP BY
            file_id
    )          b
WHERE
    a.file# = b.file_id (+)
    AND ( a.bytes - hwm * a.block_size ) > 0
ORDER BY
    5;

2.查看表空间数据

SELECT
    *
FROM
    dba_users;

3.新建表空间

CREATE TABLESPACE sanhua_qg
DATAFILE 'C:\ORACLE\ORADATA\ORCL\QG01.DBF' size 800M
         EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

4.新建用户并授权

--创建用户
create user qinggang identified by sanhua;
--修改用户默认表空间
alter user qinggang default tablespace sanhua_qg;
--或者创建用户并设置默认表空间
create user qinggang identified by sanhua
default tablespace sanhua_qg;
--赋权限
grant connect,resource to qinggang;
grant create any sequence to qinggang;
grant create any table to qinggang;
grant delete any table to qinggang;
grant insert any table to qinggang;
grant select any table to qinggang;
grant unlimited tablespace to qinggang;
grant execute any procedure to qinggang;
grant update any table to qinggang;
grant create any view to qinggang;
--或者赋DBA权限
grant connect,resource,dba to qinggang;

5.查看表空间是否自增长

SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files;

6.设置表空间自增长

-- 打开自动增长
ALTER DATABASE DATAFILE '/home/database/oracle/oradata/xhlesdata.dbf' AUTOEXTEND ON;
-- 每次自动增长200m
ALTER DATABASE DATAFILE '/home/database/oracle/oradata/xhlesdata.dbf' AUTOEXTEND ON NEXT 200M ;
-- 每次自动增长200m，数据表最大不超过1G，一般不用这句
ALTER DATABASE DATAFILE 'c:\SmartDB01.ora' AUTOEXTEND ON NEXT 200M MAXSIZE 1024M;