概述
本文档记录openGauss 3.0.0数据库每日一练第10天课程作业，掌握表空间的管理，包括创建表空间、删除表空间、重命名表空间、查看表空间的情况。
课程练习
创建表空间t_tbspace、用户test，并使用test，在这个表空间上创建表t1
omm@local:/opt/huawei/tmp [postgres]=#CREATE TABLESPACE t_tbspace RELATIVE LOCATION 'tablespace/t_tbspace1';
CREATE TABLESPACE
omm@local:/opt/huawei/tmp [postgres]=#select oid,* from pg_tablespace ;
oid | spcname | spcowner | spcacl | spcoptions | spcmaxsize | relative
-------+--------------+----------+--------+------------+------------+----------
1663 | pg_default | 10 | | | | f
1664 | pg_global | 10 | | | | f
16393 | music_tbs | 10 | | | | t
16394 | music_tbs1 | 10 | | | | t
16469 | newtbs1 | 10 | | | | t
16470 | ds_location1 | 10 | | | | t
16485 | newtbs2 | 10 | | | | t
24576 | t_tbspace | 10 | | | | t <==========
(8 rows)
omm@local:/opt/huawei/tmp [postgres]=#CREATE USER test IDENTIFIED BY 'zs@123456';
CREATE ROLE
omm@local:/opt/huawei/tmp [postgres]=#GRANT CREATE ON TABLESPACE t_tbspace TO test;
GRANT
omm@local:/opt/huawei/tmp [postgres]=#\c newdb1
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "newdb1" as user "omm".
omm@local:/opt/huawei/tmp [newdb1]=#GRANT USAGE ON schema public TO test;
GRANT
omm@local:/opt/huawei/tmp [newdb1]=#GRANT CREATE ON schema public TO test;
GRANT
omm@local:/opt/huawei/tmp [newdb1]=#\c - test
Password for user test:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "newdb1" as user "test".
test@local:/opt/huawei/tmp [newdb1]=>CREATE TABLE day10(id int,name varchar(20)) TABLESPACE t_tbspace;
CREATE TABLE
复制查看表空间t_tbspace的oid和大小
test@local:/opt/huawei/tmp [newdb1]=>SELECT PG_TABLESPACE_SIZE('t_tbspace');
pg_tablespace_size
--------------------
25
(1 row)
test@local:/opt/huawei/tmp [newdb1]=>select oid,* from pg_tablespace ;
oid | spcname | spcowner | spcacl | spcoptions | spcmaxsize | relative
-------+--------------+----------+------------------------+------------+------------+----------
1663 | pg_default | 10 | | | | f
1664 | pg_global | 10 | | | | f
16393 | music_tbs | 10 | | | | t
16394 | music_tbs1 | 10 | | | | t
16469 | newtbs1 | 10 | | | | t
16470 | ds_location1 | 10 | | | | t
16485 | newtbs2 | 10 | | | | t
24576 | t_tbspace | 10 | {omm=C/omm,test=C/omm} | | | t
(8 rows)
复制查看数据库在默认表空间下有哪些对象
test@local:/opt/huawei/tmp [newdb1]=>with objectInDefaultTS as
newdb1-> ( select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),
newdb1(> reltablespace,relowner
newdb1(> from pg_class a
newdb1(> where a.relkind in ('r', 'i') and reltablespace='0'
newdb1(> )
newdb1-> select *
newdb1-> from objectInDefaultTS
newdb1-> where relname not like 'pg_%' and relname not like 'gs_%' and relname not like 'sql_%'
newdb1-> order by relpages desc;
relname | relkind | relpages | pg_size_pretty | reltablespace | relowner
------------------------------------------+---------+----------+----------------+---------------+----------
streaming_gather_agg_index | i | 2 | 16 kB | 0 | 10
statement_history_time_idx | i | 1 | 8192 bytes | 0 | 10
snapshot_id_key | i | 1 | 8192 bytes | 0 | 10
streaming_cont_query_oid_index | i | 1 | 8192 bytes | 0 | 10
streaming_cont_query_matrelid_index | i | 1 | 8192 bytes | 0 | 10
streaming_cont_query_lookupidxid_index | i | 1 | 8192 bytes | 0 | 10
streaming_cont_query_schema_change_index | i | 1 | 8192 bytes | 0 | 10
streaming_reaper_status_id_index | i | 1 | 8192 bytes | 0 | 10
streaming_reaper_status_oid_index | i | 1 | 8192 bytes | 0 | 10
snapshot_pkey | i | 1 | 8192 bytes | 0 | 10
streaming_stream_oid_index | i | 1 | 8192 bytes | 0 | 10
streaming_stream_relid_index | i | 1 | 8192 bytes | 0 | 10
streaming_cont_query_relid_index | i | 1 | 8192 bytes | 0 | 10
streaming_cont_query_defrelid_index | i | 1 | 8192 bytes | 0 | 10
streaming_cont_query_id_index | i | 1 | 8192 bytes | 0 | 10
plan_table_data | r | 0 | 0 bytes | 0 | 10
statement_history | r | 0 | 0 bytes | 0 | 10
streaming_stream | r | 0 | 0 bytes | 0 | 10
streaming_reaper_status | r | 0 | 0 bytes | 0 | 10
snapshot | r | 0 | 0 bytes | 0 | 10
streaming_cont_query | r | 0 | 0 bytes | 0 | 10
newt2 | r | 0 | 0 bytes | 0 | 16472
(22 rows)
复制查看数据库在非默认表空间下有哪些对象
test@local:/opt/huawei/tmp [newdb1]=>select relname,relkind,relpages,pg_size_pretty(pg_relation_size(a.oid)),
newdb1-> reltablespace,relowner
newdb1-> from pg_class a, pg_tablespace tb
newdb1-> where a.relkind in ('r', 'i')
newdb1-> and a.reltablespace=tb.oid
newdb1-> and tb.spcname='t_tbspace'
newdb1-> order by a.relpages desc;
relname | relkind | relpages | pg_size_pretty | reltablespace | relowner
---------+---------+----------+----------------+---------------+----------
day10 | r | 0 | 0 bytes | 24576 | 24577
(1 row)
复制重命名表空间
test@local:/opt/huawei/tmp [newdb1]=>\c - omm
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "newdb1" as user "omm".
omm@local:/opt/huawei/tmp [newdb1]=#ALTER TABLESPACE t_tbspace RENAME TO app_tbs;
ALTER TABLESPACE
omm@local:/opt/huawei/tmp [newdb1]=#select oid,* from pg_tablespace ;
oid | spcname | spcowner | spcacl | spcoptions | spcmaxsize | relative
-------+--------------+----------+------------------------+------------+------------+----------
1663 | pg_default | 10 | | | | f
1664 | pg_global | 10 | | | | f
16393 | music_tbs | 10 | | | | t
16394 | music_tbs1 | 10 | | | | t
16469 | newtbs1 | 10 | | | | t
16470 | ds_location1 | 10 | | | | t
16485 | newtbs2 | 10 | | | | t
24576 | app_tbs | 10 | {omm=C/omm,test=C/omm} | | | t <=============
(8 rows)
omm@local:/opt/huawei/tmp [newdb1]=# select relname,relkind,relpages,pg_size_pretty(pg_relation_size(a.oid)),
newdb1-# reltablespace,relowner
newdb1-# from pg_class a, pg_tablespace tb
newdb1-# where a.relkind in ('r', 'i')
newdb1-# and a.reltablespace=tb.oid
newdb1-# and tb.spcname='app_tbs'
newdb1-# order by a.relpages desc;
relname | relkind | relpages | pg_size_pretty | reltablespace | relowner
---------+---------+----------+----------------+---------------+----------
day10 | r | 0 | 0 bytes | 24576 | 24577
(1 row)
复制删除表空间
–用户必须是表空间的owner或者系统管理员才能删除表空间。需要先删除表空间的对象，再删除表空间app_ts。
omm@local:/opt/huawei/tmp [newdb1]=#drop table day10 ;
DROP TABLE
omm@local:/opt/huawei/tmp [newdb1]=#DROP TABLESPACE app_tbs;
DROP TABLESPACE
omm@local:/opt/huawei/tmp [newdb1]=#select oid,* from pg_tablespace ;
oid | spcname | spcowner | spcacl | spcoptions | spcmaxsize | relative
-------+--------------+----------+--------+------------+------------+----------
1663 | pg_default | 10 | | | | f
1664 | pg_global | 10 | | | | f
16393 | music_tbs | 10 | | | | t
16394 | music_tbs1 | 10 | | | | t
16469 | newtbs1 | 10 | | | | t
16470 | ds_location1 | 10 | | | | t
16485 | newtbs2 | 10 | | | | t
(7 rows)
复制最后修改时间：2022-12-04 12:16:26
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
目录
