Oracle, Postgres, MySQL database build, create user, lead export backup summary

What this article contains: Use commands to manipulate import and export of Oracle, Postgres, MySQL, including logging on to data

Note: I am using the Center OS operating system in the company, so both Oracle and Postgres are used in Linux and not tested in other tools, and MySQL is used on its own laptops, both under Linux and DOS commands.

1.Oracle:

• Switch to Oracle User: su-oracle
• Login database:sqlplus username/password @172.10.103.78:1521/orcl as SYSDBA; (user name can be used in system: SYS)
• Creating tablespace: Createtablespace table space name datafile '/xx/xxx/xx.dbf ' size 1000M autoextend on next 100M;
• Creating User: Create user usernameidentified by password default tablespace table space;
• Authorization:grant CONNECT,RESOURCE,DBA to user name; (Subject to availability)

　　

　　Note: empty table processing may be required before exporting a table:

　　　　Select COUNT (1) from user_tables where num_rows = 0; --Query the number of empty tables

　　　　Select ' ALTER TABLE ' | | table_name | | ' Allocate extent; ' from user_tables where num_rows = 0;

• Export:EXP user name/password @ip:sid/orcl file=/xxx/xxx/xx.dmp such as: Exp platform/[email PROTECTED]:1521/ORCL file=/data/oracle /platform_20180824.dmp;
• Import:imp username/password @ip:sid/orcl file=/xxx/xxx/xxx.dmp ignore=y full=y; such as: Imp platform/[email protected]:1521/orcl file=/data/oracle/platform_20180824.dmp ignore=y full=y;

　　

Other operations

• Check User: SELECT * from Dba_users;
• Query use tablespace: Select Tablespace_name from User_tables Group by Tablespace_name;
• Query the current user default tablespace: Select Default_tablespace from dba_users where username= ' username ';
• View table space Name, path: Select T1.name, t2.name from V$tablespace T1, V$datafile T2 where T1.ts #=t2.tx#;

2.postgres

　　

Oracle, Postgres, MySQL database build, create user, lead export backup summary