锐客网

  1. 首页 > 生活百科 > it技术 > >

oracle视图如何迁移 oracle视图传入参数

IT技术Orcl

oracle中怎样将视图数据传到表中直接把select出来的结果插入表中
insert into total(表) select total(试图列) from view1
如何将ORACLE数据库数据文件迁移到其它目录您好，很高兴为您解答 。
源数据库数据文件位置：/u01/oradata/orcl
实验中想把数据文件迁移到的位置：/u01/oradata/orcl_test
源数据库数据文件位置：
sys@ORCL select file_name from dba_data_files;
FILE_NAME
----------------------------------------
/u01/oradata/orcl/system01.dbf
/u01/oradata/orcl/undotbs01.dbf
/u01/oradata/orcl/sysaux01.dbf
/u01/oradata/orcl/users01.dbf
sys@ORCL select file_name from dba_temp_files;
FILE_NAME
----------------------------------------
/u01/oradata/orcl/temp01.dbf
sys@ORCL col member for a30
sys@ORCL select member from v$logfile ;
MEMBER
------------------------------
/u01/oradata/orcl/redo01.log
/u01/oradata/orcl/redo02.log
/u01/oradata/orcl/redo03.log
需要移动所有的数据文件 ， 采用alter database 方法
1、关闭数据库
sys@ORCL shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
2、移动数据文件到/u01/oradata/orcl_test目录
[oracle@ora10gserv orcl]$ pwd
【oracle视图如何迁移 oracle视图传入参数】/u01/oradata/orcl
[oracle@ora10gserv orcl]$ mv * /u01/oradata/orcl_test/
[oracle@ora10gserv orcl]$ ls
[oracle@ora10gserv orcl]$ cd /u01/oradata/orcl_test/
[oracle@ora10gserv orcl_test]$ ls
control01.ctlcontrol03.ctlredo02.logsysaux01.dbftemp01.dbfusers01.dbf
control02.ctlredo01.logredo03.logsystem01.dbfundotbs01.dbf
3、修改控制文件位置
[oracle@ora10gserv /]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 2 00:05:27 2011
Copyright (c) 1982, 2005, Oracle.All rights reserved.
SQL conn /as sysdba
Connected to an idle instance.
SQL startup nomount
ORACLE instance started.
Total System Global Area465567744 bytes
Fixed Size1220024 bytes
Variable Size146801224 bytes
Database Buffers314572800 bytes
Redo Buffers2973696 bytes
SQL create pfile='/u01/initorcl.ora' from spfile;
File created.
SQL host vi /u01/initorcl.ora
orcl.__db_cache_size=314572800
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=138412032
orcl.__streams_pool_size=0
*.audit_file_dest='/dba/admin/orcl/adump'
*.background_dump_dest='/dba/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_file_record_keep_time=14
*.control_files='/u01/oradata/orcl_test/control01.ctl','/u01/oradata/orcl_test/control02.ctl','/u01/oradata/orcl_test/control03.ctl'
*.core_dump_dest='/dba/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain='lsf.com.cn'
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/dba/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/arch_orcl'
*.open_cursors=300
*.pga_aggregate_target=154140672
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=2000
*.sga_target=463470592
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/dba/admin/orcl/udump'
SQL shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL create spfile from pfile='/u01/initorcl.ora';
File created.
4、启动数据库到mount状态，更改数据文件位置
idle startup mount
ORACLE instance started.
Total System Global Area465567744 bytes
Fixed Size1220024 bytes
Variable Size146801224 bytes
Database Buffers314572800 bytes
Redo Buffers2973696 bytes
Database mounted.
idle alter database rename file '/u01/oradata/orcl/system01.dbf' to '/u01/oradata/orcl_test/system01.dbf';
Database altered.
idle alter database rename file '/u01/oradata/orcl/undotbs01.dbf' to '/u01/oradata/orcl_test/undotbs01.dbf';
Database altered.
idle alter database rename file '/u01/oradata/orcl/sysaux01.dbf' to '/u01/oradata/orcl_test/sysaux01.dbf';
Database altered.
idle alter database rename file '/u01/oradata/orcl/users01.dbf' to '/u01/oradata/orcl_test/users01.dbf';
Database altered.
5、继续接着第4步，更改联机日志文件位置
idle alter database rename file '/u01/oradata/orcl/redo01.log' to '/u01/oradata/orcl_test/redo01.log';
Database altered.
idle alter database rename file '/u01/oradata/orcl/redo02.log' to '/u01/oradata/orcl_test/redo02.log';
Database altered.
idle alter database rename file '/u01/oradata/orcl/redo03.log' to '/u01/oradata/orcl_test/redo03.log';
Database altered.
6、打开数据库
idle alter database open;
Database altered.
idle conn /as sysdba
Connected.
sys@ORCL select file_name from dba_data_files;
FILE_NAME
----------------------------------------
/u01/oradata/orcl_test/system01.dbf
/u01/oradata/orcl_test/undotbs01.dbf
/u01/oradata/orcl_test/sysaux01.dbf
/u01/oradata/orcl_test/users01.dbf
sys@ORCL select file_name from dba_temp_files;
FILE_NAME
----------------------------------------
/u01/oradata/orcl/temp01.dbf
sys@ORCL col member for a40
sys@ORCL select member from v$logfile;
MEMBER
----------------------------------------
/u01/oradata/orcl_test/redo01.log
/u01/oradata/orcl_test/redo02.log
/u01/oradata/orcl_test/redo03.log
sys@ORCL col name for a50
sys@ORCL select name from v$controlfile;
NAME
--------------------------------------------------
/u01/oradata/orcl_test/control01.ctl
/u01/oradata/orcl_test/control02.ctl
/u01/oradata/orcl_test/control03.ctl
7、临时文件的处理
由于控制文件不记录临时文件的信息 ， 所以不能使用alter database rename file 命令完成，只能删除掉原来的再创建一个
sys@ORCL create temporary tablespace temp02 tempfile '/u01/oradata/orcl_test/temp02.dbf' size 200m autoextend on extent management local uniform size 1m;
Tablespace created.
sys@ORCL alter database default temporary tablespace temp02;
Database altered.
sys@ORCL drop tablespace temp including contents and datafiles;
Tablespace dropped.
sys@ORCL select file_name from dba_temp_files;
FILE_NAME
----------------------------------------
/u01/oradata/orcl_test/temp02.dbf
[oracle@ora10gserv orcl_test]$ ls -l
total 1427572
-rw-r----- 1 oracle oinstall7061504 Aug2 00:20 control01.ctl
-rw-r----- 1 oracle oinstall7061504 Aug2 00:20 control02.ctl
-rw-r----- 1 oracle oinstall7061504 Aug2 00:20 control03.ctl
-rw-r----- 1 oracle oinstall52429312 Aug2 00:20 redo01.log
-rw-r----- 1 oracle oinstall52429312 Aug2 00:15 redo02.log
-rw-r----- 1 oracle oinstall52429312 Aug2 00:15 redo03.log
-rw-r----- 1 oracle oinstall 251666432 Aug2 00:20 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 Aug2 00:20 system01.dbf
-rw-r----- 1 oracle oinstall 209723392 Jul 21 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall 209723392 Aug2 00:20 temp02.dbf
-rw-r----- 1 oracle oinstall 419438592 Aug2 00:20 undotbs01.dbf
-rw-r----- 1 oracle oinstall 104865792 Aug2 00:15 users01.dbf
[oracle@ora10gserv orcl_test]$ rm -rf temp01.dbf
sys@ORCL select username,temporary_tablespace from dba_users;
USERNAMETEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEWTEMP02
SYSTEMP02
SYSTEMTEMP02
DBSNMPTEMP02
SYSMANTEMP02
OUTLNTEMP02
MDSYSTEMP02
ORDSYSTEMP02
CTXSYSTEMP02
ANONYMOUSTEMP02
EXFSYSTEMP02
DMSYSTEMP02
WMSYSTEMP02
XDBTEMP02
ORDPLUGINSTEMP02
SI_INFORMTN_SCHEMATEMP02
OLAPSYSTEMP02
MDDATATEMP02
DIPTEMP02
TSMSYSTEMP02
20 rows selected.
如若满意，请点击右侧【采纳答案】 ， 如若还有问题，请点击【追问】
希望我的回答对您有所帮助，望采纳！
~ O(∩_∩)O~
oracle数据库 ， 不同实例，如何做用户迁移？简单oracle视图如何迁移的说一下过程oracle视图如何迁移：
首先要创建迁移的用户和表空间oracle视图如何迁移；
备份要迁移的数据；
将要迁移的数据表空间remove到新建的表空间中即可；
如果oracle视图如何迁移你只需要用户访问数据oracle视图如何迁移，直接授权就行；(?)
如果要迁移表空间，还有很多事项要注意，网络上有很多资料；
最后删除之前的表空间和不要的用户 。
用oracle sql developer进行数据迁移，从mysql数据库迁移到oracle数据库，视图没有迁移成功 。我前段时间做过这样的迁移，mysqltooracle 这个工具应该可以帮到你 ， 但是迁移过去可能还是会有好多问题 ， 你要自己慢慢的改 。
怎么将oracle视图同步到sqlserver2008视图如果你导入到oracle里变成表，数据虽然现在都一样
但实际上已经没法随着表里的数据变化而变化了
不行你就把sqlserver的建立视图脚本拿出来，有语法错误的话，改成oracle的就好了
oracle视图如何迁移的介绍就聊到这里吧 ， 感谢你花时间阅读本站内容，更多关于oracle视图传入参数、oracle视图如何迁移的信息别忘了在本站进行查找喔 。

    推荐阅读


    上一篇：新媒体产品如何破圈吸粉，新媒体如何吸引粉丝

    下一篇：视频号删除影响用微信吗，微信视频号删除有影响吗