Oracle数据库小白备忘

sqlplus相关

• 导入sql文件

在sqlplus中，导入一个sql文件，是使用@或者start。

如当前目录下有一个hello.sql，则可以使用

@hello.sql

或者

start hello.sql

来进行导入，功能类似于mysql里面的source。

• 退出编辑模式

当使用sqlplus编写存储过程，或者触发器，sqlplus会进入一种编辑模式，即前面一个行号，后面是一个输入区域。

退出这个编辑模式的字符是’/'。其它各种诸如EXIT、QUIT、Ctrl-C、Ctrl-D等都不行，只有/可以退出来。

帐号相关

• 创建帐号、授权

Oracle的权限角色很多，其中CONNECT和RESOURCE是对数据库进行增删改查的角色，SELECT_CATALOG_ROLE可以查询一些数据库的配置信息。

CREATE USER hello IDENTIFIED BY world;
GRANT CONNECT, RESOURCE SELECT_CATALOG_ROLE TO hello;

可以使用如下命令查询当前帐号的角色

select granted_role from user_role_privs;

还可以切换到sysdba，查询所有的角色，以及特定用户的角色。

查询所有可以使用的角色

select role from dba_roles;

查询特定用户的角色

select granted_role from dba_role_privs where grantee=‘HELLO’;

注意上面的grantee=后面的帐号需要大写

• 删除帐号

Oracle删除帐号可以加上CASCADE，即把帐号下面的表、视图等数据库对象都删除掉。

DROP USER hello CASCADE;

否则，就需要先删除帐号下面的所有数据库对象，之后再删除帐号。

• 帐号解锁

查看帐号锁定状态

select username,account_status,lock_date from dba_users where username=‘READER’;

解锁

alter user READER account unlock;

注意：在Oracle数据库中，username一列存储的值是大写的。做where查询的时候，需要注意。

Debezium相关

如果使用Debezium获取数据库的变更，用户除了要有CONNECT、RESOURCE角色以外，还需要有对数据库配置信息以及logminer相关的一些角色。

Debezium的官方文档有一个列表，如下：

sqlplus sys/top_secret@//localhost:1521/ORCLCDB as sysdba
  CREATE TABLESPACE logminer_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/logminer_tbs.dbf'
    SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
  exit;

sqlplus sys/top_secret@//localhost:1521/ORCLPDB1 as sysdba
  CREATE TABLESPACE logminer_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/logminer_tbs.dbf'
    SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
  exit;

sqlplus sys/top_secret@//localhost:1521/ORCLCDB as sysdba

  CREATE USER c##dbzuser IDENTIFIED BY dbz
    DEFAULT TABLESPACE logminer_tbs
    QUOTA UNLIMITED ON logminer_tbs
    CONTAINER=ALL;

  GRANT CREATE SESSION TO c##dbzuser CONTAINER=ALL; 
  GRANT SET CONTAINER TO c##dbzuser CONTAINER=ALL; 
  GRANT SELECT ON V_$DATABASE to c##dbzuser CONTAINER=ALL; 
  GRANT FLASHBACK ANY TABLE TO c##dbzuser CONTAINER=ALL; 
  GRANT SELECT ANY TABLE TO c##dbzuser CONTAINER=ALL; 
  GRANT SELECT_CATALOG_ROLE TO c##dbzuser CONTAINER=ALL; 
  GRANT EXECUTE_CATALOG_ROLE TO c##dbzuser CONTAINER=ALL; 
  GRANT SELECT ANY TRANSACTION TO c##dbzuser CONTAINER=ALL; 
  GRANT LOGMINING TO c##dbzuser CONTAINER=ALL; 

  GRANT CREATE TABLE TO c##dbzuser CONTAINER=ALL; 
  GRANT LOCK ANY TABLE TO c##dbzuser CONTAINER=ALL; 
  GRANT CREATE SEQUENCE TO c##dbzuser CONTAINER=ALL; 

  GRANT EXECUTE ON DBMS_LOGMNR TO c##dbzuser CONTAINER=ALL; 
  GRANT EXECUTE ON DBMS_LOGMNR_D TO c##dbzuser CONTAINER=ALL; 

  GRANT SELECT ON V_$LOG TO c##dbzuser CONTAINER=ALL; 
  GRANT SELECT ON V_$LOG_HISTORY TO c##dbzuser CONTAINER=ALL; 
  GRANT SELECT ON V_$LOGMNR_LOGS TO c##dbzuser CONTAINER=ALL; 
  GRANT SELECT ON V_$LOGMNR_CONTENTS TO c##dbzuser CONTAINER=ALL; 
  GRANT SELECT ON V_$LOGMNR_PARAMETERS TO c##dbzuser CONTAINER=ALL; 
  GRANT SELECT ON V_$LOGFILE TO c##dbzuser CONTAINER=ALL; 
  GRANT SELECT ON V_$ARCHIVED_LOG TO c##dbzuser CONTAINER=ALL; 
  GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO c##dbzuser CONTAINER=ALL; 
  GRANT SELECT ON V_$TRANSACTION TO c##dbzuser CONTAINER=ALL; 

  GRANT SELECT ON V_$MYSTAT TO c##dbzuser CONTAINER=ALL; 
  GRANT SELECT ON V_$STATNAME TO c##dbzuser CONTAINER=ALL; 

  exit;

