数据库运维
记录DBA学习成长历程

第12章:GBase 8s 元数据查询

文章目录

1 查询数据库

database sysmaster;

select name, is_logging, is_case_insens from sysdatabases;
>database sysmaster;
> select name, is_logging, is_case_insens from sysdatabases;



name            sysmaster
is_logging      1
is_case_insens  0

name            sysutils
is_logging      1
is_case_insens  0

name            sysuser
is_logging      1
is_case_insens  0

name            sysadmin
is_logging      1
is_case_insens  0

name            gbasedb
is_logging      1
is_case_insens  0

name            mydb
is_logging      1
is_case_insens  0

6 row(s) retrieved.

> 

2 查询数据库字符集

database sysmaster;

select * from sysdbslocale;
> database sysmaster; 
> select * from sysdbslocale;



dbs_dbsname  sysmaster
dbs_collate  en_US.819

dbs_dbsname  sysutils
dbs_collate  en_US.819

dbs_dbsname  sysuser
dbs_collate  en_US.819

dbs_dbsname  sysadmin
dbs_collate  en_US.819

dbs_dbsname  gbasedb
dbs_collate  zh_CN.57372

dbs_dbsname  mydb
dbs_collate  zh_CN.57372

6 row(s) retrieved.

> 

3 查询表

database <db_name>;

select tabid, tabname, tabtype from systables where tabid >= 100 and tabtype = 'T';
[gbasedbt@train ~]$ dbaccess - -
Your evaluation license will expire on 2022-06-09 12:00:00
> database mydb;

Database selected.

> create table t_user(f_userid int, f_username varchar(20));

Table created.

> select tabid, tabname, tabtype from systables where tabid >= 100 and tabtype = 'T';



tabid    102
tabname  t_user
tabtype  T

1 row(s) retrieved.

> 

4 查询列

database <db_name>;

select colname from syscolumns where tabid = <tab_id>;

select colname, coltype, coltypename from syscolumnsext where tabid = <tab_id> order by colno;
> database mydb;

Database closed.


Database selected.

> select colname from syscolumns where tabid = 102;



colname  f_userid

colname  f_username

2 row(s) retrieved.

> select colname, coltype, coltypename from syscolumnsext where tabid = 102 order by colno;



colname      f_userid
coltype      2
coltypename  INTEGER 

colname      f_username
coltype      13
coltypename  VARCHAR(20) 

2 row(s) retrieved.

> 


5 查询视图

database <db_name>;

select tabname,tabtype from systables where tabid >= 100 and tabtype = 'V';
> database mydb;

Database closed.


Database selected.

> create view v_user as select * from t_user;

View created.

> select tabname,tabtype from systables where tabid >= 100 and tabtype = 'V';



tabname  v_user
tabtype  V

1 row(s) retrieved.

> 

6 查询表的索引

database <db_name>;

select tabid, idxname,tabid,idxtype from sysindexes where tabid = <tab_id>;
> database mydb;

Database closed.


Database selected.

> create index idx_user_name on t_user(f_username asc);

Index created.

> select tabid, idxname,tabid,idxtype from sysindexes where tabid = 102;



tabid    102
idxname  idx_user_name
tabid    102
idxtype  D

1 row(s) retrieved.

> 

7 查询触发器

database <db_name>;

select * from systriggers;
> database mydb;

Database closed.


Database selected.

> create table t_log(f_logid serial, f_message varchar(50), f_operatedate date);

Table created.

> create table t_sale(f_saleid serial, f_productname varchar(20), f_qty int);

Table created.

create or replace trigger trg_sale_insert insert on t_sale
referencing new as new_item
for each row 
(
insert into t_log(f_message, f_operatedate) values(concat('insert:', new_item.f_productname), today)
);

Trigger created.

> select * from systriggers;



trigid     1
trigname   trg_sale_insert
owner      gbasedbt
tabid      107
event      I
old        
new        new_item
mode       O
collation  zh_CN.57372

