oracle笔记

常用：

1.sysdate,rownum,user,rowid
2.dict, V$fixed_table, dict_columns

=====================================================================
一。重点
创建表空间:
CREATE SMALLFILE TABLESPACE "XF" DATAFILE 'E:\oracle\product\10.2.0\oradata\orcl\xf.dbf' SIZE 1000M
AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

CREATE SMALLFILE TABLESPACE "CXXF" DATAFILE 'E:\oracle\product\10.2.0\oradata\orcl\cxxf.dbf' SIZE 2000M
AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

创建用户:
CREATE USER "XF" PROFILE "DEFAULT" IDENTIFIED BY "XF" DEFAULT TABLESPACE "XF" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "XF";
GRANT "RESOURCE" TO "XF";

CREATE USER "CXXF" PROFILE "DEFAULT" IDENTIFIED BY "CXXF" DEFAULT TABLESPACE "CXXF" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "CXXF";
GRANT "RESOURCE" TO "CXXF";

导入业务数据:
导入业务数据时注意检查触发器.
导入地图数据:
可以用ORACLE的备份文件导入, 也可以用 DeskPro 直接导入SDB数据

导出命令：exp system/password@orcl file=filename.dmp owner=(xf,cxxf)

导入命令 imp system/password@orcl file=filename.dmp fromuser=(xf,cxxf) touser=(xf,cxxf)
删除用户命令：drop user “name” cascade;

二。Oracle 修改表格字段

关键字: oracle 修改表格字段
给指定的表格添加一个字段

alter table 你的表 add (字段名 字段类型);

实际应用

alter table t_user add (t_create_date date);

//给一个表格的字段天加一默认的值

alter table t_user mofidy 你的字段 default 你的默认值

举例

alter table t_user mofidy t_create_date default sysdate;sysdate(为ORACLE默认的系统时间)



删除表的字段
alter table 你的表 drop 你的表字段
举例
alter table t_user drop t_create_date;
一次性删除表多个字段：
alter table table_name drop (column_name1,column_name2,...)

添加主键 constraint 翻译 约束

alter table 你的表 add constraint 约束名字 约束关键字 (你的列);

alter table t_user add constraint t_pk primary key(id);

添加外键约束 references 参考

alter table 主表 add constraint 外键约束名 约束关键字(关联列) references 参考的表(参考表的列);

alter table t_test add contraint t_fk foreign key (id) references t_user(id);

//删除外键

alter table t_test drop constraint 外键名

alter tabl t_test drop constaint t_fk;


alter table table_name add (col1 type,col2 type);

ALTER TABLE TABLE_AAA ADD (STOCKMAN NUMBER(18))//添加一列

COMMENT ON COLUMN TABLE_AAA.STOCKMAN IS '管理人员' //给列添加注释

ALTER TABLE TABLE_AAA ADD CONSTRAINT FK_TABLE_AAA_R_TABLE_BBB_S FOREIGN KEY (STOCKMAN) REFERENCES TABLE_BBB (ID)//给列添加外键

修改表字段名称：
alter table tablename rename column columnName_old to columnName_new;


---------------------------------------------------------------------

-----------。常用操作
1.查看触发器
SELECT * FROM All_Objects WHERE OBJECT_TYPE='TRIGGER'
2.TNS配置实例，oracle的G:\oracle\product\10.2.0\db_1 \NETWORK\ADMIN\tnsnames.ora文件
ORCL_101 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
2.insert into a(smx,smy,waterresname) select b.smx,b.smy,b.name from b; //b表的数据插入a表中
3.查看oracle对象源码：user_source
4.oracle表批量授权 从一個用戶到另外一個用戶
将A用户下所有的表授权给B用户；
sqlplus A/A
select 'grant select,insert,update,delete on A.' || table_name || ' to B;' from user_tables;
然后选择上面出现的所有的语句，进入sysdba权限进行操作：
sqlplus "/as sysdba"
然后执行上面的所有的语句就可以了；
5.oracle 表结构操作：
(1)修改表名字：alter table commandllist rename to commandlist

