Create table space and import oracle data in Renda Jincang under Tongxin

Because of the localization, the database adopts the domestically produced renda gold warehouse, and the data originally stored in Oracle 12c will also be migrated.

Renda Jincang, changed from the open source database project pg (postgreSql), this is also admitted by Renda Jincang. Therefore, if the information is insufficient, it is also possible to directly check the pg, which is highly similar. However, this is understandable. First, solve the problem of existence and modify it with an open source framework. After you have fully understood it and reserve talents, you can introduce new ideas and bring forth new ideas. Operating systems, databases, middleware, and basic application software (such as matlab) are all in their own hands.

What are the steps for data migration from Oracle to KingBase8?

1. Create a database, table space and schema in the NPC Jincang

1. The basic concepts of TableSpace and Schema

Oracle has tablespaces and schemas, and Renda Jincang also has them. Table space is a concept of physical storage, storing data files, index files and the like. The schema is a logical concept, corresponding to the relational schema of the database, such as table structure, field types, view definitions, and so on.

In Oracle, it seems that there is no place to create and modify this schema. Some only create tablespaces. Then it seems that the schema name and the tablespace name are naturally consistent, which often makes people equate the two. In the Renda Jincang, you can operate this scheme independently. A database can correspond to multiple table spaces, and a table space can also be used by multiple databases. In Oracle, the table space is more like a namespace, a logical concept; in the Renda Jincang, the table space is essentially a folder.

TableSpace and Schema of Renda Jincang

Insert picture description here


Insert picture description here


2. Operation commands Renda
Jincang provides graphical database object management tools, which can be used to create databases, table spaces, schemas, query data, and so on. But the most convenient way is to use its command line.

1) Log in to the database In the
operating system terminal, enter:

ksql -h 127.0.0.1 -U 账号 -d 数据库名称

After entering the password in this way, you enter the command line of kingbase or, say, enter the terminal of kingbase.

2) Exit the NPC Jincang terminal and return to the operating system terminal

\q

A slash plus a q. The kingbase command starts with a slash. Just write sql.

3) Switch account or database When
logging in, the account and database are specified. After entering, if you want to switch accounts or databases, you don’t have to log out and enter again. can

\c 数据库名 账号名

This will enter the new database with a new account

Insert picture description here


4) Create and delete tablespaces

GZFBC=# create tablespace gzfbc location '/home/chenqu/data/gzfbc';    
CREATE TABLESPACE

GZFBC=# drop tablespace GZFBC;
DROP TABLESPACE

To delete a table space, the table space needs to be empty and not used.

4-1) Modify the default tablespace of the database

GZFBC=# alter database GZFBC set tablespace HYDG;
错误:  无法改变当前已打开数据库的表空间

Note that the current database cannot modify itself. Therefore, you should switch to another database, and then operate.

5) View all table spaces under the current instance

GZFBC=# \db
 GZFBC       | SYSTEM | /home/chenqu/data/gzfbc
 SYS_DEFAULT | SYSTEM | 
 SYS_GLOBAL  | SYSTEM | 

6) View all modes under the current database

GZFBC=# \dn
       List of schemas
        Name        | Owner  
--------------------+--------
 GZFBC_HYDG         | WORK
 HYDG               | SYSTEM
 INFORMATION_SCHEMA | SYSTEM
 PUBLIC             | SYSTEM
 SYSAUDIT           | SYSTEM
 SYSLOGICAL         | SYSTEM
 XLOG_RECORD_READ   | SYSTEM
(7 rows)

7) View the table in the current mode

GZFBC=# \dt
                List of relations
 Schema |         Name          | Type  | Owner  
--------+-----------------------+-------+--------
 PUBLIC | PATHMAN_CONFIG        | table | SYSTEM
 PUBLIC | PATHMAN_CONFIG_PARAMS | table | SYSTEM
(2 rows)

8) View the table structure

GZFBC=# \d catchresult
 CATCHRESULTID | NUMERIC(18,0)               | not null
 WEBSITEID     | NUMERIC(18,0)               | not null
 CATEGORYID    | NUMERIC(18,0)               | not null
 WEBPAGEID     | NUMERIC(18,0)               | not null
 TITLE         | CHARACTER VARYING(300 byte) | not null
 URL           | CHARACTER VARYING(300 byte) | not null
 CREATEDATE    | TIMESTAMP WITHOUT TIME ZONE | not null
 CONTENT       | CLOB                        | 

9) Administrator and ordinary account
Note that if it is an administrator, the command is followed by the "#" sign, and the ordinary account is followed by the ">" sign

Insert picture description here

Two, data migration

The database migration uses the migration tool provided by Renda Jincang. The name is "Data Migration Tool".

1. First establish 2 connections between the source database and the target database.

Insert picture description here


Insert picture description here


2. Data Migration
Create a data migration task, select the source and target databases, and go step by step. Okay.

3. Table space and schema When
migrating to kingbase, the system defaults to the same Oracle schema as that of kingbase. If there is no corresponding schema on kingbase, the system will automatically create one. Of course, it can be set before migration. If discovered after importing, you can also adjust:

1) Rename the schema

GZFBC=# ALTER SCHEMA HYDG rename to GZFBC_HYDG;
ALTER SCHEMA

Set the default schema of the user work. Note that after the schema is renamed, the original default settings will become invalid and need to be specified again.

GZFBC=# ALTER USER "work" set search_path to GZFBC_HYDG;

2) Grant permissions The permissions of
Renda Jincang are divided into three levels: database level, model level, and objects under the model. Set the owner of a mode to an account, then under normal circumstances, the table, view, etc. under the mode, the account can logically have all permissions. But this is limited to normal conditions. If the data migration comes to a certain mode, it seems that there is no such inheritance relationship. But I'm not sure if this is the case, because I have operated a lot, and I changed my name and owner.

What if the account doesn't have the authority to own the objects under the mode? Can only be manually empowered:

grant all on all sequences  in schema GZFBC_HYDG to "work";
grant all on all tables in schema GZFBC_HYDG to "work";

3) View the table space corresponding to the database

select d.datname,p.spcname from sys_database d, sys_tablespace p where d.datname='数据库名称' and p.oid = d.dattablespace;

Three, summary

The current Renda Jincang is basically very similar to pg. It feels like simply changing the prefix of the system table from pg_ to sys_. It seems that all the pg data that I have read can be used.

One of the strange things is that the database name and user name are automatically changed to uppercase by the system, even if we write them in lowercase deliberately. Kingbase is case sensitive, like account numbers. But SQL is an exception. So if you really want to use lowercase, you should enclose the name in double quotes. Why is there such a regulation, it feels like the designer's head has been kicked by a donkey.