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;