Oracle tablespace creation and deletion, OMF, partition table Creation

Now there is a table (MT) with at least million data every day, and we plan to build a partition table in the form of monthly partitions. When partitioning, "Let Oracle run faster 2" said p21:

SQL>alter system set db_create_file_dest='F:\oradata\orclyxkj\tbs_test_t_mt_2012';

SQL>create tablespace ts_mt_2012_1 datafile size 50M autoextend on;

The first sentence means to set a data file storage path for Oracle to automatically create a file under this path and name the data file in Oracle's own way. Indeed, after executing the above two sentences, it is as follows:

It can be found that Oracle has created two folders, orclyxkj \ datafile, and created them. the DBF data file is not the ts_mt_2012_1.dbf file I wrote. However, the size of the file created by Oracle is indeed 50 MB, indicating that it is the data file of the tablespace. Why? Because:

Alter system set db_create_file_dest = 'f: \ oradata \ orclyxkj \ tbs_test_t_mt_2012 ';

This statement means that Oracle's OMF is enabled, that is, Oracle's Automatic File Management. Therefore, all file names are automatically completed by Oracle. Generally, OMF is not enabled for a single database instance.

To enable OMF, another method is to create a database:

When creating a database, select 3rd items. This is the OMF management method. Oracle automatically manages the data file name, so it is not your name. Select 2nd items.

 

Then, I saw the file and directory, not the regular names I expected, and I didn't want to create the tablespace in this way, so I decided to delete the tablespace that I just created (some statements directly Delete the tablespace and data files), but I didn't delete the statements at this time, but directly in PL/SQL, find ts_mt_2012_1 in the tablespace folder, and then press the reverse key to delete it.

After the file is deleted, go to the 'f: \ oradata \ orclyxkj \ tbs_test_t_mt_2012 'directory and find that the file created by Oracle is still there. I am afraid to delete it manually, the data cannot be opened because it is inconsistent with the data file after deletion. In fact, this problem does not exist at this time, because the tablespace has been deleted. The database cannot be started unless the tablespace is not deleted and the data file is not offline or deleted.
The problem has been described. Conclusion:

1. Create a tablespace to the specified directory:

SQL> Create tablespace wfcrmdb datafile 'e:/tools/Oracle/oradata/wfcrm/wfcrmdb. DBF 'size 100 m autoextend on next 50 m maxsize unlimited; -- disable automatic table space expansion because automatic expansion may cause database downtime without warning. Solution: see the "partition table" below"

Interpolation:
-- When creating a new tablespace, the data file extension is ora, but the system tablespace data file is DBF, and some existing tablespace data files are DAT files, is there any difference?

-- The file type is identified by the extension. For data files, either ora, dat, or DBF is the same and there is no difference. Generally, when a database adds a datafile or something, it will give you a default suffix, which is based on the description on the second floor. In fact, there is no error, and there is no impact, if you really want to strictly demand yourself, rename is a big deal. The extension is just a standard file name. There are no strict requirements, but it is best to standardize object naming in a good application system.
. DBF-data file,. tmp-temporary file,. Log-redo log file,. CTL-Control File
. Ora-parameter file,. dat-Oracle System File
Q:

Which statement is better for creating a tablespace:
1.
Create smallfile tablespace gzxm datafile 'e: \ oracle \ product \ 10.2.0 \ oradata \ gzxm'
Size 512 M autoextend on
Next 128 M maxsize
Unlimited logging extent management local segment space management auto;

2.
SQL> Create tablespace wfcrmdb datafile 'e:/tools/Oracle/oradata/wfcrm/wfcrmdb. dat 'size 100 m autoextend on next 50 m maxsize unlimited;

A:

Not good

