openGauss每日一练第7天|学习心得

终于来到表空间了！！！学习一门新技术还是需要花点时间的，虽然语法方面大体都一样

1.登录数据库环境

Welcome to 墨天轮.
This is Web Terminal of modb.pro; Good Good Study, Day Day Up.

root@modb:~# 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';
CREATE TABLESPACE

omm=# CREATE ROLE joe IDENTIFIED BY 'abce@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

设置ds_location2表空间的owner为joe

omm=# CREATE TABLESPACE ds_location2 OWNER joe RELATIVE LOCATION 'tablespace/tablespace_2';
CREATE TABLESPACE

3.查看表空间信息
omm=# \db
(4 rows)

omm=# 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.重命名表空间ds_location1
omm=# ALTER TABLESPACE ds_location1 RENAME TO ds_location3;
ALTER TABLESPACE

5.创建角色jay并创建表空间ds_location2设置owner为jay

omm=# CREATE ROLE jay IDENTIFIED BY 'abcd@789';
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)

6.创建表并设置表空间为ds_location2
omm=# create table ds_t1(id int, name char(30)) tablespace ds_location2;
CREATE TABLE

7.通过查看视图pg_tables表名为ds_t1的信息
omm=# select * from pg_tables where tablename = 'ds_t1';
public | ds_t1 | omm | ds_location2 | f | f | f | omm | 2021-12-15 09:37:36.82366
9+08 | 2021-12-15 09:37:36.823669+08
(1 row)

schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | tablecreator | created
| last_ddl_time
------------+-----------+------------+--------------+------------+----------+-------------+--------------+--------------------------
-----+-------------------------------

8.删除表及表空间

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
omm=#


作业

omm=# CREATE TABLESPACE tspc1 RELATIVE LOCATION 'tablespace/tspc1';
CREATE TABLESPACE

omm=# CREATE ROLE Lucy IDENTIFIED BY 'abce@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

omm=# CREATE TABLESPACE tspc2 owner Lucy RELATIVE LOCATION 'tablespace/tspc2';
CREATE TABLESPACE


omm=# create table table_tspc1(id int, name char(30)) tablespace tspc1;
CREATE TABLE

omm=# select * from pg_tables where tablename='table_tspc1';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | tablecreator | created
| last_ddl_time
------------+-------------+------------+------------+------------+----------+-------------+--------------+--------------------------
-----+-------------------------------
public | table_tspc1 | omm | tspc1 | f | f | f | omm | 2021-12-15 09:42:26.20370
2+08 | 2021-12-15 09:42:26.203702+08
(1 row)

omm=#

omm=# drop tablespace tspc1_new;
omm=# ERROR: tablespace "tspc1_new" is not empty

omm=# drop table table_tspc1;
omm=# DROP TABLE

omm=# \db
List of tablespaces
Name | Owner | Location
------------+-------+------------------
pg_default | omm |
pg_global | omm |
tspc1_new | omm | tablespace/tspc1
(3 rows)

omm=# drop tablespace tspc1_nwe;
ERROR: Tablespace "tspc1_nwe" does not exist.
omm=# drop tablespace tspc1_new;
DROP TABLESPACE
omm=#



omm=# ALTER TABLESPACE tspc1 RENAME TO tspc1_new;
ALTER TABLESPACE
omm=# CREATE ROLE Lily IDENTIFIED BY 'abcd@789';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# ALTER TABLESPACE tspc2 OWNER TO Lily;
ALTER TABLESPACE
omm=# \db
omm=# List of tablespaces
Name | Owner | Location
------------+-------+------------------
pg_default | omm |
pg_global | omm |
tspc1_new | omm | tablespace/tspc1
tspc2 | lily | tablespace/tspc2
(4 rows)


omm=#
drop table table_tspc1
DROP TABLESPACE IF EXISTS tspc1_new;
omm=# DROP TABLESPACE IF EXISTS tspc2;
DROP TABLESPACE

这次作业不写注释了，你能看懂了吗？