oracle及shell相关学习记录

最新推荐文章于 2022-08-30 10:40:35 发布

川xc

最新推荐文章于 2022-08-30 10:40:35 发布

阅读量324

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/qq_35201754/article/details/82562307

版权

oracle 专栏收录该内容

17 篇文章 1 订阅

订阅专栏

Oracle
简单创建dblink

CREATE PUBLIC DATABASE LINK DBLINK_DRPMID CONNECT TO 用户名IDENTIFIED BY "密码" USING 'ip:1521/数据库名'

--查看表空间

SELECT created, log_mode, log_mode FROM v$database; 
--1G=1024MB 
--1M=1024KB 
--1K=1024Bytes 
--1M=11048576Bytes 
--1G=1024*11048576Bytes=11313741824Bytes 
SELECT a.tablespace_name "表空间名", 
total "表空间大小", 
free "表空间剩余大小", 
(total - free) "表空间使用大小", 
total / (1024 * 1024 * 1024) "表空间大小(G)", 
free / (1024 * 1024 * 1024) "表空间剩余大小(G)", 
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", 
round((total - free) / total, 4) * 100 "使用率 %" 
FROM (SELECT tablespace_name, SUM(bytes) free 
FROM dba_free_space 
GROUP BY tablespace_name) a, 
(SELECT tablespace_name, SUM(bytes) total 
FROM dba_data_files 
GROUP BY tablespace_name) b 
WHERE a.tablespace_name = b.tablespace_name

1.安装pl/sql development 远程连接

修改tnsnames.ora 我的tnsnames.ora在D:\Program Files\PLSQL（绿色版）\PLSQL\instantclient_11_2文件夹下

修改为：

orcl =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.102)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

主要是更改连接的host和端口

服务端修改listener.ora

我的在 D:\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN”目录下的

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.102)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

主要是增加一条address 将本机的ip增加监听

创建表空间

create tablespace xcc datafile 'e:\oracle\oradata\test\data_1.dbf' size 2000M;

创建用户

Create user xcc identified by xcc;

Create user xcc identified by xcc profile default tablespace xcc account xcc;

授权

Grant create user,drop user,alter user,create any view,drop any view,exp_full_database,imp_full_databse,dba,connect,resource,create session to xcc;

Conn xcc/xcc

建表

Create table tt (id number(32) primary key,

Name varchar2(16),

Age integer(2),

Sex varchar(2) default ‘男’ check( sex in (‘男’,’女’)));

--Oracle下语法：
create table BranchAccount as select * from TATA.dbo.BranchAccount
--SqlServer下语法：
select * into BranchAccountfrom TATA.dbo.BranchAccount

改表名

Rename tt to student;

该字段

Alter table student modify(age integer not null);

Alter table student rename column age to age1;

其他

alter table tablename add (column datatype [default value][null/not null],….);

alter table tablename modify (column datatype [default value][null/not null],….);

alter table tablename drop (column);

alter table tablename drop constraint foreignkeyname;

外键

单列外键

create table book (id number(32) primary key,

name varchar2(30),

s_id number(32),

constraint s_id foreign key (id) references student(id)) ;

多列

create table tb_supplier (

supplier_id number not null,

supplier_name varchar2(50) not null,

contact_name varchar2(50),

CONSTRAINT pk_supplier PRIMARY KEY (supplier_id,supplier_name) );

create table tb_products (

product_id number not null,

product_name varchar2(100),

supplier_name varchar2(50),

supplier_id number not null,

constraint fk_products_supplier foreign key (supplier_id,supplier_name) references tb_supplier(supplier_id,supplier_name) );

alter table tb_products add constraint fk_products_supplier  foreign key (supplier_id,supplier_name)  references tb_supplier(supplier_id,supplier_name);

批量插入

insert all

into book(id,name,S_ID) values('01','english','01')

into book(id,name,S_ID) values('02','math','02')

select 1 from DUAL;

DUAL表的用途：

Dual 是 Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的Select语句块中

--查看当前连接用户

SQL> select user from dual;

--查看当前日期、时间

SQL> select sysdate from dual;

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

--当作计算器用

SQL> select 1+2 from dual;

--查看序列值

SQL> create sequence aaa increment by 1 start with 1;

SQL> select aaa.nextval from dual;

存储过程

无参存储过程

--create or replace procedure procedureName

--as声明

--自定义变量或游标

--;

--begin执行

--;

--exception异常

--;

--end;

create or replace procedure TheCopyTable

my_exception exception;

sid student.id%type;

sname student.name%type;

row_id int;

resu int;

cursor s_cur is select * from student;

begin

dbms_output.put_line('您调用了TheCopyTable这个存储过程');

row_id := 1;

for stu in s_cur

loop

sid:=stu.id;

sname:=stu.name;

