神通数据库常用命令行

左手的ㄟ右手 2022-12-10 04:58 4961阅读 0赞

![在这里插入图片描述][20200818170841448.png_pic_center]

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

### ![在这里插入图片描述][20200818170819947.png_pic_center] ###

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;

![在这里插入图片描述][20200818170857503.png_pic_center]

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);

![在这里插入图片描述][20200818170941597.png_pic_center]

审计工具          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

![在这里插入图片描述][20200818171022113.png_pic_center]

物理备份：
    
    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		显示版本
     -?		显示帮助

![在这里插入图片描述][20200818171118266.png_pic_center]

备份：
    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          是否清空原表中的数据

[20200818170841448.png_pic_center]: /images/20221123/3c49807cdae4480d97565fb00dbf68ad.png
[20200818170819947.png_pic_center]: /images/20221123/d8d3a817a74f4561b40a6c531fc469a9.png
[20200818170857503.png_pic_center]: /images/20221123/8a3e5375c496420abdd906a5c62124ce.png
[20200818170941597.png_pic_center]: /images/20221123/05c34ef268ec4b998828209666d2b3b7.png
[20200818171022113.png_pic_center]: /images/20221123/ad12c64895c449ff93d8ee61f7ad90d0.png
[20200818171118266.png_pic_center]: /images/20221123/1515a2cc482d4446aadcf95ce5a101da.png