oracle的一些有用的方法总结

570w4k4h09

--------------重要-------------------------
select count(*) from user_tables;
select * from user_lobs;
select 'alter table ' ||t.table_name||' move lob('||t.column_name|| ') store as (tablespace ilearn);' from user_lobs t;
--下面这个是形式的写法？？、
select 'ALTER TABLE ' ||t.table_name|| ' MOVE TABLESPACE ilearn  LOB ('||t.column_name||') store as (tablespace ilearn);' from user_lobs t;


select 'alter index '||index_name ||' rebuild;'from user_indexes t where t.status ='UNUSABLE';
select index_name from user_indexes t where t.status ='UNUSABLE';
--alter index index_name rebuild;
Select 'alter table '|| table_name || ' move tablespace ilearn;' from user_tables;
select 'alter index '|| index_name ||' rebuild tablespace ilearn;' from user_indexes;
select table_name,tablespace_name from user_tables;
select   segment_name,bytes/(1024*1024),t.segment_type
  from   user_segments t
  where   tablespace_name='USERS';  
select   segment_name,bytes/(1024*1024),t.segment_type
  from   user_segments t
  where   tablespace_name='ILEARN'
  and t.segment_name='SYS_IL0000030462C00010$$'
  ;
select count(*)  from user_all_tables;
select * from PORTAL_PREFERENCE;
select * from  PLAN_TABLE;

drop table PORTAL_PREFERENCE;
drop table PLAN_TABLE;


select * from user_indexes t where t.index_name='SYS_IL0000030402C00010$$'




--------------重要-------------------------
select   SEGMENT_NAME ,t.bytes/(1024*1024),t.segment_type  from   user_segments t   where   tablespace_name='ILEARN'
and t.segment_name='CLASSROOM'
order by t.bytes desc;

select * from user_indexes t where t.index_name='CLASSROOM_CAL_EVENT_ID_INDEX';
select * from classroom;
select  t.sample_size  from user_all_tables t where t.table_name='CLASSROOM';
SELECT *
FROM (SELECT  BYTES/(1024*1024), segment_name, segment_type, owner
      FROM dba_segments
      WHERE tablespace_name = 'ILEARN'
    ORDER BY BYTES DESC)
WHERE ROWNUM < 18
and owner='ILEARN';
select  CAL_EVENT_ID from classroom





1. 启动ORACLE服务
# su - oracle
$ sqlplus /nolog
sqlplus > conn / as sysdba
sqlplus > startup
sqlplus > exit
2. 关闭ORACLE服务
sqlplus >shutdown immediate;
sqlplus >exit;

3.启动或关闭listener
$ lsnrctl startup
或者
lsnrctl
start
stop

拷贝文件夹 cp -R
解压 tar zxvf back.tar.gz
压缩 tar cvfz back.tar.gz /back/

ftp传输

ftp
cd
lcd
ls
mput *


导入或是导出
export NLS_LANG=AMERICAN_AMERICA.UTF8
set NLS_LANG=AMERICAN_AMERICA.UTF8
imp log=/opt/oracle/plsimp.log file=/opt/oracle/racexambkp/opt/oracle/backup/temp/examusr01_09_02_02_17_03_33.dmp userid=ilearn/manager1@ilearndb fromuser=ilearn touser=ilearn buffer=30720 commit=yes grants=yes ignore=no indexes=yes  constraints=yes



给某个用户解除某个表空间和授权某个表空间
revoke unlimited tablespace on users from ilearn;
alter  user ilearn quota 0 on users;
alter   user   ilearn   quota   unlimited   on   ilearn;  
alter   user  ilearn       default   tablespace   ilearn;

脱机表和联机空间

设置表空间的只读和可写状态。

删除数据库实例
oradim -delete -sid sidname




1、在数据库关闭状态下备份数据库所有的数据文件，联机日志，控制文件（在一个目
录下），如果成功备份，所有文件是一致的;
2、备份参数文件，参数文件可以另外备份，没有必要每次都备份，只需要在改变设置后备份一次;
3、冷备份建议下人工干预下执行。

对于冷备份也有两种，一种是NOARCHIVE模式下的，另外一种是ARCHIVE模式下的。
如何查看你的数据库是处于哪种模式，需要有DBA的权限运行如下命令
SQL>select name,log_mode from v$database;
或者
SQL>archive log list
如果是NOARCHIVE模式的话
    备份方法：以DBA的权限关闭数据库，然后在OS级别下用OS的拷贝方式将你数据库的数据文件、控制文件、重做日志文件、参数文件、密码文件拷贝到备份的路径中即可。
    恢复方法：以DBA的权限关闭数据库，然后将备份路径中的数据文件、控制文件、重做日志文件、参数文件、密码文件拷贝到其原来相应的位置上，然后用DBA的权限启动数据库即可。

