kingbase数据库使用遇到的问题以及解决办法【随时更新】

您所在的位置：网站首页 › 人大金仓数据库缺点有哪些 › kingbase数据库使用遇到的问题以及解决办法【随时更新】

kingbase数据库使用遇到的问题以及解决办法【随时更新】

2024-07-10 19:04| 来源: 网络整理| 查看: 265

觉得不错点赞收藏下吧随时更新哦有问题可以交流

1. 数据库的启停

到 Server/bin 目录下执行进入数据库：ksql -U用户名 -W -d数据库 1. 起库：sys_ctl -D /home/kingbase/data start 停库：sys_ctl -D /home/kingbase/data stop

2.初始化数据库实例：

initdb -USYSTEM -x 密码 -D /abc/data (大小写敏感) initdb -USYSTEM -x 密码 -D /abc/data --enable-ci V008R006 C005 之后的版本适用 (大小写不敏感 on是不敏感) initdb -USYSTEM -x 密码 -D /abc/data --case-insensitive V008R006 C004 之前适用 (大小写不敏感 on敏感 )

3.更改 search_path 解决查表不加模式名时报错：关系 xxx 不存在

ALTER DATABASE 数据库名 SET search_path to "$user", 模式名, public, sys_catalog, sys, pg_catalog; 然后执行 select sys_reload_conf(); 重载配置文件。

4.设置返回字段大写

ALTER DATABASE 数据库名 SET enable_upper_colname=on; 然后执行 select sys_reload_conf(); 重载配置文件。

5.兼容MySQL的 GROUP BY 子句中缺省字段名的语法

ALTER DATABASE 数据库名 SET sql_mode=''; 然后执行 select sys_reload_conf(); 重载配置文件。

6.忘记或没有设置 SYSTEM 密码导致无法登录数据库

先去 data 目录下找到 sys_hba.conf 打开后找到最下面 IPv4 local connections: 项，到第一行，把 scram-sha-256 改为 trust 然后保存，重启数据库后免密登录数据库，再执行下面的语句修改system 密码 ALTER USER system PASSWORD ' 新密码 ';

7.kingbase7去除死锁的语句是什么？已经查到pid

SELECT sys_cancel_backend(pid);

8.windows跟换授权后显示write license control file faild

修改目录权限

9.windows中54321端口被占用

netstat -ano |findstr '54321' tasklist | findstr "进程号" taskkill /pid 进程号

10.查询表准确膨胀率的sql

select relname,n_live_tup,n_dead_tup from sys_stat_user_tables order by n_dead_tup desc;

11.输入一个日期，让sql往后或者往前推60年

select date'2022-3-5' + interval '1 year'; 配置 ’ ’ 代替 null ALTER DATABASE 数据库 set ora_input_emptystr_isnull=true; select sys_reload_conf();

13.应用代码中事务自动提交的问题

conn.setAutoCommit(false);

14.查询模式下的所有的记录数

SELECT count(table_name) FROM information_schema.TABLES WHERE table_schema =‘information_schema’; --schema填自己的实际的schema_name 或者： SELECT relname, reltuples FROM sys_catalog.sys_class r JOIN sys_catalog.sys_namespace n ON (relnamespace = n.oid) WHERE relkind = 'r' AND n.nspname = 'public' order by reltuples desc

15.模糊查询字段

SELECT * FROM TABLE_NAME WHERE CONCAT(FIELD1,FIELD2) LIKE '%STRING%'

16.日期采用date，不要后缀00:00:00

ALTER DATABASE TEST set ORA_STYLE_NLS_DATE_FORMAT to 'on'; select sys_reload_conf();

17.查看事务隔离级别

SHOW TRANSACTION ISOLATION KINGBASE_INTERNAL_LEVEL;

18.R6非交互式的方式登录数据库

ksql "host=1.1.1.1 port=54321 user=system dbname=test password=123456"

19.非交互式还原数据库