数据相关

• 显示用户下的数据表

select table_name from user_tables;

• 批量创建数据表

BEGIN  
    FOR i IN 1..5  
        LOOP  
            -- 创建5个表  
            EXECUTE IMMEDIATE 'CREATE TABLE test_table_' || i || ' (  
            id NUMBER GENERATED BY DEFAULT AS IDENTITY,            name VARCHAR2(100),            age NUMBER,            address VARCHAR2(255),            created_at TIMESTAMP,            clob_field CLOB,            blob_field BLOB,            number_field NUMBER,            date_field DATE,            varchar_field VARCHAR2(200),            PRIMARY KEY (id)        )';  
        END LOOP;  
END;  
/

• 批量插入测试数据

BEGIN
    -- 对每个表进行插入
    FOR i IN 1..5
        LOOP
            -- 插入100万条数据
            FOR j IN 1..1000000
                LOOP
                    DECLARE
                        l_blob BLOB;
                        l_clob CLOB;
                    BEGIN
                        -- 初始化BLOB和CLOB
                        DBMS_LOB.CREATETEMPORARY(l_blob, TRUE);
                        DBMS_LOB.CREATETEMPORARY(l_clob, TRUE);

                        -- 填充CLOB字段
                        DBMS_LOB.WRITEAPPEND(l_clob, LENGTH('This is a CLOB field for record ' || j),
                                             'This is a CLOB field for record ' || j);

                        -- 填充BLOB字段（插入10K个字节的随机数据）
                        FOR k IN 1..10000
                            LOOP
                                DBMS_LOB.WRITEAPPEND(l_blob, 1, CHR(MOD(DBMS_RANDOM.VALUE(0, 256), 256)));
                            END LOOP;

                        -- 插入数据
                        EXECUTE IMMEDIATE 'INSERT INTO test_table_' || i || ' (name, age, address, created_at, clob_field, blob_field, number_field, date_field, varchar_field) VALUES (
                    ''Name '' || ' || j || ',
                    ' || MOD(j, 100) || ',
                    ''Address '' || ' || j || ',
                    SYSTIMESTAMP,
                    :clob_data,
                    :blob_data,
                    ' || MOD(j, 1000) || ',
                    SYSDATE,
                    ''Varchar value '' || ' || j || '
                )' USING l_clob, l_blob;

                        -- 释放临时LOB
                        DBMS_LOB.FREETEMPORARY(l_blob);
                        DBMS_LOB.FREETEMPORARY(l_clob);
                    END;
                END LOOP;
        END LOOP;
END;
/

• 查询数据库的dbid

select dbid from v$database;

维护相关

• 确定删除归档日志文件

不再需要的归档日志，可以清理掉，节省磁盘空间。

使用sqlplus查询归档日志

select name, completion_time, status from v$archived_log;

对归档文件进行操作前，先关闭数据库实例

shutdown immediate

进入命令行，删除不使用的归档日志

rm -rf ARCHI*

重新启动

startup

使用rman备份工具，清除失效的archivelog

connect target /
crosscheck archivelog all;
delete expired archivelog all;

另外，可以使用这些sql语句更改archivelog配置

alter system set db_archive_file_dest_size=50G;
shutdown immediate;
startup;
show parameter db_recorver;