课后作业

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

omm=# create tablespace newtbs1 RELATIVE LOCATION 'tablespace/yangkai_newtbs1';             
CREATE TABLESPACE
omm=# create tablespace ds_location1 RELATIVE LOCATION 'tablespace/yangkai_location1';
CREATE TABLESPACE
omm=# \db
             List of tablespaces
 Name     | Owner |           Location           
omm=# --------------+-------+------------------------------
ds_location1 | omm   | tablespace/yangkai_location1
newtbs1      | omm   | tablespace/yangkai_newtbs1
pg_default   | omm   | 
pg_global    | omm   | 
(4 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 'kunpeng@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# alter user user5 sysadmin;
ALTER ROLE
omm=# 

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 varchar(20)) tablespace ds_location1;
CREATE TABLE
newdb1=> 

4.查看表所在的表空间

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

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 in('newtbs1','ds_location1') order by a.relpages desc;