如果是ARCHIVE模式的话
    备份方式：同NOARCHIVE模式。切记一点是无需备份重做日志文件。
    恢复方式：先正常关闭数据库，然后只用重建出错或者被破坏掉的文件；如果是数据文件的重建，则需要提供从该备份时间之后的所有归档日志文件，Oracle会自行识别处理该时间之后的事务处理。然后以DBA的权限启动数据库即可。千万不要恢复重做日志文件(最好是开始就不要做重做日志文件的备份，这样就不担心会恢复重做日志文件了)。


/home/ilearn/product/ias904/j2ee/oil/applications/ilearning/ilearn/WEB-INF
export NLS_LANG=AMERICAN_AMERICA.UTF8
imp log=/opt/oracle/plsimp.log file=/opt/oracle/racexambkp/opt/oracle/backup/temp/examusr01_09_02_02_17_03_33.dmp userid=ilearn/manager1@ilearndb fromuser=ilearn touser=ilearn buffer=30720 commit=yes grants=yes ignore=no indexes=yes  constraints=yes
exp ilearn/manager1 file=/opt/oracle/backup/temp/examusr01_$rq.dmp log=/opt/oracle/backup/logs/bkplog_$rq owner=ilearn






Move表、索引、LOB/Long [zt]


move一个表到另外一个表空间时,索引不会跟着一起move，而且会失效。（LOB类型例外）

表move，我们分为：
*普通表move
*分区表move
*LONG,LOB大字段类型move来进行测试和说明。

索引的move，我们通过rebuild来实现
SQL> select * from v$version;
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

一：move普通表、索引基本语法：
alter table tab_name move tablespace tbs_name;

move过的普通表，在不用到失效的索引的操作语句中，语句执行正常，但如果操作的语句用到了索引（主键当做唯一索引），则此时报告用到的索引失效，语句执行失败，其他如外键，非空约束，缺省值等不会失效。

我们需要重新创建主键或索引，基本语法为：
alter index index_name rebuild;
alter index pk_name rebuild;

如果我们需要move索引，则使用rebuild语法：
alter index index_name rebuild tablespace tbs_name;
alter index pk_name rebuild tablespace tbs_name;

提示：查询表所具有的索引，可以使用user_indexes视图（索引和主键都在这个视图里可找到）。

二：move分区表及索引和普通表一样，索引会失效，区别的仅仅是语法而已。

分区基本语法：特别提醒注意，如果是单级分区，则使用关键字PARTITION，如果是多级分区，则使用SUBPARTITION替代PARTITION。
如果分区或分区索引比较大，可以使用并行move或rebuild，PARALLEL (DEGREE 2);如：
ALTER TABLE PART_ALARMTEXTDATA move SUBPARTITION ALARMTEXTDATA_050910_ATD01 TABLESPACE users PARALLEL (DEGREE 2);
ALTER INDEX GLOBAL_ALARMTEXTDATA REBUILD tablespace users PARALLEL (DEGREE 2);
ALTER INDEX LOCAL_ALARMTEXTDATA REBUILD SUBPARTITION ALARMTEXTDATA_050910_ATD01 TABLESPACE users PARALLEL (DEGREE 2);

移动表的某个分区：
================
ALTER TABLE tab_name move PARTITION partition_name TABLESPACE tbs_name;

重建全局索引：
================
ALTER INDEX global_index REBUILD;或
ALTER INDEX global_index REBUILD tablespace tbs_name;
[xsb注: 分区操作时可以带上with update global indexes选项更新全局索引。]

重建局部索引：
================
ALTER TABLE tab_name MODIFY PARTITION partition_name REBUILD UNUSABLE LOCAL INDEXES;或
ALTER INDEX local_index_name REBUILD PARTITION partition_name TABLESPACE tbs_name;

提示：
USER_PART_TABLES
USER_IND_PARTITIONS
USER_IND_SUBPARTITIONS
USER_LOB_PARTITIONS
USER_LOB_SUBPARTITIONS
USER_PART_INDEXES
USER_PART_LOBS可查询分区相关内容，同时，分区对象，也是segment，所以也可在dba_segments里查的到。

三：move LONG,LOB类型（据说DBMS_REDEFINITION包可以提供一些方便，没用过。）