Create smallfile tablespace gzxm datafile 'e: \ oracle \ product \ 10.2.0 \ oradata \ gzxm'
Size 512 M autoextend off
Disable automatic extension of data files. If it is enabled, if your system encounters a problem and a large amount of data is inserted, the tablespace will pop up, resulting in database downtime.
Q: I want to enable it. The data growth is not very stable now. The data volume may suddenly increase to several major accounts, and the probability is very high ~
A:

This requires design and automatic scaling is not conducive to post-Maintenance
First, estimate how much data is there.
For example, the tablespace is 100 GB.
Each file can be set to 4 GB, with a total of 25 files
You can add more files later.
The advantage is that if a data file breaks down, the database can be reduced to a minimum loss.
Q: Let's look at the table I want to partition. In the future, it will be 300 million yuan a day (unstable), and 9000 yuan a month. I plan to partition by month. A total of 12 partitions are 12 tablespaces, each of which is 9000 mb. If it is not extended, the year is 105 GB.
If it is expanded, I will only give the size of 50 MB for each tablespace, and the subsequent data will automatically grow. After the growth in this month, it will go to the next tablespace of 50 MB that has been created, in this way, some tablespaces do not reach MB in a month, and the space is saved. If they exceed MB in a month, they will automatically increase. In this way, their space can be complementary, and I don't have to worry about a sudden insufficient tablespace location. A:

 

2. Delete the tablespace and its physical data files.

Drop tablespace XXX including contents and datafiles; -- delete a space with a file

You do not need to execute alter database datafile '...' offline drop; this statement

If the tablespace is deleted and the data file is still in progress, you can manually delete the file under the directory.
 

How to check whether OMF is enabled/disabled and modify it:

View

select * from v$parameter where name like '%db_create%'

Close

alter system set db_create_file_dest='';

If no value is set, it is disabled. If yes, it is enabled.

 

Partition Table

Mt table (300 million per day, at least 9 GB per month. After an insert operation is performed on several columns at a time, this table is only used for statistics. mx table (300 million + per day, after one insert operation, multiple select and update operations ), mt table partition policy:

1. We recommend that you consider partitions when the size of a table exceeds 2 GB. Some people also say that partitions can be used only in two cases and should be used with caution. I prefer partitioning.

Advantages of Table Partitioning:

Partitioned Tables are easy to maintain and flexible. For example, if you want to delete the data in a certain month partition, you can use the truncate partition. For normal tables, you must use Delete, which is too resource-consuming.

Creating a partition table is a large number of tables. To facilitate management, it has nothing to do with tablespaces.

2. Before partitioning, design the tablespace and the size of each data file used by the tablespace. Automatic scaling is not conducive to post-maintenance. First, estimate the amount of data. For example, if the tablespace is 100 GB, you can set 4 GB for each data file, with a total of 25 files. In the future, you can add more files. The advantage is that if a data file breaks down, the database can be reduced to a minimum loss.

Disadvantages of automatic tablespace expansion:

For example, if an error occurs in the system that inserts data into your table, the system inserts data into your table. Your tablespace keeps Automatically increasing. For example, when your disk space is 500 GB, the database goes down and your single data volume is 300 GB.

What do you do? Everything is possible. Don't be lucky. This is a question that must be considered when designing a database. You can control your own database system, and you cannot control others' systems.

In addition, a single data file cannot be too large:

Windows
The maximum size of a single fat16 file system cannot exceed 2 GB
A single file system of FAT32 cannot exceed 4 GB
The maximum size of a single file in the nfts file system is 2 TB.
Linux
Ext2 and ext3 file systems. Ideally, a single file can support 2 TB

Now the MT table creation method is as follows:

1. The MT table is 300 W a day, 9000 w a month, nearly 9g, and 108g a year. This is the most conservative estimate. My hard disk now has 135 gb ntfs on drive F, so I put the table space tbs_mt_2012 dedicated to the MT table into this disk, and physically assigned 33 data files to the table space tbs_mt_2012, 4 GB for each file, 132 GB in total (Note: After a data file is created, it cannot be deleted. Data is evenly distributed to each data file .)

Q: I manage only the size of the tablespace.
A: Yes. There are two ways to increase the tablespace size:
1. Add data files
2. Or resize the data file, which is added or removed on the original basis and cannot be deleted.

Q: When resize a data file, you need to know which data file is resize. How do I know which data file should I resize when data is evenly distributed to each data file?
A: You can add or remove any file. Note: If the number is reduced, it depends on whether all the file space is occupied. If not, it can be reduced.
In the operating system, viewing the size of each file is the size of the file you created, so you can only view the usage of the table space to determine the usage of the data file:

Simple SQL (view the idle rate of a tablespace ):

SQL> select tablespace_name, sum (Bytes/1024/1024) MB from dba_free_space group by tablespace_name; -- the table space idle rate is used here.
Tablespace_name MB ------------------------------ ---------- undotbs1 5571.4375 sysaux 5.9375 users 1.8125ydsoft _ base 1181.5625 system 4.6875 example 22.3125ydsoft _ base_index 255.9375

Complex and comprehensive:

SQL> select DF. tablespace_name "tablespace name", totalspace "total space M", freespace "residual space M", round (1-freespace/totalspace) *, 2) "usage %" 2 from 3 (select tablespace_name, round (sum (bytes)/1024/1024) totalspace 4 from dba_data_files 5 group by tablespace_name) DF, 6 (select tablespace_name, round (sum (bytes)/1024/1024) freespace 7 from dba_free_space 8 group by tablespace_name) FS 9 where DF. tablespace_name = FS. tablespace_name; tablespace name total space M remaining space M usage % bytes ---------- undotbs1 5670 5571 1.75 440 sysaux 98.64 6 5120 Users 5 2 60ydsoft_base 1182 76.91 500 system 100 5 99 example 256 22 78ydsoft_base_index 256 07 rows selected

