暂无图片
暂无图片
1
暂无图片
暂无图片
1
暂无图片

openGauss每日一练第10天 | 学习心得体会

原创 英特纳熊奈尔 2022-12-03
194

一、作业

1、创建表空间t_tbspace、用户test,并使用test,在这个表空间上创建表t1:

(1)首先打开虚拟机上的数据库服务并进入初始数据库

su - omm
gs_om -t start
gsql -d postgres -p 26000 -r
复制

(2)创建表空间t_tbspace

CREATE TABLESPACE t_tbspace RELATIVE LOCATION 'tablespace/test_t_tbspace';
复制

(3)创建用户test并赋予权限

CREATE USER test IDENTIFIED BY 'kunpeng@1234';
ALTER USER test SYSADMIN;
复制

(4)使用test,在这个表空间上创建表t1

\c postgres test
CREATE TABLE t1(i int) TABLESPACE t_tbspace;
复制

2、查看表空间t_tbspace的oid和大小:

(1)查看系统有哪些表空间

select oid,* from pg_tablespace ;
复制

(2)查看表空间大小

SELECT PG_TABLESPACE_SIZE('t_tbspace');
复制

3、查看数据库在默认表空间下有哪些对象:

根据之前学习的内容,可以用下列代码查询:

--执行下面的SQL语句,查询数据库默认表空间下有哪些对象:

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_pr
etty | reltablespace | relowner
-----------------------------------------------+---------+----------+-----------
-----+---------------+----------
 pg_attribute                                  | r       |      168 | 1344 kB
     |             0 |       10
 pg_proc                                       | r       |      107 | 856 kB
     |             0 |       10
 pg_depend                                     | r       |       53 | 424 kB
     |             0 |       10
 pg_class                                      | r       |       44 | 352 kB
     |             0 |       10
 pg_attribute_relid_attnam_index               | i       |       41 | 328 kB
     |             0 |       10
 pg_rewrite                                    | r       |       38 | 304 kB
     |             0 |       10
 pg_proc_proname_args_nsp_index                | i       |       33 | 264 kB
     |             0 |       10
 pg_collation                                  | r       |       31 | 248 kB
     |             0 |       10
 pg_depend_reference_index                     | i       |       31 | 248 kB
     |             0 |       10
 pg_depend_depender_index                      | i       |       31 | 248 kB
     |             0 |       10
 pg_attribute_relid_attnum_index               | i       |       28 | 224 kB         |             0 |       10
 pg_type                                       | r       |       19 | 152 kB         |             0 |       10
 pg_statistic                                  | r       |       18 | 144 kB         |             0 |       10
 pg_description                                | r       |       17 | 136 kB         |             0 |       10
 pg_operator                                   | r       |       15 | 120 kB         |             0 |       10
 pg_class_relname_nsp_index                    | i       |       13 | 104 kB         |             0 |       10
 pg_proc_oid_index                             | i       |       12 | 96 kB          |             0 |       10
 pg_description_o_c_o_index                    | i       |       10 | 80 kB          |             0 |       10
 pg_class_tblspc_relfilenode_index             | i       |        9 | 72 kB          |             0 |       10
 pg_amop                                       | r       |        7 | 56 kB          |             0 |       10
 sql_features                                  | r       |        7 | 56 kB          |             0 |       10
 pg_class_oid_index                            | i       |        7 | 56 kB          |             0 |       10
 pg_type_typname_nsp_index                     | i       |        7 | 56 kB          |             0 |       10
 pg_collation_name_enc_nsp_index               | i       |        7 | 56 kB          |             0 |       10
 pg_index                                      | r       |        6 | 48 kB          |             0 |       10
 pg_amop_opr_fam_index                         | i       |        5 | 40 kB          |             0 |       10
 pg_amop_oid_index                             | i       |        5 | 40 kB          |             0 |       10
 pg_operator_oprname_l_r_n_index               | i       |        5 | 40 kB          |             0 |       10
 pg_type_oid_index                             | i       |        5 | 40 kB          |             0 |       10
 pg_operator_oid_index                         | i       |        5 | 40 kB          |             0 |       10
 pg_collation_oid_index                        | i       |        5 | 40 kB          |             0 |       10
 pg_amop_fam_strat_index                       | i       |        5 | 40 kB          |             0 |       10
 pg_cast_source_target_index                   | i       |        4 | 32 kB          |             0 |       10
 pg_ts_config_map_index                        | i       |        4 | 32 kB          |             0 |       10
 pg_toast_2618_index                           | i       |        4 | 32 kB          |             0 |       10
 pg_cast_oid_index                             | i       |        4 | 32 kB          |             0 |       10
 pg_rewrite_rel_rulename_index                 | i       |        4 | 32 kB          |             0 |       10
 pg_statistic_relid_kind_att_inh_index         | i       |        4 | 32 kB          |             0 |       10
 pg_amproc_oid_index                           | i       |        4 | 32 kB          |             0 |       10
 pg_amproc_fam_proc_index                      | i       |        4 | 32 kB          |             0 |       10
 pg_opclass                                    | r       |        3 | 24 kB          |             0 |       10
 pg_amproc                                     | r       |        3 | 24 kB          |             0 |       10
 pg_conversion                                 | r       |        3 | 24 kB          |             0 |       10
 pg_cast                                       | r       |        3 | 24 kB          |             0 |       10
 pg_aggregate_fnoid_index                      | i       |        2 | 16 kB          |             0 |       10
 pg_toast_2619_index                           | i       |        2 | 16 kB          |             0 |       10
 pg_ts_dict_dictname_index                     | i       |        2 | 16 kB          |             0 |       10
 pg_ts_dict_oid_index                          | i       |        2 | 16 kB          |             0 |       10
 pg_attrdef_adrelid_adnum_index                | i       |        2 | 16 kB          |             0 |       10
 pg_attrdef_oid_index                          | i       |        2 | 16 kB          |             0 |       10
 pg_constraint_conname_nsp_index               | i       |        2 | 16 kB          |             0 |       10
 pg_constraint_conrelid_index                  | i       |        2 | 16 kB          |             0 |       10
 pg_constraint_contypid_index                  | i       |        2 | 16 kB          |             0 |       10
 pg_constraint_oid_index                       | i       |        2 | 16 kB          |             0 |       10
 pg_index_indrelid_index                       | i       |        2 | 16 kB          |             0 |       10
 pg_index_indexrelid_index                     | i       |        2 | 16 kB          |             0 |       10
 pg_opfamily                                   | r       |        2 | 16 kB          |             0 |       10
 pg_opfamily_am_name_nsp_index                 | i       |        2 | 16 kB          |             0 |       10
 pg_opfamily_oid_index                         | i       |        2 | 16 kB          |             0 |       10
 pg_opclass_am_name_nsp_index                  | i       |        2 | 16 kB          |             0 |       10
 pg_opclass_oid_index                          | i       |        2 | 16 kB          |             0 |       10
 pg_am_name_index                              | i       |        2 | 16 kB          |             0 |       10
 pg_am_oid_index                               | i       |        2 | 16 kB          |             0 |       10
 pg_language_name_index                        | i       |        2 | 16 kB          |             0 |       10
 pg_language_oid_index                         | i       |        2 | 16 kB          |             0 |       10
 pg_aggregate                                  | r       |        2 | 16 kB          |             0 |       10
 streaming_gather_agg_index                    | i       |        2 | 16 kB          |             0 |       10
 pg_rewrite_oid_index                          | i       |        2 | 16 kB          |             0 |       10
 pg_namespace_nspname_index                    | i       |        2 | 16 kB          |             0 |       10
 pg_namespace_oid_index                        | i       |        2 | 16 kB          |             0 |       10
 pg_conversion_default_index                   | i       |        2 | 16 kB          |             0 |       10
 pg_conversion_name_nsp_index                  | i       |        2 | 16 kB          |             0 |       10
 pg_conversion_oid_index                       | i       |        2 | 16 kB          |             0 |       10
 pg_ts_config_cfgname_index                    | i       |        2 | 16 kB          |             0 |       10
 pg_ts_config_oid_index                        | i       |        2 | 16 kB          |             0 |       10
 pg_ts_config_map                              | r       |        2 | 16 kB          |             0 |       10
 pg_ts_parser_prsname_index                    | i       |        2 | 16 kB          |             0 |       10
 pg_ts_parser_oid_index                        | i       |        2 | 16 kB          |             0 |       10
 pg_ts_template_tmplname_index                 | i       |        2 | 16 kB          |             0 |       10
 pg_ts_template_oid_index                      | i       |        2 | 16 kB          |             0 |       10
 pg_extension_oid_index                        | i       |        2 | 16 kB          |             0 |       10
 pg_extension_name_index                       | i       |        2 | 16 kB          |             0 |       10
 pg_foreign_data_wrapper_oid_index             | i       |        2 | 16 kB          |             0 |       10
 pg_foreign_data_wrapper_name_index            | i       |        2 | 16 kB          |             0 |       10
 pg_foreign_server_oid_index                   | i       |        2 | 16 kB          |             0 |       10
 pg_foreign_server_name_index                  | i       |        2 | 16 kB          |             0 |       10
 pg_range_rngtypid_index                       | i       |        2 | 16 kB          |             0 |       10
 pg_toast_12360_index                          | i       |        1 | 8192 bytes     |             0 |       10
 pg_toast_12346_index                          | i       |        1 | 8192 bytes     |             0 |       10
 pg_toast_12310_index                          | i       |        1 | 8192 bytes     |             0 |       10
 pg_synonym_oid_index                          | i       |        1 | 8192 bytes     |             0 |       10
 pg_directory_oid_index                        | i       |        1 | 8192 bytes     |             0 |       10
 pg_directory_name_index                       | i       |        1 | 8192 bytes     |             0 |       10
 pg_toast_9026_index                           | i       |        1 | 8192 bytes     |             0 |       10
 pg_hashbucket_oid_index                       | i       |        1 | 8192 bytes     |             0 |       10
 pg_hashbucket_bid_index                       | i       |        1 | 8192 bytes     |             0 |       10
 streaming_stream_oid_index                    | i       |        1 | 8192 bytes     |             0 |       10
 streaming_stream_relid_index                  | i       |        1 | 8192 bytes     |             0 |       10
 streaming_cont_query_relid_index              | i       |        1 | 8192 bytes     |             0 |       10
 streaming_cont_query_defrelid_index           | i       |        1 | 8192 bytes     |             0 |       10
 streaming_cont_query_id_index                 | i       |        1 | 8192 bytes     |             0 |       10
 streaming_cont_query_oid_index                | i       |        1 | 8192 bytes     |             0 |       10
 streaming_cont_query_matrelid_index           | i       |        1 | 8192 bytes     |             0 |       10
 streaming_cont_query_lookupidxid_index        | i       |        1 | 8192 bytes     |             0 |       10
 streaming_cont_query_schema_change_index      | i       |        1 | 8192 bytes     |             0 |       10
 streaming_reaper_status_id_index              | i       |        1 | 8192 bytes     |             0 |       10
 streaming_reaper_status_oid_index             | i       |        1 | 8192 bytes     |             0 |       10
 gs_matview_oid_index                          | i       |        1 | 8192 bytes     |             0 |       10
 gs_matviewdep_oid_index                       | i       |        1 | 8192 bytes     |             0 |       10
 pgxc_slice_relid_index                        | i       |        1 | 8192 bytes     |             0 |       10
 pgxc_slice_order_index                        | i       |        1 | 8192 bytes     |             0 |       10
 gs_opt_model_name_index                       | i       |        1 | 8192 bytes     |             0 |       10
 pg_largeobject_loid_pn_index                  | i       |        1 | 8192 bytes     |             0 |       10
 pg_user_mapping_user_server_index             | i       |        1 | 8192 bytes     |             0 |       10
 pg_toast_9016_index                           | i       |        1 | 8192 bytes     |             0 |       10
 pg_partition_reloid_index                     | i       |        1 | 8192 bytes     |             0 |       10
 pg_partition_parentoid_index                  | i       |        1 | 8192 bytes     |             0 |       10
 pg_partition_indextblid_index                 | i       |        1 | 8192 bytes     |             0 |       10
 pg_partition_partoid_index                    | i       |        1 | 8192 bytes     |             0 |       10
 pg_attrdef                                    | r       |        1 | 8192 bytes     |             0 |       10
 pg_toast_2604_index                           | i       |        1 | 8192 bytes     |             0 |       10
 gs_auditing_policy_oid_index                  | i       |        1 | 8192 bytes     |             0 |       10
 pg_constraint                                 | r       |        1 | 8192 bytes     |             0 |       10
 pg_toast_2606_index                           | i       |        1 | 8192 bytes     |             0 |       10
 gs_auditing_policy_name_index                 | i       |        1 | 8192 bytes     |             0 |       10
 gs_auditing_policy_access_oid_index           | i       |        1 | 8192 bytes     |             0 |       10
 gs_auditing_policy_access_row_index           | i       |        1 | 8192 bytes     |             0 |       10
 pg_inherits_parent_index                      | i       |        1 | 8192 bytes     |             0 |       10
 pg_inherits_relid_seqno_index                 | i       |        1 | 8192 bytes     |             0 |       10
 pg_user_mapping_oid_index                     | i       |        1 | 8192 bytes     |             0 |       10
 gs_auditing_policy_filters_oid_index          | i       |        1 | 8192 bytes     |             0 |       10
 pg_statistic_ext_relid_kind_inh_key_index     | i       |        1 | 8192 bytes     |             0 |       10
 gs_auditing_policy_filters_row_index          | i       |        1 | 8192 bytes     |             0 |       10
 gs_auditing_policy_privileges_oid_index       | i       |        1 | 8192 bytes     |             0 |       10
 pg_toast_12332_index                          | i       |        1 | 8192 bytes     |             0 |       10
 gs_auditing_policy_privileges_row_index       | i       |        1 | 8192 bytes     |             0 |       10
 pg_am                                         | r       |        1 | 8192 bytes     |             0 |       10
 gs_policy_label_oid_index                     | i       |        1 | 8192 bytes     |             0 |       10
 gs_policy_label_name_index                    | i       |        1 | 8192 bytes     |             0 |       10
 statement_history_time_idx                    | i       |        1 | 8192 bytes     |             0 |       10
 pg_language                                   | r       |        1 | 8192 bytes     |             0 |       10
 gs_masking_policy_oid_index                   | i       |        1 | 8192 bytes     |             0 |       10
 pg_largeobject_metadata_oid_index             | i       |        1 | 8192 bytes     |             0 |       10
 gs_masking_policy_name_index                  | i       |        1 | 8192 bytes     |             0 |       10
 pg_toast_12300_index                          | i       |        1 | 8192 bytes     |             0 |       10
 pg_toast_12286_index                          | i       |        1 | 8192 bytes     |             0 |       10
 gs_masking_policy_actions_oid_index           | i       |        1 | 8192 bytes     |             0 |       10
 pg_toast_2620_index                           | i       |        1 | 8192 bytes     |             0 |       10
 pg_trigger_tgconstraint_index                 | i       |        1 | 8192 bytes     |             0 |       10
 pg_trigger_tgrelid_tgname_index               | i       |        1 | 8192 bytes     |             0 |       10
 pg_trigger_oid_index                          | i       |        1 | 8192 bytes     |             0 |       10
 pg_toast_2609_index                           | i       |        1 | 8192 bytes     |             0 |       10
 pg_toast_12001_index                          | i       |        1 | 8192 bytes     |             0 |       10
 pg_toast_11995_index                          | i       |        1 | 8192 bytes     |             0 |       10
 pg_enum_oid_index                             | i       |        1 | 8192 bytes     |             0 |       10
 pg_enum_typid_label_index                     | i       |        1 | 8192 bytes     |             0 |       10
 pg_enum_typid_sortorder_index                 | i       |        1 | 8192 bytes     |             0 |       10
 pg_namespace                                  | r       |        1 | 8192 bytes     |             0 |       10
 gs_masking_policy_actions_row_index           | i       |        1 | 8192 bytes     |             0 |       10
 gs_masking_policy_actions_policy_oid_index    | i       |        1 | 8192 bytes     |             0 |       10
 gs_masking_policy_filters_oid_index           | i       |        1 | 8192 bytes     |             0 |       10
 gs_masking_policy_filters_row_index           | i       |        1 | 8192 bytes     |             0 |       10
 pg_toast_11988_index                          | i       |        1 | 8192 bytes     |             0 |       10
 pg_toast_1255_index                           | i       |        1 | 8192 bytes     |             0 |       10
 pg_ts_config                                  | r       |        1 | 8192 bytes     |             0 |       10
 pg_toast_14458_index                          | i       |        1 | 8192 bytes     |             0 |       10
 sql_implementation_info                       | r       |        1 | 8192 bytes     |             0 |       10
 pg_toast_3220_index                           | i       |        1 | 8192 bytes     |             0 |       10
 pg_ts_parser                                  | r       |        1 | 8192 bytes     |             0 |       10
 pg_toast_14463_index                          | i       |        1 | 8192 bytes     |             0 |       10
 sql_languages                                 | r       |        1 | 8192 bytes     |             0 |       10
 pg_ts_template                                | r       |        1 | 8192 bytes     |             0 |       10
 pg_toast_14468_index                          | i       |        1 | 8192 bytes     |             0 |       10
 sql_packages                                  | r       |        1 | 8192 bytes     |             0 |       10
 pg_extension                                  | r       |        1 | 8192 bytes     |             0 |       10
 pg_toast_14473_index                          | i       |        1 | 8192 bytes     |             0 |       10
 sql_parts                                     | r       |        1 | 8192 bytes     |             0 |       10
 pg_foreign_data_wrapper                       | r       |        1 | 8192 bytes     |             0 |       10
 pg_toast_14478_index                          | i       |        1 | 8192 bytes     |             0 |       10
 sql_sizing                                    | r       |        1 | 8192 bytes     |             0 |       10
 pg_foreign_server                             | r       |        1 | 8192 bytes     |             0 |       10
 pg_toast_14483_index                          | i       |        1 | 8192 bytes     |             0 |       10
 pg_toast_9001_index                           | i       |        1 | 8192 bytes     |             0 |       10
 pgxc_class_pcrelid_index                      | i       |        1 | 8192 bytes     |             0 |       10
 pg_foreign_table_relid_index                  | i       |        1 | 8192 bytes     |             0 |       10
 pg_toast_3254_index                           | i       |        1 | 8192 bytes     |             0 |       10
 pg_rlspolicy_oid_index                        | i       |        1 | 8192 bytes     |             0 |       10
 pg_rlspolicy_polrelid_polname_index           | i       |        1 | 8192 bytes     |             0 |       10
 pg_default_acl_role_nsp_obj_index             | i       |        1 | 8192 bytes     |             0 |       10
 pg_default_acl_oid_index                      | i       |        1 | 8192 bytes     |             0 |       10
 pg_toast_3596_index                           | i       |        1 | 8192 bytes     |             0 |       10
 pg_seclabel_object_index                      | i       |        1 | 8192 bytes     |             0 |       10
 student_sname_key                             | i       |        1 | 16 kB          |             0 |    16632
 pg_ts_dict                                    | r       |        1 | 8192 bytes     |             0 |       10
 student_pkey                                  | i       |        1 | 16 kB          |             0 |    16632
 pg_range                                      | r       |        1 | 8192 bytes     |             0 |       10
 pg_toast_14488_index                          | i       |        1 | 8192 bytes     |             0 |       10
 gs_encrypted_columns_oid_index                | i       |        1 | 8192 bytes     |             0 |       10
 gs_encrypted_columns_rel_id_column_name_index | i       |        1 | 8192 bytes     |             0 |       10
 gs_column_keys_oid_index                      | i       |        1 | 8192 bytes     |             0 |       10
 gs_column_keys_name_index                     | i       |        1 | 8192 bytes     |             0 |       10
 gs_column_keys_distributed_id_index           | i       |        1 | 8192 bytes     |             0 |       10
 gs_column_keys_args_oid_index                 | i       |        1 | 8192 bytes     |             0 |       10
 gs_client_global_keys_oid_index               | i       |        1 | 8192 bytes     |             0 |       10
 gs_client_global_keys_name_index              | i       |        1 | 8192 bytes     |             0 |       10
 gs_client_global_keys_args_oid_index          | i       |        1 | 8192 bytes     |             0 |       10
 gs_asp_sampletime_index                       | i       |        1 | 8192 bytes     |             0 |       10
 pg_object_index                               | i       |        1 | 16 kB          |             0 |       10
 pg_synonym_name_nsp_index                     | i       |        1 | 8192 bytes     |             0 |       10
 gs_wlm_plan_encoding_table                    | r       |        0 | 0 bytes        |             0 |       10
 plan_table_data                               | r       |        0 | 0 bytes        |             0 |       10
 gs_masking_policy                             | r       |        0 | 0 bytes        |             0 |       10
 gs_column_keys_args                           | r       |        0 | 0 bytes        |             0 |       10
 pg_largeobject_metadata                       | r       |        0 | 0 bytes        |             0 |       10
 pg_obsscaninfo                                | r       |        0 | 0 bytes        |             0 |       10
 gs_opt_model                                  | r       |        0 | 0 bytes        |             0 |       10
 gs_client_global_keys                         | r       |        0 | 0 bytes        |             0 |       10
 gs_masking_policy_actions                     | r       |        0 | 0 bytes        |             0 |       10
 gs_wlm_plan_operator_info                     | r       |        0 | 0 bytes        |             0 |       10
 gs_auditing_policy                            | r       |        0 | 0 bytes        |             0 |       10
 gs_wlm_ec_operator_info                       | r       |        0 | 0 bytes        |             0 |       10
 gs_wlm_operator_info                          | r       |        0 | 0 bytes        |             0 |       10
 pg_trigger                                    | r       |        0 | 0 bytes        |             0 |       10
 gs_client_global_keys_args                    | r       |        0 | 0 bytes        |             0 |       10
 sql_sizing_profiles                           | r       |        0 | 0 bytes        |             0 |       10
 pgxc_class                                    | r       |        0 | 0 bytes        |             0 |       10
 gs_auditing_policy_privileges                 | r       |        0 | 0 bytes        |             0 |       10
 pg_user_mapping                               | r       |        0 | 0 bytes        |             0 |       10
 gs_asp                                        | r       |        0 | 0 bytes        |             0 |       10
 gs_wlm_user_resource_history                  | r       |        0 | 0 bytes        |             0 |       10
 pg_foreign_table                              | r       |        0 | 0 bytes        |             0 |       10
 gs_wlm_session_query_info_all                 | r       |        0 | 0 bytes        |             0 |       10
 pg_partition                                  | r       |        0 | 0 bytes        |             0 |       10
 pg_enum                                       | r       |        0 | 0 bytes        |             0 |       10
 pg_rlspolicy                                  | r       |        0 | 0 bytes        |             0 |       10
 pg_object                                     | r       |        0 | 8192 bytes     |             0 |       10
 gs_auditing_policy_filters                    | r       |        0 | 0 bytes        |             0 |       10
 pg_synonym                                    | r       |        0 | 0 bytes        |             0 |       10
 pg_default_acl                                | r       |        0 | 0 bytes        |             0 |       10
 streaming_reaper_status                       | r       |        0 | 0 bytes        |             0 |       10
 gs_wlm_instance_history                       | r       |        0 | 0 bytes        |             0 |       10
 gs_masking_policy_filters                     | r       |        0 | 0 bytes        |             0 |       10
 gs_policy_label                               | r       |        0 | 0 bytes        |             0 |       10
 pg_seclabel                                   | r       |        0 | 0 bytes        |             0 |       10
 pg_directory                                  | r       |        0 | 0 bytes        |             0 |       10
 pg_statistic_ext                              | r       |        0 | 0 bytes        |             0 |       10
 gs_auditing_policy_access                     | r       |        0 | 0 bytes        |             0 |       10
 gs_matview                                    | r       |        0 | 0 bytes        |             0 |       10
 student                                       | r       |        0 | 8192 bytes     |             0 |    16632
 pg_hashbucket                                 | r       |        0 | 0 bytes        |             0 |       10
 statement_history                             | r       |        0 | 0 bytes        |             0 |       10
 gs_matview_dependency                         | r       |        0 | 0 bytes        |             0 |       10
 pg_largeobject                                | r       |        0 | 0 bytes        |             0 |       10
 gs_encrypted_columns                          | r       |        0 | 0 bytes        |             0 |       10
 streaming_stream                              | r       |        0 | 0 bytes        |             0 |       10
 pgxc_slice                                    | r       |        0 | 0 bytes        |             0 |       10
 gs_column_keys                                | r       |        0 | 0 bytes        |             0 |       10
 pg_inherits                                   | r       |        0 | 0 bytes        |             0 |       10
 streaming_cont_query                          | r       |        0 | 0 bytes        |             0 |       10
