Oracle Tips and Tricks of the Week Part 3 More and more we are using locally managed tablespaces. They offer. a large amount of benefits, so why should we not use this new feature? Some thoughts are needed when you decided to use Uniform Extent. Allocation. With the uniform method, you specify an extent size when you create the. The uniform method also provides an enforcement mechanism, because you. The goal is to allocate as much disk space as really needed and as really. With the uniform extent allocation you can calculate or even estimate the number of. Gaps or unused disk space within the tablespace should be. Lets assume that we create a tablespace with the uniform extent size of 1. MByte and 1. 0 extents. Remember that locally managed tablespaces will use another 6. KBytes. or the Header Bitmap: 1. K + 6. 4K = 1. 03. KNote that all calculations are made in KBytes and that your chosen extent. The following statement creates this. MByte: CREATE TABLESPACE uni_test. DATAFILE ‘C: \Oradata\ASU1\tab\uni_test. SIZE 1. 03. 04. KEXTENT MANAGEMENT LOCAL UNIFORM SIZE 1. K; Now every object created within the newly created tablespace gets its. MByte: CREATE TABLE tab_1 (num NUMBER) TABLESPACE uni_test; CREATE TABLE tab_2 (num NUMBER,text VARCHAR2(2. TABLESPACE uni_test. STORAGE(INITIAL 1. KNEXT. 1. 00. KMINEXTENTS 1. Access-Freak :: Getting started with Microsoft(R) Access 2007 (Step by Step Tutorials/Samples.). MAXEXTENTS UNLIMITEDPCTINCREASE 0); CREATE TABLE tab_3 (num NUMBER,text VARCHAR2(2. DATE) TABLESPACE uni_test. STORAGE(MINEXTENTS 2. MAXEXTENTS UNLIMITEDPCTINCREASE 0); If you are including a STORAGE clause when you create tables or indexes. Oracle will allocate as much extents as you indicate to use. Table TAB_1 will be allocated. Easysoft JDBC-Access Driver Getting Started Guide. How to connect to Microsoft Access or Microsoft Excel from Java. Introduction. JDBC is a Java application. There doesn’t appear to be a neat little function to read an external directory. At least, there’s not one in the UTL_FILE or DBMS_LOB packages where you’d. TAB_2 too because you need at least 1. KBytes. Table TAB_3 will be. This could also be done by defining an INITIAL value of 2. MBytes. The allocated blocks and extents can be verified using the view. DBA_SEGMENTS: SELECT segment_name, segment_type, blocks, extents. FROM dba_segments. WHERE owner = 'TEST'ORDER BY EXTENTS/SEGMENT_NAME. SEGMENT_TYPE. BLOCKS EXTENTS- -- -- -- -- -- -- -- -- -- - - -- -- -- -- -- -- -- -- - - -- -- -- -- - - -- -- -- -- -TAB_1. TABLE. 2. 56 1. TAB_2. TABLE. 2. 56 1. TAB_3. TABLE. 5. 12 2. The free space in the tablespace UNI_TEST can be verified using the view. DBA_FREE_SPACE: SELECT tablespace_name, bytes, blocks. FROM dba_free_space. WHERE tablespace_name = 'UNI_TEST'/. TABLESPACE_NAME. BYTES BLOCKS- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - - -- -- -- -- - - -- -- -- -- -UNI_TEST. That means in the tablespace UNI_TEST are still 1. How many extents are these blocks? This can be calculated by multiplying the number of. K / 1. 02. 4K = 6 extents. That fits with our calculations and verifications: 4 extents are already. If you check the physical file size used for the tablespace UNI_TEST you. Instead of the calculated 1. KBytes (1. 0'5. 51'2. Bytes) you will find. Bytes. Oracle allocates another block which can. Some of the Oracle tools such as the Tablespace Manger. In our example this are. KBytes (for header bitmap). Keep the following rules in mind during the sizing of tablespaces: Each extent size is the multiple of your defined block size. The usable tablespace size is the multiple of your estimated number. The defined tablespace size used during CREATE TABLESPACE statement. KBytes for the header bitmap (HB) to the usable tablespace size. The physical file size adds one block (AB) to the defined tablespace. In Oracle. 8i BLOB's (Binary Large Objects) can be used instead of. LONG RAW's to store binary unformatted data, like documents, images, audio and video. On. the new BLOB data type many of the former LONG RAW restrictions are not valid anymore and. GB can be stored. This tip shows how to migrate LONG RAW columns to BLOB's. It is worth to create a separate tablespace for the LOB's bigger contents. The tables containing LOB columns can be stored together with other. However. the LOB columns referencing their data in a separate tablespace (called btab here). CREATE TABLESPACE btab. DATAFILE '../lob/POR1_lob. SIZE 5. 12. 06. 4K REUSEAUTOEXTEND ON NEXT 1. M MAXSIZE UNLIMITEDEXTENT MANAGEMENT LOCAL UNIFORM SIZE 2. KPERMANENTONLINE; A new table must be created that contains the new BLOB column. Even if it. is possible to add a BLOB column to an existing table we cannot migrate old LONG RAW data. The required SQL function TO_LOB can be used. SELECT subqueries of INSERT statements only. Lets assume the old table docs looks like this.. NUMBER(1. 0) NOT. NULLbdata LONG RAW NULL.. CREATE TABLE newdocs (id NUMBER(1. CONSTRAINT. nn_newdocs_id NOT NULL,bdata. BLOB DEFAULT. NULL )PCTFREE 5. PCTUSED 4. 0INITRANS 1. MAXTRANS 2. 55. TABLESPACE tab STORAGE (INITIAL. KNEXT 5. KMINEXTENTS 1. MAXEXTENTS UNLIMITEDPCTINCREASE 0. FREELISTS 1)LOB (bdata) STORE AS (TABLESPACE btab. STORAGE (INITIAL 1. M NEXT 1. 00. M PCTINCREASE. CHUNK 5. 0PCTVERSION 1. NOCACHE LOGGING); LOB's in general do not use rollback segments. To maintain read. Oracle creates new LOB page versions every time a lob changes. PCTVERSION is the percentage of all used LOB data space that can be occupied by old. LOB data pages. As soon as old versions of LOB data pages start to occupy more. PCTVERSION amount of used LOB space, Oracle tries to reclaim the old versions and. In other words, PCTVERSION is the percent of used LOB data blocks that is. LOB data. The PCTVERSION can be set to the percentage of LOB's. If LOB's are inserted once and afterwards usually read only. If CACHE is specified Oracle places LOB pages in the buffer cache. NOCACHE can be used if there are occasionally no writes to stored LOB's. CACHE READ is good for busy read operations and infrequent. Set CHUNK to the number of blocks of LOB data that will be. This reduces network roundtrip overheads. The INITIAL and NEXT. CHUNK * DB_BLOCK_SIZE size. Use bigger CHUNK's if. The default setting ENABLE STORAGE IN ROW stores LOB's less than. KB within the table and greater LOB's are automatically moved out of the row. This is the. recommended setting. DISABLE STORAGE IN ROW can be used to store all data outside the rows. A lot of small LOB's within a table can decrease performance of table operations like full. Consider that CHUNK and ENABLE/DISABLE STORAGE IN ROW cannot be altered. Finally we can use the following SQL statement to migrate the data from. INSERT INTO newdocs (id, bdata)SELECT id, TO_LOB(bdata)FROM docs; To copy the data is easy. The SQL function TO_LOB( ) can be used. LONG RAW to BLOB. It's also possible to convert LONG to CLOB if required. The. main thing of the whole data migration is to choose good storage parameter settings. LOB's in size need to be stored. One option for secure communication between the Net. Secure Shell. protocol. Conceptually, it works like this. First, you install an SSH. Net. 8 client. You use the SSH client to. SSH connection to the remote host where the Net. You also. use the SSH client to establish a "listen" on a local port for Net. Here's the cool part: when you fire up your Net. Net. 8 port on localhost - your machine - instead of connecting to port 1. The SSH client then forwards everything it receives on the. Net. 8 port through the SSH session, or tunnel, to the remote SSH daemon, which then. Net. 8 port on the remote host. How does the SSH daemon on the receiving end know what to do. Net. 8 information coming at it? Well, the information is part of the. SSH. session. For example, you'd invoke SSH from your unix client machine like this$ ssh - f - L localport: remotehost: remoteport tail - f. Tfhe command must be invoked as root because root privilege. The - f option tells SSH to run in the. L. localport: remotehost: remoteport specifies that the given port on the local (client). In our example, we. The server port must be whichever port listens for Net. Depending on the SSH client, you'll either be prompted for. SSHD 1. 94. 7. 5. In all. cases, you'll have to use SSH to log in to the remote host before you can use it to. The entire Net. 8 port- forwarding scenario is shown in the next. Example. We start by using lsof (list open files), a program that tells you which open. TCP port 5. 55. 5. There is none. We confirm this by trying to telnet to localhost. At this point, we're certain that there's no activity, such. That port is okay to. Next, we set up the port forwarding by issuing an SSH command. Remember that you have. L 5. 55. 5: 1. 92. The tail - f /dev/null that we tacked on the end of. SSH command is just a low- overhead command to keep the session open.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |