OcenBase（OB）适配相关SQL备忘
show variables like '%timeout%';
select * from nls_database_parameters;
select table_name from user_tables;
set global ob_trx_timeout = 86400000000;
set global ob_query_timeout=3600000000;
set global ob_trx_idle_timeout=240000000;
set global nls_timestamp_format='yyyy-mm-dd hh24:mi:ss.ff3';
###适配参数待确认
ALTER SYSTEM SET open_cursors=1500;
#查看字符集（SHOW VARIABLES 其实可以查看所有的配置信息）
1.-- 游标修改
SHOW PARAMETERS LIKE 'open_cursors';
ALTER SYSTEM SET open_cursors=1500;
2.获取建库时间解决序列号问题
select created,(select sysdate from dual) from dba_users where username= (SELECT USERNAME FROM USER_SYS_PRIVS where ROWNUM = 1)
select USERNAME,created,(select sysdate from dual) from dba_users
select created,(select sysdate from dual) from dba_users where username='SYS'
SELECT * FROM USER_SYS_PRIVS where PRIVILEGE = 'CREATE USER'
-- 低权限
select created,(select sysdate from dual) from all_users where username= (SELECT USERNAME FROM USER_SYS_PRIVS where ROWNUM = 1)
3.时间格式话问题
-- 会话层 重启会失效
select * from nls_session_parameters where parameter='NLS_DATE_LANGUAGE';
-- 服务层 重启不会失效
select * from nls_database_parameters
-- 解决元数据问题
alter session set nls_date_language='AMERICAN';
alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF';
set global nls_timestamp_format='yyyy-mm-dd hh24:mi:ss.ff3';
-- 修改时间格式
ALTER SESSION SET nls_date_format='YYYY-MM-DD';
ALTER SESSION SET nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF';
ALTER SESSION SET nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS.FF';
4.GSPDATABASEOBJECT 创建 单独运行dbo脚本需要先建这个表
CREATE TABLE GSPDATABASEOBJECT (
ID VARCHAR2(36) CONSTRAINT "GSPDATABASEOBJECT_OBNOTNULL_1681177619784495" NOT NULL ENABLE,
CODE VARCHAR2(256) CONSTRAINT "GSPDATABASEOBJECT_OBNOTNULL_1681177619784512" NOT NULL ENABLE,
RULEID VARCHAR2(36),
RULECODE VARCHAR2(256),
NAME VARCHAR2(256) CONSTRAINT "GSPDATABASEOBJECT_OBNOTNULL_1681177619784520" NOT NULL ENABLE,
BUSINESSOBJECTID VARCHAR2(36),
TYPE NUMBER(38) CONSTRAINT "GSPDATABASEOBJECT_OBNOTNULL_1681177619784525" NOT NULL ENABLE,
ISI18NOBJECT CHAR(1) CONSTRAINT "GSPDATABASEOBJECT_OBNOTNULL_1681177619784528" NOT NULL ENABLE,
ISFISCALTABLE CHAR(1) CONSTRAINT "GSPDATABASEOBJECT_OBNOTNULL_1681177619784532" NOT NULL ENABLE,
TENANTIDCOLUMNCODE VARCHAR2(256),
VERSION VARCHAR2(36) CONSTRAINT "GSPDATABASEOBJECT_OBNOTNULL_1681177619784537" NOT NULL ENABLE,
CONTENT CLOB CONSTRAINT "GSPDATABASEOBJECT_OBNOTNULL_1681177619784541" NOT NULL ENABLE,
CREATEDTIME TIMESTAMP(6),
LASTMODIFIEDTIME TIMESTAMP(6),
CONSTRAINT "PK_GSPDATABASEOBJECT" PRIMARY KEY (ID)
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0;
5.版本号获取
select banner as version from v$version
6. 权限
-- 查看所有表
select * from user_tables;
-- 用户sys的系统权限
SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = 'SYS';
-- 所有用户 需要高级权限
select * from all_users;
-- 所有角色 需要高级权限
select * from dba_roles;
-- 用户hr所具有的角色
SELECT GRANTEE,GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE ='SYS';
7.创建用户
-- 创建用户
CREATE USER user1 IDENTIFIED BY aaAA11__;
-- 授权
GRANT CREATE SESSION TO user1;
-- 给用户授权
GRANT CONNECT,RESOURCE,CREATE SESSION,DROP ANY TABLE,SELECT ANY TABLE,CREATE ANY VIEW,DROP ANY VIEW,
CREATE ANY SEQUENCE, CREATE ANY TABLE, CREATE TRIGGER,
CREATE VIEW to user1
select * from all_users
8.连接是否超时
show variables like '%timeout%';
set global ob_trx_timeout = 3600000000;