1 row(s) retrieved.

> 
 

8 存储过程

database <db_name>;

select procname, procid from sysprocedures where procname like '<key_word>%';
> database mydb;

Database closed.


Database selected.

create procedure up_useradd(userid int, username varchar(20))
insert into t_user values(userid, username);
end procedure;

Routine created.

> select procname, procid from sysprocedures where procname like 'up_%';   

procname  up_useradd
procid    636

1 row(s) retrieved.

> 

9 函数

database <db_name>;

select * from sysprocedures where procname like '<key_word>%';
database mydb;

create function fn_user_add(user_num int)
returning int as userid, varchar(20) as username

define i int;
define userid int;
define username varchar(20);

for i = 1 to user_num
    let userid = i;
    let username = concat('user_', to_char(i));
    
    return userid, username with resume;
end for;
    
end function;

> database mydb;

Database closed.


Database selected.

> create function fn_user_add(user_num int)
> returning int as userid, varchar(20) as username
> 
> define i int;
> define userid int;
> define username varchar(20);
> 
> for i = 1 to user_num
>     let userid = i;
>     let username = concat('user_', to_char(i));
>     
>     return userid, username with resume;
> end for;
>     
> end function;

Routine created.

> select * from sysprocedures where procname like 'fn_%';

procname        fn_user_add
owner           gbasedbt
procid          638
mode            O
retsize         239
symsize         941
datasize        982
codesize        152
numargs         1
isproc          f
specificname    
externalname    
paramstyle      I
langid          2
paramtypes      integer 
variant         t
client          f
handlesnulls    t
iterator        t
percallcost     0
commutator      
negator         
selfunc         
internal        f
class           
stack           
parallelizable  f
costfunc        
selconst        0.00
collation       zh_CN.57372
procflags       0
type            0
belongid        0

1 row(s) retrieved.

> 


10 同义词

database <db_name>;

select * from syssyntable where tabname is not null;

select a.tabid, a.btabid, b.tabname as syn_name, c.tabname as tab_name
from
(select * from syssyntable where tabname is null) a
inner join systables b
on a.tabid = b.tabid
inner join systables c
on a.btabid = c.tabid;

> database mydb;

Database closed.


Database selected.

> create synonym syn_user for t_user;

Synonym created.

> select a.tabid, a.btabid, b.tabname as syn_name, c.tabname as tab_name
> from
> (select * from syssyntable where tabname is null) a
> inner join systables b
> on a.tabid = b.tabid
> inner join systables c
> on a.btabid = c.tabid;



tabid     105
btabid    102
syn_name  syn_user
tab_name  t_user

1 row(s) retrieved.

> 

11 约束

drop table if exists t_dept;

create table t_dept(f_deptid int primary key constraint pk_dept_deptid,
    f_deptname varchar(20) not null unique constraint uni_dept_deptname);

drop table if exists t_employee;
    
create table t_employee(f_employeeid int primary key constraint pk_employee_employeeid,
    f_deptid int references t_dept(f_deptid),
    f_employeename varchar(20) not null,
    f_showname varchar(20) not null unique constraint uni_employee_showname,
    f_age int default 18 check (f_age >0 and f_age <= 120),
    f_employeedate date default today);

—11.1 主键

database <db_name>;

select * from sysconstraints where constrtype = 'P';
> select * from sysconstraints where constrtype = 'P';

constrid    5
constrname  pk_dept_deptid
owner       gbasedbt
tabid       109
constrtype  P
idxname      109_5
collation   zh_CN.57372

constrid    8
constrname  pk_employee_employeeid
owner       gbasedbt
tabid       110
constrtype  P
idxname      110_8
collation   zh_CN.57372

2 row(s) retrieved.

> 


—11.2 外键

database <db_name>;

select * from sysconstraints where constrtype = 'R';
> select * from sysconstraints where constrtype = 'R';



