--在Oracle数据库中,创建用户账号通常需要DBA权限
CREATE USER tzyIDENTIFIED BY "1qaz@WSX";
--如果你想要赋予该用户管理权限,可以赋予DBA角色
GRANT DBA TO tzy;
--如果你只想给予特定的权限,可以使用GRANT语句来授予所需的权限
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO tangzongyun;
--查看用户列表
SELECT USERNAME FROM DBA_USERS;
-- 创建表空间并设置表空间名(OK)
CREATE tablespace "tzyts" datafile 'tzyf' SIZE 50 M autoextend on next 5M maxsize 1000M;
-- 创建表空间
create tablespace LQ datafile 'E:/oracle_data/LQ_01.dbf' size 2000M autoextend on next 200M maxsize 10000M;
-- 创建表空间(不设置上限)
create tablespace LQ datafile 'E:/oracle_data/LQ_01.dbf'size 2000M autoextend on next 200M maxsize unlimited extent management local autoallocate segment space management auto;
-- 修改表空间文件大小(支持TEMP临时表空间)
ALTER DATABASE DATAFILE 'E:/oracle_tablespaces/DEMOSPACE_TBSPACE.dbf' RESIZE 10000M;
-- 修改表空间文件自增长
ALTER DATABASE DATAFILE 'E:/oracle_tablespaces/DEMOSPACE_TBSPACE.dbf' AUTOEXTEND ON NEXT 500M;
-- 增加表空间文件
ALTER TABLESPACE demospace ADD DATAFILE 'E:/oracle_tablespaces/DEMOSPACE_TBSPACE_02.dbf'
SIZE 2000M autoextend on next 200M maxsize 10000M;
--缩小表空间
select tablespace_name, file_id, file_name,
--表空间最大值
round(bytes/(1024*1024),0) total_space,
--已使用表空间的最大值
(SELECT MAX(block_id)*8/1024 FROM dba_extents WHERE tablespace_name = 'LQ') max_space
from dba_data_files f order by tablespace_name;
--创建用户&指定表空间
-- 创建用户时直接指定
create user demo identified by demo default tablespace DEMOSPACE ;
-- 分步指定
create user demo identified by demo ;
alter user demo default tablespace "demospace";
-- 同时指定临时表空间
create user demo identified by demo default tablespace demospace temporary tablespace temp;
-- 查看每个用户占用的表空间
SELECT OWNER as "用户名", sum(BYTES) / 1024 / 1024 / 1024 as "所有表的大小(GB)" FROM DBA_SEGMENTS
WHERE SEGMENT_NAME in (select t2.OBJECT_NAME from dba_objects t2
where t2.OBJECT_TYPE = 'TABLE') group by OWNER order by 2 desc ;
-- 当前用户每张表占用的表空间
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name order by Sum(bytes)/1024/1024 desc ;
-- 查看每个用户的临时表空间
select username,default_tablespace,temporary_tablespace from dba_users ;
--查看当前用户使用的表空间
select * from user_users;
--查看所有表空间
select tablespace_name from dba_tablespaces;
--查询用户表空间
SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS;
--查询用户角色
SELECT * FROM DBA_ROLE_PRIVS;
-- 查询用户权限
SELECT DISTINCT * FROM DBA_SYS_PRIVS;
--查询锁定用户
SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS LIKE '%LOCKED%'
-- 修改用户密码
ALTER USER [username] IDENTIFIED BY [new_password];
create table student(
student_number varchar2(10),
student_name varchar2(10),
sex varchar2(4),
score number(4)
);
comment on table student is '学生信息表';
comment on column student.student_number is '学生学号';
comment on column student.student_name is '学生姓名';
comment on column student.sex is '性别';
select * from student;
Oracle创建用户、表空间
于 2024-03-17 11:07:50 首次发布