oracle的一些有用的方法总结
--------------重要-------------------------
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、在数据库关闭状态下备份数据库所有的数据文件，联机日志，控制文件（在一个目
录下），如果成功备份，所有文