乘坐2009年11月20日15：08分由北京西始发直达福州Z59次列车，开始我全新的生活，暂别了熙熙攘攘的首都北京，南漂了，告别了宇博信诚（北京）信息技术有限公司，我工作了 SQL> SELECT to_date('2009/11/17','YYYY/MM/DD')-to_date('2007/04/12','YYYY/MM/DD') DAYS FROM DUAL;
2009年11月7日，搭乘国航CA1420由重庆飞北京，标志着我长达半年之久的差旅结束了！是的，5月3日就到重庆农村商业银行客户现场了，半年的出差工作给我自己带来莫大的帮助：技术层面有了进一步的积累和发展，也学习并掌握了一些新技术；业务层面，接触学习了银行金融系统。金融行业在我看来绝对是一个持久不衰的行业啊，尤其是和IT行业纵横交织在一起的时候，也引伸出一个结论：懂金融业务的IT复合型人才绝对是香饽饽！ 
感谢重庆农村商业银行外币改造项目组工作的全体同仁，以及负责项目上线测试的客户业务人员！ 重庆≥美食+美景+美女。
A collection of 19 FAQs on creating and managing tablespaces and data files. Clear answers are provided with tutorial exercises on creating and dropping tablespaces; listing available tablespaces; creating and dropping data files; setting tablespaces and data files offline; removing corrupted data files. Topics included in this FAQ are:
Sample scripts used in this FAQ assumes that you are connected to the server with the SYSTEM user account on the default database instance XE. See other FAQ collections on how to connect to the server.
What Is an Oracle Tablespace?
An Oracle tablespace is a big unit of logical storage in an Oracle database. It is managed and used by the Oracle server to store structures data objects, like tables and indexes.
What Is an Oracle Data File?
An Oracle data file is a big unit of physical storage in the OS file system. One or many Oracle data files are organized together to provide physical storage to a single Oracle tablespace.
How a Tablespace Is Related to Data Files?
Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.
How a Database Is Related to Tablespaces?
A database’s data is collectively stored in the datafiles that constitute each tablespace of the database. For example, the simplest Oracle database would have one tablespace and one datafile. Another database can have three tablespaces, each consisting of two datafiles (for a total of six datafiles).
How To View the Tablespaces in the Current Database?
If you want to get a list of all tablespaces used in the current database instance, you can use the DBA_TABLESPACES view as shown in the following SQL script example:
SQL> connect SYSTEM/fyicenter Connected. SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS 2 FROM USER_TABLESPACES; TABLESPACE_NAME STATUS CONTENTS ------------------------------ --------- --------- SYSTEM ONLINE PERMANENT UNDO ONLINE UNDO SYSAUX ONLINE PERMANENT TEMP ONLINE TEMPORARY USERS ONLINE PERMANENT
What Are the Predefined Tablespaces in a Database?
When you create a new database, Oracle server will create 4 required tablespaces for the new database:
How To View the Data Files in the Current Database?
If you want to get a list of all tablespaces used in the current database instance, you can use the DBA_TABLESPACES view as shown in the following SQL script example:
SQL> connect SYSTEM/fyicenter Connected. SQL> col tablespace_name format a16; SQL> col file_name format a36; SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES 2 FROM DBA_DATA_FILES; TABLESPACE_NAME FILE_NAME BYTES --------------- ------------------------------- --------- USERS \ORACLEXE\ORADATA\XE\USERS.DBF 104857600 SYSAUX \ORACLEXE\ORADATA\XE\SYSAUX.DBF 461373440 UNDO \ORACLEXE\ORADATA\XE\UNDO.DBF 94371840 SYSTEM \ORACLEXE\ORADATA\XE\SYSTEM.DBF 356515840
How To Create a new Oracle Data File? There is no dedicated statement to create a data file. Data files are created as part of statements that manages other data structures, like tablespace and database. How To Create a New Tablespace? If you want a new dataspace, you can use the CREATE TABLESPACE … DATAFILE statement as shown in the following script:
SQL> CREATE TABLESPACE my_space 2 DATAFILE '/temp/my_space.dbf' SIZE 10M; Tablespace created. SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS 2 FROM USER_TABLESPACES; TABLESPACE_NAME STATUS CONTENTS ---------------- --------------- --------- SYSTEM ONLINE PERMANENT UNDO ONLINE UNDO SYSAUX ONLINE PERMANENT TEMP ONLINE TEMPORARY USERS ONLINE PERMANENT MY_SPACE ONLINE PERMANENT SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES 2 FROM DBA_DATA_FILES; TABLESPACE_NAME FILE_NAME BYTES --------------- -------------------------------- --------- USERS \ORACLEXE\ORADATA\XE\USERS.DBF 104857600 SYSAUX \ORACLEXE\ORADATA\XE\SYSAUX.DBF 461373440 UNDO \ORACLEXE\ORADATA\XE\UNDO.DBF 94371840 SYSTEM \ORACLEXE\ORADATA\XE\SYSTEM.DBF 356515840 MY_SPACE \TEMP\MY_SPACE.DBF 10485760
So one statement created two structures: a tablespace and a data file. If you check your file system with Windows file explorer, you will see the data file is located in the \temp directory of. The data file size is about 10MB. Its contents should be blank and full of \x00 at this time.
How To Rename a Tablespace?
You can easily rename a tablespace by using the ALTER TABLESPACE … RENAME TO statement as shown in the example below:
SQL> CREATE TABLESPACE my_space 2 DATAFILE '/temp/my_space.dbf' SIZE 10M; Tablespace created. SQL> ALTER TABLESPACE my_space RENAME TO your_space; Tablespace created. SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS 2 FROM USER_TABLESPACES; TABLESPACE_NAME STATUS CONTENTS ---------------- --------------- --------- SYSTEM ONLINE PERMANENT UNDO ONLINE UNDO SYSAUX ONLINE PERMANENT TEMP ONLINE TEMPORARY USERS ONLINE PERMANENT YOUR_SPACE ONLINE PERMANENT
How To Drop a Tablespace?
If you have an existing tablespace and you don’t want it anymore. You can delete a tablespace by using the DROP TABLESPACE statement as shown in the example below:
SQL> CREATE TABLESPACE my_space 2 DATAFILE '/temp/my_space.dbf' SIZE 10M; Tablespace created. SQL> DROP TABLESPACE my_space; Tablespace dropped.
What Happens to the Data Files If a Tablespace Is Dropped?
If a tablespace is dropped, what happens to its data files? By default, data files will remain in OS file system, even if the tablespace they are mapped is dropped. Of course, you delete the data files using OS commands, if they are no longer needed.
Another way of deleting data files is to use the INCLUDING clause in the DROP TABLESPACE statement. Here is a SQL sample script:
SQL> CREATE TABLESPACE my_space 2 DATAFILE '/temp/my_space.dbf' SIZE 10M; Tablespace created. SQL> DROP TABLESPACE my_space INCLUDING CONTENTS 2 AND DATAFILES; Tablespace dropped.
With the INCLUDING CONTENTS AND DATAFILES clause, all contents and mapped data files are also deleted.
How To Create a Table in a Specific Tablespace?
After you have created a new tablespace, you can give it to your users for them to create tables in the new tablespace. To create a table in a specific tablespace, you need to use the TABLESPACE clause in the CREATE TABLE statement. Here is a sample script:
SQL> connect SYSTEM/fyicenter
Connected.
SQL> CREATE TABLESPACE my_space
2 DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE my_team TABLESPACE my_space
2 AS SELECT * FROM employees;
Table created.
SQL> SELECT table_name, tablespace_name, num_rows
2 FROM USER_TABLES
3 WHERE tablespace_name in ('USERS', 'MY_SPACE');
TABLE_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ---------------- ----------
MY_TEAM MY_SPACE -
EMPLOYEES USERS 107
...
How To See Free Space of Each Tablespace?
One of the important DBA tasks is to watch the storage usage of all the tablespaces to make sure there are enough free space in each tablespace for database applications to function properly. Free space information can be monitored through the USER_FREE_SPACE view. Each record in USER_FREE_SPACE represents an extent, a contiguous area of space, of free space in a data file of a tablespace.
Here is SQL script example on how to see free space of a tablespace:
SQL> connect HR/fyicenter
Connected.
SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES
2 FROM USER_FREE_SPACE
3 WHERE TABLESPAE_NAME IN ('USERS', 'MY_SPACE');
TABLESPACE_NAME FILE_ID BYTES
------------------------------ ---------- ----------
MY_SPACE 5 10354688
USERS 4 101974016
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
USERS 4 65536
This tells us that:
of 64KB.
How To Bring a Tablespace Offline?
If you want to stop users using a tablespace, you can bring it offline
using the ALTER TABLESPACE … OFFLINE statement as shown in the
following script:
SQL> connect HR/fyicenter Connected. SQL> CREATE TABLESPACE my_space 2 DATAFILE '/temp/my_space.dbf' SIZE 10M; Tablespace created. SQL> ALTER TABLESPACE my_space OFFLINE NORMAL; Tablespace altered.
After bringing a tablespace offline, you can backup or rename the
data file safely.
How To Bring a Tablespace Online?
If you have brought a tablespace offline, now you want to make it
available to users again, you can use the ALTER TABLESPACE … ONLINE
statement as shown in the following script:
SQL> connect HR/fyicenter SQL> CREATE TABLESPACE my_space 2 DATAFILE '/temp/my_space.dbf' SIZE 10M; Tablespace created. SQL> ALTER TABLESPACE my_space OFFLINE NORMAL; Tablespace altered. ... SQL> ALTER TABLESPACE my_space ONLINE; Tablespace altered.
How To Add Another Datafile to a Tablespace?
If you created a tablespace with a data file a month ago, now 80% of
the data file is used, you should add another data file to the
tablespace. This can be done by using the ALTER TABLESPACE … ADD
DATAFILE statement. See the following sample script:
SQL> connect HR/fyicenter
SQL> CREATE TABLESPACE my_space
2 DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.
SQL> ALTER TABLESPACE my_space
2 DATAFILE '/temp/my_space_2.dbf' SIZE 5M;
Tablespace altered.
SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES
2 FROM DBA_DATA_FILES;
TABLESPACE_NAME FILE_NAME BYTES
--------------- --------------------------------- ---------
USERS C:\ORACLEXE\ORADATA\XE\USERS.DBF 104857600
SYSAUX C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF 461373440
UNDO C:\ORACLEXE\ORADATA\XE\UNDO.DBF 94371840
SYSTEM C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF 356515840
MY_SPACE C:\TEMP\MY_SPACE.DBF 10485760
MY_SPACE C:\TEMP\MY_SPACE_2.DBF 5242880
SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES
2 FROM USER_FREE_SPACE
3 WHERE TABLESPAE_NAME IN ('MY_SPACE');
TABLESPACE_NAME FILE_ID BYTES
------------------------------ ---------- ----------
MY_SPACE 6 5177344
MY_SPACE 5 10354688
This script created one tablespace with two data files.
What Happens If You Lost a Data File?
After you shuting down an Oracle database, you accidently deleted a
data file from the operating system. If you try to start the database
again you will get error when Oracle tries to open the database after
mounting the database. The following tutorial examples shows you what
will happen if the data file c:\temp\my_space.dbf is deleted. Oracle
can still start the database instance and mount the database. But it
will fail on openning the database as shown below in a SQL*Plus
session:
>sqlplus /nolog SQL> connect SYSTEM/fyicenter AS SYSDBA SQL> STARTUP ORACLE instance started. Total System Global Area 100663296 bytes Fixed Size 1285956 bytes Variable Size 58720444 bytes Database Buffers 37748736 bytes Redo Buffers 2908160 bytes Database mounted. ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: 'C:\TEMP\MY_SPACE.DBF' SQL> SHUTDOWN; ORA-01109: database not open Database dismounted. ORACLE instance shut down.
How Remove Data Files befor opening a Database?
Let’s say you have a corrupted data file or lost a data file. Oracle
can mount the database. But it will not open the database. What you
can do is to set the bad data file as offline befor opening the
database. The tutorial exercise shows you how to set two data files
offline and open the database without them:
>sqlplus /nolog SQL> connect SYSTEM/fyicenter AS SYSDBA SQL> STARTUP MOUNT; ORACLE instance started. Total System Global Area 100663296 bytes Fixed Size 1285956 bytes Variable Size 58720444 bytes Database Buffers 37748736 bytes Redo Buffers 2908160 bytes Database mounted. SQL> ALTER DATABASE DATAFILE '\temp\my_space.dbf' 2 OFFLINE DROP; Database altered. SQL> ALTER DATABASE DATAFILE '\temp\my_space_2.dbf' 2 OFFLINE DROP; Database altered. SQL> ALTER DATABASE OPEN; Database altered. SQL> col file_name format a36; SQL> col tablespace_name format a16; SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES 2 FROM DBA_DATA_FILES; TABLESPACE_NAME FILE_NAME BYTES --------------- --------------------------------- --------- USERS C:\ORACLEXE\ORADATA\XE\USERS.DBF 104857600 SYSAUX C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF 503316480 UNDO C:\ORACLEXE\ORADATA\XE\UNDO.DBF 94371840 SYSTEM C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF 367001600 MY_SPACE C:\TEMP\MY_SPACE.DBF MY_SPACE C:\TEMP\MY_SPACE_2.DBF
At this point, if you don’t care about the data in MY_SPACE, you can
drop it now with the database opened.
| ~ | tilde (sounds like til-da); be prepared to explain to computer-illiterate people saying "you know, the wave-shaped thingy" |
| ! | exclamation; commonly read as bang in case of #!/bin/sh |
| @ | at |
| # | pound; but commonly read as shee in case of #!/bin/sh, not sure why |
| $ | dollar |
| % | percent |
| ^ | caret; not many people know this word so be prepared to say "no, not carrot; it's the character above 6, an arrow pointing up" |
| & | ampersand |
| * | star; some read asterisk |
| ( | opening parenthesis (some may shorten it saying paren) |
| ) | closing parenthesis |
| _ | underscore; once I heard people say underbar |
| + | plus |
| – | minus; as symbol before arguments in commands, some people including me read dash, easier to say one syllable |
| = | equals |
| ` | backtick or backquote |
| { | opening brace |
| } | closing brace |
| [ | opening bracket |
| ] | closing bracket |
| | | pipe or vertical bar |
| \ | backslash; be prepared to explain to some computer-illiterate people |
| : | colon |
| ; | semicolon |
| " | double quote |
| ' | single quote |
| < | less than; some may read left angle bracket |
| > | greater than |
| , | comma |
| . | dot; period if in English text |
| ? | question mark |
| / | slash or forward slash; some computer-illiterate people may be confused about / and \ |
| space | |
| (), [] and {} | may also be called brackets in general. In that case, they specifically call [] square brackets and {} curly brackets. I never like this. Open and Closing may also be called left and right. |
—–Original From http://www.anysql.net/aulmydul/aul_version_history.html
Oracle是什么? 是一个管理数据的软件系统, 很重要, 和结婚前的恋人差不多, 此篇献给不懂Oracle的本站读者, 很早时写过一两篇的, 但站点被黑因此全丢了.
Oracle Standby, 有一颗红心, 两种或多种准备, 万一一个恋爱谈崩了, 马上移到下一个. 从Primary切到Standby一般是不太愿意的, 放弃第一个而取第二个, 同样也是不太愿意的. 备用的一开始不需要很细心的照顾, 稍微留点心, 就可以保持了, 出问题时努力一下就成了Primary了. 如果出了问题就得重新建立了.
Oracle Dataguard, 其实和Standby一样, 只是关系更密切一些, 需要投入更多一点的资源, 需要切换成Primary时更省力一些而已, 但还是有主从之分的.
Oracle RAC, 这就比较花心了, 可以说成脚踩两只或多只船, 可能分不清楚主从关系. 所需要代价也是比较高的, 所以有钱的人才上RAC, 中国特别多, 政府部门大都喜欢上RAC系统. 但不容易管理, 常出问题, 上RAC系统比较能满足心里要求. 加减节点同样是件非常不容易的事.
Oracle Tuning, 调优就是让人开心了. 一个节点时只调一边就行了. RAC时可能需要调所有的节点, 还常关系到资源的分配问题, 经常是因为资源问题让整个RAC系统跑不起来的, 现实生活中更是如此了. 而Dataguard和Standby上的调优则比较简单, 没有多少可以调的地方. 调优的关键是要知道问题所在, 让人开心的关键也是知道对方需要什么, 并不是增加的资源越多, 系统就越好的.
Oracle Recovery, 就是修复破损关系了, 有时会遇到不能修复的情况, 就只能放弃选择重建了.
如果你的对方懂Oracle, 可以适当用用这些术语了.
—–Derived From http://www.anysql.net/life/oracle_before_marry.html