这里有一个示例, 自己在 SQL*Plus 或者SQL Developer里面跑一下吧, select DEFAULT_TABLESPACE, translate(wmsys.wm_concat(username),',','|') from dba_users group by DEFAULT_TABLESPACE;
3) FBI index, virtual column index and SHRINK clause
有个听众提个问题, 说在10.2以下版本, 有Function Based Index的表不能做空间回收-Shrink. Dan Morgan这位老大自己没测试过, 随口就说11g上,在一个表的虚拟列上的构建索引,这张表可以Shrink, 岂不是犯了和 老旦一样的错误. (老旦:Dan. 你们都知道是谁, 曾被老刘 Lewis 严肃的教育过, 以后有 另外一篇文章评论,关于PGA 和 Parallel execution)
第二天到办公室一测试, 发现11.1也不行.
以下是测试用例:
--drop table scott.y1; create table scott.y1(sal number, comm number);
drop index scott.yi_fbi1; create index scott.yi_fbi1 on scott.y1(sal + comm) --tablespace data_auto ;
alter table scott.y1 enable row movement;
alter TABLE scott.y1 shrink space compact; alter TABLE scott.y1 shrink space;
ERROR at line 1: ORA-10631: SHRINK clause should not be specified for this object
drop index scott.yi_fbi1;
alter table scott.y1 add (income AS (sal + comm));
drop index scott.yi_vi1; create index scott.yi_vi1 on scott.y1(income);
alter TABLE scott.y1 shrink space;
ERROR at line 1: ORA-10631: SHRINK clause should not be specified for this object
US Withholding for Canadian Independent Contractors
Using Form W-8BEN to Claim US-Canada Tax Treaty Benefits American companies generally withhold income taxes on income being paid to foreign nationals. You may qualify for reduced withholding if meet some rules. Basically, there are three steps to this process. First, you must clarify in which country you are a resident. Second, you must decide where your "fixed place of business" is located. Third, you must notify your clients of your tax status using Form W-8BEN. Withholding The tax treaty specifically allows for US companies to withhold income taxes on self-employed Canadian residents (Article XVII, paragraph 1). Withholding will be 10% on the first $5,000 of income, and 30% on income over that threshold. The client and independent contractor may agree on a lesser percentage of withholding if these amounts are considered "excessive" (Article XVII, paragraph 2). Normally, US companies are required to "withhold 30% of any payment of an amount subject to withholding made to a payee that is a foreign person" (Instructions for Form W-8BEN). Form W-8BEN is used to inform the US company that you are "a beneficial owner that is a foreign person entitled to a reduced rate of withholding." You qualify for a reduced rate of withholding if you meet the residency and fixed place of business rules Filling out Form W-8BEN Provide your name in Line 1 and check "individual" in Line 3. However, if you are working under a business name, provide your business name in Line 1 and check the appropriate type of entity in Line 3. See: http://taxes.about.com/od/taxplanning/qt/form_W8BEN.htm
Claiming Tax Treaty Benefits Exemption From Withholding If a tax treaty between the United States and your country provides an exemption from, or a reduced rate of, withholding for certain items of income, you should notify the payor of the income (the withholding agent) of your foreign status to claim the benefits of the treaty. Generally, you do this by filing Form W-8BEN, Certificate of Foreign Status of Beneficial Owner for United States Tax Withholding with the withholding agent.
Rules that Apply to Compensation for Personal Services Independent contractors. If you perform personal services as an independent contractor (rather than an employee) and you can claim an exemption from withholding on that personal service income because of a tax treaty, submit Form 8233 to each withholding agent from whom amounts will be received. See: http://www.irs.gov/businesses/small/international/article/0,,id=96438,00.html
Instructions for the Withholding Agent
Requirement To Withhold A withholding agent must withhold 30% of any payment of an amount subject to withholding made to a payee that is a foreign person unless it can associate the payment with documentation (for example, Form W-8 or Form W-9) … Responsibilities of the Withholding Agent If you are a withholding agent making a payment of U.S. source interest, dividends, rents, royalties, commissions, nonemployee compensation, other fixed or determinable annual or periodical gains, profits, or income, and certain other amounts (including broker and barter exchange transactions, and certain payments made by fishing boat operators), you are generally required to obtain from the payee either a Form W-9, Request for Taxpayer Identification Number and Certification, or a Form W-8. These forms are also used to establish a person's status for purposes of domestic information reporting (for example, on a Form 1099) and backup withholding. If you receive a Form W-9, you must generally make an information return on a Form 1099. If you receive a Form W-8, you are exempt from reporting on Form 1099, but you may have to file Form 1042-S and withhold under the rules applicable to payments made to foreign persons. See the Instructions for Form 1042-S for more information. Generally, you must withhold 30% from the gross amount paid to a foreign person unless you can reliably associate the payment with a Form W-8. You can reliably associate a payment with a Form W-8 if you hold a valid form, you can reliably determine how much of the payment relates to the form, and you have no actual knowledge or reason to know that any of the information or certifications on the form are unreliable or incorrect. Do not send Forms W-8 to the IRS. Instead, keep the forms in your records for as long as they may be relevant to the determination of your tax liability under section 1461. Use the information on Forms W-8 to prepare Forms 1042-S. See: http://www.irs.gov/instructions/iw8/ch01.html
We recommend going to AL32UTF8 as the ultimate solution for Oracle 11g-. AL32UTF8 is the database character set that supports the latest version (5.0 in Oracle 11.1) of the Unicode standard. It also provides support for the newly defined supplementary characters.
Here are some major points I briefed as a reference.
How to move to AL32UTF8 / UTF8 (Unicode) Database Character Set Note:119119.1
to check you database Character Set, select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';
Usualy database will grow when going to AL32UTF8, use CSSCAN to generate the size expansion report.
The NLS_LENGTH_SEMANTICS initialization parameter determines whether a new column of character datatype uses byte or character semantics. The default value of the parameter is BYTE. The BYTE and CHAR qualifiers shown in the VARCHAR2 definitions should be avoided when possible because they lead to mixed-semantics databases. Instead, set NLS_LENGTH_SEMANTICS in the initialization parameter file and define column datatypes to use the default semantics based on the value of NLS_LENGTH_SEMANTICS.
columne_name VarChar2(300 char/byte)
Related function: lengthb(), substrb()
UniStr() over Chr() select Chr(163) from dual; select UniStr('\C2A3') from dual;
convert(string_column,'AL32UTF8','US7ASCII'), convert from US7ASCII to AL32UTF8.
To use WE8MSWIN1252 over WE8ISO8559P1, WE8MSWIN1252 supports European Code.
Reference
* US7ASCII: US 7-bit ASCII character set * WE8ISO8859P1: ISO 8859-1 West European 8-bit character set * WE8MSWIN1252: Microsoft Windows West European Code Page 1252 * UTF8: Unicode 3.0 Universal character set CESU-8 encoding form * AL32UTF8: Unicode 5.0 Universal character set UTF-8 encoding form
**Unicode character sets in the Oracle database, Note:260893.1
exp/imp
set NLS_LANG= export
set NLS_LANG= import into the new UTF8 db.
The conversion to UTF8 is done while inserting the data in the UTF8 database.
下面看看 System Architect 的定义, 还有我喜欢的职位 - Database Designer
System Architect Definition The system architect has the task of putting together the skeleton of a software project.
Depending on the specifications gathered by the requirements analyst, the system architect will choose to focus on ease of maintenance, application performance, compatibility with existing systems, or a combination of all three. Each decision that the system architect makes has to be carefully considered because a wrong move the beginning of a project can have damaging effects later in the software evelopment life cycle.
Database Designer Definition Most software projects boil down to information storage and retrieval. Deciding how and where this information is stored is the domain of the database designer. Working with a system architect and a requirements analyst, the database designer ensures that all necessary data has a place to be stored. At the same time, the speed at which the data can be stored and retrieved are taken in to account so that user's are not left waiting for unreasonable amounts of time.
Sometimes database designers take on database related activities such as arranging for backups, creating ad-hoc reports, and server tuning. However, these other tasks are often part of a Database Administrator's (DBA) job.
还谈到了 Messaging Queue, Enterprise Service Bus, Coherence ( like TimesTen, 网格内存数据库, 刚刚被Oracle并购, 比TimesTen有更好的分布扩充性)
o SOA o Recent database issues with connection pool usage and Oracle 10g bugs o Data Replication/Data Warehousing/Data Archiving within Oracle -- 竟然又开始推销 Quest SharePlex -- 而我强烈坚持升级Oracle 11g, 启用Active Standby database. o Site Separation model - App server(s) - Oracle Partitioning
IT Architecture[Operation Driven] o Best Practices in implementing Operations into Product Development process flow o Config Management[Build, Packaging, Deployment, Promotion Model]
*********************************************** Youtube: part 1 http://www.youtube.com/watch?v=YoW2WYdOsvg (English) part 2 http://www.youtube.com/watch?v=iqH47MIpuoA
part 1 http://youtube.com/watch?v=TgENINXPrAM (Chinese) part 2 http://www.youtube.com/watch?v=mwdzxTMFUl4 网站：www.RapeofNanking.info 捐款：http://rapeofnanking.info/Donate.html ***********************************************
When to use Hibernate DAL • One step SQL (SELECT OR UPDATE) to finish your goal • Heavy and forced interaction in mid tier, like CCBB encryption, that means high coulping too, and hard to encapsulate the process logic.
Jave developer can help us on this list.
When to use Database API and Store Procedure Package • Complex SQL For example, book delete check, reference Single Hash Table and list of IDs. • Many (more than 2) steps data machinations logic in a sigle module • Reture rows more than one page, let's say 20 rows from database.
We like Database API approach, because it: 1.. make software components modular, I'm totally into modular programming 2.. software modules must carry out a very specific task (and be very efficient at carrying it out) 3.. each software module should be loosly coupled (to limit dependencies) 4.. It removes the need for triggers as all inserts, updates and deletes are wrapped in APIs. Instead of writing triggers you simply add the code into the API. I loath triggers. 5.. It prevents people who don't understand SQL writing stupid queries. All SQL would be written by PL/SQL developers or DBAs, reducing the likelyhood of dodgy queries. 6.. The underlying structure of the database is hidden from the users, so I can make structural changes without client applications being changed.The API implementation can be altered and tuned without affecting the client application. 7.. The same APIs are available to all applications that access the database. No duplication of effort.
Anything that generates SQL on-the-fly worries me, not just Java. I want to be able to cut and paste the SQL, not try and capture or trace it during a run.
Our concept is "build the data API in the database, you call the data API" . Database API has been layered by different UI technologies over time.
All about API's. The applications don't do table level(select/insert/update/delete) stuff, the apps don't even really know about tables.
On top of database schema we have an API that does the data stuff.
In the application we call this API but have our own application logic as well (only application logic is in the application, data logic is – well, right where it belongs – next to the data, waiting for the 'next great programming paradigm to come along')
The fact that our UI is in Java isn't really relevant. You could pretty much see how you would use this package from C#, Java/JSP, Swing, VB, Pro*C, Forms, Powerbuilder, a mobile phone, .
– Storing application code in the database has had it's champions and detractors.
When you start putting application code in the database, you are in the thoroughly non-portable arena. That code, were you to port your application to another database, would have to be rewritten. But think about your application mid-tier layer, will you port Java code to C#/.Net,-> Pascal -> C -> VB, then to Python, Ruby on Rail ...etc., will you do that?
Its very specificity to that database means it can also take advantage of, and wire close to that engine. There are situations where stored code in the database can be notably faster. Supposed you have to update some chunk of a million rows after doing some machinations on the data.
In a stored procedure the data is read, manipulated, and updated in one step. Meanwhile if you did the same in your middle tier application code, you would have to send that data set over the network, do your manipulations, and send it back. Not only would it make this one task slower, but other transactions vying for that same data could potentially have to wait while that data is in transit, and being manipulated. Also, stored code can serve to encapsulate specific requests which can be invaluable at simplifying your overall application. All three databases support stored procedures and functions. Oracle also supports packages, or collections of stored procedures as well as various object oriented features, which almost no one ever uses. An additional note, a database engine actually context switches between stored code, and the SQL code embedded therein. As since of 9i, Oracle introduced bulk binding, so you can do work on large sets of rows, and update them all in one go, instead of each loop iteration. This feature can even further improve performance quite dramatically.
Document the SQL access database
We'll build a small application for this target, on Oracle APEX (HTML DB). then QA (and Developer) need to record all the SQL access db in the new developing applications. * SQL execution path * SQL performance statistics
Grow That DBA Career Square one - How to be an expert. Salary center: A typical Database Administrator working in British Columbia -- Victoria earns a median total cash compensation of $96,509, according to our analysis of data reported by corporate HR departments. Half of the people in this job earn between $82,461 and $105,361, from http://salary.monster.ca/ -- Should I Become A DBA? How do I get my first DBA job? You’ve landed your first job! Junior DBA Roadmap Junior to Intermediate DBA Intermediate to Senior DBA -- Attitude and skill set for future Approach to design database application Reactive Tuning Q & A
欢迎各位IT老大(大佬)前来, 有钱的捧个前场, 没钱的捧个人场.
Time: 3:00-5:00pm 15-Sep-2007 Location: 2nd Flr meeting room Emmanuel Baptist Church (Victoria Evangelical Chinese Bible Church) 2121 Cedar Hill Cross Rd. 就在UVic维多利亚大学南门, 穿过马路西南角.
顺便看一下今天的星相,还是积极乐观的,吻合了我的心愿. You are an inventor, ready to change routines and improve the ordinary with slight but brilliant tweaks and modifications. That optimistic, hopeful spirit encourages and amazes those around you.
Posts