I:LONG类型
long类型不能通过MOVE来传输特别提示，尽量不要用LONG类型，特难管理。参考：http://www.anysql.net/2005/12/long_vs_lob.html
1，LONG不能使用insert into ... select ...等带select的模式。如
create table t123 (id int,en long);则
insert into t123(id,en) select * from t123;报告错误，可以用pl/sql来帮助解决，如：
declare
cursor cur_t123 is select * from t123;
use_t123 cur_t123%rowtype;
begin
open cur_t123;
loop
fetch cur_t123 into use_t123;
exit when cur_t123%notfound;
insert into t123(id,en) values (use_t123.id,use_t123.en);
end loop;
close cur_t123;
end;
/

对有LONG类型字段的表的转移，可以使用：
create新表的方法。
* create一个新的表，存储在需要转移的表空间。
* 创建新的索引（使用tablespace 子句指定新的表空间）。
* 把数据转移过来
方法一：用COPY的方法：
copy from bigboar/bigboar@bigboar_sid insert t123(id,en) using select id,en from t123;
方法二：PL/SQL（如上）
方法三：直接就把LONG转换成CLOB类型
create table t321(id int,en clob) tablespace users;
insert into t321(id,en) select id,to_lob(en) from t123;
方法四：exp/imp
exp bigboar/bigboar file=a.dat tables=t123
imp bigboar/bigboar file=a.dat full=y IGNORE =y
* drop掉旧表。
* rename 新表为旧表表名。

II: LOB类型在建立含有lob字段的表时，oracle会自动为lob字段建立两个单独的segment,一个用来存放数据（segment_type= LOBSEGMENT），另一个用来存放索引（segment_type=LOBINDEX）。默认它们会存储在和表一起的表空间。我们对表MOVE时， LOG类型字段和该字段的索引不会跟着MOVE，必须要单独来进行MOVE，语法如下如：
alter table t321 move tablespace users;
alter table t321 move lob(en) store as (tablespace users);

ref: 移动LOB字段至新表空间 http://bigboar.itpub.net/post/8411/55325 http://tolywang.itpub.net/post/48/100595
xsb 发表于:2006.06.13 15:03 ::分类: ( Oracle ) ::阅读:(1939次) :: Permanent link :: 引用 (0)



我以前一直使用users表空间作为默认空间,最近想把现有用户的数据都转到一个新建的表空间中,可是发现Imp后所有表还是在users表空间里面,查阅了一些资料,说是Oracle自己没有提供分配表空间的功能,Exp的时候把表的storage选项也导出了,Imp按照原表的建表语句,默认表空间是原来的users.要想导入到新的表空间,提供了几种方法,包括先在新表空间建表,然后再imp就可以了,还有指定user的表空间分配额度（quota），以及先导入然后更改表的表空间的办法。

我用的表多，先试了更改user的quota选项的方法，决定这是最方便而且彻底解决问题的办法。
过程如下：

先建立一个新的表空间tn和用户test1
用system登录sqlplus
SQL> create tablespace tn datafile '/u01/oracle/oradata/gdfaooa/tn01.dbf' size 10M autoextend on next 5M maxsize 2048M default storage(initial 64K next 1M pctincrease 0) extent management local uniform size 64K SEGMENT SPACE MANAGEMENT AUTO;
SQL> create user test1 identified by manager default tablespace tn temporary tablespace temp profile default;
SQL> grant connect to test1;
SQL> grant resource to test1;
SQL> alter user test1 quota unlimited on tn;
SQL> alter user test1 quota unlimited on users;
SQL> alter user test1 quota unlimited on system;
SQL> alter user test1 quota unlimited on tools;
如果已经建立用户，应该收回目的用户的"UNLIMITED TABLESPACE"权限：
revoke unlimited tablespace from username;
建表空间的参数上,值得慢慢琢磨一下,default storage可以不要,最主要是dbf的size与下一个extend的大小问题,如何保证节省空间.
Itpub上有人说resource权限导致oracle默认用户在系统表空间的quota是unlimited,这个我还要确定一下。
现在执行imp
imp test1/manager file=test.dmp log=test1.log fromuser=htest touser=test1 ignore=y
中间报了很多错，也显示一些表导入成功。看test1.log查错误，发现还是报在users表空间quota不够。我奇怪了半天，怎么有些表可以导入到dn表空间，有些却不行那，后来仔细看，发现错的表都是含LOB字段的。
原来是lob字段不能导入到新表空间，怎么解决呢？查资料、问人，都说lob字段特殊，在建表存储的时候系统就特殊处理lob字段的存储，所以导入导出的时候不按通常表来处理，真够郁闷的。
现在只好想别的办法了，表很多，挨个在新表空间建表多麻烦啊，还要找建表的sql。可是先导入再用工具改或者用move 命令，都一样，一百多个lob字段的表啊!!还有toad只找到试用版，不知道改变表的表空间的功能可不可以用。
后来想起自己常用的PLSQL Developer是不是也有这样的功能，就去看了看，好像没有改变表空间的功能，但是有导出当前用户所有Object的功能，生成的是所有object（表和视图）的create的sql文件。
sql文件打开进行编辑建表的sql语句都指明了存储的表空间，全部把users换成tn，这样用test1登录进去执行 sql文件就可以建立所有表和视图的表结构了，然后导入dmp数据就没有问题了。
然后我进去查询，发现一切OK啦，所有的表都存储在tn表空间上。不过tn表空间的数据文件也增大了很多，本来70多 M的dmp导入进去，数据文件从10M已经增长到300多M了，看来还是要好好琢磨一下表空间的参数了
总结过程：
1.建立新的表空间
2.建立新用户，默认表空间为刚建立的新的表空间
3.设定用户权限，quota还是要设定一下。
4.从原用户导出数据：exp username/password file=filename owner=username rows=y grants=y
5.从原用户那里，用PLSQL Developer（其他的工具也可以）导出原用户的所有object，生成sql文件。
6.编辑sql文件，替换所有的旧表空间为新表空间。
7.用新用户登录，并执行sql文件，生成表结构
8.执行Imp导入数据：imp newusername/password file=filename log=logfile fromuser=olduser touser=newuser ignore=y
9.查看新用户下的表的存储情况，确定完全存放在新的表空间下面：

