【基本命令】linux、数据库基本操作命令

最新推荐文章于 2024-05-14 15:38:07 发布

entrypio

最新推荐文章于 2024-05-14 15:38:07 发布

阅读量1.5k

点赞数

文章标签：数据库 linux 运维

本文链接：https://blog.csdn.net/entrypio/article/details/129657209

版权

常用命令总结

1、数据库导出导入语句：

--创建数据库用户并赋予权限

create user ccv73afg identified by smart default tablespace tab_cc temporary tablespace temp;

grant all privileges to ccv73afg;

grant dba to ccv73afg;

grant select any dictionary to ccv73afg;

--数据库导出导入

exp 数据库名/密码@连接串 file=路径/文件名.dmp

imp 数据库名/密码@连接串 fromuser=user touser=user file=路径/文件名.dmp ignore=y

exp ccafh_st2/smart@CC33 file=/oracle11/lihui/ccafh.dmp

imp ccv73afg/smart@CC170ORA11 fromuser=ccafh touser=ccv73afg file=/oracle11/lihui/ccafh.dmp ignore=y （需要知道并指定 fromuser和touser）

imp ccv73afg /smart@CC170ORA11 file=/oracle11/lihui/ ccafh.dmp ignore=y full=y （不需要指定 fromuser和touser）

--查看表空间使用情况

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,

(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"

FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C

WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

2、创建dblink命令(LINK_RB/LINK_CC)

-- Create database link

create database link LINK_RB

connect to RBSH70J identified by smart

using '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.45.4.170)(PORT = 1522))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = rb)

)

)';

-- Create database link

create database link LINK_CC

connect to CCSH70J identified by smart

using '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.45.4.170)(PORT = 1522))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = cc)

)

)';

3、存储过程：

------------------批量制造订户存过的存储过程------------------
------------------测试发送邮件，按照调通号码为模板，批量制造数据------------------
----一般在subs_upp_inst表之前的表都是加添订户必须的表，其他业务相关表需要根据具体业务进行添加-----

CREATE OR REPLACE PROCEDURE ADDUSER_EMAIL(PREFIX IN VARCHAR2,
START_ACC_NBR IN VARCHAR2,
END_ACC_NBR IN VARCHAR2) AS
I VARCHAR2(60);
J NUMBER(38);
M NUMBER(38);
N NUMBER(38);
O NUMBER(38);
P NUMBER(9);
Q NUMBER(38);
R VARCHAR2(60);
S NUMBER(38);
T NUMBER(38);
--X NUMBER(38);
Y NUMBER(38);
Z NUMBER(38);
--A NUMBER(38);
BEGIN
I := START_ACC_NBR;
J := 0;
R := PREFIX;
WHILE I <= END_ACC_NBR LOOP
SELECT ACC_NBR_ID_SEQ.NEXTVAL INTO M FROM DUAL;
SELECT SIM_CARD_ID_SEQ.NEXTVAL INTO N FROM DUAL;
SELECT CUST_ID_SEQ.NEXTVAL INTO O FROM DUAL;
SELECT ACCT_ID_SEQ.NEXTVAL INTO P FROM DUAL;
SELECT PROD_ID_SEQ.NEXTVAL INTO Q FROM DUAL;
SELECT CERT_ID_SEQ.NEXTVAL INTO S FROM DUAL;
SELECT ACCT_BOOK_ID_SEQ.NEXTVAL INTO T FROM DUAL;
SELECT SUBS_UPP_INST_ID_SEQ.NEXTVAL INTO Y FROM DUAL;
SELECT LOG_ID_SEQ.NEXTVAL INTO Z FROM DUAL;
--SELECT USER_SUBS_ID_SEQ.NEXTVAL INTO X FROM DUAL;

------------------ACC_NBR---------------
INSERT INTO ACC_NBR

VALUES
(M,
R,
I,
NULL,
42,
3,
5,
'A',
1,
'G',
21,
null,
sysdate,
NULL,
NULL,
NULL,
'StarHub Operator',
NULL,
'Y',
NULL,
'E',
NULL,
SYSDATE);

------------------SIM_CARD---------------
INSERT INTO SIM_CARD
VALUES
(N,
2,
I,
1,
I,
1234,
55215810,
5678,
15632953,
NULL,
NULL,
42,
'C',
21,
sysdate,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate,
NULL,
'Y',
NULL,
'E',
NULL,
SYSDATE);

------------------SIM_NBR---------------
INSERT INTO SIM_NBR
VALUES
(SIM_NBR_ID_SEQ.NEXTVAL, N, M, 'A',
sysdate,
sysdate,
NULL,NULL,NULL);
------------------cert--------
INSERT INTO cert
VALUES(S,65,I,'1-KHFUZBU',NULL,NULL,NULL,'2817',null);

