Altibase 常用命令

1. 常用命令

-- 查看Altibase库中所有的表
select * from tab;

--查看所有的sequence
select * from v$seq;

--给某个字段设置默认值
alter table 表名 alter column (字段名 set default '01');

 

2. 查看altibase各表使用空间，分配空间及使用率
SELECT C.USER_NAME,B.TABLE_NAME TABLE_NAME,
        A.MEM_SLOT_SIZE SLOT_SIZE,
        trunc((( FIXED_ALLOC_MEM+VAR_ALLOC_MEM) / 1024/1024),2) ALLOC_BYTE,
        trunc((( FIXED_USED_MEM+VAR_USED_MEM) / 1024/1024),2) USED_SIZE ,
        ROUND( (FIXED_USED_MEM+VAR_USED_MEM)/(FIXED_ALLOC_MEM+VAR_ALLOC_MEM)*100,2) EFFICIENCY
FROM   V$MEMTBL_INFO A ,SYSTEM_.SYS_TABLES_ B, SYSTEM_.SYS_USERS_ C
WHERE   A.TABLE_OID = B.TABLE_OID AND B.USER_ID = C.USER_ID
AND C.USER_ID != 1
AND B.TABLE_TYPE = 'T'
ORDER BY efficiency desc;

 

3. altibase表空间大小

set linesize 150
set colsize 50

SELECT c.user_name,B.TABLE_NAME, ( A.MEM_PAGE_CNT * 32 / 1024) + ( A.MEM_VAR_PAGE_CNT * 32 / 1024 ) AS ALLOC_SIZE
FROM V$MEMTBL_INFO A,
SYSTEM_.SYS_TABLES_ B,
SYSTEM_.SYS_USERS_ C
WHERE A.TABLE_OID = B.TABLE_OID
AND B.USER_ID = C.USER_ID
order by alloc_size desc ;

4. 查看所有序列的视图
select * from  v$seq;

 

5. 查看所有表的视图
select * from  system_.sys_tables_;

 

6. 查看所有用户的视图
select * from  system_.sys_users_ ;

 

7. 启停altibase
su到altibase用户，直接在命令行输入  server start可以启动altibase
su到altibase用户，直接在命令行输入  server stop可以停止altibase

 

8. 会话相关
--查询应用程序执行的是哪些sql语句
select a.query, count(*) num from v$statement  a
where a.session_id = 30926
group by a.query order by num desc;

 

--查看各个session有多少个连接
select count(*) num, session_id
from v$statement
group by session_id
order by num desc;

 

--查看altibase一共有多少个会话
select count(*) from v$session;

 

select count(*) num, comm_name, client_pid
from v$session
group by comm_name,client_pid
order by num desc
limit 5;

 

--查看连接数最多的5个进程
select a.session_id,b.comm_name, b.client_pid,count(*) num from v$statement a,v$session b
where a.session_id =b.id
group by a.session_id,b.comm_name, b.client_pid
order by num desc
limit 5;