select table_name,tablespace_name from user_tables;

如果没有lob字段的表,不用执行5、6、7三步。



oracle 9i 一个表空间 导入到另外一个表空间

缺省情况下，导入试图在与导出相同的表空间中创建对象。如果用户不具有那个表空间的权限，或者那个表空间不存在时，Oracle在用户帐户的缺省表空间中创建数据库对象。这些特性可以用于使用导出与导入在表空间之间移动数据库对象。要为USER_A将TABLESPACE_A的所有对象移动到TABLESPACE_B，应遵循以下步骤：   
   
  1   为USER_A导出TABLESPACE_A中的所有对象。   
   
  2   执行REVOKE   UNLIMITED   TABLESPACE   ON   TABLESPACE_A   FROM   USER_A;以收回任何授予用户帐户的无限制表空间权限。   
   
  3   执行ALTER   USER   USER_A   QUOTA   0   ON   TABLESPACE_A;以使USER_A帐户不能在TABLESPACE_A上创建任何对象。   
   
  4   删除TABLESPACE_A中USER_A拥有的所有对象。   
   
  5   执行ALTER   USER   USER_A   DEFAULT   TABLESPACE   TABLESPACE_B;以使TABLESPACE_B成为USER_A用户帐户的缺省表空间。Oracle试图将对象导入TABLESPACE_A，因为这些对象是从TABLESPACE_A导出的。注意用户不具有TABLESPACE_A上的配额。然后将查看用户的缺省表空间。在Oracle可以将数据导入TABLESPACE_B之前，必须给予USER_A用户该表空间上足够大的配额，如下步骤所示。   
   
  6   执行ALTER   USER   USER_A   QUOTA   UNLIMITED   ON   TABLESPACE_B;   
   
  7   导入被导出的数据库对象。缺省情况下，导入工具试图将它们导入到TABLESPACE_A中，然而，因为用户不具有这个表空间的配额，所以所有的对象将被创建在USER_A的缺省表空间TABLESPACE_B中。
Top



REVOKE   UNLIMITED   TABLESPACE   ON   itmpspace  FROM  itmp  ;
ALTER   USER      itmp    QUOTA   0   ON   itmpspace ;

ALTER   USER  itmp       DEFAULT   TABLESPACE   idmp;
ALTER   USER   itmp     DEFAULT   TABLESPACE    idmp;
ALTER   USER   itmp      QUOTA   UNLIMITED   ON    idmp;   


alter table t move lob(c2) store as (tablespace users);
alter table test move tablespace users lob(c2) store as (tablespace users);
ref: Move表、索引、LOB/Long
（导出、导入时同分区表一样，必须预创建此表空间）
alter table t move lob(c2) store as (tablespace users);
alter table test move tablespace users lob(c2) store as (tablespace users);
如果原表使用了long类型字段，则必须先转成clob或blob类型。Long类型不单独使用segments,而lob类型单独使用segments.