openGauss每日一练第8天 - 学习心得体会

1. 创建表空间newtbs1、 ds_location1，查看表空间

CREATE TABLESPACE newtbs1 RELATIVE LOCATION 'tablespace/test_ts1';
CREATE TABLESPACE ds_location1 RELATIVE LOCATION 'tablespace/test_ts2';
\db
--查看表空间
omm=# \db
            List of tablespaces
     Name     | Owner |      Location       
--------------+-------+---------------------
 ds_location1 | omm   | tablespace/test_ts2
 newtbs1      | omm   | tablespace/test_ts1
 pg_default   | omm   | 
 pg_global    | omm   | 
(4 rows)

2.创建一个数据库newdb1，默认表空间为newtbs1

CREATE DATABASE newdb1  WITH TABLESPACE = newtbs1;

3.创建用户user5，并授予SYSADMIN权限，访问数据库newdb1，在表空间ds_location1上，创建一个表newt1（表结构自定义）

--执行下面的SQL语句，创建用户user5：

 CREATE USER user5 IDENTIFIED BY 'kunpeng@1234';
 
--授予user1数据库系统的SYSADMIN权限：

ALTER USER user5 SYSADMIN;

--在表空间ds_location1上，创建一个表newt1
\c newdb1 user5

 create table newt1 (col1 char(10)) tablespace ds_location1;

4.查看表所在的表空间

select * from pg_tables where tablename = 'newt1';
 schemaname | tablename | tableowner |  tablespace  | hasindexes | hasrules | hastriggers | tablecreator |           c
reated            |        last_ddl_time         
------------+-----------+------------+--------------+------------+----------+-------------+--------------+------------
------------------+------------------------------
 public     | newt1     | user5      | ds_location1 | f          | f        | f           | user5        | 2022-12-01 
20:54:34.38468+08 | 2022-12-01 20:54:34.38468+08
(1 row)

5.查看表空间newtbs1、 ds_location1上的对象

\c newdb1 user5
-- 查看表空间newtbs1上的对象
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='newdb1'  
order by a.relpages desc;

 relname | relkind | relpages | pg_size_pretty | reltablespace | relowner 
---------+---------+----------+----------------+---------------+----------
(0 rows)
--查看表空间ds_location1上的对象
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='ds_location1'  
order by a.relpages desc;
newdb1=>  relname | relkind | relpages | pg_size_pretty | reltablespace | relowner 
---------+---------+----------+----------------+---------------+----------
 newt1   | r       |        0 | 0 bytes        |         16390 |    16393
(1 row)