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

学习心得

今天难得上班时间有点空,带薪写个作业, 依然是先写的作业, 再看的内容, 创建表并指定表空间都是根据其他库的经验试着敲的, 有的命令等报错了再去看看内容, 感觉反而记得更深一点, 或者更有意思一点

课后作业

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

omm=# create tablespace newtbs1 relative location 'tablespace/newtbs1';
CREATE TABLESPACE
omm=# create tablespace ds_location1 relative location 'tablespace/ds_location1';
CREATE TABLESPACE
omm=# \db
              List of tablespaces
     Name     | Owner |        Location         
--------------+-------+-------------------------
 ds_location1 | omm   | tablespace/ds_location1
 newtbs1      | omm   | tablespace/newtbs1
 pg_default   | omm   | 
 pg_global    | omm   | 
(4 rows)

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

omm=# create database newdb1 with tablespace=newtbs1;
CREATE DATABASE

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

omm=# create database newdb1 with tablespace=newtbs1;
CREATE DATABASE
omm=# create user user5 identified by 'user@123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# alter user user5 sysadmin;
ALTER ROLE
omm=# \c newdb1 user5
Password for user user5: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "newdb1" as user "user5".
newdb1=> create table newt1 (i int) tablespace ds_location1;
CREATE TABLE
newdb1=> insert into newt1 values (1);
INSERT 0 1
newdb1=> select * from newt1;
 i 
---
 1
(1 row)

4.查看表所在的表空间

#创建一个表,指定库所在的默认表空间, 显示为空

newdb1=# create table newt2 (i int) tablespace newtbs1;
CREATE TABLE
newdb1=# select * from pg_tables where tablename in ('newt1', 'newt2');
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | tablecreator |            created            |         las
t_ddl_time         
------------+-----------+------------+------------+------------+----------+-------------+--------------+-------------------------------+------------
-------------------
 public     | newt1     | user5      |            | f          | f        | f           | user5        | 2022-12-01 14:49:25.102939+08 | 2022-12-01 
14:49:25.102939+08
 public     | newt2     | omm        |            | f          | f        | f           | omm          | 2022-12-01 15:07:05.680728+08 | 2022-12-01 
15:07:05.680728+08
(2 rows)

newdb1=# \db
 pg_global    | omm   | 
(4 rows)

newdb1=#               List of tablespaces
     Name     | Owner |        Location         
--------------+-------+-------------------------
 ds_location1 | omm   | tablespace/ds_location1
 newtbs1      | omm   | tablespace/newtbs1
 pg_default   | omm   | 


#创建一个表,指定其他的表空间, 则显示

newdb1=# create table newt3 (i int) tablespace ds_location1;
CREATE TABLE
newdb1=# select * from pg_tables where tablename in ('newt1', 'newt2', 'newt3');
 schemaname | tablename | tableowner |  tablespace  | hasindexes | hasrules | hastriggers | tablecreator |            created            |         l
ast_ddl_time         
------------+-----------+------------+--------------+------------+----------+-------------+--------------+-------------------------------+----------
---------------------
 public     | newt1     | user5      |              | f          | f        | f           | user5        | 2022-12-01 14:49:25.102939+08 | 2022-12-0
1 14:49:25.102939+08
 public     | newt2     | omm        |              | f          | f        | f           | omm          | 2022-12-01 15:07:05.680728+08 | 2022-12-0
1 15:07:05.680728+08
 public     | newt3     | omm        | **ds_location1 **| f          | f        | f           | omm          | 2022-12-01 15:08:51.128798+08 | 2022-12-0
1 15:08:51.128798+08
(3 rows)

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

#查看指定表空间上的对象
newdb1=>
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 in ('ds_location1','newtbs1')
order by a.relpages desc;

relname | relkind | relpages | pg_size_pretty | reltablespace | relowner 
---------+---------+----------+----------------+---------------+----------
 newt3   | r       |        0 | 0 bytes        |         16390 |       10
(1 row)

#查看默认表空间上的对象
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'  
order by a.relpages desc;