Study--Oracle-03-数据库常规操作

-- 创建用户并授予权限
CREATE USER c##john IDENTIFIED BY password;
GRANT CONNECT, RESOURCE TO c##john;
 
-- 创建表空间
CREATE TABLESPACE users
DATAFILE 'D:\ORACLE\ORADATA\ORCL\users01.dbf' SIZE 50M
AUTOEXTEND ON NEXT 5M MAXSIZE 100M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL;
 
-- 创建用户并指定表空间
CREATE USER c##jane IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
PROFILE DEFAULT
ACCOUNT UNLOCK;
 
-- 给用户授权
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO c##jane;
GRANT CREATE PROCEDURE, CREATE TRIGGER TO c##jane WITH ADMIN OPTION;
 
-- 查看用户系统权限
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'C##JOHN';
 
-- 查看用户对象权限
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'C##JOHN';
 
-- 查看角色权限
SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE = 'CONNECT';
 
-- 查看数据库版本
SELECT * FROM V$VERSION;
 
-- 查看表空间使用情况
SELECT df.tablespace_name,
       round(sum(df.bytes) / 1024 / 1024, 2) AS total_mb,
       round(sum(free.bytes) / 1024 / 1024, 2) AS free_mb,
       round(sum(df.bytes) / sum(free.bytes) * 100, 2) AS used_percent
FROM   dba_free_space free, dba_data_files df
WHERE   free.tablespace_name = df.tablespace_name
GROUP BY df.tablespace_name;
 
-- 查看数据文件大小
SELECT
    tablespace_name,
    file_id,
    file_name,
    round(bytes / (1024 * 1024), 2) AS file_size_mb
FROM
    dba_data_files;
 
-- 查看表空间的大小和所属数据文件
SELECT
    tablespace_name,
    file_id,
    file_name,
    round(bytes / (1024 * 1024), 2) AS file_size_mb
FROM
    dba_data_files
ORDER BY
    tablespace_name,
    file_id;
 
-- 查看表空间的自动扩展设置
SELECT
    tablespace_name,
    file_id,
    autoextensible,
    increment_by * 1024 * 1024 AS increment_size_mb
FROM
    dba_data_files
WHERE
    autoextensible = 'YES';
 
-- 查看表空间的使用情况
SELECT
    df.tablespace_name,
    round(sum(df.bytes) / 1024 / 1024, 2) AS total_mb,
    round(sum(free.bytes) / 1024 / 1024, 2) AS free_mb,
    round(sum(df.bytes) / sum(free.bytes) * 100, 2) AS used_percent
FROM
    dba_free_space free,
    (SELECT
         tablespace_name,
         bytes
     FROM
         dba_data_files
     UNION ALL
     SELECT
         tablespace_name,
         sum(bytes) AS bytes
     FROM
         dba_temp_files
     GROUP BY
         tablespace_name) df
WHERE
    free.tablespace_name = df.tablespace_name
GROUP BY
    df.tablespace_name;
 
-- 查看表空间的自动扩展设置
SELECT
    tablespace_name,