oracle使用笔记

一、清空数据表

可以用truncate、delete两种方式清空

truncate table [表名];
delete from [表名];

二、修改数据库字符集为gbk

#su - oracle
$sqlplus /nolog
SQL> connect /as sysdba;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter system enable restricted session;
SQL> alter system set JOB_QUEUE_PROCESSES=0;
SQL> alter system set AQ_TM_PROCESSES=0;
SQL> alter database open;
SQL> alter database character set INTERNAL_USE ZHS16GBK;
SQL> shutdown immediate;
SQL> startup

三、创建表空间、用户、用户授权

# 查看所有用户
select * from dba_users; 
# 删除用户grp及数据
DROP USER grp CASCADE;
# 删除表空间grp
drop tablespace grp including contents and datafiles;
# 删除表空间grp不删除用户
drop tablespace GRP including contents and datafiles;
# 查看所有表空间
select tablespace_name from dba_tablespaces;
# 查看表空间路径
select * from dba_data_files;
# 创建表空间（“grp”为表空间名称，“/data/oracle/oradata/orcl/grp.dbf”表空间路径，根据系统路径修改；）
create tablespace grp logging datafile '/data/oracle/oradata/orcl/grp.dbf' size 100m autoextend on next 50m maxsize 20480m extent management local;
# 创建用户并指定表空间（”grp“为用户名；”123“为密码）
create user grp identified by 123 default tablespace grp temporary tablespace temp profile default;
# 给用户赋权（给grp用户附以connect，resource，dba权限）
grant connect,resource,dba to grp;

四、修改system用户密码

sqlplus /nolog 
conn /as sysdba；
alter user system identified by password;

五、查询、扩容表空间容量

https://blog.csdn.net/qq_43427354/article/details/123555263