constrid    10
constrname  r110_10
owner       gbasedbt
tabid       110
constrtype  R
idxname      110_10
collation   zh_CN.57372

1 row(s) retrieved.

> 

—11.3 唯一索引

database <db_name>;

select * from sysconstraints where constrtype = 'U';
> select * from sysconstraints where constrtype = 'U';



constrid    6
constrname  uni_dept_deptname
owner       gbasedbt
tabid       109
constrtype  U
idxname      109_6
collation   zh_CN.57372

constrid    9
constrname  uni_employee_showname
owner       gbasedbt
tabid       110
constrtype  U
idxname      110_9
collation   zh_CN.57372

2 row(s) retrieved.

> 

—11.4 NULL

database <db_name>;

select * from sysconstraints where constrtype = 'N';

> select * from sysconstraints where constrtype = 'N';



constrid    1
constrname  n106_1
owner       gbasedbt
tabid       106
constrtype  N
idxname     
collation   zh_CN.57372

constrid    2
constrname  n107_2
owner       gbasedbt
tabid       107
constrtype  N
idxname     
collation   zh_CN.57372

constrid    7
constrname  n109_7
owner       gbasedbt
tabid       109
constrtype  N
idxname     
collation   zh_CN.57372

constrid    17
constrname  n111_17
owner       gbasedbt
tabid       111
constrtype  N
idxname     
collation   zh_CN.57372

constrid    18
constrname  n111_18
owner       gbasedbt
tabid       111
constrtype  N
idxname     
collation   zh_CN.57372

5 row(s) retrieved.

> 

—11.5 默认值

database <db_name>;

select * from sysdefaults where tabid = <tab_id>;
> select * from sysdefaults where tabid = 111;



tabid    111
colno    5
type     L
default  AAAAEg 18
class    T

tabid    111
colno    6
type     T
default  
class    T

2 row(s) retrieved.

> 

—11.6 Check

database <db_name>;

select * from sysconstraints where constrtype = 'C';

select * from syschecks where type = 'T' and constrid = <constr_id>;
> select * from sysconstraints where constrtype = 'C';



constrid    13
constrname  c110_13
owner       gbasedbt
tabid       110
constrtype  C
idxname     
collation   zh_CN.57372

1 row(s) retrieved.

> select * from syschecks where type = 'T' and constrid = 13;


   constrid type  seqno checktext                        

         13 T         0 ((f_age > 0 ) AND (f_age <= 120 
         13 T         1 ) )                             

2 row(s) retrieved.

> 

12 查看数据库空间

database sysmaster;

select * from sysdbspaces;
database sysmaster;
select first 3 dbsnum, name, pagesize, fchunk, nchunks, is_temp, is_blobspace, is_sbspace from sysdbspaces;



dbsnum        1
name          rootdbs
pagesize      2048
fchunk        1
nchunks       1
is_temp       0
is_blobspace  0
is_sbspace    0

dbsnum        2
name          llogdbs
pagesize      2048
fchunk        2
nchunks       1
is_temp       0
is_blobspace  0
is_sbspace    0

dbsnum        3
name          plogdbs
pagesize      2048
fchunk        3
nchunks       1
is_temp       0
is_blobspace  0
is_sbspace    0

3 row(s) retrieved.

> 

13 查看Chunk

database sysmaster;

select * from syschunks;
> select first 3 chknum, dbsnum, pagesize, chksize, nfree, fname from syschunks;



chknum    1
dbsnum    1
pagesize  2048
chksize   102400
nfree     91498
fname     /opt/gbase/gbaseserver_dbs/rootdbs

chknum    2
dbsnum    2
pagesize  2048
chksize   102400
nfree     2347
fname     /opt/gbase/gbaseserver_dbs/llogdbs

chknum    3
dbsnum    3
pagesize  2048
chksize   102400
nfree     2947
fname     /opt/gbase/gbaseserver_dbs/plogdbs

