学习目标

    学习表空间与数据库对象的关系。
    在musicdb数据库中创建的所有的表，没有指定表空间的名字，因此都创建在数据库默认的表空间music_tbs中，当我们在musicdb数据库中创建表warehouse_t1的时候，明确指定在表空间ds_location1中创建时，这个表会存储在这个指定的表空间。即一个数据库中的对象，可以位于不同的表空间.

课程学习

    1.连接数据库，检查数据库环境，准备测试环境

    2.创建表空间、查看表空间

    3.使用user1用户，访问musicdb数据库 

    4.查看musicdb数据库目前有哪些表

    5.查询表在哪个表空间

    6.查看openGuass数据库的默认表空间

    7.查询数据库的默认表空间上的对象

    8.查询表空间ds_location1上的对像

实验环境准备

    连接数据库并检查数据库环境过程：

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=# \db
      List of tablespaces
    Name    | Owner | Location 
------------+-------+----------
 pg_default | omm   | 
 pg_global  | omm   | 
(2 rows)

omm=# \du
                                                              List of roles
 Role name |                                                    Attributes                                                    | Member of
 
-----------+------------------------------------------------------------------------------------------------------------------+----------
-
 gaussdb   | Sysadmin                                                                                                         | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}

omm=# \l
                         List of databases
   Name    | Owner | Encoding | Collate | Ctype | Access privileges 
-----------+-------+----------+---------+-------+-------------------
 omm       | omm   | UTF8     | C       | C     | 
 postgres  | omm   | UTF8     | C       | C     | 
 template0 | omm   | UTF8     | C       | C     | =c/omm           +
           |       |          |         |       | omm=CTc/omm
 template1 | omm   | UTF8     | C       | C     | =c/omm           +
           |       |          |         |       | omm=CTc/omm
(4 rows)

omm=# 

    测试环境准备过程：

omm=# CREATE TABLESPACE music_tbs RELATIVE LOCATION 'tablespace/test_ts1';
CREATE TABLESPACE
omm=# CREATE DATABASE musicdb  WITH TABLESPACE = music_tbs;
CREATE DATABASE
omm=# --执行下面的SQL语句，创建用户user1：
omm=#  CREATE USER user1 IDENTIFIED BY 'xiaocx_1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE--授予user1数据库系统的SYSADMIN权限：
omm=# ALTER USER user1 SYSADMIN;
ALTER ROLE
omm=# 

2.创建表空间、查看表空间

--执行下面的命令，查看当前表空间：
omm=# \db
           List of tablespaces
    Name    | Owner |      Location       
------------+-------+---------------------
 music_tbs  | omm   | tablespace/test_ts1
 pg_default | omm   | 
 pg_global  | omm   | 
(3 rows)
--创建一个新的名为ds_location1的表空间：
omm=#  CREATE TABLESPACE ds_location1 RELATIVE LOCATION 'tablespace/tablespace_1';
CREATE TABLESPACE
--执行下面的命令，查看当前表空间：
omm=# \db
              List of tablespaces
     Name     | Owner |        Location         
--------------+-------+-------------------------
 ds_location1 | omm   | tablespace/tablespace_1
 music_tbs    | omm   | tablespace/test_ts1
 pg_default   | omm   | 
 pg_global    | omm   | 
(4 rows)
omm=# 

3.使用user1用户，访问musicdb数据库 ，在表空间ds_location1上创建表warehouse_t1:

omm=# \c musicdb user1
Password for user user1: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb" as user "user1".
musicdb=>  create table warehouse_t1 (col1 char(10)) tablespace ds_location1;
CREATE TABLE
musicdb=>

4.查看musicdb数据库目前有哪些表：

musicdb=> select table_catalog, table_schema, table_name, table_type from information_schema.tables where table_schema not in ('pg_catalog', 'information_schema','dbe_perf');
 table_catalog |  table_schema   |  table_name  | table_type 
---------------+-----------------+--------------+------------
 musicdb       | db4ai           | snapshot     | BASE TABLE
 musicdb       | dbe_pldeveloper | gs_errors    | BASE TABLE
 musicdb       | dbe_pldeveloper | gs_source    | BASE TABLE
 musicdb       | public          | warehouse_t1 | BASE TABLE
(4 rows)

musicdb=>

5.查询表在那个表空间

    系统表在默认表空间，非系统表在指定的表空间中（否则在默认表空间）
    建表warehouse_t1指定表空间ds_location1，查看表warehouse_t1所在的表空间：

musicdb=> select * from pg_tables where tablename = 'warehouse_t1';
 schemaname |  tablename   | tableowner |  tablespace  | hasindexes | hasrules | hastriggers | tablecreator |            created         
   |         last_ddl_time         
------------+--------------+------------+--------------+------------+----------+-------------+--------------+----------------------------
---+-------------------------------
 public     | warehouse_t1 | user1      | ds_location1 | f          | f        | f           | user1        | 2022-12-01 10:29:57.356602+
08 | 2022-12-01 10:29:57.356602+08
(1 row)

musicdb=>

    创建表warehouse_t12未指定表空间，则在默认表空间（不显示默认表空间名）

musicdb=> create table warehouse_t12 (col1 char(10));
CREATE TABLE
musicdb=> select * from pg_tables where tablename = 'warehouse_t12';
 schemaname |   tablename   | tableowner | tablespace | hasindexes | hasrules | hastriggers | tablecreator |            created          
  |         last_ddl_time         