dbms_output.put_line('student表中第'||row_id||'行:'||sid||sname);

row_id := row_id +1;

select count(1) into resu from s1 where id = sid and name = sname;

if resu = 0 then

dbms_output.put_line('正在插入数据...');

insert into s1 select * from student where student.id = sid;

dbms_output.put_line('插入完成');

else

dbms_output.put_line('已存在记录');

end if;

if row_id =3 then

raise my_exception ;

end if;

end loop;

exception

when my_exception then

dbms_output.put_line('已结束');

when no_data_found then

dbms_output.put_line('Data is not found!');

when too_many_rows then

dbms_output.put_line('Too many rows round!');

when OTHERS then

dbms_output.put_line('others error');

end;

--调用

--begin

--procedureName;

--end;

call  TheCopyTable();

将一个表中数据插入另一个表

insert into s1 select * from student where student.id = sid;

有参存储过程

create or replace procedure calculator(a in number,b in number,c out number)

i number;

my_exception exception;

begin

i := 10;

c := a;

while i>1

loop

c := c/b;

i := i-1;

if b = 0 then

raise my_exception;

else

dbms_output.put_line(a||'/'||b||'='||c);

end if;

end loop;

end;

调用

declare

c number(10);

begin

calculator(10,2,c);

end;

循环

for…in…loop 一般用于游标和数组
while 各种语句 loop

数组

固定长度的数组

declare

type vararray is varray(30) of varchar2(100);

var_a vararray := vararray('a','b','c','d');

begin

for i in 1..var_a.count loop

dbms_output.put_line(var_a(i));

end loop;

end;

1..var_a.count 是指1到var_a长度之间是一个范围

也可使用var_a.first..var_a.last

未知长度

declare

type vararray is table of varchar2(20) index by binary_integer;

var_a vararray;

begin

for i in 1..15 loop

var_a(i):=i+10;

end loop;

dbms_output.put_line(var_a.count);

end;

查看表空间

SELECT a.tablespace_name "表空间名", 
total "表空间大小", 
free "表空间剩余大小", 
(total - free) "表空间使用大小", 
total / (1024 * 1024 * 1024) "表空间大小(G)", 
free / (1024 * 1024 * 1024) "表空间剩余大小(G)", 
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", 
round((total - free) / total, 4) * 100 "使用率 %" 
FROM (SELECT tablespace_name, SUM(bytes) free 
FROM dba_free_space 
GROUP BY tablespace_name) a, 
(SELECT tablespace_name, SUM(bytes) total 
FROM dba_data_files 
GROUP BY tablespace_name) b 
WHERE a.tablespace_name = b.tablespace_name

oracle查看表行数所占空间大小

select ut.table_name,ut.OWNER,ut.tablespace_name,num_rows,us.BYTES from dba_tables ut,
(select segment_name,sum(bytes)/1024/1024||'M' bytes from user_segments group by segment_name) us 
where ut.TABLE_NAME = us.segment_name
order by num_rows desc nulls last;

oracle查看表空间块大小

select tablespace_name,blocK_size,status,contents from dba_tablespaces;

增加表空间

alter tablespace PDM_DATA add datafile 'D:\APP\ADMINISTRATOR\ORADATA\PDM\PDM_DATA02.DBF' size 1000m autoextend on next 320m maxsize 30480m

oracle查找数据绝对文件号

select file#,name,status from v$datafile

一些shell脚本

linux:

#查看该目录所在的硬盘大小

df -h [目录]

ex: df -h /usr/local

#移动文件命令

mv [source] [target]

ex: mv ./a.txt /home

#删除命令

rm -rf [目录]

