作业

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

root@modb:~# su - omm
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=# create tablespace t_tbspace relative location 'tablespace/zouyang_tbs';
CREATE TABLESPACE
--创建用户
omm=# create user test identified by 'kunpeng@1234';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
--授权
omm=# GRANT CREATE ON TABLESPACE t_tbspace TO test;
GRANT
--使用test，在这个表空间上创建表t1
omm=# \c omm test
omm=> CREATE TABLE t1 (id int(1)) TABLESPACE t_tbspace;
CREATE TABLE
omm=>

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

omm=# select oid,PG_TABLESPACE_SIZE('t_tbspace') as size from pg_tablespace where spcname='t_tbspace';
oid | size
-------+------
16389 | 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、查看数据库在非默认表空间下有哪些对象

--执行下面的SQL语句，查询数据库studentdb的非默认表空间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;

5、重命名表空间

omm=# ALTER TABLESPACE t_tbspace RENAME TO app_tbs;
ALTER TABLESPACE
omm=# \db
List of tablespaces
Name | Owner | Location
------------+-------+------------------------
app_tbs | omm | tablespace/yangkai_tbs
pg_default | omm |
pg_global | omm |
(3 rows)

6、删除表空间

--先表空间中的对象才能删除表空间
omm=# drop table test.t1;
DROP TABLE
omm=# DROP TABLESPACE app_tbs;
DROP TABLESPACE