Table Partitioning Schemes-Partitioning

There are currently nine methods by which you can partition tables in Oracle: •\ Range partitioning: You may specify ranges of data that should be stored together. For example, everything that has a timestamp within the month of Jan-2014 will be stored in partition 1, everything with a timestamp within Feb-2014 in partition 2, and
Read more

SecureFiles RETENTION-Datatypes

SecureFiles use RETENTION to control read consistency (just like BasicFiles). In the CREATE TABLE output of DBMS_METADATA for the SecureFiles LOB, there is no RETENTION clause. This is because the default RETENTION is set to AUTO, which instructs Oracle to retain undo long enough for read-consistent purposes. If you want to alter the default RETENTION
Read more

Internal LOBs-Datatypes

Starting with Oracle Database 11g, Oracle introduced a new underlying architecture for LOBs known as SecureFiles. The prior existing LOB architecture is known as BasicFiles. By default in 11g, when you create a LOB, it will be created as a BasicFiles LOB. Starting with Oracle 12c, when creating a LOB column in an ASSM-managed tablespace,
Read more

Range Partitioning-Partitioning

The first type we will look at is a range partitioned table. The following CREATE TABLE statement creates a range partitioned table using the column RANGE_KEY_COLUMN. All data with a RANGE_KEY_COLUMN strictly less than 01-JAN-2021 will be placed into the partition PART_1, and all data with a value strictly less than 01-JAN-2022 (and greater than
Read more

Read Consistency for LOBs-Datatypes

In previous chapters, we’ve discussed read consistency, multiversioning, and the role that undo plays in that. Well, when it comes to LOBs, the way read consistency is implemented changes. The LOBSEGMENT does not use undo to record its changes; rather, it versions the information directly in the LOBSEGMENT itself. The LOBINDEX generates undo just as
Read more

LOB STORAGE Clause-Datatypes

And lastly, the CREATE TABLE statement returned from DBMS_METADATA previously included the following for SecureFiles:LOB (“TXT”) STORE AS SECUREFILE (…STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) And here is the corresponding output for BasicFiles:LOB (“TXT”) STORE AS BASICFILE ( …STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
Read more

Getting the Difference Between Two DATEs-Datatypes

Another frequently asked question is, “How do I retrieve the difference between two dates?” The answer is deceptively simple: you just subtract them. This will return a number representing the number of days between the two dates.Additionally, you have the built-in function MONTHS_BETWEEN that will return a number representing the number of months—including fractional months—between
Read more

Coping with Legacy LONG Types-Datatypes

A question that arises frequently is, “What about the data dictionary in Oracle?” It is littered with LONG columns, and this makes using the dictionary columns problematic. For example, it is not possible using SQL to search the ALL_VIEWS dictionary view to find all views that contain the text HELLO:$ sqlplus eoda/foo@PDB1SQL> select * from
Read more
Copyright All right reserved
BACK TO TOP