[Oracle] 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#
  
18.netstat -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<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='表空间名'

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;