------------------CUST--------------------
INSERT INTO CUST
VALUES
(O,
I,
I,
NULL,
NULL,
NULL,
NULL,
'A',
S,
NULL,
21,
NULL,
NULL,
NULL,
'Y',
NULL,NULL,NULL,NULL,NULL,NULL,NULL,'F',NULL,null,
sysdate,
sysdate,
'A',
'A',
sysdate,
'e73adf9842e38aab89b6a8b9c8824051',
1,
'A',
0,NULL,1,'A',
0);

------------------ACCT--------------------
INSERT INTO ACCT

VALUES
(P,
NULL,
1,
NULL,
NULL,
O,
P,
1,
'B',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
sysdate,
sysdate,
'A',
sysdate,
'Y',0,NULL,NULL,NULL,NULL,'H','39','Y',NULL,'Y',
NULL);

------------------PROD--------------------
INSERT INTO PROD
VALUES
(Q,
sysdate,
'A',
105,
2006,
null,
NULL,NULL,
sysdate,
sysdate,
sysdate,
'A',
sysdate,
'00000000000000',NULL,'Y',SYSDATE,NULL,SYSDATE,'M',
24,
NULL,NULL,NULL);
------------------PROD_ATTR_VALUE--------------------
INSERT INTO PROD_ATTR_VALUE
VALUES
(Q,
'472',5,
SYSDATE,
NULL,
sysdate,
'Y',
NULL);
------------------SUBS--------------------
INSERT INTO SUBS
VALUES
(Q,R,I,P,O,O,NULL,NULL,sysdate,0,
NULL,NULL,NULL,'Y',NULL,NULL,'21',null,null);

------------------USED_RES--------------------
INSERT INTO USED_RES
VALUES
(USED_RES_ID_SEQ.NEXTVAL, Q, 'A', M, SYSDATE, 'A', SYSDATE,0);

------------------USED_RES--------------------
INSERT INTO USED_RES
VALUES
(USED_RES_ID_SEQ.NEXTVAL, Q, 'B', N, SYSDATE, 'A', SYSDATE,0);

------------------subs_upp_inst--------------------
INSERT INTO subs_upp_inst
VALUES
(SUBS_UPP_INST_ID_SEQ.NEXTVAL, Q, 2010,
sysdate,
sysdate,
null,sysdate,
'A', sysdate,
null,null,sysdate,
'Y',NULL,NULL,NULL,NULL,NULL,NULL);

------------------ebs_spend_report_log--------------------

INSERT INTO ebs_spend_report_log
VALUES
(Z,O,Q,
'lihui.afp',sysdate,SYSDATE,'C');

------------------ebs_esc_user--------------------
INSERT INTO ebs_esc_user
VALUES
(O,'li.hui15@zte.com.cn','A',
sysdate,NULL,O,'Y',NULL,NULL,'X','G',
'3236',SYSDATE,SYSDATE,
null,O,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

------------------ebs_esc_user_subs--------------------

INSERT INTO ebs_esc_user_subs
VALUES
(Z,O,Q,'91832496','X',
sysdate,
null,null,null,
'F','451',SYSDATE);

------------------cust_attr_value--------------------
INSERT INTO cust_attr_value
VALUES
(O,'6738','Y','N',NULL);

/* ------------------ATTR-----------------
INSERT INTO ATTR
VALUES
('6738','EBS spend report','1',NULL,'EBS_SPEND_REPORT',
'Y','Y','Y',NULL);*/

------------------EBS_DEPARTMENT------------------
INSERT INTO EBS_DEPARTMENT
VALUES
(O,O,NULL,'25190801',NULL,NULL,
sysdate,'A',SYSDATE,
NULL,NULL);


------------------EBS_ESC_SUBS_EX------------------
INSERT INTO EBS_ESC_SUBS_EX
VALUES
(Q,Q,'li.hui15@zte.com.cn',O,O,'LH','1402',
NULL,NULL,NULL,'A',SYSDATE, NULL,NULL, NULL,'F',
'9463',SYSDATE,NULL,'B',SYSDATE,NULL,SYSDATE,NULL);

J := J + 1;
I := I + 1;

IF MOD(J, 1000) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END ADDUSER_EMAIL;

4、spool导出语句：

利用spool命令导出dat文件，适用于往tt的bal表里批量导入数据。

spool E:\bal\bal1124.dat;

select a.subs_id||','||a.acct_id||','||2||','||-20000||','||0||','||0||','||0||','||0||','||'2016-01-23 00:00:00.000000'||','||'2016-02-22 00:00:00.000000'||','||'2016-01-23 00:00:00.000000'||','||'NULL'||','||'NULL'||','||'NULL'||','||'NULL'||','||'NULL'||','||'NULL'||','||'NULL'||','||'NULL'||','||'NULL'||','||'2016-01-23 00:00:00.000000'||','||'NULL'||','||'NULL'||','||'NULL'

from subs a where subs_id between 13000000 and 13150000;

spool off;

注意：执行这条语句之前，需要将PLSQL下面的开关双击关闭，否则有可能失败。