openGauss每日一练第8天 | 学习表空间与数据库对象的关系

打卡第八天，心得体会

在数据库中创建的所有的表，没有指定表空间的名字，都创建在数据库默认的表空间中，当我们在数据库中创建表的时候，明确指定在表空间中创建时，这个表会存储在这个指定的表空间。

即一个数据库中的对象，可以位于不同的表空间. 这个跟oracle 相似。

课后作业

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

omm=# CREATE TABLESPACE newtbs1 RELATIVE LOCATION 'tablespace/newtbs1'; 
omm=# CREATE TABLESPACE
CREATE TABLESPACE ds_location1 RELATIVE LOCATION 'tablespace/ds_location1';
omm=# CREATE TABLESPACE
omm=# \db
omm=#               List of tablespaces
     Name     | Owner |        Location         
--------------+-------+-------------------------
 ds_location1 | omm   | tablespace/tablespace_1
 music_tbs    | omm   | tablespace/test_ts1
 newtbs1      | omm   | tablespace/newtbs1
 pg_default   | omm   | 
 pg_global    | omm   | 
(5 rows)

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

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

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

omm=# create database newdb1 with tablespace=newtbs1;
CREATE DATABASE
omm=# create user user5 identified by huawei@123;
ERROR:  Password must be quoted
omm=# create user user5 identified by 'huawei@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(col char(20)) tablespace ds_location1;
CREATE TABLE
newdb1=> 

4.查看表所在的表空间

newdb1=> \d+ newt1 
                            Table "public.newt1"
 Column |     Type      | Modifiers | Storage  | Stats target | Description 
--------+---------------+-----------+----------+--------------+-------------
 col    | character(20) |           | extended |              | 
Has OIDs: no
Tablespace: "ds_location1"
Options: orientation=row, compression=no

newdb1=> select * from pg_tables where tablename='newt1';
 schemaname | tablename | tableowner |  tablespace  | hasindexes | hasrules | hastriggers | tablecreator |           crea
ted            |        last_ddl_time         
------------+-----------+------------+--------------+------------+----------+-------------+--------------+---------------
---------------+------------------------------
 public     | newt1     | user5      | ds_location1 | f          | f        | f           | user5        | 2022-12-02 09:
37:55.43768+08 | 2022-12-02 09:37:55.43768+08
(1 row)

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

newdb1=> select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),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='ds_location1'  
newdb1-> order by a.relpages desc;
 relname | relkind | relpages | pg_size_pretty | reltablespace | relowner 
---------+---------+----------+----------------+---------------+----------
 newt1   | r       |        0 | 0 bytes        |         16390 |    16392
(1 row)
newdb1=> select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner  
newdb1-> from pg_class a  
newdb1-> where a.relkind in ('r', 'i')  
newdb1-> and reltablespace='0'  
newdb1-> order by a.relpages desc;
                    relname                     | relkind | relpages | pg_size_pretty | reltablespace | relowner 
------------------------------------------------+---------+----------+----------------+---------------+----------
 pg_attribute                                   | r       |      184 | 1472 kB        |             0 |       10
 pg_proc                                        | r       |      140 | 1120 kB        |             0 |       10
 pg_depend                                      | r       |       59 | 472 kB         |             0 |       10
 pg_class                                       | r       |       52 | 416 kB         |             0 |       10
 pg_attribute_relid_attnam_index                | i       |       44 | 352 kB         |             0 |       10
 pg_proc_proname_all_args_nsp_index             | i       |       41 | 328 kB         |             0 |       10
 pg_proc_proname_args_nsp_new_index             | i       |       39 | 312 kB         |             0 |       10
 pg_proc_proname_args_nsp_index                 | i       |       39 | 312 kB         |             0 |       10
 pg_rewrite                                     | r       |       37 | 296 kB         |             0 |       10
 pg_depend_depender_index                       | i       |       34 | 272 kB         |             0 |       10
 pg_depend_reference_index                      | i       |       34 | 272 kB         |             0 |       10