环境介绍：
OB 3.2.4
RHEL 7.6
1、MySQL租户支持索引、表、数据库、租户
2、Oracle租户支持，索引、表
MySQL模式的时间和snapshot转换可以通过time_to_usec和usec_to_time函数实现。
由于时间精度为微妙，所以在转换时后6位为0，Oracle租户scn和time转换暂时是通过MySQL的租户进行转换。
租户管理员操作
mysql -h192.168.15.66 -P2883 -uroot@sysbench_poc#Gaoyc:1667379307 -p'an~7&jtGcd|F43uoo*AOK' -A
mysql -h192.168.15.66 -P2883 -usysbench_test@sysbench_poc#Gaoyc:1667379307 -p'an~7&jtGcd|F43uoo*AOK' -A
obclient -h192.168.15.66 -P2883 -uSYS@ORADB#Gaoyc:1667379307 -p'an~7&jtGcd|F43uoo*AOK' -A
obclient -h192.168.15.66 -P2883 -uUSERGYC@ORADB#Gaoyc -pusergyc -A
[root@dbdriver ~]# obclient -h10.51.175.66 -P2883 -uUSERGYC@ORADB#Gaoyc -pusergyc -A
Oracle查看当前时间：
MySQL [(none)]> select sysdate from dual;
+---------------------+
| SYSDATE |
+---------------------+
| 2023-05-17 11:21:57 |
+---------------------+
1 row in set (0.001 sec)
MySQL [(none)]> show variables like '%undo%';
+----------------+-------+
| VARIABLE_NAME | VALUE |
+----------------+-------+
| undo_retention | 1800 |
+----------------+-------+
1 row in set (0.001 sec)
闪回查询：
MySQL [(none)]> create table test as select * from all_objects;
Query OK, 470 rows affected (1.523 sec)
MySQL [(none)]> commit;
Query OK, 0 rows affected (0.000 sec)
MySQL [(none)]> select sysdate from dual;
+---------------------+
| SYSDATE |
+---------------------+
| 2023-05-17 11:24:44 |
+---------------------+
1 row in set (0.001 sec)
MySQL [(none)]> select count(*) from test;
+----------+
| COUNT(*) |
+----------+
| 470 |
+----------+
1 row in set (0.012 sec)
MySQL [(none)]> insert into test select * from test;
Query OK, 470 rows affected (0.013 sec)
Records: 470 Duplicates: 0 Warnings: 0
MySQL [(none)]> commit;
Query OK, 0 rows affected (0.003 sec)
MySQL [(none)]> insert into test select * from test;
Query OK, 940 rows affected (0.007 sec)
Records: 940 Duplicates: 0 Warnings: 0
MySQL [(none)]> commit;
Query OK, 0 rows affected (0.005 sec)
MySQL [(none)]> select sysdate from dual;
+---------------------+
| SYSDATE |
+---------------------+
| 2023-05-17 11:25:10 |
+---------------------+
1 row in set (0.001 sec)
MySQL [(none)]> insert into test select * from test;
Query OK, 1880 rows affected (0.013 sec)
Records: 1880 Duplicates: 0 Warnings: 0
MySQL [(none)]> commit;
Query OK, 0 rows affected (0.009 sec)
MySQL [(none)]> select sysdate from dual;
+---------------------+
| SYSDATE |
+---------------------+
| 2023-05-17 11:25:17 |
+---------------------+
1 row in set (0.000 sec)
MySQL [(none)]> select count(*) from test;
+----------+
| COUNT(*) |
+----------+
| 3760 |
+----------+
1 row in set (0.002 sec)
MySQL [(none)]> desc test;
+----------------+---------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+----------------+---------------+------+-----+---------+-------+
| OWNER | VARCHAR2(128) | YES | NULL | NULL | NULL |
| OBJECT_NAME | VARCHAR2(128) | YES | NULL | NULL | NULL |
| SUBOBJECT_NAME | VARCHAR2(128) | YES | NULL | NULL | NULL |
| OBJECT_ID | NUMBER | YES | NULL | NULL | NULL |
| DATA_OBJECT_ID | NUMBER | YES | NULL | NULL | NULL |
| OBJECT_TYPE | VARCHAR2(23) | YES | NULL | NULL | NULL |
| CREATED | DATE | YES | NULL | NULL | NULL |
| LAST_DDL_TIME | DATE | YES | NULL | NULL | NULL |
| TIMESTAMP | VARCHAR2(256) | YES | NULL | NULL | NULL |
| STATUS | VARCHAR2(7) | YES | NULL | NULL | NULL |
| TEMPORARY | VARCHAR2(1) | YES | NULL | NULL | NULL |
| GENERATED | VARCHAR2(1) | YES | NULL | NULL | NULL |
| SECONDARY | VARCHAR2(1) | YES | NULL | NULL | NULL |
| NAMESPACE | NUMBER | YES | NULL | NULL | NULL |
| EDITION_NAME | VARCHAR2(128) | YES | NULL | NULL | NULL |
+----------------+---------------+------+-----+---------+-------+
15 rows in set (0.002 sec)
MySQL [(none)]> delete from test where object_id<1000;
Query OK, 0 rows affected (0.005 sec)
MySQL [(none)]> delete from test where object_id<100000;
Query OK, 0 rows affected (0.004 sec)
MySQL [(none)]> select min(object_id) from test;
+----------------+
| MIN(OBJECT_ID) |
+----------------+
| 1099511628777 |
+----------------+
1 row in set (0.005 sec)
MySQL [(none)]> select max(object_id) from test;
+------------------+
| MAX(OBJECT_ID) |
+------------------+
| 1100611139453779 |
+------------------+
1 row in set (0.004 sec)
MySQL [(none)]> delete from test where object_id<1099511628999;
Query OK, 2472 rows affected (0.023 sec)
MySQL [(none)]> commit;
Query OK, 0 rows affected (0.013 sec)
MySQL [(none)]> select count(*) from test;
+----------+
| COUNT(*) |
+----------+
| 1288 |
+----------+
1 row in set (0.002 sec)
MySQL [(none)]> select count(*) from test as of timestamp to_timestamp('2023-05-17 11:25:17','yyyy-mm-dd hh24:mi:ss');
+----------+
| COUNT(*) |
+----------+
| 3760 |
+----------+
1 row in set (0.005 sec)
MySQL [(none)]> select count(*) from test as of timestamp to_timestamp('2023-05-17 11:25:10','yyyy-mm-dd hh24:mi:ss'), test as of timestamp to_timestamp('2023-05-17 11:24:44','yyyy-mm-dd hh24:mi:ss');
+----------+
| COUNT(*) |
+----------+
| 883600 |
+----------+
1 row in set (0.067 sec)
MySQL [(none)]> select count(*) from test as of timestamp to_timestamp('2023-05-17 11:25:10','yyyy-mm-dd hh24:mi:ss');
+----------+
| COUNT(*) |
+----------+
| 1880 |
+----------+
1 row in set (0.002 sec)
MySQL [(none)]> create table tt as select * from test as of timestamp to_timestamp('2023-05-17 11:25:10','yyyy-mm-dd hh24:mi:ss');
Query OK, 18880 rows affected (0.113 sec)
时间转换scn，通过MySQL租户转换
MySQL [gyc]> select time_to_usec('2023-05-17 11:25:10');
+-------------------------------------+
| time_to_usec('2023-05-17 11:25:10') |
+-------------------------------------+
| 1684293910000000 |
+-------------------------------------+
1 row in set (0.00 sec)
MySQL [gyc]> select time_to_usec('2023-05-17 11:25:17');
+-------------------------------------+
| time_to_usec('2023-05-17 11:25:17') |
+-------------------------------------+
| 1684293917000000 |
+-------------------------------------+
MySQL [gyc]> select usec_to_time(1684293917000000);
+-------------------------------------+
| time_to_usec('1684293917000000') |
+-------------------------------------+
| 2023-05-17 11:25:17.000000 |
+-------------------------------------+
MySQL [(none)]> select count(*) from test as of scn 1684293910000000;
+----------+
| COUNT(*) |
+----------+
| 1880 |
+----------+
1 row in set (0.006 sec)
MySQL [(none)]> select count(*) from test as of scn 1684293917000000;
+----------+
| COUNT(*) |
+----------+
| 3760 |
+----------+
1 row in set (0.002 sec)
MySQL查看当前时间：
[root@dbdriver ~]# mysql -h10.51.175.66 -P2883 -usysbench_test@sysbench_poc#Gaoyc:1667379307 -p'an~7&jtGcd|F43uoo*AOK' -A
MySQL [(none)]> select now();
+---------------------+
| now() |
+---------------------+
| 2023-05-17 11:22:40 |
+---------------------+
1 row in set (0.00 sec)
MySQL [(none)]> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2023-05-17 11:22:43 |
+---------------------+
1 row in set (0.00 sec)
MySQL [(none)]> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2023-05-17 11:23:25 |
+---------------------+
1 row in set (0.00 sec)
MySQL [(none)]> show variables like '%undo%';
+----------------+-------+
| VARIABLE_NAME | VALUE |
+----------------+-------+
| undo_retention | 1800 |
+----------------+-------+
1 row in set (0.001 sec)
MySQL [gyc]> select now();
+---------------------+
| now() |
+---------------------+
| 2023-05-17 11:34:52 |
+---------------------+
1 row in set (0.00 sec)
MySQL [gyc]> select time_to_usec('2023-05-17 11:34:52');
+-------------------------------------+
| time_to_usec('2023-05-17 11:34:52') |
+-------------------------------------+
| 1684294492000000 |
+-------------------------------------+
1 row in set (0.00 sec)
MySQL [gyc]> select time_to_usec('2023-05-17 11:34:52') snapshot;
+------------------+
| snapshot |
+------------------+
| 1684294492000000 |
+------------------+
1 row in set (0.00 sec)
MySQL [gyc]> select time_to_usec('2023-05-17 11:25:10');
+-------------------------------------+
| time_to_usec('2023-05-17 11:25:10') |
+-------------------------------------+
| 1684293910000000 |
+-------------------------------------+
1 row in set (0.00 sec)
MySQL [gyc]> select time_to_usec(now()),now();
+---------------------+---------------------+
| time_to_usec(now()) | now() |
+---------------------+---------------------+
| 1684294672000000 | 2023-05-17 11:37:52 |
+---------------------+---------------------+
1 row in set (0.00 sec)
MySQL [gyc]> select count(*) from testdb;
+----------+
| count(*) |
+----------+
| 12 |
+----------+
1 row in set (0.00 sec)
MySQL [gyc]> insert into testdb select * from testdb;
Query OK, 12 rows affected (0.01 sec)
Records: 12 Duplicates: 0 Warnings: 0
MySQL [gyc]> insert into testdb select * from testdb;
Query OK, 24 rows affected (0.00 sec)
Records: 24 Duplicates: 0 Warnings: 0
MySQL [gyc]> commit;
Query OK, 0 rows affected (0.00 sec)
MySQL [gyc]> select time_to_usec(now()),now();
+---------------------+---------------------+
| time_to_usec(now()) | now() |
+---------------------+---------------------+
| 1684294704000000 | 2023-05-17 11:38:24 |
+---------------------+---------------------+
1 row in set (0.00 sec)
MySQL [gyc]> insert into testdb select * from testdb;
Query OK, 48 rows affected (0.00 sec)
Records: 48 Duplicates: 0 Warnings: 0
MySQL [gyc]> insert into testdb select * from testdb;
Query OK, 96 rows affected (0.01 sec)
Records: 96 Duplicates: 0 Warnings: 0
MySQL [gyc]> commit;
Query OK, 0 rows affected (0.00 sec)
MySQL [gyc]> select time_to_usec(now()),now();
+---------------------+---------------------+
| time_to_usec(now()) | now() |
+---------------------+---------------------+
| 1684294717000000 | 2023-05-17 11:38:37 |
+---------------------+---------------------+
1 row in set (0.00 sec)
MySQL [gyc]> delete from testdb;
Query OK, 192 rows affected (0.00 sec)
MySQL [gyc]> commit;
Query OK, 0 rows affected (0.00 sec)
MySQL [gyc]> select count(*) from testdb as of snapshot 1684294717000000;
+----------+
| count(*) |
+----------+
| 192 |
+----------+
1 row in set (0.00 sec)
MySQL [gyc]> select count(*) from testdb as of snapshot 1684294704000000;
+----------+
| count(*) |
+----------+
| 48 |
+----------+
1 row in set (0.00 sec)
MySQL [gyc]> select count(*) from testdb as of snapshot 1684294672000000;
+----------+
| count(*) |
+----------+
| 12 |
+----------+
1 row in set (0.00 sec)
MySQL [gyc]> select count(*) from testdb;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
MySQL [gyc]> create table tdb as select * from testdb as of snapshot 1684294704000000;
Query OK, 48 rows affected (0.00 sec)
注：生产上的操作请经过严格测试。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24585765/viewspace-2952741/ ，如需转载，请注明出处，否则将追究法律责任。
