hs_oracle

1.未进行truncate操作的表的数据恢复
  select * from test_11 as of timestamp to_date('20130507174426','yyyymmddhh24miss');


  sqlldr ng4_szy/ng4_szy@119
  control=td_let_tect.ctl
  direct=y
  bad=bad/td_let_tect.bad
  log=log/td_let_tect.log  
 
2.删除用户 drop user yhs cascade;  
  修改密码 alter user yhs identified by "abc1234%"

3.comment on column yhs_test.name is "名字"   给字段添加描述
  comment on table yhs_test is "严海生测试"   给表添加描述
 
4.查询表空间情况:
  select sum(a.bytes)/1024/1024/1024/1024 from dba_data_files a;  -- 数据库空间总容量 单位t
  select sum(a.bytes)/1024/1024/1024/1024 from dba_free_space a;  -- 数据库空间剩余容量 单位t
 
5.建立、删除索引
  create index index_test on yhs_new_1 (start_time);
  drop index index_test ;
  以2200万数据为例:
      1.建立索引前查询select /*+ parallel(a,9)*/ count(1)语句，共45秒，建立索引后查询时间1.734秒
                        没加 /*+ parallel(a,9)*/             ，共86秒，建立索引后查询时间1.87秒  where必须带  索引字段条件
        
      2.表占用空间14700M (14G)，增加索引后，索引占用空间480M 。
        所有字段lengthb 11162M(11G),索引字段lengthb 167M
        
        lengthb接近表占用空间
       

6.unicode字符(如:汉字)，占2个字符长度，英文标点之类字符占1个字节。
  varchar2 最大长度为 4000，超过就报错，也就是最多可放4000个英文(2000个中文)
 
7.按用户导入文件
  imp system/oracle@192.168.212.240/orcl fromuser=bicp touser=bicp file=BICP20120524.DMP log=bicp.log buffer=40000 ignore=y;
 
8.不存日志情况下表备份
   create table test nologging as
   select * from dba_objects
   
9.锁表的处理

  第一步，查询出被锁的表的session_id,和serial#。
  SQL code
  SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username,
  l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time
  FROM v$locked_object l, all_objects o, v$session s
  WHERE l.object_id = o.object_id
  AND l.session_id = s.sid
  ORDER BY sid, s.serial# ;
 
 
  第二步，kill session
  SQL code
  alter system kill session 'AAA,BBB' -- 其中AAA是上面查询出的sid,BBB是serial#的值
 
 
  此处注意，看好是不是你自己锁的表，看准了在kill
  第三部，一般上两个步骤就搞定锁表了，如果杀不掉，就进入到OS级别杀......
  SQL code
  select spid, osuser, s.program
  from v$session s,v$process p
  where s.paddr=p.addr and s.sid=24 （24是上面的sid）
 
10.查询当前实例
   select instance_name from v$instance;
 
  linux下 kill -9 12345(12345为上边查询出的spid)
   

9.高水位 ？？？？
 
   \\192.168.1.8
   
   
10.启动oracle数据库。
   用下面的步骤来启动oracle数据库。
   首先使用oracle用户登录Linux，然后在shell命令行中执行下面的命令：
   第一步：打开Oracle监听
   lsnrctl start
   第二步：进入sqlplus
   sqlplus /nolog
   SQL>
   第三步：使用sysdab角色登录sqlplus
   SQL> conn /as sysdba
   第四步：启动数据库
   SQL> startup
   经过上面的四个步骤，oracle数据库就可以启动了。   
   
   
   
   --适用于频繁删除插入的大数据表
select creator,name,ctime,npages,fpages,stats_time,definer,tbspace from SYSIBM.SYSTABLES
where   name = 'TA_CCM_COMP_DETAIL'  
order by fpages desc;
 
runstats on table ts_db2.TA_CCM_COMP_DETAIL and indexes all;

reorg table TA_CCM_COMP_DETAIL;
 
select *  from TA_CCM_COMP_DETAIL;

-- 缺失数据文件导致数据库无法启动
alter database datafile '/opt/oracle/oradata/boss2/TBS_BICP_DATA4.dbf' offline drop;
alter database open;
--删除了表空间
DROP tablespace 表空间名 including contents and datafiles

-- 共享池太小，数据库无法启动
sqlplus /nolog
conn /as sysdba
找到\Db_1\database 目录
create pfile from spfile;
生成INITyhs.ORA
修改INITyhs.ORA文件里面的*.shared_pool_size=251658240
create spfile from pfile;                 

-- 改用户名
UPDATE USER$ SET NAME='NOAS_APP_BAK' WHERE NAME='NOAS_APP';
ALTER SYSTEM CHECKPOINT;                                   
                                      
-- 用户解锁
alter user scott account unlock;                    


-- 分区表允许行移动  即:分区字段可样进行update
alter  table  表名  enable  row  movement;  



-- 客户端字符集
select * from v$nls_parameters;
计算机环境变量设置 nls_lang SIMPLIFIED CHINESE_CHINA.ZHS16GBK
-- 新增环境变量
   nls_lang=SIMPLIFIED CHINESE_CHINA.ZHS16GBK   
    
    