PGPASSWORD=123456 sys_restore -h127.0.0.1 -Usystem -d abc xxx.dmp

20.数据库迁移编码报错问题

配置文件添加参数，重启数据库后生效 ignore_char_null_check=on

21.授权序列

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public to www;

22.查看当前账号会话数

select count(*), usename from sys_stat_activity group by usename;

23.设置varchar和char之间匹配导致索引失效的问题

set ora_enable_varchar_match_bpchar to on

24.查看表的存储路径

select sys_relation_filepath('表名');

25.查看会话进程

select datname,pid,usename,state,query from sys_stat_activity;

26.通过copy还原、导出csv格式的数据

\copy table_1 from '/home/kingbase/b.csv' csv 通过copy还原csv格式的数据到表中 \copy table_1 to '/home/kingbase/b.csv' csv 导出表数据到csv文件

27.查看历史执行的sql命令

修改配置文件kingbase.conf, 开启参数 log_statement='all'，sys_stat_statements.track='all' ，重启数据库。进入数据库执行select query,calls from sys_stat_statements

28.查看sql的执行时间

select query,calls,round(mean_exec_time,2)as mean_exec_time from sys_stat_statements order by mean_exec_time desc;

29.两表关联更新字段

update A set A.1 = B.1 from B where A.id = B.id

30.授权A用户访问B用户的表

grant usage on schema 模式名 to 用户名; grant select on table 表名 to 用户名

31.时间戳转换为时间

select to_char(to_timestamp(1628088734), 'yyyy-mm-dd hh24:mi:ss')

32.设置时间分区

set ora_func_style = false; create table part_test(id int, info text, crt_time timestamp not null); select create_range_partitions( 'PART_TEST'::regclass, 'crt_time', '2018-10-01 00:00:00'::timestamp, interval '1 month', 24, false) ;

33.生成UUID的命令

select sys_guid();

34.将日期格式化成特定格式

DATE_FORMAT(date TIMESTAMP,format TEXT);

35.初始化编码问题

初始化添加参数 --locate=C

36.将一个字段中的值拼接

string_agg(字段A,'分隔符')

37.将字符串转换为数字类型后面99999表示保留的位数，数字只能用9

to_number('12345','99999')

38.实现mysql的date_add功能

select current_date + interval '0 hours'; 获取月份的函数 select date_part('month',now()); 查看所有的序列属性 SELECT * FROM "pg_class" "c" WHERE "c"."relkind" = 'S'; 查看字段名称，类型，注释 SELECT a.attname as 字段名, format_type(a.atttypid,a.atttypmod) as 类型, a.attnotnull as 非空, col_description(a.attrelid,a.attnum) as 注释 FROM pg_class as c,pg_attribute as a where a.attrelid = c.oid and a.attnum>0 and c.relname = '你的表名'; 设置中文占多少字节配置文件添加这个参数 char_default_type=''char'' V8R6默认就是char不需要设置

43.查看字段类型

select sys_typeof( name ) from aa

44.查看库表数据量【不是太准】

