神通数据库常用命令
最近适配连接神通数据库，整理收集常用操作命令行
转载于 https://blog.csdn.net/asd051377305/article/details/108766792
1、命令行连接：isql -d osrdb -P 2003 -U sysdba/szoscar55 2、后台创建库命令（中括号代表可选） 2.1 进入后台：oscar/kstore 列出库：list database 删除库: drop database osrdb 创建库：create database osrdb [encoding 'GBK'] 2.2 标准化库：oscar -o restrict -d osrdb 2.3 安装库：oscar -o install -d osrdb 2.4 卸载库：oscar -o uninstall -d osrdb 3、用户相关： 创建用户：create user test_usr with password 'test' default tablespace TS_USER_YS_USR_DATA role sysdba; 变更密码：alter user test_usr with password 'test_change'; 4、权限相关： create role ROLE_ALL_SELECT; grant select on sys_class to role ROLE_ALL_SELECT; select 'grant create on schema ' || TABLE_SCHEM || ' to role ROLE_ALL_SELECT;' from (select distinct TABLE_SCHEM from v_sys_tables where TABLE_SCHEM not in ('INFO_SCHEM','SYSDBA','STAGENT')); select 'grant select on ' || TABLE_NAME || ' to role ROLE_ALL_SELECT;' from (select TABLE_SCHEM|| '.' ||table_name as table_name from v_sys_tables where TABLE_SCHEM not in ('INFO_SCHEM','SYSDBA','STAGENT')); 5、查询表及字段类型备注 select TABLE_NAME,REMARKS from V_SYS_TABLES where table_schem='' order by TABLE_NAME select TABLE_NAME,COLUMN_NAME,TYPE_NAME,COLUMN_SIZE,REMARKS,IS_NULLABLE from V_SYS_COLUMNS where table_name='' Oscar --version 产看数据库版本 Oscar –o normal –d osrdb 正常模式启动OSRDB数据库 Oscar –o install –d osrdb 安装数据库实例OSRDB的服务 Oscar –o uninstall –d osrdb 卸载数据库实例OSRDB的服务 Oscar –o uninstall all 卸载所有数据库实例的服务 Oscar –h d:\ShenTong –o normal –d osrdb 设定数据库安装目录并正常启动 Oscar –o normal –d osrdb –P 2004 在2004端口启动数据库实例 Oscar –e “create database osrdb” 执行创建数据库OSRDB实例的命令 oscaragent –v 查看agent版本号 oscaragent –c 命令行方式启动 oscaragent –r 以服务方式启动(windows) oscaragent –e d:\ShenTong –c 设定数据库安装目录并启动 oscaragent –s 关闭agent（通过socket向agent发送退出信号） oscaragent –S 关闭agent服务 oscaragent –S –A 停止所有数据库服务 oscaragent –S –D <DBNAME> 停止指定的数据库实例服务 oscaragent –Q 查询agent的服务运行状态 oscaragent –Q -A 查询所有数据库实例服务的运行状态 oscaragent –Q –D <DBNAME> 查询指定的数据库实例服务运行状态 oscaragent –i 安装agent系统服务（跨平台） oscaragent –u 卸载agent系统服务（跨平台） oscaragent –h/-? 帮助 echo alter database drop logfile member '/stl_db/db_data/stdb/odbs/STLCDR/STLCDR01.log'; | isql -h 132.35.96.1 -d STLCDR -Ustl_usr/stl_usr
常用SQL系统表查询
1.--查询表空间使用情况 SELECT T.TSNAME AS TSNAME, D.PATH, COUNT(*) "File Count", SUM(D.SIZE) / 1048576 AS "Size(MB)", SUM(D.FREESIZE) / 1048576 AS "Free Size(MB)", (SUM(D.SIZE) - SUM(D.FREESIZE)) / 1048576 AS "Used Size(MB)" FROM SYS_TABLESPACE T LEFT JOIN V_SYS_DATAFILE_INFO D ON T.TSID = D.TABLESPACEID GROUP BY T.TSNAME,D.PATH; 2.--查询各节点表空间使用情况 execute direct on all 'SELECT T.TSNAME AS Schema, SUM(D.SIZE) / 1048576 AS "Size(MB)", SUM(D.FREESIZE) / 1048576 AS "Free Size(MB)", (SUM(D.SIZE) - SUM(D.FREESIZE)) / 1048576 AS "Used Size(MB)" FROM SYS_TABLESPACE T,V_SYS_DATAFILE_INFO D where T.TSID = D.TABLESPACEID and T.TSNAME=''STLTS'' GROUP BY T.TSNAME'; 3.--查询数据文件使用情况 SELECT T.TSNAME as "NAME", PATH, "CREATIONTIME" as "Creation Time", "NEXTSIZE" / 1048576 as "Next Size(MB)", "MAXSIZE" / 1048576 AS "Max Size(MB)", "SIZE" / 1048576 AS "Size(MB)", FREESIZE / 1048576 AS "Free Size(MB)", ("SIZE" - FREESIZE) / 1048576 AS "Used Size(MB)", ("SIZE" - FREESIZE) * 100.0 / SIZE AS "Used RATIO" FROM V_SYS_DATAFILE_INFO, SYS_TABLESPACE T WHERE T.TSID = TABLESPACEID; 4.--新主线查询数据文件使用情况 SELECT T.TSNAME as "NAME", PATH, "CREATIONTIME" as "Creation Time", "NEXTSIZE" / 1048576 as "Next Size(MB)", "MAXSIZE" / 1048576 AS "Max Size(MB)", "SIZE" / 1048576 AS "Size(MB)", FREESIZE / 1048576 AS "Free Size(MB)", ("SIZE" - FREESIZE) / 1048576 AS "Used Size(MB)", ("SIZE" - FREESIZE) * 100.0 / SIZE AS "Used RATIO" FROM V_SYS_DATAFILE_INFO, SYS_TABLESPACE T WHERE T.TSID = TABLESPACEID; 5.--查询日志文件使用情况 SELECT PATH, "CREATIONTIME" as "Creation Time", "INITSIZE" / 1048576 as "Init Size(MB)", "REALSIZE" / 1048576 as "Real Size(MB)", "NEXTSIZE" / 1048576 as "Next Size(MB)", "MAXSIZE" / 1048576 AS "Max Size(MB)", "ISACTIVE" AS "Is Active)", ("REALSIZE" * (100-"USAGERATIO")/100) / 1048576 AS "Free Size(MB)", ("REALSIZE" * "USAGERATIO"/100) / 1048576 AS "Used Size(MB)", "USAGERATIO" AS "Used RATIO" FROM V_SYS_LOGFILE_INFO; 6.--查询普通表空间占用情况 select u.USENAME, c.relname, s.size, ts.tsname from sys_class c, v_segment_info s, sys_tablespace ts, v_sys_user u where c.oid = s.relid and c.relname='MM_T_GW_NORATE_17_201107' and s.fileid = ts.tsid and u.USESYSID = c.RELOWNER and u.USENAME='TWBTEST' order by s.size; 7.--查询表分区名 select * from V_SYS_PARTITION_INFO_EX where RELNAME='T_YD_UE_17_201109'; 8.--查询分区表的总大小 select sum(SIZE) from V_SYS_PARTITION_INFO_EX where RELNAME='T_YD_NORATE_17_201109'; 9.--查询当前Session状态 select "SESSION ID", "APPNAME", "CURRENT SQL", "LAST SQL" from v_sys_sessions where "LOGON USER" != 'INVALID USER'; 10.--查询当前Session诊断信息 select w.SESSION_ID,w.EVENT_NAME, s."SESSION ID", s."APPNAME", s."MACHINE", s."LOGONTIME", s."TOTAL PORTAL", s."SQL COUNT", s."CURRENT SQL", s."LAST SQL" from v_sys_sessions s, V$SESSION_WAIT w where s."SESSION ID"=w.SESSION_ID; select w.SESSION_ID,w.EVENT_NAME, s."SESSION ID", s."APPNAME", s."MACHINE", s."LOGONTIME", s."TOTAL PORTAL", s."SQL COUNT", s."CURRENT SQL", s."LAST SQL" from v_sys_sessions s, V$SESSION_WAIT w where s."SESSION ID"=w.SESSION_ID and s."APPNAME"<>'java.exe'; select w.SESSION_ID,w.EVENT_NAME, s."SESSION ID", s."APPNAME", s."MACHINE", s."LOGONTIME", s."TOTAL PORTAL", s."SQL COUNT", s."CURRENT SQL", s."LAST SQL" from v_sys_sessions s, V$SESSION_WAIT w where s."SESSION ID"=w.SESSION_ID and s."APPNAME"='insert_db'; 11.--查看等待链 select * from V$WAIT_CHAINS; --kill掉session id kill session 631 abort; alter tablespace stlts default nologging; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 12.--列与字符串的链接 select b.called_number || ',' || sum(a.total_due_amount)/1000 from t_sms_13_201202 a,call b where a.called_number=b.CALLING_NUMBER and record_type=0 group by b.called_number; 13.--查询表定义 select SYS_GET_TABLEDEF from v_sys_table where tablename='XXX'; select SYS_GET_TABLEDEF((SELECT OID from sys_class where relname='TEST_02')); 14.--查询计划 create table test(tc1 int) partition by hash(tc1) partitions 4; explain select * from test; 15.水平分区查询 --根据分区键值查询 select count(*) from bill partition for('2010-01-01'); --根据分区名查询 select * from t1 partition(分区名); 16.查询依赖 查询相关表的试图 select * from v_sys_views where DEFINITION like '%T_GW_NORATE_88_201112%' select c.relname,d.* from sys_depend d,sys_class c where d.OBJID=c.oid 17.查询数据库信息 SELECT * FROM V_SYS_DATABASE_INFO; 18.查询表的级联 select r.relname, r.REFOBJID, c2.relname "REF RelName", r.OBJID "REF OID" from (select c.relname relname,d.* from sys_depend d,sys_class c where d.REFOBJID=c.oid and c.relname='T_GW_UE_18_201204 ') r, sys_class c2 where c2.oid=(r.OBJID::integer-1); 19.查询阻塞语句sid select s.prev_sql, 'kill session ' || s.sid || ' abort ;', w.pid, w.BLOCKER_PID from v$wait_chains w, v$session s where BLOCKER_PID is null and s.sid = w.SESSION_ID; 20.查询用户默认表空间 select defaulttablespace as "default tablespace" from sys_shadow where usename='SYSDBA'; select SID from V$SESSION where LOGON_USER<>'' and CURRENT_SQL ='' and PREV_SQL_EXEC_START<=now()::timestamp-1/48; select BUFFERS*8/1024 total,FREE_BUFFERS*8/1024 free,DIRTY_BUFFERS*8/1024 dirty from V$BUFFER_STATISTICS ; 查询失效索引 execute direct on all 'select * from V_SYS_IND_SUBPARTITIONS where IDXUSABLE=''f'''; 不同模式表分区显示 select d.NSPNAME || '.' || c.relname,a.* from v_sys_tab_partitions a,sys_tabpart b , sys_class c,SYS_NAMESPACE d where a.oid = b.oid and b.BO=c.oid and c.RELNAMESPACE = d.oid;
常用初始化语句
1.--创建库,表空间 CREATE DATABASE "HNBILL" NOARCHIVELOG LOGFILE 'HNBILL01.log' SIZE 10G AUTOEXTEND ON NEXT 100M CONTROLFILE “/OSCAR/admin/test.ctrl” DATAFILE 'HNBILL01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M; 表空间 CREATE TABLESPACE DR_G_A DATAFILE 'DR_G_A.dbf' SIZE 200M AUTOEXTEND ON NEXT 20M; 2.-- 创建用户 CREATE USER stl_usr WITH DEFAULT TABLESPACE stlts PASSWORD 'stl_usr' ROLE SYSDBA; drop user dba_usr; CREATE USER dba_usr WITH PASSWORD 'dba_usr' ; 3.-- 删除用户及所有级联 drop user JX_DW cascade; 4.-- 修改SYSDBA用户的初始密码 ALTER USER SYSDBA WITH PASSWORD 'UNIcom00'; 5.-- 添加修改日志文件 ALTER DATABASE stlcdr ADD LOGFILE '/stl_db/db_data/stdb/odbs/STLCDR/STLCDR00.log' SIZE 50G AUTOEXTEND ON NEXT 1G; ALTER DATABASE RENAME FILE ‘/diska/logs/log1a.log’ TO ‘/diskc/logs/log1c.log’; 6.---归档模式修改 select * from V_SYS_ARCHIVELOG_INFO; ALTER DATABASE ARCHIVELOG ‘/OSCAR/arch/’;(归档，归档路径为/OSCAR/arch/) ALTER DATABASE NOARCHIVELOG;(不归档) 7.--修改表空间的最大值 ALTER DATABASE "OSCAR" datafile '/kstore1/KSTORE/odbs/KSTORE/KSTORE06.dbf' AUTOEXTEND ON MAX 700G; 8.--序列管理 create sequence SEQ_UP_MONI_COL_ID minvalue 1 maxvalue 1111111111111 start with 132 increment by 1 cache 20; select nextval('SEQ_UP_MONI_COL_ID'); SELECT currval('SEQ_UP_MONI_COL_ID'); select * from SEQ_UP_MONI_COL_ID; 9.--字符串列长度的有关查询 select rowid, ROAM_TYPE from T_GPRS_UE_84_201203 where char_length(ROAM_TYPE) > 4 limit 1; 10.分裂分区 ALTER TABLE maininfoall_test SPLIT PARTITION PART_MAX at (1341187200) INTO ( PARTITION PART_MAX, PARTITION PART_20120701 TABLESPACE TS_MAININFO_TEST_20120701) update indexes; ALTER TABLE MB_SSJC SPLIT PARTITION PART_MB_SSJC_MAX AT ('2012-12-07 00:00:00') INTO (PARTITION PART_MB_SSJC_20121206 TABLESPACE TS_MB_SSJC_20121206 INIT 1024K NEXT 1024K, PARTITION PART_MB_SSJC_MAX TABLESPACE TS_MB_SSJC_MAX INIT 10240K NEXT 10240K) QUICK TO right; DUMP： alter session set events(immediate trace name errorstack);
前台工具路径
审计工具 Auditor -> /settle/ShenTong/bin/auditor 安装卸载 Change Installation -> /settle/ShenTong/installation/ChangeInstallation 数据库配置工具 Database Configer -> /settle/ShenTong/bin/dbconfiger 数据迁移工具 Data Migration Tool -> /settle/ShenTong/bin/migration_java 升级工具 Datebase Update Tool -> /settle/ShenTong/bin/update DBA管理工具 DBA Manager -> /settle/ShenTong/bin/dba 数据库维护工具 Maintenance Utility -> /settle/ShenTong/bin/oem 性能监测工具 Performance Monitor -> /settle/ShenTong/bin/perfmonitor 政策管理工具 Policy Tool -> /settle/ShenTong/bin/policy SQL交互工具 SQL Analyzer -> /settle/ShenTong/bin/sqlanalyzer 参数配置工具 System Parameter Congfiger -> /settle/ShenTong/bin/sysviewer
物理备份与恢复
物理备份： brcmd -O backup -d HNBILL -t full -k file -f "/kstore/sttmp/test_T.osrbk" -c TRUE 备份恢复： brcmd -O restore -d HNBAK -u KSTORE -p kstore -P 2003 -t backup_point_last -k file -f "/kstore/sttmp/test.osrbk" -b 8192 -e "/kstore/sttmp/brcmd_log_021509.txt" brcmd备份和恢复使用： -O Operatin类型的参数：backup/restore/delete_backup/query_db_verify_code 备份/恢复/删除备份/查询数据库验证代码 -f 备份文件的完整路径（管道） -d 数据库的名称，注意大写和小写字母,默认值是OSRDB -u 数据库的用户名,默认值是SYSDBA -p 的用户名的密码, 默认值是szoscar55 -P 数据库服务的监听端口，默认值是2003 -k 数据传输类型：file/pipe，文件/管道，默认值是文件 -e brcmd的日志路径， 默认值是oscaragent的日志路径 -b 数据传输缓冲区大小（KB）,默认数量为4096，最大数量是8192 备份参数： -t 备份类型：full/incr/diff,完全/增量/差异 -S 备份阶段：all/df/lsn/al -D 备份描述,默认值是null -c 是压缩备份文件？默认值是false -m 是永远保持备份文件？默认值是false -A 是自动将数据库设置为归档模式时，你会做一个在线备份，但数据库正在运行无存档模式吗？默认值是false -n 是没有在线备份归档日志？默认值是false 恢复参数： -t 还原类型： backup_point/backup_point_last/full/full_last/full_last_ignore_error/time_point/time_point_last 备份点/最后一次备份点/完全/上一次完全/上一次完全忽略错误/时间点/最后一次时间点 -T 将数据库恢复到某一时间点，时间格式是YYYYMMDD_HHMMSS，只有当使用了-t参数是time_point/time_point_last -s 还原数据库的数据文件路径，只有当使用了-t参数是backup_point / backup_point_last -l 恢复数据库的日志文件的路径，只有当使用了-t参数是backup_point / backup_point_last -a 恢复拱数据库的日志文件路径,只有当使用了-t参数是backup_point / backup_point_last -B DB_CFG_BUF_DATA_BUFFER_PAGES参数的值,默认值是8192，数据缓冲区的实际大小是64MB,操作系统的虚拟内存应该超过此值 -F 还原参数的XML文件的路径 -v 显示版本 -? 显示帮助
逻辑备份与恢复
备份： osrexp:备份工具，提取神通内容转化为脚本文件的工具。 全库级别 osrexp -hlocalhost -p2003 -usysdba/szoscar55 -dosrdb level=full file=d:/backup log=d:/bk.log mode=entirety ignore=false view=true sequence=true procedure=true constraint=true trigger=true index=true 模式级别： osrexp -hlocalhost -p2003 -usysdba/szoscar55 -dosrdb level=schema file=d:/backup log=d:/bk.log mode=entirety ignore=false schema=(sysdba,public) excludetable=(public.test,sysdba.test) view=true sequence=true procedure=true constraint=true trigger=true index=true 表级别： osrexp -hlocalhost -p2003 -usysdba/szoscar55 -dosrdb level=table file=d:/backup log=d:/bk.log mode=entirety ignore=false table=(messagebord.test,sysdba.test) view=false procedure=false sequence=false constraint=true trigger=true index=true 参数说明： -h 服务器名 -p 服务器端口 -u 用户名和密码(sysdba/szoscar55) -k 表示以操作系统用户登录 -d 数据库名称 level 备份级别 parfile 指定一个文件名,文件包含备份操作参数的列表 file 指定备份的文件 log 指定备份的日志 mode 备份方式 ignore 是否忽略不重要的错误 view 是否备份视图 sequence 是否备份序列 procedure 是否备份存储过程 constraint 是否备份表约束 trigger 是否备份表触发器 index 是否备份表索引 schema 模式级别下要备份的模式 table 表级别下要备份的表对象 single 是否只备份单个表对象 excludetable 模式级别下不要备份的表 恢复： osrimp:恢复工具，根据osrexp工具生成的数据库备份文件还原神通内容。 全库恢复级别： osrimp -hlocalhost -p2003 -usysdba/szoscar55 -dosrdb level=full file=d:/backup log=d:/rt.log mode=entirety ignore=y recreateschema=false view=true sequence=true procedure=true recreateotherobject=true recreatetable=true constraint=true deletetabledata=true trigger=true index=true (如果recreateschema=true 则后面的recreateotherobject=true recreatetable=true无效了) 模式恢复级别： osrimp -hlocalhost -p2003 -usysdba/szoscar55 -dosrdb level=schema file=d:/backup log=d:/rt.log mode=entirety ignore=true schema=(sysdba,public) recreateschema=false view=true sequence=true procedure=true recreateotherobject=true excludetable=(sysdba.test,public.test) recreatetable=false constraint=false deletetabledata=true trigger=false index=false 表恢复级别： osrimp -hlocalhost -p2003 -usysdba/szoscar55 -dosrdb level=table file=d:/backup log=d:/rt.log mode=entirety ignore=true recreateschema=false view=false procedure=false sequence=true recreateotherobject=false table=(sysdba.test,public.test) recreatetable=true constraint=true deletetabledata=true trigger=true index=true (recreatetable=true 后面的trigger=true index=true才有效) 参数说明： -h 服务器名 -p 服务器端口 -u 用户名和密码(sysdba/szoscar55) -k 表示以操作系统用户登录 -d 数据库名称 level 恢复级别 parfile 指定一个文件名,文件包含恢复操作参数的列表 file 指定恢复的文件 log 指定恢复的日志 mode 恢复方式 ignore 是否忽略不重要的错误 view 是否恢复视图 sequence 是否恢复序列 procedure 是否恢复存储过程 constraint 是否恢复表约束 trigger 是否恢复表触发器 index 是否恢复表索引 schema 模式级别下要恢复的模式 table 表级别下要恢复的表对象 single 是否只恢复单个表对象 excludetable 模式级别下不要恢复的表 recreateschema 是否重构模式 recreateotherobject 是否重构视图、存储过程、序列 recreatetable 是否重构表 tablespace 是否恢复表空间 deletetabledata 是否清空原表中的数据