-- 服务器直接导出数据
sqlplus -s noas_app/Zongfen_12<<EOF
set trimspool on
set linesize 120
set pagesize 20000
set newpage 1
set heading off
set term off
set feedback off
spool /home/oracle/wcq/yhs.txt
select t.region_id||'|'||t.region_name from noas_app.dim_region t;
spool off
eof;

exp noas_app/noas_app file=a.dmp owner = noas_app
imp noas_app/noas_app file=a.dmp fromuser=noas_app touser=noas_app    

create database link APP_116
  connect to NOAS_APP identified by noas_app
  using 'ora116';
select * from 表名@dblink名  
-- 数据泵备份、恢复数据
create directory yhs_directory as '/opt/data/yhs_test';  -- 创建目录
grant write,read on directory yhs_directory to noas_app; -- 给目录设置访问权限
expdp noas_app/Zongfen_12 tables=yhs_1 directory=yhs_directory dumpfile=a.dmp schemas=bicp;  -- 导出数据
impdp noas_app/Zongfen_12 tables=yhs_1 directory=yhs_directory dumpfile=a.dmp remap_schema=bicp:BICP_aa1 ignore=true; -- 导入数据


-- 外部表
create directory yhs_directory as '/opt/data/yhs_test';  -- 创建目录
grant write,read on directory yhs_directory to noas_app; -- 给目录设置访问权限
drop table yhs_test;
 create table yhs_test
 (
   id varchar2(20)
   ,
   name1 varchar2(20)
 )
 organization external
 (
  type oracle_loader
  default directory yhs_directory
  access parameters
  (
    records delimited by newline
    fields terminated by '|'
  )
  location('yhs.txt')
 )
 reject limit unlimited;
 
-- 查看和修改共享池大小  
  alter system flush shared_pool;
ALTER SYSTEM SET SHARED_POOL_SIZE='120M' SCOPE=BOTH

select count(*) from v$process;
--设置的最大连接数（默认值为150）
select value from v$parameter where name = 'processes';  
--修改最大连接数
alter system set processes = 300 scope = spfile;


-- tns路径
cd $ORACLE_HOME/network/admin/tnsnames.ora

-- 监听状态
lsnrctl status/start/stop
netca 设置监听

随机生产100条数据
  select dbms_random.value,dbms_random.value from dual
  connect by rownum<= 100

-- 错误解决 No space left on device
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device
vi /etc/sysctl.conf
修改 kernel.shmall = 5174605  

show parameter background_dump_dest


-- 赋权
grant execute any procedure to noas_dw; -- 所有过程
grant select any table to noas_dw;



4、ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","alter
system flush shared_pool","sga heap(1,0)","kglsim object batch")
解决办法：
   a. SQL> alter system flush shared_pool;
   b. SQL> alter system flush buffer_cache;
   c. SQL>alter session set events 'immediate trace name flush_cache level 1';
   d. SQL> clear buffer


select table_name ,partitioned from dba_all_tables t where t.owner = 'NI_IASS' and t.partitioned = 'YES';--查询是否是分区表
select * from user_extents t;
select * from user_tab_partitions t; --查询分区表一级分区详细信息
select * from user_tab_subpartitions t; --查询分区表子分区详细信息
select * from User_Part_Tables t;--查询分区表列表
select * from user_part_key_columns t;--查询分区表分区字段名
select * from dba_subpart_key_columns t; --查询分区表子分区字段名


-- 创建带分区的表结构
CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE(TIME)
 (PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')),
 PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),
 PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),
 PARTITION P4 VALUES LESS THAN (MAXVALUE))
 AS SELECT ID, TIME FROM T;


insert后报系统内部代码错误
原因可能是因为系统不正常断电，
1.startup mount
2.recover database;
3.alter database open;


查看表空间已经使用的百分比
select a.tablespace_name,
       a.bytes / 1024 / 1024 "Sum MB",
       (a.bytes - b.bytes) / 1024 / 1024 "used MB",
       b.bytes / 1024 / 1024 "free MB",
       round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
  from (select tablespace_name, sum(bytes) bytes
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes, max(bytes) largest
          from dba_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 order by ((a.bytes - b.bytes) / a.bytes) desc;


-- 设置环境变量
export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/acfsmounts/acfs_db1

-- 压缩表/解压表
alter table test compress;
alter table test nocompress;

-- 压缩表空间(新建表为压缩后)/解压表空间
alter tablespace test_tab default compress;
alter tablespace test_tab default nocompress;

set autotrace only;
select * from scott.emp;

explain plan set statement_id='yhs01' for select * from scott.emp;
 select * from plan_table where statement_id='yhs01';

启动日志
$ORACLE_BASE/admin/orasid/bdump/alert_orasid.log


C:\Program Files\NetSarang\Xmanager Enterprise 3



修改oracle服务器字符集
SHUTDOWN IMMEDIATE;
STARTUP MOUNT EXCLUSIVE;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;
SHUTDOWN immediate;
startup;