openGauss每日一练第10天 | 逻辑结构：表空间管理

1、创建表空间t_tbspace、用户test，并使用test，在这个表空间上创建表t1

--进入数据库omm，创建测试表空间t_tbspace
[omm@ogdb1 ~]$ gsql -d omm -p 40000 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

--创建测试表空间t_tbspace
omm=# create tablespace t_tbspace relative location 'tablespace/t_tbspace_01';
CREATE TABLESPACE

--再次查询当前表空间
omm=# \db
             List of tablespaces
    Name    | Owner |        Location         
------------+-------+-------------------------
 pg_default | omm   | 
 pg_global  | omm   | 
 t_tbspace  | omm   | tablespace/t_tbspace_01
(3 rows)

--创建用户test,授予数据库系统的SYSADMIN权限：
omm=# create user test identified by 'aabb@123';
CREATE ROLE
omm=# alter user test sysadmin;
ALTER ROLE

--数据库系统管理员执行如下命令将“t_tbspace”表空间的访问权限赋予数据用户jack。
omm=# grant create on tablespace t_tbspace to test;
GRANT

--执行如下命令，使用test用户在指定表空间t_tbspace创建表t1。
omm=# \c omm test
Password for user test: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm" as user "test".
omm=> create table t1 (i int)tablespace t_tbspace;
CREATE TABLE
omm=> 
复制

2、查看表空间t_tbspace的oid和大小

--查看表空间t_tbspace的oid 
omm=> select oid,* from pg_tablespace where spcname='t_tbspace';
  oid  |  spcname  | spcowner |         spcacl         | spcoptions | spcmaxsize | relative 
-------+-----------+----------+------------------------+------------+------------+----------
 16496 | t_tbspace |       10 | {omm=C/omm,test=C/omm} |            |            | t
(1 row)

--查看表空间t_tbspace大小
omm=> select pg_tablespace_size('t_tbspace');
 pg_tablespace_size 
--------------------
                 25
复制

3、查看数据库在默认表空间下有哪些对象

with objectInDefaultTS as
          ( select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),
                  reltablespace,relowner
            from pg_class a
            where a.relkind in ('r', 'i')  and reltablespace='0'
         )
 select * 
 from objectInDefaultTS 
   where relname not like 'pg_%' and relname not like 'gs_%' and relname not like 'sql_%'
 order by relpages desc;

复制

4、查看数据库在非默认表空间下有哪些对象

--执行下面的SQL语句，查询数据库omm的非默认表空间t_tbspace下有哪些对象：
 select relname,relkind,relpages,pg_size_pretty(pg_relation_size(a.oid)),
          reltablespace,relowner
   from pg_class a, pg_tablespace tb
   where a.relkind in ('r', 'i')
   and a.reltablespace=tb.oid
   and tb.spcname='t_tbspace'
   order by a.relpages desc;
 
 relname | relkind | relpages | pg_size_pretty | reltablespace | relowner 
---------+---------+----------+----------------+---------------+----------
 t1      | r       |        0 | 0 bytes        |         16496 |    16497
(1 row)

复制

5、重命名表空间

--命名表空间t_tbspace为app_tbs
omm=> ALTER TABLESPACE t_tbspace RENAME TO app_tbs;
ALTER TABLESPACE

--执行下面的gsql命令，查看数据库当前的表空间信息：
omm=> \db
             List of tablespaces
    Name    | Owner |        Location         
------------+-------+-------------------------
 app_tbs    | omm   | tablespace/t_tbspace_01
 pg_default | omm   | 
 pg_global  | omm   | 
(3 rows)

omm=> 
复制

6、删除表空间

--用户必须是表空间的owner或者系统管理员才能删除表空间。需要先删除表空间的对象，再删除表空间app_ts：

omm=> drop table test.t1 ;
DROP TABLE
omm=> 
omm=> DROP TABLESPACE app_tbs;
DROP TABLESPACE

omm=> \db
      List of tablespaces
    Name    | Owner | Location 
------------+-------+----------
 pg_default | omm   | 
 pg_global  | omm   | 
(2 rows)
复制