6.调用oracle中的 Java source

      SQL>   create   or   replace   and   compile   java   source   named   HelloWorld 
2   as 
3   public   class   HelloWorld   { 
4   public   static   void   db_run   (){ 
5   System.out.println( "Hello   World "); 
6   } 
7   } 
8   / 

Java   created. 

SQL>   create   or   replace   procedure   run_helloworld   
2   as   language   java 
3   name   'HelloWorld.db_run() '; 
4   / 

Procedure   created. 
SQL>   set   serveroutput   on   size   5000 
SQL>   call   dbms_java.set_output(5000)   ; 

Call   completed. 

SQL>   
SQL>   exec   run_helloworld   ; 
Hello   World 

PL/SQL   procedure   successfully   completed.

7.倒序取中间记录

      select   *   from   (select  v.*,   rownum   as   rid   from 
  (select * from vehiclelist where vehicleid='wj10-x7016' order by  vid desc) v   where   rownum<10)   where   rid   >   4
 或者：
   select *
  from (select a.*, rownum row_num
          from (select a.* from vehiclelist a where vehicleid='wj10-x7016'   order by vid desc) a)
 where row_num between 5 and 9

8.记录一下自己创建外部表的过程，因为中间出了一些小错误。

-- 1. 创建文件目录
SQL>create directory upload_dir as '/storagepool/upload';
Directorycreated.
-- 2. 创建外部表
SQL>create table analog_tmp_entrypage_ext_1(
 2  PROFILE_ID NUMBER(22),
 3  REPORT_TIME NUMBER(22),
 4  SESSION_ID NUMBER(22),
 5  URL VARCHAR2(2048)
 6  )
 7  organization external
 8  (type oracle_loader
 9  default directory upload_dir
10  access parameters
11  (records delimited by newline
12  fields terminated by X'05'
13  missing field values are null
14  (PROFILE_ID, REPORT_TIME, SESSION_ID, URL)
15  )
16  location('weblog.analog_tmp_entrypage_0.dat')
17  );
Tablecreated.
-- 3. 查询报错。因为外部表和sqlldr原理类似，加载外部数据时需要创建log文件，无法加载的数据，会创建bad文件。
--   这里出错的原因是：oracle对路径'/storagepool/upload'没有访问权限，无法在该路径下创建log文件和bad文件。
SQL>select * from analog_tmp_entrypage_ext_1 where rownum < 2;
select* from analog_tmp_entrypage_ext_1 where rownum < 2
*
ERRORat line 1:
ORA-29913:error in executing ODCIEXTTABLEOPEN callout
ORA-29400:data cartridge error
KUP-04063:unable to open log file ANALOG_TMP_ENTRYPAGE_EXT_1_12190.log
OSerror Permission denied
ORA-06512:at "SYS.ORACLE_LOADER", line 19
-- 4. 创建log文件和bad文件的路径，指向tmp分区。
SQL>create directory upload_log_dir as '/tmp/upload_log';
Directorycreated.
-- 5. 重建外部表，将log和bad指向新路径
SQL>drop table analog_tmp_entrypage_ext_1 purge;
Tabledropped.
SQL>create table analog_tmp_entrypage_ext_1(
 2  PROFILE_ID NUMBER(22),
 3  REPORT_TIME NUMBER(22),
 4  SESSION_ID NUMBER(22),
 5  URL VARCHAR2(2048)
 6  )
 7  organization external
 8  (type oracle_loader
 9  default directory upload_dir
10  access parameters
11  (records delimited by newline
12  badfile upload_log_dir:'analog_tmp_entrypage_ext_1%A_%P.bad'
13  logfile upload_log_dir:'analog_tmp_entrypage_ext_1%A_%P.log'
14  fields terminated by X'05'
15  missing field values are null
16  (PROFILE_ID, REPORT_TIME, SESSION_ID, URL)
17  )
18  location('weblog.analog_tmp_entrypage_0.dat')
19  );
Tablecreated.
-- 6. 重新查询，仍然报错。
SQL>select * from analog_tmp_entrypage_ext_1 where rownum < 2;
select* from analog_tmp_entrypage_ext_1 where rownum < 2
*
ERRORat line 1:
ORA-29913:error in executing ODCIEXTTABLEFETCH callout
# 7. 检查日志文件，原来是因为url默认长度为255，而该列长度为2048，存储了很多长度超过255的记录。
oracle@dw_nearline1:/tmp/upload_log>moreanalog_tmp_entrypage_ext_1_13056.log 
LOG file opened at 07/16/09 13:45:45
FieldDefinitions for table ANALOG_TMP_ENTRYPAGE_EXT_1
 Record format DELIMITED BY NEWLINE
 Data in file has same endianness as the platform
 Rows with all null fields are accepted
 Fields in Data Source: 
   PROFILE_ID                      CHAR (255)
     Terminated by "05"
     Trim whitespace same as SQL Loader
   REPORT_TIME                     CHAR (255)
     Terminated by "05"
     Trim whitespace same as SQL Loader
   SESSION_ID                      CHAR (255)
     Terminated by "05"
     Trim whitespace same as SQL Loader
   URL                            CHAR (255)
     Terminated by "05"
     Trim whitespace same as SQL Loader
