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

--创建一个数据库 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)

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

CREATE USER user5 IDENTIFIED BY 'kunpeng@1234';
ALTER USER 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 (col1 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)

4.查看表所在的表空间

newdb1=> select tablename,tablespace from pg_tables where tablename = 'newt1';
 tablename |  tablespace  
-----------+--------------
 newt1     | ds_location1
(1 row)

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

newdb1=> select a.relname, tb.spcname  
newdb1-> from pg_class a, pg_tablespace tb  
newdb1-> where 
newdb1-> a.reltablespace=tb.oid  
newdb1-> and tb.spcname in('newtbs1','ds_location1')  
newdb1-> order by a.relpages desc;
 relname |   spcname    
---------+--------------
 newt1   | ds_location1
(1 row)