ex: rm -rf /home/temp/*

#查看文件所占空间大小

du [file/directory]

-sh 

ex: du -sh /home/temp

#tgz压缩

tar zcvf [Directory/targetFile.tgz]  [sourceFile/Directory]

ex: tar zcvf   kernel.tgz   /home/bak

#查看目录下一层深度文件大小
du -h --max-depth=1 

[root@FineReportAppServer ~]# du -h --max-depth=1 
4.0K	./桌面
4.0K	./.abrt
4.0K	./.nautilus
4.0K	./.gnome2_private
384K	./.gstreamer-0.10
12M	./mysql-connector-java-5.1.40
260K	./.visualvm
20K	./satools
16K	./.gnote
4.0K	./公共的
12K	./.dbus
52K	./.config
4.0K	./图片
22G	./.FineReport80
4.0K	./文档

#删除指定日期的文件夹
find ./ -mtime +3  -type d  -exec rm -rf {} \;
#+3 是指3天前  d是文件夹 


[root@FineReportAppServer cache]# ls -l
؜ԃ 72
drwxr-xr-x. 2 root root  4096 1Ղ  31 10:03 DATA_1548900202002_769
drwxr-xr-x. 2 root root  4096 1Ղ  31 10:04 DATA_1548900278760_21
drwxr-xr-x. 2 root root  4096 1Ղ  31 13:29 DATA_1548901256295_826
drwxr-xr-x. 2 root root  4096 2Ղ   1 15:24 DATA_1548953225620_832
drwxr-xr-x. 2 root root  4096 2Ղ   1 00:47 DATA_1548953231140_703
drwxr-xr-x. 2 root root  4096 2Ղ   1 14:40 DATA_1549003241568_18
drwxr-xr-x. 2 root root  4096 2Ղ   1 18:08 DATA_1549005970559_623
drwxr-xr-x. 2 root root  4096 2Ղ   1 17:02 DATA_1549011716648_413
drwxr-xr-x. 2 root root  4096 2Ղ   4 16:34 DATA_1549269248567_754
drwxr-xr-x. 2 root root 20480 2Ղ  12 09:48 DATA_1549935601750_579
drwxr-xr-x. 2 root root  4096 2Ղ  12 09:42 DATA_1549935761321_205
drwxr-xr-x. 2 root root  4096 2Ղ  12 09:45 DATA_1549935916375_702
drwxr-xr-x. 2 root root  4096 2Ղ  12 09:46 DATA_1549935970785_753
[root@FineReportAppServer cache]# find ./ -mtime +10  -type d  -exec rm -rf {} \;
find: ¡°./DATA_1548900278760_21¡±: ûԐŇ¸?þ»?¼
find: ¡°./DATA_1548901256295_826¡±: ûԐŇ¸?þ»?¼
find: ¡°./DATA_1548953231140_703¡±: ûԐŇ¸?þ»?¼
find: ¡°./DATA_1548900202002_769¡±: ûԐŇ¸?þ»?¼
[root@FineReportAppServer cache]# find ./ -mtime +3  -type d  -exec rm -rf {} \;
find: ¡°./DATA_1549003241568_18¡±: ûԐŇ¸?þ»?¼
find: ¡°./DATA_1548953225620_832¡±: ûԐŇ¸?þ»?¼
find: ¡°./DATA_1549011716648_413¡±: ûԐŇ¸?þ»?¼
find: ¡°./DATA_1549005970559_623¡±: ûԐŇ¸?þ»?¼
find: ¡°./DATA_1549269248567_754¡±: ûԐŇ¸?þ»?¼
[root@FineReportAppServer cache]# ls -l
؜ԃ 36
drwxr-xr-x. 2 root root 20480 2Ղ  12 09:48 DATA_1549935601750_579
drwxr-xr-x. 2 root root  4096 2Ղ  12 09:42 DATA_1549935761321_205
drwxr-xr-x. 2 root root  4096 2Ղ  12 09:45 DATA_1549935916375_702
drwxr-xr-x. 2 root root  4096 2Ղ  12 09:46 DATA_1549935970785_753


#添加定时任务清理finereport的cache文件，建立sh文件内容如下
[root@FineReportAppServer myshell]# cat del_old_cache.sh 
#!/usr/bin/bash
# del >2 dir
find /root/.FineReport80/cache/  -mtime +2  -type d  | xargs rm -rf
#添加定时任务
[root@FineReportAppServer myshell]# crontab -e


*/1 * * * * . /etc/profile; /home/shellScript/tomcatRestart2.0.sh
* */3 * * * /usr/sbin/ntpdate 192.168.10.12 && /usr/sbin/hwclock --systohc
0 23 * * * /home/myshell/del_old_cache.sh
#查看定时任务
[root@FineReportAppServer myshell]# crontab -l

*/1 * * * * . /etc/profile; /home/shellScript/tomcatRestart2.0.sh 
* */3 * * * /usr/sbin/ntpdate 192.168.10.12 && /usr/sbin/hwclock --systohc
0 23 * * * /home/myshell/del_old_cache.sh	


补充:后来发现上面写的定时任务未执行，做了一些修改如下
[root@FineReportAppServer cache]# vim /home/myshell/del_old_cache.sh

#!/bin/bash
#. /etc/profile
source /etc/profile

find /root/.FineReport80/cache/  -mtime +1  -type d  | xargs rm -rf
保存

[root@FineReportAppServer cache]# crontab -e