Among them, My undo tablespace undotbs1 is very large for others.

Q: How is my undo tablespace so large?

A: Don't worry. Even if 99% is okay, you will release it yourself. Since the establishment of undo, it generally only increases and does not decrease. Therefore, the data files in the Undo tablespace cannot be automatically expanded!
For example, assume that the tablespace undo 4G

Delete A 4g AA table without commit

At this time, the Undo will allocate 4 GB space to store the AA table. If you want to delete the 4 gb bb table, it will stop there because there is no undo free space. In this case, the space of the commit AA table is free and the BB table is deleted smoothly. Otherwise, the BB table remains waiting for deletion.

 

Partition implementation:

Create a tablespace

Data Table space tbs2012 target size 200 GB each data file 4 GB, a total of 50 files
Index tablespace tbs2012_idx

The partition table TBL is created on the table space tbs2012, and 12 partitions are created based on the month. The partitions are also created on the tbs2012 space.

The index of the partition table is created on the tbs2012_idx tablespace.

If you have any operations, you 'd better create an index and create a partition index.

It is costly to perform a full table scan.

To sum up, create a GB tablespace tbs_mt_2012 and assign 34 data files to the tablespace (manually created). Then, the tablespace data is evenly distributed among the 34 data files. The MT table Creates 12 partitions in tbs_mt_2012 based on the monthly partition method. You can manage these data files on your own later. When the tablespace is not enough, you can manually add data files (set the location by yourself). When the tablespace is exceeded, You can resize any of the data files.

Create a tablespace:

