Oracle dba_ts_quotas

原创于 2023-09-16 23:01:50 发布 · 351 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #dba #数据库

DBA 专栏收录该内容

1073 篇文章

订阅专栏

本文介绍了如何在Oracle数据库中修改用户bpx1的表空间配额，使用SQL语句查看和设置用户配额，并通过实例演示了创建表、分配表空间以及插入数据的过程。

修改用户表空间配额

用户bpx1默认表空间是bpx1
select default_tablespace from dba_users where username in (‘BPX1’);
在这里插入图片描述
SQL> alter user bpx1 quota unlimited on ycy;

User altered.

select * from dba_ts_quotas;
在这里插入图片描述
The SQL statement select * from dba_ts_quotas is used to retrieve information about the tablespace quotas assigned to users in the database. It will return a result set containing the following columns:

USERNAME: The name of the user.
TABLESPACE_NAME: The name of the tablespace.
BYTES: The amount of space allocated to the user in bytes.
MAX_BYTES: The maximum amount of space that can be allocated to the user in bytes.
BLOCKS: The number of blocks allocated to the user.
MAX_BLOCKS: The maximum number of blocks that can be allocated to the user.

By executing this query, you can view the tablespace quotas for all users in the database and their corresponding limits.

模拟操作

ALTER SESSION SET CURRENT_SCHEMA = username;

CREATE TABLE employees
(
  employee_id NUMBER(5) PRIMARY KEY,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  hire_date DATE,
  salary NUMBER(10, 2)
)
TABLESPACE YCY;


-- 使用多个INSERT语句
INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
VALUES (1, 'John', 'Doe', TO_DATE('2022-01-01', 'YYYY-MM-DD'), 5000);

INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
VALUES (2, 'Jane', 'Smith', TO_DATE('2022-02-01', 'YYYY-MM-DD'), 6000);


-- 使用单个INSERT语句和多个VALUE子句
INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
VALUES 
  (1, 'John', 'Doe', TO_DATE('2022-01-01', 'YYYY-MM-DD'), 5000),
  (2, 'Jane', 'Smith', TO_DATE('2022-02-01', 'YYYY-MM-DD'), 6000);

SELECT OWNER, TABLESPACE_NAME,TABLE_NAME
FROM DBA_TABLES
WHERE TABLESPACE_NAME = 'YCY';

select * from bpx1.employees;