*/1 * * * * . /etc/profile; /home/shellScript/tomcatRestart2.0.sh
* */3 * * * /usr/sbin/ntpdate 192.168.10.12 && /usr/sbin/hwclock --systohc
26 10  * * * .  /home/myshell/del_old_cache.sh
保存
查看执行结果
[root@FineReportAppServer cache]# tailf /var/log/cron
Feb 18 10:20:01 FineReportAppServer CROND[22050]: (root) CMD (/usr/lib64/sa/sa1 1 1)
Feb 18 10:21:01 FineReportAppServer CROND[22567]: (root) CMD (. /etc/profile; /home/shellScript/tomcatRestart2.0.sh )
Feb 18 10:22:01 FineReportAppServer CROND[22983]: (root) CMD (. /etc/profile; /home/shellScript/tomcatRestart2.0.sh )
Feb 18 10:22:01 FineReportAppServer CROND[22984]: (root) CMD (/home/myshell/del_old_cache.sh)
Feb 18 10:23:01 FineReportAppServer CROND[23380]: (root) CMD (. /etc/profile; /home/shellScript/tomcatRestart2.0.sh )
Feb 18 10:23:31 FineReportAppServer crontab[23633]: (root) BEGIN EDIT (root)
Feb 18 10:23:49 FineReportAppServer crontab[23633]: (root) REPLACE (root)
Feb 18 10:23:49 FineReportAppServer crontab[23633]: (root) END EDIT (root)
Feb 18 10:24:01 FineReportAppServer crond[2336]: (root) RELOAD (/var/spool/cron/root)
Feb 18 10:24:02 FineReportAppServer CROND[23911]: (root) CMD (. /etc/profile; /home/shellScript/tomcatRestart2.0.sh )
Feb 18 10:25:01 FineReportAppServer CROND[24414]: (root) CMD (. /etc/profile; /home/shellScript/tomcatRestart2.0.sh )
Feb 18 10:26:01 FineReportAppServer CROND[24990]: (root) CMD (.  /home/myshell/del_old_cache.sh)
查看文件是否被清除

[root@FineReportAppServer cache]# ll --time-style=long-iso
؜ԃ 184
drwxr-xr-x. 2 root root 36864 2019-02-17 09:07 DATA_1550219087448_433
drwxr-xr-x. 2 root root  4096 2019-02-16 14:01 DATA_1550296886131_170
drwxr-xr-x. 2 root root  4096 2019-02-16 14:01 DATA_1550296909780_401
drwxr-xr-x. 2 root root  4096 2019-02-17 11:31 DATA_1550374260630_584
drwxr-xr-x. 2 root root  4096 2019-02-17 22:46 DATA_1550414808179_572
drwxr-xr-x. 2 root root  4096 2019-02-17 23:46 DATA_1550418394328_607
drwxr-xr-x. 2 root root 81920 2019-02-18 09:39 DATA_1550446528958_468
drwxr-xr-x. 2 root root  4096 2019-02-18 07:58 DATA_1550447926628_761
drwxr-xr-x. 2 root root  4096 2019-02-18 08:02 DATA_1550448125852_120
drwxr-xr-x. 2 root root  4096 2019-02-18 09:34 DATA_1550453633755_587
drwxr-xr-x. 2 root root  4096 2019-02-18 10:26 DATA_1550454107679_936
drwxr-xr-x. 2 root root  4096 2019-02-18 09:58 DATA_1550455091380_641
drwxr-xr-x. 2 root root  4096 2019-02-18 09:59 DATA_1550455175422_369
drwxr-xr-x. 2 root root  4096 2019-02-18 10:00 DATA_1550455252403_77
drwxr-xr-x. 2 root root  4096 2019-02-18 10:07 DATA_1550455622368_528
drwxr-xr-x. 2 root root  4096 2019-02-18 10:07 DATA_1550455669754_324
drwxr-xr-x. 2 root root  4096 2019-02-18 10:08 DATA_1550455711320_780
drwxr-xr-x. 2 root root  4096 2019-02-18 10:10 DATA_1550455836335_577
测试生效了

#从一个文件中查找error字符的内容包含后五行 A：error所在行的后五行 B:前五行 C:上下五行
[root@FineReportAppServer shellScript]#  grep  -A 5  error* TomcatMonitor.log

--
[error]页面访问出错,开始重启tomcat
Using CATALINA_BASE:   /usr/local/tomcat
Using CATALINA_HOME:   /usr/local/tomcat
Using CATALINA_TMPDIR: /usr/local/tomcat/temp
Using JRE_HOME:        /usr/java/jdk1.7.0_79
Using CLASSPATH:       /usr/local/tomcat/bin/bootstrap.jar:/usr/local/tomcat/bin/tomcat-juli.jar
--
[error]页面访问出错,开始重启tomcat
Using CATALINA_BASE:   /usr/local/tomcat
Using CATALINA_HOME:   /usr/local/tomcat
Using CATALINA_TMPDIR: /usr/local/tomcat/temp
Using JRE_HOME:        /usr/java/jdk1.7.0_79
Using CLASSPATH:       /usr/local/tomcat/bin/bootstrap.jar:/usr/local/tomcat/bin/tomcat-juli.jar
--

oracle:
用户即将到期时通过修改密码可以使
账户的到期时间重新以该天开始计算