create tablespace tableau datafile 'E:\ORACLE\ORADATA\data\tableau01.DBF' size 4096m autoextend off, 'E:\ORACLE\ORADATA\data\tableau02.DBF' size 4096m autoextend off, 'E:\ORACLE\ORADATA\data\tableau03.DBF' size 4096m autoextend off, 'E:\ORACLE\ORADATA\data\tableau04.DBF' size 4096m autoextend off, 'E:\ORACLE\ORADATA\data\tableau05.DBF' size 4096m autoextend off, 'E:\ORACLE\ORADATA\data\tableau06.DBF' size 4096m autoextend off, 'E:\ORACLE\ORADATA\data\tableau07.DBF' size 4096m autoextend off, 'E:\ORACLE\ORADATA\data\tableau08.DBF' size 4096m autoextend off, 'E:\ORACLE\ORADATA\data\tableau09.DBF' size 4096m autoextend off, 'E:\ORACLE\ORADATA\data\tableau10.DBF' size 4096m autoextend off, 'E:\ORACLE\ORADATA\data\tableau11.DBF' size 4096m autoextend off, 'E:\ORACLE\ORADATA\data\tableau12.DBF' size 4096m autoextend off, 'E:\ORACLE\ORADATA\data\tableau13.DBF' size 4096m autoextend off, 'E:\ORACLE\ORADATA\data\tableau14.DBF' size 4096m autoextend off, 'E:\ORACLE\ORADATA\data\tableau16.DBF' size 4096m autoextend off, 'E:\ORACLE\ORADATA\data\tableau17.DBF' size 4096m autoextend off, 'E:\ORACLE\ORADATA\data\tableau18.DBF' size 4096m autoextend off, 'E:\ORACLE\ORADATA\data\tableau19.DBF' size 4096m autoextend off, 'E:\ORACLE\ORADATA\data\tableau20.DBF' size 4096m autoextend off, 'E:\ORACLE\ORADATA\data\tableau21.DBF' size 4096m autoextend off, 'E:\ORACLE\ORADATA\data\tableau22.DBF' size 4096m autoextend off, 'E:\ORACLE\ORADATA\data\tableau23.DBF' size 4096m autoextend off, 'E:\ORACLE\ORADATA\data\tableau24.DBF' size 4096m autoextend off, 'E:\ORACLE\ORADATA\data\tableau25.DBF' size 4096m autoextend off  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Create a partition table (in this case, the specified tablespace for each partition in the partition table is the same ):

create table sale_data(sale_id      number(5), saleman_name varchar2(30), sales_amount number(10), sales_date   date)partition by range (sales_date)(partition sales_2009_1 values less than(to_date('01/02/2009','dd/mm/yyyy')) tablespace mobile,partition sales_2009_2 values less than(to_date('01/03/2009','dd/mm/yyyy')) tablespace mobile,partition sales_2009_3 values less than(to_date('01/04/2009','dd/mm/yyyy')) tablespace mobile,partition sales_2009_4 values less than(to_date('01/05/2009','dd/mm/yyyy')) tablespace mobile,partition sales_2009_5 values less than(to_date('01/06/2009','dd/mm/yyyy')) tablespace mobile,partition sales_2009_6 values less than(to_date('01/07/2009','dd/mm/yyyy')) tablespace mobile,partition sales_2009_7 values less than(to_date('01/08/2009','dd/mm/yyyy')) tablespace mobile,partition sales_2009_8 values less than(to_date('01/09/2009','dd/mm/yyyy')) tablespace mobile,partition sales_2009_9 values less than(to_date('01/10/2009','dd/mm/yyyy')) tablespace mobile,partition sales_2009_10 values less than(to_date('01/11/2009','dd/mm/yyyy')) tablespace mobile,partition sales_2009_11 values less than(to_date('01/12/2009','dd/mm/yyyy')) tablespace mobile,partition sales_2009_12 values less than(to_date('01/01/2010','dd/mm/yyyy')) tablespace mobile,partition sales_other values less than(maxvalue) tablespace  mobile);

 

Besides, 32-bit Oracle and 64-bit security are required for 32-bit systems.

For 32-bit Oracle, the maximum memory in Oracle is 1.7 GB, so even if your server memory is larger, Oracle can only use GB of memory, the speed is still quite slow. 64-bit Oracle has no restrictions. Therefore, you need to install a 64-bit system and a 64-bit oracle.