常用:
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
7.倒序取中间记录
8.记录一下自己创建外部表的过程,因为中间出了一些小错误。
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.