oracle常用命令
1.oracle用户下环境变量
more .bash_profile
该文件存放数据库环境变量
2.find -name name 查找文件
3.查看是否存在用户组prestat
more /etc/group | grep prestat
4.查看是否存在用户prestat
more /etc/passwd | grep prestat
5.连接数据库
sqlplus "/ as sysdba"
sqlplus "PRESTAT/PRESTAT@pisa1"
6.oracle用户下查看环境变量
env | grep oracle 返回环境变量
7.oracle下运行netca出现net configration assistant
运行dbca出现database configration assistant
8.赋ftp权限(linux)
root用户
more ./etc/ftpusers 在相应的用户前加#
10.查看Job作业执行情况
select * from user_jobs
select * from user_scheduler_jobs
11.启动oracle数据库
启动监听：lsnrctl start
连接数据库：sqlplus '/as sysdba'
启动实例：startup
关闭实例：shutdown immediate
每次只能启动一个实例，如果要启动多个实例，需要修改SID，具体操作如下：
vi $ORACLE_BASE/.bash_profile
修改export ORACLE_SID=?
12.查看当前实例名
SQL>select name from v$database;
14. drop user PISA_DATA提示不能删除连接的用户。
解决方法：断开监听，shutdown immediate ,startup
15.修改linux操作系统用户密码
password username
old password:
new password:
again new password:
17.查看当前链接用户
select username,sid,serial# from v$session;
username,sid,serial#
https://www.360docs.net/doc/c36622047.html,stat -an列出所有的端口
netsata -an |grep 7800
19.查看该用户下表
select table_name from user_tables;
20 查看表结构
desc user_jobs;
21.sqlplus下，!+系统命令
如 ! ls
22.查看文件名的后300行
tail -300 "文件名"
27.启动em
emctl start dbconsole
28.启动出错
startup
ORA-01078: failure in processing system parameters
LRM-00109: ???????????????? '/opt/oracle/product/11g/db_1/dbs/initPISARPT.ora'
解决办法：
cp $ORACLE_BASE/admin/$ORACLE_SID/pfile/init.ora.4242009161236 $ORACLE_HOME/dbs/init$ORACLE_SID.ora
cp /opt/oracle/admin/PISARPT/pfile/init.ora.742009113739 $ORACLE_HOME/dbs/initPISARP.ora
>lsnrctl start
>sqlplus /nolog
SQL*Plus: Release 10.1.0.3.0 - Production on Thu Jun 8 13:16:00 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 778212 bytes
Variable Size 57679900 bytes
Database Buffers 109051904 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.
SQL>
问题解决！
注：Oracle在启动过程中,需要读取参数文件(pfile/spfile)来创建实例.
Oracle在启动过程中,寻找参数文件
的顺序为:spfile
29.select * from dba_data_files where FILE_NAME like '%PISA_CDR';
30.select FILE_NAME,TABLESPACE_NAME,BYTES,USER_BYTES from dba_data_files where FILE_NAME like '%PISA_CDR';
31.单独启动一个实例
export ORACLE_SID=DPFRPT
sqlplus '/as sysdba'
startup
32.查oracle下某一表空间下的所有表
select * from dba_tables where tablespace_name='表空间名'
34.表空间的使用情况
(方法1)
set linesize 1000;
set pagesize 10000;
select a.tablespace_name "Tablespace",
total "Total Size(Mb)",
nvl(free, 0) "Free Size(Mb)",
trunc(nvl(free, 0) * 100 / total) || '%' "Free Usage",
case
when trunc(nvl(free, 0) * 100 / total) <= 5 then
'critical'
when trunc(nvl(free, 0) * 100 / total) <= 10 then
'minor'
else
'prompt'
end "Level"
from (select tablespace_name, trunc(sum(bytes) / 1024 / 1024) total
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, trunc(sum(bytes) / 1024 / 1024) free
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
order by 5 desc, 1;
方法2：
SELECT fs.tablespace_name "Tablespace",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
ROUND (100 * (fs.freespace / df.totalspace)) "Pct. Free"
FROM (SELECT tablespace_name, ROUND (SUM (BYTES) / 1024 / 1024)
totalspace
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, ROUND (SUM (BYTES) / 1024 / 1024) freespace
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name;
方法3：
select b.file_name FileName, b.tablespace_name "Tablespace",
round(b.bytes / 1024 / 1024 / 1024, 2) "SpaceSize(G)",
round((b.bytes - sum( nvl( a.bytes,0))) / 1024 / 1024 / 1024, 2) "Used(G)",
round(substr((b.bytes - sum( nvl( a.bytes , 0))) / ( b.bytes) * 100 , 1, 5), 2) "Used(%)"
from dba_free_space a, dba_data_files b
where a.file_id = b.file_id
group by b.tablespace_name, b.file_name, b.bytes
order by b.tablespace_name;
35.更改表空间
需要sys用户
alter database datafile '/opt/oracle/oradata/PISA_CDR'
autoextend on next 50m maxsize 1204m
36.查看当前用户每个表占用空间的大小：
方法一
select substr(a.segment_name, instr(a.segment_name, '2009'), 8) dtime,
round(sum(a.bytes) / 1024 / 1024) ojbsize
from dba_segments a
where a.owner = 'WISGRPT' and a.segment_name like '%2009%'
group by substr(a.segment_name, instr(a.segment_name, '2009'), 8);
方法二
Select Segment_Name,Sum(bytes)/1024/1024 a From User_Extents Group By Segment_Name order by a;
41.表空间扩展
(先扩
磁盘空间,linux命令: lvextend -L +8G /dev/mapper/datavg00-data_tbs_lv #给该设备增加8G空间
alter tablespace data_tbs resize 488G; --假设原先为480G
42.创建表空间
create tablespace test_data
logging
datafile 'C:\app\opt\oracle\oradata\orcl\test_data01.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;
解决ora-01652无法通过128（在temp表空间中）扩展temp段的过程
43.创建临时表空间
create temporary tablespace TEMP temp1 'C:\app\opt\oracle\oradata\orcl\temp01.DBF' SIZE 5G REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
44.改变缺省临时表空间 为刚刚创建的新临时表空间temp1
alter database default temporary tablespace temp1;
45.删除原来临时表空间
drop tablespace temp including contents and datafiles;