oracle日常操作记录
SQL中上下按键错误
- rlwrap下载
SQL> ^[[A^[[A^[[D^[[C^[[D^[
需要安装 rlwrap-0.42.tar.gz 这个软件包
直接解压./configure && make && make install #需要使用root用户执行
在到oracle用户环境变量中添加以下数据:
[oracle@ccod ~]$ vim .bash_profile
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
创建用户和表空间
- 创建用户
create user 用户名 identified by "密码";
- 授权
grant create session to 用户名; # 授权允许用户登录
grant create table to 用户名; # 创建表的权限
gant create tablespace to 用户名; # 创建回滚表空间
garnt create view to 用户名; # 创建视图权限
- 创建表空间
create tablespace demps[ace datafile '路径以.bdf结尾' size 1500M autoextend on ext 5M maxsize 3000M;
SQL行中的技巧
- 调用系统命令
SQL> !ls -htr
oracle_database_linux32.zip db_home database oracle10g db_files rlwrap-0.42 rlwrap-0.42.tar.gz
按照用户导出和导入
[oracle@iZ25clyc8k3Z ~]$ exp ccod/ccod file=test.dmp #导出
[oracle@ccod ~]$ imp ccod/ccod file=test.dmp fromuser=ccod touser=ccod #导入
表空间扩容
一、查询使用率:
SELECT Upper(F.TABLESPACE_NAME) "表空间名",
Round(D.TOT_GROOTTE_MB/1024,2) "表空间大小(GB)",
Round(F.TOTAL_BYTES/1024,2) "空闲空间(GB)",
To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
|| '%' "使用比",
Round((D.TOT_GROOTTE_MB - F.TOTAL_BYTES)/1024,2) "已使用空间(GB)",
F.MAX_BYTES/1024 "最大块(MB)"
FROM (SELECT TABLESPACE_NAME,
Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;
- 新版查询表空间使用率
select a.tablespace_name,a.bytes/1024/1024/1024 "Sum GB",(a.bytes-b.bytes)/1024/1024/1024 "used GB",b.bytes/1024/1024/1024 "free GB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc
二、查看使用数据文件扩容到哪一步:
select name from v$datafile;
select file_name,tablespace_name from dba_data_files;
select username,default_tablespace from dba_users where default_tablespace='USERS'; 查询USERS表空间哪个用户在使用。
三、添加数据文件:
alter tablespace users add datafile'/home/oracle/oracle10g/oradata/DATAONE/users11.dbf' size 1000M autoextend on next 100M MAXSIZE 32000M;
列:alter tablespace CCOD_TABLE_0211270012#表空间名 add datafile'/home/oracle/oracle10g/oradata/CCOD_TABLE_0211270012_01.dbf'#路径 size 1000M autoextend on next 100M MAXSIZE 32000M;
Oracle启动关闭
- 停止监听
关掉oracle的监听进程:命令为“lsnrctl stop”并回车,这时外部没法连接到数据库了
命令行:lsnrctl stop”
- 强制重启数据
SHUTDOWN ABORT;
- 关闭数据库
SQL>shutdown immediate
- 启动数据库
SQL>startup;
- 启动监听
命令行lsnrctl start
- 启停止汇总命令
SQL> shutdown normal--普通关闭
SQL> shutdown immediate --立即关闭
SQL> shutdown transactional --事务保证关闭
SQL> shutdown abort --强制终止/强制关闭
SQL> shutdown 就相当于使用了shutdown normal
SQL> startup;
# lsnrctl start
Orcle连接数
SQL> show parameter process #查看当前oracle设置的连接数
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 300
修改processes和sessions值必须重启oracle服务器才能生效
ORACLE的连接数(sessions)与其参数文件中的进程数(process)有关,它们的关系如下:
sessions=(1.1*process+5)
SQL> alter system set sessions=555 scope=spfile; #设置连接数
SQL> alter system set processes=500 scope=spfile; #设置连接数
SQL> select count(*) from v$process; #查看当前的oracle数据的连接数
COUNT(*)
----------
55
删除oracle归档文件
- 在主库和备份执行分别执行看值
set linesize 160 pagesize 999;
col name for a60;
col member for a60;
select dbid,database_role from v$database;
select max(sequence#) from v$archived_log;
select max(sequence#) from v$archived_log where applied='YES';
#执行过程,在主库执行表示
SQL> set linesize 160 pagesize 999;
col name for a60;
col member for a60;
select dbid,database_role from v$database;
select max(sequence#) from v$archived_log;
select max(sequence#) from v$archived_log where applied='YES';SQL> SQL> SQL>
DBID DATABASE_ROLE
---------- ----------------
2388749677 PHYSICAL STANDBY
SQL>
MAX(SEQUENCE#) #目前归档文件最大值是253
--------------
253
SQL>
MAX(SEQUENCE#) #表示备库,已经应用归档文件
--------------
253
SQL>
- 查询归档文件路径
set linesize 160 pagesize 999
col destination for a30;
col error for a60;
select status,destination, error from v$archive_dest;
#在主库中查询
SQL> select status,destination, error from v$archive_dest;
STATUS DESTINATION ERROR
--------- ------------------------------ ------------------------------------------------------------
VALID /home/oracle/oracle10g/arch1/WENDING #本地归档文件
VALID db_phystdby #备库的路径
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
10 rows selected.
- 查询归档进程是否存在
elect process,status from v$managed_standby;
基本查询
- 按照时间查询数据
# 查询from campaign_base_info表中ENDTIME字段小于2017-05-01 00:00:00中的数据
select * from campaign_base_info where ENDTIME < to_date('2017-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss');
Oracle数据导出
#!/usr/bin/perl
##perl script used to connect to Oracle
use strict;
use DBI;
use Encode;
#autoflush
$| = 1;
my $tnsname="ora198";
my $username="xxxxx";
my $password="xxxxx";
my $dbh=DBI->connect("dbi:Oracle:$tnsname", $username, $password) or die "Cannot conenct db: $DBI::errstr\n";
print "I have connected to the Oracle database!\n";
$dbh->{AutoCommit} = 0;
$dbh->{RaiseError} = 1;
$dbh->{ora_check_sql} = 0;
$dbh->{RowCacheSize} = 16;
$dbh->{LongReadLen} = 66000;
$dbh->{LongTruncOk} = 1;
my $sql=qq(select "FEED_ID","PRODUCT_CODE","PRODUCT_OBJECT_ID",replace("FEED_DATA",'![](index_files/_25W@GJ_24ACOF_28TYDYECOKVDYB.png)\\u','\\\\u'),"USER_ID_EN","USER_NICK",to_char("CREATE_TIME",'YYYY-MM-DD HH2
4:MI:SS'),to_char("NEXT_DISPATCH_TIME",'YYYY-MM-DD HH24:MI:SS'),"DISPATCH_STATUS","RANGE_STATUS","SPREAD_STATUS","FEED_ACCEPTORS","FEED_STATE","FEED_TYPE" from
"CENTER"."T_WIKI_FEED") ;
open FH,">/home/zhangguangnan/T_WIKI_FEED.txt" or die "open file faile";
my $sth=$dbh->prepare($sql);
$sth->execute();
while (my @row = $sth->fetchrow_array()) {
my $data=join('|#|',@row);
print FH "$data\t\n";
}
close FH;
$dbh->disconnect or warn "DB disconnect failed: $DBI::errstr\n";
print "Disconnected from Oracle databae!\n";
Mysql导入
load data local infile 'T_WIKI_FEED.500000ae' IGNORE into table t_wiki_feed character set gbk fields terminated by '|#|' ENCLOSED BY '\\' lines terminated byy '\t\n';
oracle基本操作
- 清空表中数据
truncate table abc;
delete from abc;
- 查询结果插入另张表中
select * into 表A form 表B where条件
- 查询结果创建另张新表中
create table campaign_clean_info_bak0202 as select * from campaign_clean_info
insert into 表A select * from 表B where 条件
- 查询数据多字段,插入另张少字段表中
insert into DXBD2016111803.ENT_RECORD_BX_TABLE_H_201805(RECORD_NAME, SESSION_ID, REMOTE_URI, LOCAL_URI, AGENT_ID, CMS_NAME, CALL_TYPE, DEVICE_NUMBER, START_TIME, END_TIME) select RECORD_NAME, SESSION_ID, REMOTE_URI, LOCAL_URI, AGENT_ID, CMS_NAME, CALL_TYPE, DEVICE_NUMBER, START_TIME, END_TIME from DXBD2016111803.ENT_RECORD_BX_TABLE where START_TIME < to_date('2018-05-27 00:00:00','yyyy-mm-dd hh24:mi:ss') and START_TIME > to_date('2018-05-1 00:00:00','yyyy-mm-dd hh24:mi:ss')
Oracle优化
- 增加游标是数
增大游标的值:alter system set open_cursors = 加大的值;
- Oracle 调整SGA、PGA大小
来源文章来源:https://www.toymoban.com/news/detail-807480.html
SQL> show parameter sga;
SQL> alter system set sga_max_size=4096M scope=spfile;
System altered.
SQL> alter system set sga_target=4096M scope=spfile;
System altered.
如果是RAC环境,需要这样增加sid='*':
> alter system set sga_target=20G scope=spfile sid='*';
SQL> shutdown immediate;
SQL> startup
SQL> show parameter sga;
SQL> alter system set workarea_size_policy=auto scope=both;
SQL> alter system set pga_aggregate_target=512m scope=both;
- oracle在线添加日志组和日志组成员
来源文章来源地址https://www.toymoban.com/news/detail-807480.html
SQL> alter database drop logfile member '/u01/app/oracle/oradata/orcl/redo01a.log';
SQL> alter database add logfile member '/u01/app/oracle/fast_recovery_area/orcl/redo01a.log' to group 1;
SQL> alter database add logfile member '/u01/app/oracle/fast_recovery_area/orcl/redo02a.log' to group 2;
SQL> alter database add logfile member '/u01/app/oracle/fast_recovery_area/orcl/redo03a.log' to group 3;
SQL> alter database add logfile group 4 ('/u01/app/oracle/oradata/orcl/redo04.log','/u01/app/oracle/fast_recovery_area/orcl/redo04a.log') size 100m;
SQL> alter database add logfile group 5 ('/u01/app/oracle/oradata/orcl/redo05.log','/u01/app/oracle/fast_recovery_area/orcl/redo05a.log') size 100m;
23. Database altered.
SQL> alter system switch logfile;
SQL> select * from v$log;
- 添加redo 日志太小 500M
alter database add logfile group 4 ('/home/oracle/db_files/ccod/redo04.log') size 512M;
alter database add logfile group 5 ('/home/oracle/db_files/ccod/redo05.log') size 512M;
alter database add logfile group 6 ('/home/oracle/db_files/ccod/redo06.log') size 512M;
Oracle技巧
- oracle技巧
#查看sql执行次数
select SQL_TEXT,EXECUTIONS,PARSE_CALLS FROM v$sqlarea
- oracle命令行调整宽度
set linesize 220
set pagesize 300
col datas for a100
col nu for 99999999
select substr(a.record_name, 1, instr(a.record_name, '/', 1) - 1) datas,
count(1) nu
from ccod.c_record_bx a where a.MATCHED_STATUS = 0
group by substr(a.record_name, 1, instr(a.record_name, '/', 1) - 1);
到了这里,关于oracle日常操作记录的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!