openGauss每日一练第10天 | 掌握表空间的管理

表空间是数据的容器。掌握表空间的管理，包括创建表空间、删除表空间、重命名表空间、查看表空间的情况。

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

CREATE USER test IDENTIFIED BY 'olabll@123';
CREATE TABLESPACE t_tbspace RELATIVE LOCATION 'tablespace/t_tbspace1';
GRANT CREATE ON TABLESPACE t_tbspace TO test;
\c omm test
create table t1(c1 varchar(20)) tablespace t_tbspace;

不赋权表空间的话无法建表

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

SELECT PG_TABLESPACE_SIZE('t_tbspace');
select oid,* from pg_tablespace where spcname ='t_tbspace';

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

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        |         16406 |    16402
(1 row)

5、重命名表空间

普通用户没有权限操作，赋管理员权限后可以

ALTER TABLESPACE t_tbspace RENAME TO t_tbspace2;
ERROR:  permission denied for tablespace t_tbspace
DETAIL:  N/A
omm=> \q
omm@modb:~$ gsql -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# ALTER TABLESPACE t_tbspace RENAME TO t_tbspace2;
ALTER TABLESPACE

alter user test SYSADMIN;
ALTER TABLESPACE t_tbspace2 RENAME TO t_tbspace3;
omm=# alter user test SYSADMIN;
ALTER ROLE
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=> ALTER TABLESPACE t_tbspace2 RENAME TO t_tbspace3;
ALTER TABLESPACE

6、删除表空间

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