oralce常用命令

解锁表空间：alter user JNPF_TEST account unlock;
修改密码：alter user JNPF_TEST identified by test

--新建表空间
CREATE TABLESPACE nameINIT DATAFILE 'C:runTimetablespacenameINIT.dbf' size 100M autoextend on next 100M maxsize unlimited;
-- 新建用户,赋连接权限
CREATE USER nameDBA IDENTIFIED BY name2020 DEFAULT TABLESPACE nameINIT;
GRANT CONNECT TO nameDBA;
GRANT DBA TO nameDBA;

如果需要为一个用户指定一个限额，可以有两种方法：

• 在创建用户的时候指定限额：
  

CREATE USER ABC IDENTIFIED BY ABC

DEFAULT TABLESPACE TEST

TEMPORARY TABLESPACE TEMP

QUOTA 3M ON TEST;

• 在创建用户完成之后对用户限额进行指定：
  

CREATE USER BCD IDENTIFIED BY BCD

DEFAULT TABLESPACE TEST;

ALTER USER BCD QUOTA 3M ON TEST;

• 更改用户的表空间限额：
  

全局：

grant unlimited tablespace to abc;

针对某个表空间：

alter user abc quota unlimited on test;

回收：

revoke unlimited tablespace from abc;

alter user abc quota 0 on test;

imp 用户名/密码@ip/服务名 file=文件.dmp full=y

exp 用户名/密码@ip/服务名 file=文件.dmp full=y

exp nameDBA/JNPF2020@localhost/ORCL file=C:runTimename_DB.dmp full=y

exp nameDBA/JNPF2020 file=C:/name_DB.dmp

批量插入语句
INSERT ALL INTO TEST_DETAILS ( F_ID, F_RECEIVABLEID, F_MONTH, F_RENT, F_WATERRENT, F_POWERRATE, F_PROPERTY, F_CLEANING, F_POLLUTANT, F_TOTALBILLING )
VALUES
( 'd33265215e2d04f018eb4dffa57f9cf2b', '602fc0119fef4fe3ac3b996733e0c455', '1', '2', '3', '4', NULL, NULL, NULL, NULL ) INTO TEST_DETAILS ( F_ID, F_RECEIVABLEID, F_MONTH, F_RENT, F_WATERRENT, F_POWERRATE, F_PROPERTY, F_CLEANING, F_POLLUTANT, F_TOTALBILLING )
VALUES
( '8e65ebc531f6a43b3bb29029472dce0fc', '602fc0119fef4fe3ac3b996733e0c455', '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL ) SELECT
1
FROM
DUAL

注意结尾不要加分号

赋予xa权限

grant select on sys.dba_pending_transactions to xmglxt ;
grant select on sys.pending_trans$ to xmglxt;
grant select on sys.dba_2pc_pending to xmglxt;
grant execute on sys.dbms_system to xmglxt;

解锁表空间：alter user JNPF_TEST account unlock;
修改密码：alter user JNPF_TEST identified by test

--新建表空间
CREATE TABLESPACE nameINIT DATAFILE 'C:runTimetablespacenameINIT.dbf' size 100M autoextend on next 100M maxsize unlimited;
-- 新建用户,赋连接权限
CREATE USER nameDBA IDENTIFIED BY name2020 DEFAULT TABLESPACE nameINIT;
GRANT CONNECT TO nameDBA;
GRANT DBA TO nameDBA;

如果需要为一个用户指定一个限额，可以有两种方法：

• 在创建用户的时候指定限额：
  

CREATE USER ABC IDENTIFIED BY ABC

DEFAULT TABLESPACE TEST

TEMPORARY TABLESPACE TEMP

QUOTA 3M ON TEST;

• 在创建用户完成之后对用户限额进行指定：
  

CREATE USER BCD IDENTIFIED BY BCD

DEFAULT TABLESPACE TEST;

ALTER USER BCD QUOTA 3M ON TEST;

• 更改用户的表空间限额：
  

全局：

grant unlimited tablespace to abc;

针对某个表空间：

alter user abc quota unlimited on test;

回收：

revoke unlimited tablespace from abc;

alter user abc quota 0 on test;

imp 用户名/密码@ip/服务名 file=文件.dmp full=y

exp 用户名/密码@ip/服务名 file=文件.dmp full=y

exp nameDBA/JNPF2020@localhost/ORCL file=C:runTimename_DB.dmp full=y

exp nameDBA/JNPF2020 file=C:/name_DB.dmp

批量插入语句
INSERT ALL INTO TEST_DETAILS ( F_ID, F_RECEIVABLEID, F_MONTH, F_RENT, F_WATERRENT, F_POWERRATE, F_PROPERTY, F_CLEANING, F_POLLUTANT, F_TOTALBILLING )
VALUES
( 'd33265215e2d04f018eb4dffa57f9cf2b', '602fc0119fef4fe3ac3b996733e0c455', '1', '2', '3', '4', NULL, NULL, NULL, NULL ) INTO TEST_DETAILS ( F_ID, F_RECEIVABLEID, F_MONTH, F_RENT, F_WATERRENT, F_POWERRATE, F_PROPERTY, F_CLEANING, F_POLLUTANT, F_TOTALBILLING )
VALUES
( '8e65ebc531f6a43b3bb29029472dce0fc', '602fc0119fef4fe3ac3b996733e0c455', '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL ) SELECT
1
FROM
DUAL

注意结尾不要加分号

赋予xa权限

grant select on sys.dba_pending_transactions to xmglxt ;
grant select on sys.pending_trans$ to xmglxt;
grant select on sys.dba_2pc_pending to xmglxt;
grant execute on sys.dbms_system to xmglxt;