------------+---------------+------------+------------+------------+----------+-------------+--------------+-----------------------------
--+-------------------------------
 public     | warehouse_t12 | user1      |            | f          | f        | f           | user1        | 2022-12-01 10:35:14.821223+0
8 | 2022-12-01 10:35:14.821223+08
(1 row)

musicdb=> 

6.查看openGuass数据库的默认表空间

musicdb=> select datname,dattablespace,spcname from pg_database d, pg_tablespace t where d.dattablespace=t.oid;
  datname  | dattablespace |  spcname   
-----------+---------------+------------
 template1 |          1663 | pg_default
 omm       |          1663 | pg_default
 musicdb   |         16389 | music_tbs
 template0 |          1663 | pg_default
 postgres  |          1663 | pg_default
(5 rows)

musicdb=>

7.查询数据库的默认表空间上的对象

musicdb=> 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;
                    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_rewrite                                     | r       |       37 | 296 kB         |             0 |       10
 pg_depend_depender_index                       | i       |       34 | 272 kB         |             0 |       10
 pg_attribute_relid_attnum_index                | i       |       30 | 240 kB         |             0 |       10
 pg_type                                        | r       |       20 | 160 kB         |             0 |       10
 pg_statistic                                   | r       |       19 | 152 kB         |             0 |       10
 pg_proc_proname_args_nsp_index                 | i       |       39 | 312 kB         |             0 |       10
 pg_description_o_c_o_index                     | i       |       16 | 128 kB         |             0 |       10
 pg_depend_reference_index                      | i       |       34 | 272 kB         |             0 |       10
 pg_description                                 | r       |       31 | 248 kB         |             0 |       10
 pg_operator                                    | r       |       15 | 120 kB         |             0 |       10
 pg_class_relname_nsp_index                     | i       |       14 | 112 kB         |             0 |       10
 pg_proc_oid_index                              | i       |       13 | 104 kB         |             0 |       10
 pg_amop                                        | r       |        9 | 72 kB          |             0 |       10
 pg_class_tblspc_relfilenode_index              | i       |       10 | 80 kB          |             0 |       10
--More-- 

8.查询表空间ds_location1上的对像

musicdb=> 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;
   relname    | relkind | relpages | pg_size_pretty | reltablespace | relowner 
--------------+---------+----------+----------------+---------------+----------
 warehouse_t1 | r       |        0 | 0 bytes        |         16395 |    16391
(1 row)

课后练习

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

omm=# CREATE TABLESPACE newtbs1 RELATIVE LOCATION 'tablespace/newtbs1';
CREATE TABLESPACEomm=# 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)
omm=# 

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

omm=# CREATE DATABASE newdb1  WITH TABLESPACE = newtbs1;
CREATE DATABASE
omm=# \l                         List of databases
   Name    | Owner | Encoding | Collate | Ctype | Access privileges 
-----------+-------+----------+---------+-------+-------------------
 newdb1    | omm   | UTF8     | C       | C     | 
 omm       | omm   | UTF8     | C       | C     | 
 postgres  | omm   | UTF8     | C       | C     | 
 template0 | omm   | UTF8     | C       | C     | =c/omm           +
           |       |          |         |       | omm=CTc/omm
 template1 | omm   | UTF8     | C       | C     | =c/omm           +
           |       |          |         |       | omm=CTc/omm
(5 rows)
omm=# 

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

omm=# CREATE USER user5 IDENTIFIED BY 'xiaocx_1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# ALTER USER user5 SYSADMIN;ALTER ROLE
omm=# \du
                                                              List of roles
 Role name |                                                    Attributes                                                    | Member of
 
-----------+------------------------------------------------------------------------------------------------------------------+----------
-
 gaussdb   | Sysadmin                                                                                                         | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
 user5     | Sysadmin                                                                                                         | {}

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 (id int,name char(10)) tablespace ds_location1;
CREATE TABLE
newdb1=> \dt
                         List of relations
 Schema | Name  | Type  | Owner |             Storage              
--------+-------+-------+-------+----------------------------------
 public | newt1 | table | user5 | {orientation=row,compression=no}
(1 row)

newdb1=> 

4.查看表所在的表空间

newdb1=> select * from pg_tables where tablename = 'newt1';
 schemaname | tablename | tableowner |  tablespace  | hasindexes | hasrules | hastriggers | tablecreator |            created            
|         last_ddl_time         
------------+-----------+------------+--------------+------------+----------+-------------+--------------+-------------------------------
+-------------------------------
 public     | newt1     | user5      | ds_location1 | f          | f        | f           | user5        | 2022-12-01 11:13:12.466822+08 
| 2022-12-01 11:13:12.466822+08
(1 row)

newdb1=>

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='ds_location1'  order by a.relpages desc;
 relname | relkind | relpages | pg_size_pretty | reltablespace | relowner 
---------+---------+----------+----------------+---------------+----------
 newt1   | r       |        0 | 0 bytes        |         16402 |    16405
(1 row)

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='newtbs1'  order by a.relpages desc;
 relname | relkind | relpages | pg_size_pretty | reltablespace | relowner 
---------+---------+----------+----------------+---------------+----------
(0 rows)

newdb1=>