oracle 常用命令

exp test_1209/test_1209@127.0.0.1/orcl tables=(TEST_INDEX) file=d:\test_index.dmp

exp test_1209/test_1209@127.0.0.1/orcl tables=(TEST_INDEX) file=d:\test_index.dmp rows=n

exp aqsc_1221/aqsc_1221@192.168.1.97/orcl rows=y indexes=n compress=n STATISTICS=none GRANTS=n TRIGGERS=n CONSTRAINTS=n buffer=65536 feedback=100000 file=aqsc_1221_0530.dmp log=aqsc_1221_0530.log tables=act_ge_bytearray,act_ge_property,act_hi_actinst,act_hi_attachment,act_hi_comment,act_hi_detail,act_hi_identitylink,act_hi_procinst,act_hi_taskinst,act_hi_varinst,act_id_group,act_id_info,act_id_membership,act_id_user,act_re_deployment,act_re_model,act_re_procdef,act_ru_event_subscr,act_ru_execution,act_ru_identitylink,act_ru_job,act_ru_task,act_ru_variable

imp aqsc_0305/aqsc_0305@127.0.0.1/orcl file=aqsc_1221_20190529.dmp log=aqsc_1221_20190529.log full=y

imp aqsc_0305/aqsc_0305@127.0.0.1/orcl file=aqsc_1221_20190529.dmp log=aqsc_1221_20190529.log full=y ignore=y

imp aqsc_1221/aqsc_1221@127.0.0.1/orcl file=aqsc_1221_0618.dmp log=aqsc_1221_0618.log tables=(sys_menu)

create view ZT_TRAFFICCOMPANY as select * from FSZHATU.ZT_TRAFFICCOMPANY;

create table zt_car as select * from fszhatu.zt_car 1=2

----
----012.xf_file:项目附件
----drop   table xf_file;
create table xf_file(
	id	VarChar2(50)	Not Null	,	--id:主键
	type	VarChar2(20)		,	--type:类型(手册、文书等)
	sx	Number(9)		,	--sx:顺序（保留接口）
	gcxx_id	VarChar2(50)		,	--gcxx_id:工程信息id（保留接口）
	gcxx_form_id	VarChar2(50)		,	--gcxx_form_id:文书id（保留接口）
	name	VarChar2(50)		,	--name:文件名称
	ext	VarChar2(20)		,	--ext:文件后缀
	path	VarChar2(100)		,	--path:文件路径
	create_date	Date	Not Null	,	--create_date:创建时间
	create_by	VarChar2(50)	Not Null	,	--create_by:创建者标识
	update_date	Date		,	--update_date:更新时间
	update_by	VarChar2(50)		,	--update_by:更新者标识
	remarks	VarChar2(250)		,	--remarks:备注信息
	del_flag	Number(4)			--del_flag:删除标记
)
;

alter table XF_FILE add constraint PK_XF_FILE primary key(ID) ;


comment on table XF_FILE			is	'xf_file:项目附件';

comment on column xf_file.id 			is	'id:主键';
comment on column xf_file.type 			is	'type:类型(手册、文书等)';
comment on column xf_file.sx 			is	'sx:顺序（保留接口）';
comment on column xf_file.gcxx_id 			is	'gcxx_id:工程信息id（保留接口）';
comment on column xf_file.gcxx_form_id 			is	'gcxx_form_id:文书id（保留接口）';
comment on column xf_file.name 			is	'name:文件名称';
comment on column xf_file.ext 			is	'ext:文件后缀';
comment on column xf_file.path 			is	'path:文件路径';
comment on column xf_file.create_date 			is	'create_date:创建时间';
comment on column xf_file.create_by 			is	'create_by:创建者标识';
comment on column xf_file.update_date 			is	'update_date:更新时间';
comment on column xf_file.update_by 			is	'update_by:更新者标识';
comment on column xf_file.remarks 			is	'remarks:备注信息';
comment on column xf_file.del_flag 			is	'del_flag:删除标记';

create tablespace test_data
logging
datafile 'C:/app/Ray/oradata/orcl/TEST_DATA.dbf'
size 200m
autoextend on next 100m
maxsize 2048m
extent management local;

drop tablespace test_data
including contents and datafiles
cascade constraints;

create user test_1209 --用户名
identified by test_1209 --密码
default tablespace test_data --默认表空间
grant dba to test_1209 --给权限

grant dba to test_1209;

grant select on table1 to user1;

drop user test_1209 cascade;

select * from dba_users;

truncate table act_hi_actinst;
truncate table act_hi_detail;
truncate table act_hi_identitylink;
truncate table act_hi_procinst;
truncate table act_hi_taskinst;
truncate table act_hi_varinst;
truncate table act_ru_event_subscr;
truncate table act_ru_execution;
truncate table act_ru_task;
truncate table act_ru_variable;
truncate table act_ru_event_subscr;
truncate table act_ru_execution;
truncate table act_ru_identitylink;
truncate table act_ru_job;


delete from act_ru_task;
delete from act_ru_identitylink;
delete from act_ru_variable;
delete from act_ru_event_subscr;
delete from act_ru_execution;

create index index_hist_sr on ZT_POSITION_HIST (SYNCHRO_DATE, RECEIVE_DATE);
create index index_hist_rd on ZT_POSITION_HIST (RECEIVE_DATE);
create index index_hist_PRM on ZT_POSITION_HIST (R_PLATE_NUMBER);

select *
from USER_INDEXES
where TABLE_NAME = 'ZT_POSITION_HIST';

select *
from user_ind_columns
where table_name = 'ZT_POSITION_HIST'
  and column_name = 'RECEIVE_DATE';

select 'INSERT INTO TEST_INDEX (ID,A,B,C)
 VALUES(' || '''' || ID ||'''' || ','
|| '''' || A || '''' || ','
|| '''' || B || '''' || ','
|| '''' || C || '''' || ');'
 From TEST_INDEX order by ID