Oracle Operation Skills

Oracle operation skills because of the customer's requirements for advanced database operations (at least for me ). Since Oracle has not been thoroughly studied before. I have never heard of many operations, but I think it is important in development as the basis for development (database ). It is worth learning. Perform the following operations on table space, partition tables, and batch import of test data. 1. Create a table space Create tablespace table space name datafile (data file address and name -. dbf File) 'size 50 m (file size); generally, the server uses linux or unix, And the tablespace address needs to be viewed in it. Generally, the connection uses cmd-> telnet-> followed by the user name and password. Therefore, it is necessary to familiarize yourself with the commands. 2. Delete the tablespace drop tablespace name including contents and datafiles cascadeconstraints; 3. query the table space usage alter database datafile 'data file address + name (for example:/oracle10/data_file_name.dbf) 'resize 500 m (size to be changed) 4. query table space usage select. tablespace_name,. bytes/1024/1024 "Sum MB", (. bytes-b.bytes)/1024/1024 "usedMB", B. bytes/1024/1024 "free MB" from (select tablespace_name, sum (bytes) bytes from dba_data_files group by tablespace_name) a, (se Lecttablespace_name, sum (bytes) bytes, max (bytes) largest from dba_free_space group bytablespace_name) bwhere. tablespace_name = B. tablespace_nameorder by (. bytes-b.bytes)/. bytes) desc. paste it directly. This is the view created by Oracle itself. I am not very clear about how it works. This is probably the case for tablespace operations. If there is a problem with the tablespace, it is enough to solve this problem. Ii. Partition Table: Let's talk about why we need to create a partition table! 1. improve query efficiency data analysis if the data volume is large and put in a tablespace, the query is not isolated, the query base is very large, and the query will be very slow. As we currently need, about more than 100000 million of the data volume will be generated every day, so queries will be very slow! If the query is divided by time or data generated by a device, the query base is greatly reduced. For the partition table to be queried, this can be controlled in the program. Of course, Oracle will also use your Partition Table rule to handle it first. 2. Reducing data files requires that the space data be stored in the database in large and small sizes. How can we reduce the required space for data files? If you store all the data in a data file, the available disk space for this file must be sufficient, or the data cannot be obtained! If you create a partition table, you can store the data files corresponding to the partition table on different disks. In this way, the limit is very small. Partition tables have the following advantages. 1. Global query is slow. If the query conditions cannot be restricted to query in several partition tables, the query will be queried in each partition table. It will be much slower! 2. Complex table space management partitioned tables can be created more and more easily, which may cause some trouble. For now, I only have these feelings about partition tables. In subsequent development, we should also encounter such problems. Next we will go to the partition creation and maintenance phase 1. CREATE a partition table create table name (Database TABLE field) partition by range (CATALOG_TIME) (Partition Table Name values less than (TO_DATE ('1970-04-18 ', 'yyyy-mm-dd') tablespace name, partition Table Name values less than (TO_DATE ('1970-04-19 ', 'yyyy-mm-dd') tablespace name, partition Table Name values less than (TO_DATE ('1970-04-20 ', 'yyyy-mm-dd') tablespace name) the Partition table is created on a daily basis. Of course, you can also create partitions according to other rules. 2. After creating a partition table and creating a partition table, it is very likely that there are fewer partitions. We need to add new partitions. Of course, we cannot delete all the previous tables, execute the preceding statement again. If there is data in the table, there will be no more data. Alter table name addpartition partitioned table name valuesless than (TO_DATE ('2017-04-22 ', 'yyyy-mm-dd') tablespace name; 3. Import the test data after the table is set up. How can we import the test data efficiently? This is a problem that has always plagued me. It is too difficult to test data. Please refer to the following code, which may be helpful to you. Since we use Oracle, Oracle does not support auto-increment of primary keys. Therefore, before importing data, you must create a sequence to help you generate a primary key. [1] creating a sequence create sequence SEQ_TEST MINVALUE 1 MAXVALUE 100000 start with 1 increment by 1 nocache order; the statement is very simple and will not be explained. [2] INSERT data (loop) declare maxrecordsCONSTANT int: = 100000; iint: = 1; begin fori in 1 .. maxrecords loop insert into Table Name (id, field name ......) VALUES (SEQ_TEST1.NEXTVAL, (Field Value); endloop; commit; end;