Database migration command memo

Source: Internet
Author: User
Tags field table idate

Database migration command memo NameValue view tablespace and position select. tablespace_name, B. file_name,. block_size,. block_size, B. bytes/1024/1024 "Sum MB" from dba_tablespaces a, dba_data_files B where. tablespace_name = B. tablespace_name; CREATE a TABLESPACE and create smallfile tablespace "MDB_TEST" DATAFILE '/home/itiltest/itildbs/oradata/ITILRPDB/comment 'size 1 mautoextend on next 10 MMAXSIZE UNLIMITED LOGGINGEXTENT MANAGEMENT LOCAL SEGMENT S Pace management auto default nocompress changes the data file location of the tablespace 1. alter tablespace MDB_DATA offline; 2. alter database rename file '/home/oracle/app/oracle11g/dbs/comment' to/home/itiltest/itildbs/oradata/ITILRPDB/homeitiltestitildbsoradataITILRPDBMDB_INDEXSPACE '3. alter tablespace MDB_DATA online; Remarks: The operation is not successful. Looking for the reason to delete the tablespace Drop tablesapce xxx remarks: if there is any content, use Drop tablespace xxx incl Uding contentsWidows file Import C: \ Users> imp mdbadmin/mdbadmin @ orcl file = I: \ reportData \ (d1.dmp, d2.dmp, d3.dmp, d4.dmp, d5.dmp, d6.dmp, d7.dmp, tables, tables, d10.dmp, d11.dmp, d12.dmp, d13.dmp, d14.dmp, d15.dmp) tables = ca_contact ignore = y impdp mdbadmin/password @ mdb directory = dump_data dumpfile = d1.dmp, d2.dmp, d3.dmp, d4.dmp, d5.dmp, d6.dmp, d7.dmp, d8.dmp, d9.dmp, d10.dmp, d11.dmp, d12.dmp, d13.dmp, d14.dmp, d15.d Mp full = File Import in yUnix I: \ reportData> impdp mdbadmin/mdbadmin @ orcl directory = dump_report dumpfile = d1.dmp, d2.dmp, d3.dmp, d4.dmp, d5.dmp, d6.dmp, d7.dmp, d8.dmp, d9.dmp, d10.dmp, d11.dmp, d12.dmp, d13.dmp, d14.dmp, d15.dmp tables = usp_contact first create directory path Remarks: This path is the relative path of the specified dumpfile 1. create directory dump_data as '/home/itiltest/data'; 2. select * from dba_directories3.grant read, write on directory dump_data to mdbadmin; Because full database migration uses expdp to export the entire database, that is, specifying full = y. However, when importing data on other machines, the expdp full Database Export has built-in tablespace creation scripts, the new machine drive letter is different from the original one, so the tablespace cannot be created. How can I specify the tablespace location before the import. For example, if it turns out to be "I: \ oracle \ product \ test" and the new machine does not have an I disk, how to manually specify "E: \ oracle "folder impdp scott/tiger FULL = y directory = dumpo dumpfile = full. dmp REMAP_DATAFILE = 'I: \ oracle \ product \ test. dbf': 'e: \ oracle \ product \ test. dbf' solves the problem where the clob field cannot be retrieved directly using dblink. incorrect name: dblink cannot use the lob positioner selected from the remote table. zhangxuegang was modified at, December 15,. Solution: 1. you can Create a temporary table, clone the remote blob field table to the temporary table, and run the link operation code: Create global temporary table qtggxx_temp as select titie, Content, create_date, ref_id from qtggxx @ test_link; 2. create or replace view qtggxx as select "title", (select p. cotent from qtggxx_temp p where p. ref_id = ref_id) as content, "create_date", "ref_id", "source_ref_url ", "bm_id" from qtggxx @ test_link only imports the table structure expdb u1/u1 @ orcl directory = xxx content = metadata_only dumpfile = xxx. dmp schemas = xx logfule logs u1.logfile import large and small proportion dmpfile --dmp.gz compression ratio is 1-5. Size of physical storage space after 3dmp is imported into the database -- the physical storage space occupied by the table data becomes smaller after the import (to be verified) select segment_name, segment_type, bytes/1024/1024 from user_segments where segment_name = 'cognos _ CHG_T '; before importing data to Aix, set the environment export ORACLE_HOME =/home/oracle/app/oracle11g/export ORACLE_SID = itilrpdbexport ORACLE_BASE =/home/itiltest/itildbsexport PATH = $ ORACLE_HOME/bin: $ PATHexport PATH =/usr/bin:/etc:/usr/sbin:/usr/ucb:/usr/bin/X11:/sbin: /usr/java5/jre/bin:/usr/java5/bi N:/home/oracle/app/oracle11g/bin Delete the user and delete the process 1. drop user mdbadmin cascade; 2. select username, sid, serial # from v $ session3.alter system kill session '31, 17 'dblink create database link ITILDB_LINK connect to mdbadmin identified by "ca1234" using' (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = (PORT = 1522) (CONNECT_DATA = (SERVICE_NAME = mdb) '; Views oracle memory usage s Elect. sid, B. name,. value from v $ sesstat a, v $ statname bwhere (B. name like '% uga %' or B. name like '% pga %') and. statistic # = B. statistic # create or replace function "DATE_TO_SEC" (strDate in char) of the order by sid FUNCTION -- convert the DATE type to integer second return intas ret NUMBER; iDate DATE; tDate varchar2 (30); BEGIN if (length (strDate)> = 18 then tDate: = 'yyyy-MM-DD HH24: MI: ss'; elsif (length (strDate)> 7 then tDate: = 'yyyy-MM-DD '; Else tDate: = 'yyyy-mm'; end if; iDate: = TO_DATE (strDate, tDate); ret: = (iDate-TO_DATE ('2017-1-1 ', 'yyyy-MM-DD HH24: MI: ss') * (24*60*60); RETURN ret; END; create materialized view COGNOS_AL_CJS_MVrefresh complete on demandasselect distinct w. object_id change ID, sec_to_date (t. open_date) time, get_per_site_name (w. ASSIGNEE) Unit, GET_PER_DEPT_NAME (w. ASSIGNEE) department, GET_analyst (w. ASSIGNEE) handled by ID, GET_per Son (w. ASSIGNEE) handled by get_person (w. group_id) operator group, w. assignee id, t. status type from chg t, wf w where t. id = w. object_id and t. create or replace view cognos_fissue_v asselect t. parent parent_id, t. ref_num task no., t. z_string7 Task Name, (select last_name from ca_contact where contact_uuid = t. z_string56) task owner, t. phone number of the person in charge of z_string15, t. z_string8 Function Requirement number, (select name from ca_site where id = t. z _ Site1) functional requirements department, t. z_string10 main business function description, t. status, sec_to_date (t. open_date) ticket creation time, sec_to_date (t. close_date) close time, t. z_string38 function development director from issue t where t. category = 'chg993' check the size of the TABLESPACE created by a user. SELECT DBMS_METADATA.GET_DDL ('tablespace', TS. tablespace_name) FROM DBA_TABLESPACES TS view the number of columns in a table select count (*) from dba_tab_columns where owner = ''and table_name =''; select count (1) from user_col_commentswhere Table_name = upper ('xs _ rxb '); expand the size of the tablespace in use. Oracle Character Set setting client & server: Execute the stored procedure in Oracle. An error is returned when a table is created: grant create any table to mdbadmin; oracle determines whether the table exists. If so, delete declare num number; begin select count (1) into num from all_tables where TABLE_NAME = 'emp' and OWNER = 'Scott '; if num = 1 then execute immediate 'drop table emp'; end if; end;/compare user objects between two databases for static and unified view of job running conditions select job, last_date, Last_sec, next_date, next_sec, broken, failures, what from dba_jobs where log_user = 'mdbadmin'; view scheduling tasks: select JOB, WHAT, interval from dba_jobs where log_user = 'mdbadmin '; view the number of job queue processes: select name, value from v $ parameter where name = 'job _ queue_processes '; oracle client cannot be properly installed, prompting a memory issue. net share c $ = c: pl/SQL display garbled solutions when PLsql developer is used, Chinese storage is encountered, especially when the table field comment is Chinese, it is often displayed garbled. Find the following solution: 1. check server encoding: Execute SQL Syntax: select * from v $ nls_parameters; if not Chinese, execute 2. set local client encoding: enter my computer, properties, advanced, environment variables, add two items: LANG = zh_CN.GBK and NLS_LANG = "SIMPLIFIED CHINESE_CHINA.ZHS16GBK" 3. set and reconnect PL/SQL Developer: In the menu of pl/SQL developer-> tools-> preferences-> user interface-> fonts, modify it to the Chinese font. Select tablespace_name, file_name, bytes/1024/1024 file_size, autoextensible from dba_temp_files; select * from database_properties where property_name = 'default _ TEMP_TABLESPACE '; view the SQLselect s of the locked process. sid, q. SQL _textfrom v $ session s, v $ SQL qwhere s. SQL _id = q. SQL _idand s. sid in (select session_id from v $ locked_object) view the jobselect * from DBA_JOBS_RUNNINGoracle junk data cleanup delete from cognos_chg_t where rowid in (select rid from (select rowid rid, row_number () over (partition by chg_ref_num order by last_mod_dt desc) rn from cognos_chg_t) where rn> 1) You have used Microsoft OneNote 2010 to create a location for storing all notes and information

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.