一、课堂练习

1. 连接环境

root@modb:/opt# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

2.创建表空间

omm=# CREATE TABLESPACE ds_location1 RELATIVE LOCATION 'tablespace/tablespace_1';
omm=# create role joe identified by 'abcd@123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# CREATE TABLESPACE ds_location2 OWNER joe RELATIVE LOCATION 'tablespace/tablespace_2';
CREATE TABLESPACE
omm=# \db
              List of tablespaces
     Name     | Owner |        Location         
--------------+-------+-------------------------
 ds_location1 | omm   | tablespace/tablespace_1
 ds_location2 | joe   | tablespace/tablespace_2
 pg_default   | omm   | 
 pg_global    | omm   | 
(4 rows)

3.修改表空间属性

omm=# alter tablespace ds_location1 rename to ds_location3;
ALTER TABLESPACE
omm=# create role jay identified by 'abcd@123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# alter tablespace ds_location2 owner to jay;
ALTER TABLESPACE
omm=# \db
              List of tablespaces
     Name     | Owner |        Location         
--------------+-------+-------------------------
 ds_location2 | jay   | tablespace/tablespace_2
 ds_location3 | omm   | tablespace/tablespace_1
 pg_default   | omm   | 
 pg_global    | omm   | 
(4 rows)

4.在表空间建表

omm=# create table ds_t1(id int, name char(30)) tablespace ds_location2;
CREATE TABLE
omm=# select * from pg_tables where tablename = 'ds_t1';
 schemaname | tablename | tableowner |  tablespace  | hasindexes | hasrules | h
astriggers | tablecreator |            created            |         last_ddl_ti
me         
------------+-----------+------------+--------------+------------+----------+--
-----------+--------------+-------------------------------+--------------------
-----------
 public     | ds_t1     | omm        | ds_location2 | f          | f        | f
           | omm          | 2021-12-17 17:32:34.646561+08 | 2021-12-17 17:32:34
.646561+08
(1 row)

5.删除表和表空间

omm=# drop table ds_t1;
DROP TABLE
omm=# drop tablespace if exists ds_location2;
DROP TABLESPACE
omm=# drop tablespace if exists ds_location3;
DROP TABLESPACE

二、课程作业

1.创建表空间，表空间tspc1使用相对路径指定所在目录，表空间tspc2指定owner为Lucy

omm=# create tablespace tspc1 relative location 'tablespace/tspc1';
CREATE TABLESPACE
omm=# create role lucy identified by 'abcd@123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
omm=# CREATE ROLE

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

2.在表空间tspc1中建表，并使用视图pg_tables查看信息

omm=# create table ds_t2(id int,name char(50)) tablespace tspc1;
CREATE TABLE
omm=# select * from pg_tables where tablename = 'ds_t2';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules 
| hastriggers | tablecreator |            created            |         las
t_ddl_time         
------------+-----------+------------+------------+------------+----------
+-------------+--------------+-------------------------------+------------
-------------------
 public     | ds_t2     | omm        | tspc1      | f          | f        
| f           | omm          | 2021-12-17 17:53:14.789559+08 | 2021-12-17 
17:53:14.789559+08
(1 row)

3.重命名tspc1，修改tspc2的用户为Lily，使用\db查看表空间信息

omm=# alter tablespace tspc1 rename to tspc3;
ALTER TABLESPACE
omm=# create role lily identified by 'abcd@123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# alter tablespace tspc2 owner to lily;
ALTER TABLESPACE
omm=# \db
          List of tablespaces
    Name    | Owner |     Location     
------------+-------+------------------
 pg_default | omm   | 
 pg_global  | omm   | 
 tspc2      | lily  | tablespace/tspc2
 tspc3      | omm   | tablespace/tspc1
(4 rows)

4.删除表空间

omm=# drop table ds_t2;
DROP TABLE
omm=# drop tablespace if exists tspc2;
DROP TABLESPACE
omm=# drop tablespace if exists tspc3;
DROP TABLESPACE