数据库常用指令

1、数据库备份

C:\ShenTong\bin>osrimp -u sysdba/szoscar55 -d osrdb file="C:\2019-09-16.osr" level=schema schema=TEST;

create user TEST password 123456;

2、查询模式下的所有表

SELECT
    n . nspname AS  "SCHEMA" ,
    c . relname AS  "table_name" ,
    CASE c . relkind 
        WHEN r THEN table 
        WHEN f THEN partition table 
        WHEN v THEN view 
        WHEN i THEN index 
        WHEN g THEN partition index 
        WHEN S THEN sequence 
        WHEN s THEN special 
        WHEN m THEN materialized view 
        WHEN k THEN table(kstore) 
        WHEN K THEN partition table(kstore) 
        WHEN e THEN table(external) 
    END AS  "TYPE" ,
    u . usename AS  "OWNER" 
FROM 
    info_schem . v_sys_class c 
    LEFT JOIN 
        info_schem . v_sys_user u 
        ON u . usesysid = c . relowner 
    LEFT JOIN 
        info_schem . v_sys_namespace n 
        ON n . oid = c . relnamespace 
WHERE 
    n . nspdbid = current_database_id ()
    AND c . relname <> SYS_JOBS 
    AND c . relkind IN (r , f , k , K , e , ‘‘ ) 
    AND n . nspname NOT IN (INFO_SCHEM ) 
    AND n . nspname IN (ADP ) 
ORDER BY 
    1 ,
    2;

3、添加字段

ALTER TABLE DSAA.SYS_LOG ADD OPERATIONID VARCHAR(100) ;
COMMENT ON COLUMN DSAA.SYS_LOG.OPERATIONID IS 操作标识 ;

4、查看kstore服务

--重启服务
/etc/init.d/kstoredb_KSTOREd restart
--查看数据库的状态
/etc/init.d/kstoredb_KSTOREd status
--查看服务
ll /etc/init.d/

5、乱码配置

 <Connector URIEncoding="UTF-8" connectionTimeout="2000000" port="8180" protocol="HTTP/1.1" redirectPort="8443"/>

 6、查询所有字段

 SELECT COLUMN_NAME as "columnEn",REMARKS as "columnCn", 
      TYPE_NAME as "datatype",COLUMN_SIZE as "fieldLength"
 FROM V_SYS_COLUMNS 
WHERE TABLE_NAME = AAAAA 
  AND TABLE_SCHEM = AAAAA
  AND COLUMN_NAME <> SYSATTR_ROWVERSION 
  AND COLUMN_NAME <> ROWID
  AND COLUMN_NAME <> ROWVERSION
  AND COLUMN_NAME <> DATAPACKNO

7、查询所有表

SELECT TABLE_NAME FROM V_SYS_TABLES

8、设置当前查询模式

SET SEARCH_PATH=SYSDBA,AAAAA,PUBLIC;

 

数据库常用指令

上一篇:防sql注入攻击


下一篇:MySQL ---------- 数据处理函数(文本、日期、数值)(十)