Sql_Trace File Identifier 我們?cè)O(shè)置了sql_trace為true之后，trace file會(huì)dump到udump中，但是該目錄下也許已經(jīng)有很多trace file了，或者其他session也設(shè)置了sql_trace為true，也在產(chǎn)生trace file。如何能迅速找到你的session產(chǎn)生的trace file呢？我們可以通過(guò)如下語(yǔ)句設(shè)置Sql_Trace File Identifier：
這樣你的session產(chǎn)生的trace file就會(huì)以設(shè)置的identifier為后綴。
然后我們可以看到udump下生成的trace file文件名為：test_ora_4168_test_identifier.trc Sample Block 抽樣表掃描（sample table scan）只讀取一個(gè)表的部分?jǐn)?shù)據(jù)。其實(shí)我們只選擇表的部分?jǐn)?shù)據(jù)的話，可以用rownum來(lái)限制也很方便。但是考慮如下情況： A表和B表join，我只想A表的部分?jǐn)?shù)據(jù)去連接B表，此時(shí)用sample就方便的多。如果用rownum就需要用一個(gè)subquery。
抽樣表掃描有兩種形式，一種是抽樣表中行數(shù)的一個(gè)百分比，一個(gè)是抽樣表中塊數(shù)的一個(gè)百分比。注意都是百分比。語(yǔ)法如下：
也可以在視圖上使用SAMPLE。
今天google分析函數(shù)row_number()的時(shí)候發(fā)現(xiàn)的，覺(jué)得很好： 表demo是重復(fù)拷貝自dba_objects，有88萬(wàn)左右，不重復(fù)的是27323，沒(méi)有索引 方法一：delete from demo a where a.rowid <> (select max(rowid) from demo b where b.object_id=a.object_id); 耗時(shí)：幾個(gè)小時(shí)以上 方法二： delete from demo where rowid in (select rid from (select rowid rid,row_number() over(partition by object_id order by rowid) rn from demo) where rn <> 1 ); 耗時(shí)：30秒 方法三： create table demo2 as select object_id,owner... from (select demo.*,row_number() over(partition by object_id order by rowid) rn from demo) where rn = 1; truncate table demo; insert into demo select * from demo2; drop table demo2; 共耗時(shí)： 10秒，適合大數(shù)據(jù)量的情況，產(chǎn)生更少回滾量； 學(xué)到了分析函數(shù)row_number(),對(duì)于object_id和rowid也有了一些認(rèn)識(shí)。oracle要學(xué)的東西太多了，什么時(shí)候是個(gè)頭啊。上面的方法不是很難理解，但也還沒(méi)有完全理解，有機(jī)會(huì)實(shí)際試試。
Orchadmin is a command line utility provided by datastage to research on data sets.The general callable format is : $orchadmin [options] [descriptor file]1. Before using orchadmin, you should make sure that either the working directory or the $APT_ORCHHOME/etc contains the file “config.apt” ORThe environment variable $APT_CONFIG_FILE should be defined for your session.Orchadmin commandsThe various commands available with orchadmin are1. CHECK: $orchadmin checkValidates the configuration file contents like , accesibility of all nodes defined in the configuration file, scratch disk definitions and accesibility of all the nodes etc. Throws an error when config file is not found or not defined properly2. COPY : $orchadmin copyMakes a complete copy of the datasets of source with new destination descriptor file name. Please not thata. You cannot use UNIX cp command as it justs copies the config file to a new name. The data is not copied.b. The new datasets will be arranged in the form of the config file that is in use but not according to the old confing file that was in use with the source.3. DELETE : $orchadmin <> [-f -x] descriptorfiles….The unix rm utility cannot be used to delete the datasets. The orchadmin delete or rm command should be used to delete one or more persistent data sets.-f options makes a force delete. If some nodes are not accesible then -f forces to delete the dataset partitions from accessible nodes and leave the other partitions in inaccesible nodes as orphans.-x forces to use the current config file to be used while deleting than the one stored in data set.4. DESCRIBE: $orchadmin describe [options] descriptorfile.dsThis is the single most important command.1. Without any option lists the no.of.partitions, no.of.segments, valid segments, and preserve partitioning flag details of the persistent dataset.-c : Print the configuration file that is written in the dataset if any-p: Lists down the partition level information.-f: Lists down the file level information in each partition-e: List down the segment level information .-s: List down the meta-data schema of the information.-v: Lists all segemnts , valid or otherwise-l : Long listing. Equivalent to -f -p -s -v -e5. DUMP: $orchadmin dump [options] descriptorfile.dsThe dump command is used to dump(extract) the records from the dataset.Without any options the dump command lists down all the records starting from first record from first partition till last record in last partition.-delim ‘’ : Uses the given string as delimtor for fields instead of space.-field : Lists only the given field instead of all fields.-name : List all the values preceded by field name and a colon-n numrecs : List only the given number of records per partition.-p period(N) : Lists every Nth record from each partition starting from first record.-skip N: Skip the first N records from each partition.-x : Use the current system configuration file rather than the one stored in dataset.6. TRUNCATE: $orchadmin truncate [options] descriptorfile.dsWithout options deletes all the data(ie Segments) from the dataset.-f: Uses force truncate. Truncate accessible segments and leave the inaccesible ones.-x: Uses current system config file rather than the default one stored in the dataset.-n N: Leaves the first N segments in each partition and truncates the remaining.7. HELP: $orchadmin -help OR $orchadmin -helpHelp manual about the usage of orchadmin or orchadmin commands
當(dāng)一個(gè)Job跑到一半終止了要還原，在DS Director中主要有三種方法來(lái)還原：1 Job-----Reset2 Job-----Clear Status File3 Job-----Cleanup Resource具體細(xì)節(jié)也不懂，有時(shí)間慢慢研究