(259 rows)
复制

或者

with objectInDefaultTS as
          ( 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'
         )
 select * 
 from objectInDefaultTS 
   where relname not like 'pg_%' and relname not like 'gs_%' and relname not like 'sql_%'
 order by relpages desc;
复制

4、查看数据库在非默认表空间下有哪些对象:

--执行下面的SQL语句,查询数据库test的非默认表空间t_tbspace下有哪些对象:
 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='t_tbspace'
   order by a.relpages desc;
复制

5、重命名表空间:

--命名表空间t_tbspace为app_tbs
ALTER TABLESPACE t_tbspace RENAME TO app_tbs;
复制

--执行下面的gsql命令,查看数据库当前的表空间信息:
\db
复制

可以看到表空间的名字已经被修改。

6、删除表空间:

用户必须是表空间的owner或者系统管理员才能删除表空间。需要先删除表空间的对象,再删除表空间app_ts:

drop table test.t1 ;
DROP TABLESPACE app_tbs;
复制

成功删除


二、心得体会

通过第10天的学习,我掌握了表空间的管理,包括创建表空间、删除表空间、重命名表空间、查看表空间的情况,对opengauss数据库表空间的认识有了提升。

「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论

墨天轮-雪宝君
暂无图片
2年前
评论
暂无图片 0
作业审核合格,一起参与21天openGauss学习打卡活动! 活动详情:https://www.modb.pro/db/551619
2年前
暂无图片 点赞
评论
目录
  • 一、作业
    • 1、创建表空间t_tbspace、用户test,并使用test,在这个表空间上创建表t1:
      • (1)首先打开虚拟机上的数据库服务并进入初始数据库
      • (2)创建表空间t_tbspace
      • (3)创建用户test并赋予权限
      • (4)使用test,在这个表空间上创建表t1
    • 2、查看表空间t_tbspace的oid和大小:
      • (1)查看系统有哪些表空间
      • (2)查看表空间大小
    • 3、查看数据库在默认表空间下有哪些对象:
    • 4、查看数据库在非默认表空间下有哪些对象:
    • 5、重命名表空间:
    • 6、删除表空间:
  • 二、心得体会