Database migration command memo

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 = 128.64.96.76) (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