KUP-04021:field formatting error for field URL
KUP-04026:field too long for datatype
KUP-04101:record 13 rejected in file /storagepool/upload/weblog.analog_tmp_entrypage_0.dat
KUP-04001:error opening file /storagepool/upload/ANALOG_TMP_ENTRYPAGE_EXT_1_13056.bad
KUP-04017:OS message: Permission denied
-- 8. 再次重建外部表，将列url长度设置为2048。
SQL>drop table analog_tmp_entrypage_ext_1 purge;
Tabledropped.
SQL>create table analog_tmp_entrypage_ext_1(
 2  PROFILE_ID NUMBER(22),
 3  REPORT_TIME NUMBER(22),
 4  SESSION_ID NUMBER(22),
 5  URL VARCHAR2(2048)
 6  )
 7  organization external
 8  (type oracle_loader
 9  default directory upload_dir
10  access parameters
11  (records delimited by newline
12  badfile upload_log_dir:'analog_tmp_entrypage_ext_1%A_%P.bad'
13  logfile upload_log_dir:'analog_tmp_entrypage_ext_1%A_%P.log'
14  fields terminated by X'05'
15  missing field values are null
16  (PROFILE_ID, REPORT_TIME, SESSION_ID, URLchar(2048))
17  )
18  location('weblog.analog_tmp_entrypage_0.dat')
19  );
Tablecreated.
SQL>select * from analog_tmp_entrypage_ext_1 where rownum < 2;
PROFILE_IDREPORT_TIME SESSION_ID URL
--------------------- --------------  -------------------------
        1    20070701     247355           /www.alibaba.com/trade/offer/detail
-- 9. 如果有多个文件，在location参数中加上即可
SQL>drop table analog_tmp_entrypage_ext_1 purge; 
Tabledropped.
etl@ALIDW>create table analog_tmp_entrypage_ext_1(
 2     PROFILE_ID NUMBER(22),
 3     REPORT_TIME NUMBER(22),
 4     SESSION_ID NUMBER(22),
 5     URL VARCHAR2(2048)
 6  )
 7  organization external(
 8     type oracle_loader
 9     default directory upload_dir
10     access parameters(
11             records delimited by newline
12             badfile upload_log_dir:'analog_tmp_entrypage_ext_1%A_%P.bad'
13             logfile upload_log_dir:'analog_tmp_entrypage_ext_1%A_%P.log'
14             fields terminated by X'05'
15             missing field values are null
16             (PROFILE_ID, REPORT_TIME, SESSION_ID, URL char(2048))
17     )
18     location(
19       'weblog.analog_tmp_entrypage_0.dat', 
20       'weblog.analog_tmp_entrypage_1.dat', 
21       'weblog.analog_tmp_entrypage_2.dat', 
22       'weblog.analog_tmp_entrypage_3.dat'
23     )
24  );
Tablecreated.