select pg_size_pretty(pg_database_size('库名')); 查询所有的函数 SELECT pg_proc.proname AS "函数名称", pg_type.typname AS "返回值数据类型", pg_proc.pronargs AS "参数个数" FROM pg_proc JOIN pg_type ON (pg_proc.prorettype = pg_type.oid) 查看所有的函数，类型，拥有者（R6） SELECT n.nspname as "Schema", p.proname as "Name", pg_catalog.pg_get_function_result(p.oid) as "Result data type", CASE p.prokind WHEN 'a' THEN 'agg'‘’ WHEN 'w' THEN 'window' WHEN 'p' THEN 'proc' ELSE 'func' END as "Type", pg_catalog.pg_get_userbyid(p.proowner) as "Owner" FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang WHERE pg_catalog.pg_function_is_visible(p.oid) AND n.nspname 'pg_catalog' AND n.nspname 'information_schema' AND n.nspname 'sys' AND n.nspname 'sys_catalog' ORDER BY 1, 2, 4; 用sql命令查看ddl语句 create extension dbms_metadata SELECT dbms_metadata.get_ddl('table', 'aa'); 用sql命令查看ddl语句（2） CREATE OR REPLACE FUNCTION tabledef(text,text) RETURNS text LANGUAGE sql STRICT AS $$ WITH attrdef AS ( SELECT n.nspname, c.relname, c.oid, pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ') as relopts, c.relpersistence, a.attnum, a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) as atttype, (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as attdefault, a.attnotnull, (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation t.typcollation) as attcollation, a.attidentity, a.attgenerated FROM pg_catalog.pg_attribute a JOIN pg_catalog.pg_class c ON a.attrelid = c.oid JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) WHERE n.nspname = $1 AND c.relname = $2 AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum ), coldef AS ( SELECT attrdef.nspname, attrdef.relname, attrdef.oid, attrdef.relopts, attrdef.relpersistence, pg_catalog.format('%I %s%s%s%s%s', attrdef.attname, attrdef.atttype, case when attrdef.attcollation is null then '' else pg_catalog.format(' COLLATE %I', attrdef.attcollation) end, case when attrdef.attnotnull then ' NOT NULL' else '' end, case when attrdef.attdefault is null then '' else case when attrdef.attgenerated = 's' then pg_catalog.format(' GENERATED ALWAYS AS (%s) STORED', attrdef.attdefault) when attrdef.attgenerated '' then ' GENERATED AS NOT_IMPLEMENTED' else pg_catalog.format(' DEFAULT %s', attrdef.attdefault) end end, case when attrdef.attidentity'' then pg_catalog.format(' GENERATED %s AS IDENTITY', case attrdef.attidentity when 'd' then 'BY DEFAULT' when 'a' then 'ALWAYS' else 'NOT_IMPLEMENTED' end) else '' end ) as col_create_sql FROM attrdef ORDER BY attrdef.attnum ), tabdef AS ( SELECT coldef.nspname, coldef.relname, coldef.oid, coldef.relopts, coldef.relpersistence, concat(string_agg(coldef.col_create_sql, E',\n ') , (select concat(E',\n ',pg_get_constraintdef(oid)) from pg_constraint where contype='p' and conrelid = coldef.oid)) as cols_create_sql FROM coldef GROUP BY coldef.nspname, coldef.relname, coldef.oid, coldef.relopts, coldef.relpersistence ) SELECT FORMAT( 'CREATE%s TABLE %I.%I%s%s%s;', case tabdef.relpersistence when 't' then ' TEMP' when 'u' then ' UNLOGGED' else '' end, tabdef.nspname, tabdef.relname, coalesce( ( SELECT FORMAT( E'\n PARTITION OF %I.%I %s\n', pn.nspname, pc.relname, pg_get_expr(c.relpartbound, c.oid) ) FROM pg_class c JOIN pg_inherits i ON c.oid = i.inhrelid JOIN pg_class pc ON pc.oid = i.inhparent JOIN pg_namespace pn ON pn.oid = pc.relnamespace WHERE c.oid = tabdef.oid ), FORMAT( E' (\n %s\n)', tabdef.cols_create_sql) ), case when tabdef.relopts '' then format(' WITH (%s)', tabdef.relopts) else '' end, coalesce(E'\nPARTITION BY '||pg_get_partkeydef(tabdef.oid), '') ) as table_create_sql FROM tabdef $$; 调用: select tabledef('模式名','表名'); kingbase强制走索引【需要安装hint插件】 select /*+ index(student ss)*/ * from student where name='xx' 给函数创建索引 create index 索引名 ON 表名(to_char(字段,'格式'))； sql强行走索引 select /*+ index(student ss)*/ * from student where name='xx' 查询表的所有记录数 SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables where schemaname='public' ORDER BY n_live_tup DESC; 查询结果加一列序列 ROW_NUMBER() over(ORDER bY name DESC ) AS num 当前时间的时间戳 select floor(extract(epoch from now())); 获取某个用户下的所有表 select * from pg_tables where TABLEOWNER='用户名'; 获取指定年份的所有数据 1、创建year函数之后调用 CREATE OR REPLACE INTERNAL FUNCTION YEAR(dtime DATE) RETURNS BIGINT AS ' BEGIN RETURN extract(year from dtime); END; ' LANGUAGE plsql; select * from date_test where year(日期字段)='年份';

2、使用to_char函数获年份实现

select * from date_test where to_char(日期字段,'YYYY')='年份'; 获取数据库中所有模式的所有表的结构(表名，表注释，字段名，字段类型，是否为空，注释) SELECT c.relname as 表名, cast(obj_description(relfilenode,'pg_class') as varchar) AS "表名描述", a.attname as 字段名, format_type(a.atttypid,a.atttypmod) as 类型, a.attnotnull as 非空, col_description(a.attrelid,a.attnum) as 注释 FROM pg_class as c,pg_attribute as a inner join pg_type on pg_type.oid = a.atttypid where a.attrelid = c.oid and a.attnum>0; 指定模式下指定表的结构（所有表可以把指定表名的条件去掉） select col.table_schema, col.table_name, col.ordinal_position, col.column_name, col.data_type, col.character_maximum_length, col.numeric_precision, col.numeric_scale, col.is_nullable, col.column_default, des.description from information_schema.columns col left join pg_description des on col.table_name::regclass = des.objoid and col.ordinal_position = des.objsubid where table_schema = 'public' and table_name = 'table_name' order by ordinal_position; 查看某个用户下有哪些数据库（u6是用户名） SELECT a.datname from sys_database a,pg_authid b where a.datdba=b.oid and b.rolname='u6'; 兼容mysql的date_format()函数兼容敏感 create or replace function date_format(para1 timestamp,para2 text) return text as declare form1 text; begin form1=replace(para2,'%M','Month'); form1=replace(form1,'%W','Day'); form1=replace(form1,'%D','DDth'); form1=replace(form1,'%Y','YYYY'); form1=replace(form1,'%y','yy'); form1=replace(form1,'%a','Dy'); form1=replace(form1,'%d','DD'); form1=replace(form1,'%e','DD'); form1=replace(form1,'%m','MM'); form1=replace(form1,'%c','MM'); form1=replace(form1,'%b','Mon'); form1=replace(form1,'%j','DDD'); form1=replace(form1,'%H','HH24'); form1=replace(form1,'%k','HH24'); form1=replace(form1,'%h','HH'); form1=replace(form1,'%I','HH'); form1=replace(form1,'%l','HH'); form1=replace(form1,'%i','MI'); form1=replace(form1,'%r','HH:MI:SS'); form1=replace(form1,'%T','HH24:MI:SS'); form1=replace(form1,'%S','SS'); form1=replace(form1,'%s','SS'); form1=replace(form1,'%%','%'); return to_char(para1,form1); end;

可以进行改写 Mysql：select date_format(CURRENT_TIMSTAMP, ‘%Y-%m-%d %H:%i:%s’) KES：select to_char (CURRENT_TIMSTAMP, ‘yyyy-mm-dd hh24:mi:ss’)

60、兼容mysql的date_sub函数

create or replace function date_sub(v_date text , v_interval interval) returns text as $$ declare v_rt text; begin select to_char(v_date::timestamp(0) - v_interval,'yyyy-mm-dd hh24:mi:ss' ) into v_rt; if length(v_date) = 10 and v_rt like '% 00:00:00' then select substr(v_rt,0,10) into v_rt; end if; return v_rt; end; $$ LANGUAGE plsql;

【本文地址】

kingbase数据库使用遇到的问题以及解决办法【随时更新】

kingbase数据库使用遇到的问题以及解决办法【随时更新】

今日新闻

推荐新闻