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

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

omm=# create tablespace t_tbspace relative location 'tablespace/t_tbspace';
CREATE TABLESPACE
omm=# create user test identified by 'abcd@123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLEomm=# grant CREATE on TABLESPACE t_tbspace to test ;
GRANT
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(id integer) tablespace t_tbspace ;
CREATE TABLE

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

omm=> select oid, * from pg_tablespace ;
  oid  |  spcname   | spcowner |         spcacl         | spcoptions | spcmaxsize | re
lative 
-------+------------+----------+------------------------+------------+------------+---
-------
  1663 | pg_default |       10 |                        |            |            | f
  1664 | pg_global  |       10 |                        |            |            | f
 16394 | t_tbspace  |       10 | {omm=C/omm,test=C/omm} |            |            | t
(3 rows)omm=> select pg_tablespace_size('t_tbspace');
 pg_tablespace_size 
--------------------
               8192
(1 row)

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、查看数据库在非默认表空间下有哪些对象

omm=> select relname,relkind,relpages,pg_size_pretty(pg_relation_size(a.oid)),
omm-> reltablespace,relowner
omm-> from pg_class a, pg_tablespace tb
omm-> where a.relkind in ('r', 'i')
omm-> and a.reltablespace=tb.oid
omm-> and tb.spcname='t_tbspace'
omm-> order by a.relpages desc;
 relname | relkind | relpages | pg_size_pretty | reltablespace | relowner 
---------+---------+----------+----------------+---------------+----------
 t1      | r       |        0 | 0 bytes        |         16394 |    16395
(1 row)

5、重命名表空间

omm=> alter tablespace t_tbspace rename to t_tbspace_new;
ERROR:  permission denied for tablespace t_tbspace
DETAIL:  N/A
omm=> \c omm omm
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm" as user "omm".
omm=# alter tablespace t_tbspace rename to t_tbspace_new;
ALTER TABLESPACE

## test用户没有修改权限

6、删除表空间

omm=# drop tablespace t_tbspace_new ;
ERROR:  tablespace "t_tbspace_new" is not empty
omm=# drop table test.t1 ;
DROP TABLE
omm=# drop tablespace t_tbspace_new ;
DROP TABLESPACE

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