ORACLE常用命令

1.oracle环境变量
oracle用户下
cd $ORACLE_HOME
ls -al
more .bash_profile
该文件存放数据库环境变量

2.find -name name 查找文件

3.查看是否存在用户组prestat
more /etc/group | grep prestat

4.查看是否存在用户prestat
more /etc/psswd | 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.sqlplus "sys/sys@ora11 as sysdba"
9.赋ftp权限
  root用户
  more ./etc/ftpusers 在相应的用户前加#
 
10.查看Job作业执行情况
select * from user_jobs
select * from user_scheduler_jobs

11.预统计初始化
db2
数据库连接串(预统计数据库名) prestat
用户名：db2ins1
密码：db2inst1

oracle
数据库连接串(sid) ora11
用户名：prestat
密码：prestat

12.启动oracle数据库
    启动监听：lsnrctl start
    连接数据库：sqlplus '/as sysdba'
    启动实例：startup
    关闭实例：shutdown immediate
    每次只能启动一个实例，如果要启动多个实例，需要修改SID，具体操作如下：
    vi $ORACLE_BASE/.bash_profile
    修改export ORACLE_SID=?
   
13.查看当前实例名
  SQL>select name from v$database;
 
14.sqlplus '/as sysdba'等于是sys用户登录

 

15. drop user PISA_DATA提示不能删除连接的用户。
    解决方法：断开监听，shutdown immediate ,startup
16.修改用户名
  password username
  old password:
  new password:
  again new password:

17.查看当前链接用户
  select username,sid,serial# from v$session;
 
  username,sid,serial#
 
18.netstat -an列出所有的端口
  netsata -an |grep 7800
 
19.查看该用户下表
  select tname from tab

20.
  查看表结构
  desc user_jobs;

21.sqlplus下，!+系统命令
  如 ! ls

22.查看文件名的后300行
  tail -300 "文件名"
 

25.更改表空间
  需要sys用户
  alter database datafile '/opt/oracle/oradata/PISA_CDR'
  autoextend on next 50m maxsize 1204m

26. 查询表空间信息
  SELECT * FROM DBA_DATA_FILES;

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/initPISARPT.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 <sid>.ora,spfile.ora,init <sid>.ora

 

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='表空间名’
17.已使用表空间大小
select tablespace_name,sum(bytes)/1024/1024 from dba_data_files
    group by tablespace_name;
   
   
33.表空间的使用情况
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;

方法二：
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;

34.查看某个表空间情况
select * from dba_data_files where tablespace_name='PISA_CDR';


35.查看当前用户每个表占用空间的大小：
select segment_name,sum(bytes)/1024/1024 a from user_extents group by segment_name order by a desc;

36.查看每个表空间占用空间的大小：
Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name;


37.查oracle下某一表空间下的所有表
select * from dba_tables where tablespace_name='表空间名’

38.已使用表空间大小
select tablespace_name,sum(bytes)/1024/1024 from dba_data_files
    group by tablespace_name;

39.表空间的使用情况

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;

40.查看某个表空间情况
select * from dba_data_files where tablespace_name='SYSAUX';

 

41.查看表空间的大小及使用情况
SELECT d.status "Status",

      d.tablespace_name "Name",

      d.contents "Type",

      d.extent_management "Extent Management",

      to_char(nvl(a.bytes / 1024 / 1024, 0), '99999999.999') "Total Size (M)",

      to_char(nvl(a.bytes - nvl(f.bytes, 0), 0) / 1024 / 1024,

              '99999999.999') "Used (M)",

      to_char(nvl(nvl(f.bytes, 0), 0) / 1024 / 1024, '99999999.999') "Free (M)",

      to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0),

              '990.00') "Used %"

  FROM sys.dba_tablespaces d,

      (SELECT tablespace_name, SUM(bytes) bytes

          FROM dba_data_files

        GROUP BY tablespace_name) a,

      (SELECT tablespace_name, SUM(bytes) bytes

          FROM dba_free_space

        GROUP BY tablespace_name) f

WHERE d.tablespace_name = a.tablespace_name(+)

  AND d.tablespace_name = f.tablespace_name(+);

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
      D.TOT_GROOTTE_MB "表空间大小(M)",
      D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
      TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
                    2),
              '990.99') "使用比",
      F.TOTAL_BYTES "空闲空间(M)",
      F.MAX_BYTES "最大块(M)"
  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 4 DESC

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;


42.解决ora-01652无法通过128（在temp表空间中）扩展temp段的过程
1.创建临时表空间
create temporary tablespace  TEMP temp1 'C:/app/opt/oracle/oradata/orcl/temp01.DBF' SIZE 5G REUSE AUTOEXTEND ON NEXT  1M MAXSIZE UNLIMITED; 

43.改变缺省临时表空间 为刚刚创建的新临时表空间temp1
alter database default  temporary tablespace  temp1;

44.删除原来临时表空间
drop tablespace temp including contents and datafiles;

45.检查alter日志
tail -f -n 100 $ORACLE_BASE/ diag/rdbms/实例名/实例名/trace/alter_实例名.log

46.查看Job执行情况
SQL> select job,last_date,last_sec,next_date,next_sec,broken,interval,failures from user_jobs;

47.查看oracle日志
/opt/oracle/diag/rdbms/mnsrpt/MNSRPT/trace/alter.log


48.
查看表：      select table_name from user_tables;
查看存储过程： select object_name from user_procedures;

49.修改ORACLE字符集

1）服务器端：select  name,value$  from  props$;

  查看服务器字符集

注：若要修改服务器字符集：

1.        shutdowm immediate

2.        STARTUP MOUNT;

3.        ALTER SYSTEM ENABLE RESTRICTED SESSION;

4.        ALTER DATABASE OPEN;

5.        ALTER DATABASE CHARACTER SET ZHS16GBK;
          ALTER DATABASE national CHARACTER SET ZHS16GBK;

6.        10g中: alter database character set internal_convert zhs16gbk;

2)客户端:

export LANG=zh_CN.GBK    //这个是linux的字符集设置
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK  //这个是oracle的字符集设置

 

50.重新设置表空间
alter database pisa_cdr resize 20g

51.查看oracle进程
ps -ef | grep ora_