3 row(s) retrieved.

> 

14 查看VP

database sysmaster;

select * from sysvplst;
> select vpid, pid, classname, thread_run, thread_idle from sysvplst;


       vpid         pid classname               thread_run    thread_idle 

          1       46724 cpu                 11.01166385829 11.39935341122
          2       46725 adm                           0.00           0.00
          3       46726 lio                 0.000877477935 0.218293923715
          4       46727 pio                 0.001072042145 0.218138731374
          5       46728 aio                 0.123853351805 43880.80047570
          6       46729 msc                 0.004894495352 72999.78886798
          7       46730 fifo                0.002760741873 0.217946357779
          8       46731 cpu                 5.522930518951 87736.87219598
          9       46732 aio                 0.138658671953 1109.742524466
         10       46733 aio                 0.108724871203 1109.552496034
         11       46734 aio                 0.084435370625 1109.358623967
         12       46735 aio                 0.057563820997 1109.162046009
         13       46736 aio                 0.148795890732 1108.849970110

13 row(s) retrieved.

> 

15 查看物理日志

database sysmaster;

select * from sysplog;
> select * from sysplog;



pl_mutex        1142321768
pl_b1mutex      1142321896
pl_b1condition  1142322024
pl_b1used       0
pl_b1copied     0
pl_b1buffer     1171687424
pl_b1wtlist     0
pl_b2mutex      1142322160
pl_b2condition  1142322288
pl_b2used       0
pl_b2copied     0
pl_b2buffer     1173805056
pl_b2wtlist     0
pl_curbp        1142322160
pl_otherbp      1142321896
pl_bufsize      1024
pl_stamp        403270
pl_chunk        3
pl_offset       53
pl_physize      99400
pl_phypos       14041
pl_phyused      0
pl_phyarch      0

1 row(s) retrieved.

> 

16 查看逻辑日志

database sysmaster;

select * from syslogs;
> select first 3 * from syslogs;

number          4
uniqid          0
size            5000
used            0
is_used         0
is_current      0
is_backed_up    0
is_new          0
is_archived     0
is_temp         0
is_pre_dropped  0
flags           0

number          5
uniqid          0
size            5000
used            0
is_used         0
is_current      0
is_backed_up    0
is_new          0
is_archived     0
is_temp         0
is_pre_dropped  0
flags           0

number          6
uniqid          9
size            5000
used            1262
is_used         1
is_current      0
is_backed_up    1
is_new          0
is_archived     1
is_temp         0
is_pre_dropped  0
flags           21

3 row(s) retrieved.

> 

17 查看会话

database sysmaster;

select * from syssessions;
> database sysmaster;

Database selected.

> select * from syssessions;

sid         47
username    gbasedbt
uid         1001
pid         58243
hostname    192.168.3.6
tty         /dev/pts/2
connected   1625878111
feprogram   /opt/gbase/bin/dbaccess
pooladdr    1217949760
is_wlatch   0
is_wlock    0
is_wbuff    0
is_wckpt    0
is_wlogbuf  0
is_wtrans   0
is_monitor  0
is_incrit   0
state       524289

sid         48
username    gbasedbt
uid         1001
pid         55990
hostname    devsvr
tty         /dev/pts/0
connected   1625878125
feprogram   /opt/gbase/bin/dbaccess
pooladdr    1216577600
is_wlatch   0
is_wlock    0
is_wbuff    0
is_wckpt    0
is_wlogbuf  0
is_wtrans   0
is_monitor  0
is_incrit   0
state       524321

2 row(s) retrieved.

> 

18 用户

select * from sysusers;
> select * from sysusers;

username  gbasedbt
usertype  D
priority  9
password  
defrole   

username  public
usertype  C
priority  5
password  
defrole   

2 row(s) retrieved.

> 

 

赞(0)
MySQL学习笔记 » 第12章:GBase 8s 元数据查询