The SET DEFAULT...TABLESPACE clause of the CREATEDATABASE statement determines the default type of tablespace for this database in subsequent CREATETABLESPACE statements.
Specify either SET DEFAULT BIGFILE TABLESPACE or SET DEFAULT SMALLFILE TABLESPACE.
If you omit this clause, then the default is a smallfile tablespace, which is the traditional type of Oracle Database tablespace. A smallfile tablespace can contain up to 1022 files with up to 4M blocks each.
The use of bigfile tablespaces further enhances the Oracle Managed Files feature, because bigfile tablespaces make data files completely transparent for users. SQL syntax for the ALTERTABLESPACE statement has been extended to allow you to perform operations on tablespaces, rather than the underlying data files.
Each pigeonhole has receipts (rows) describing shirts sold to a customer, ordered by ship date. In this analogy, a zone map is like a stack of index cards. Each card corresponds to a “zone” (contiguous range) of pigeonholes, such as pigeonholes 1-10. For each zone, the card lists the minimum and maximum ship dates for the receipts stored in the zone.
When someone wants to know which shirts shipped on a certain date, the manager flips the cards until she comes to the date range that contains the requested date, notes the pigeonhole zone, and then searches only pigeonholes in this zone for the requested receipts. In this way, the manager avoids searching every pigeonhole in the bookcase for the receipts.
This example illustrates how a zone map can prune data in a query whose predicate contains a constant.
Assume you create the following lineitem table:
CopyCREATE TABLE lineitem
( orderkey NUMBER ,
shipdate DATE ,
receiptdate DATE ,
destination VARCHAR2(50) ,
quantity NUMBER );
The table lineitem contains 4 data blocks with 2 rows per block. Table 2-3 shows the 8 rows of the table.
Table 2-3 Data Blocks for lineitem Table
Block
orderkey
shipdate
receiptdate
destination
quantity
1
1
1-1-2014
1-10-2014
San_Fran
100
1
2
1-2-2014
1-10-2014
San_Fran
200
2
3
1-3-2014
1-9-2014
San_Fran
100
2
4
1-5-2014
1-10-2014
San_Diego
100
3
5
1-10-2014
1-15-2014
San_Fran
100
3
6
1-12-2014
1-16-2014
San_Fran
200
4
7
1-13-2014
1-20-2014
San_Fran
100
4
8
1-15-2014
1-30-2014
San_Jose
100
You can use the CREATE MATERIALIZED ZONEMAP statement to create a zone map on the lineitem table. Each zone contains 2 blocks and stores the minimum and maximum of the orderkey, shipdate, and receiptdate columns. Table 2-4 shows the zone map.
When you execute the following query, the database can read the zone map and then scan only blocks 1 and 2 because the date 1-3-2014 falls between the minimum and maximum dates:
CopySELECT * FROM lineitem WHERE shipdate = '1-3-2014';
Bigfile tablespaces can contain only one file, but that file can have up to 4G blocks. The maximum number of data files in an Oracle Database is limited (usually to 64K files). Therefore, bigfile tablespaces can significantly enhance the storage capacity of an Oracle Database.
This section discusses the clauses of the CREATEDATABASE statement that let you include support for bigfile tablespaces.
Specifying the Default Tablespace Type The SET DEFAULT...TABLESPACE clause of the CREATEDATABASE statement determines the default type of tablespace for this database in subsequent CREATETABLESPACE statements.
Overriding the Default Tablespace Type The SYSTEM and SYSAUX tablespaces are always created with the default tablespace type. However, you optionally can explicitly override the default tablespace type for the UNDO and DEFAULTTEMPORARY tablespace during the CREATEDATABASE operation.
You can minimize the number of clauses and parameters that you specify in your CREATEDATABASE statement by using the Oracle Managed Files feature.
Specify either a directory or Oracle Automatic Storage Management (Oracle ASM) disk group in which your files are created and managed by Oracle Database.
By including any of the initialization parameters DB_CREATE_FILE_DEST,DB_CREATE_ONLINE_LOG_DEST_n, or DB_RECOVERY_FILE_DEST in your initialization parameter file, you instruct Oracle Database to create and manage the underlying operating system files of your database. Oracle Database will automatically create and manage the operating system files for the following database structures, depending on which initialization parameters you specify and how you specify clauses in your CREATEDATABASE statement:
The following CREATEDATABASE statement shows briefly how the Oracle Managed Files feature works, assuming you have specified required initialization parameters:
CREATE DATABASE mynewdb USER SYS IDENTIFIED BY sys_password USER SYSTEM IDENTIFIED BY system_password EXTENT MANAGEMENT LOCAL UNDO TABLESPACE undotbs1 DEFAULT TEMPORARY TABLESPACE tempts1 DEFAULT TABLESPACE users;
The SYSTEM tablespace is created as a locally managed tablespace. Without the EXTENTMANAGEMENTLOCAL clause, the SYSTEM tablespace is created as dictionary managed, which is not recommended.
No DATAFILE clause is specified, so the database creates an Oracle managed data file for the SYSTEM tablespace.
No LOGFILE clauses are included, so the database creates two Oracle managed redo log file groups.
No SYSAUX DATAFILE is included, so the database creates an Oracle managed data file for the SYSAUX tablespace.
No DATAFILE subclause is specified for the UNDO TABLESPACE and DEFAULTTABLESPACE clauses, so the database creates an Oracle managed data file for each of these tablespaces.
No TEMPFILE subclause is specified for the DEFAULT TEMPORARY TABLESPACE clause, so the database creates an Oracle managed temp file.
If no CONTROL_FILES initialization parameter is specified in the initialization parameter file, then the database also creates an Oracle managed control file.
If you are using a server parameter file (see “Managing Initialization Parameters Using a Server Parameter File”), then the database automatically sets the appropriate initialization parameters.
A zone map is an independent access structure that divides data blocks into zones. Oracle Database implements each zone map as a type of materialized view.
Whenever CLUSTERING is specified on a table, the database automatically creates a zone map on the specified clustering columns. The zone map correlates minimum and maximum values of columns with consecutive data blocks in the attribute-clustered table. Attribute-clustered tables use zone maps to perform I/O reduction.
You can create attribute-clustered tables that do not use zone maps. You can also create zone maps without attribute-clustered tables. For example, you can create a zone map on a table whose rows are naturally ordered on a set of columns, such as a stock trade table whose trades are ordered by time. You execute DDL statements to create, drop, and maintain zone maps.
Attribute clustering that is based on joined columns is called join attribute clustering. In contrast with table clusters, join attribute clustered tables do not store data from a group of tables in the same database blocks.
For example, consider an attribute-clustered table, sales, joined with a dimension table, products. The sales table contains only rows from the sales table, but the ordering of the rows is based on the values of columns joined from products table. The appropriate join is executed during data movement, direct path insert, and CREATE TABLE AS SELECT operations. In contrast, if sales and products were in a standard table cluster, the data blocks would contain rows from both tables.
Oracle Database Data Warehousing Guide to learn more about join attribute clustering
I/O Reduction Using Zones
A zone is a set of contiguous data blocks that stores the minimum and maximum values of relevant columns. When a SQL statement contains predicates on columns stored in a zone, the database compares the predicate values to the minimum and maximum stored in the zone to determine which zones to read during SQL execution.
I/O reduction is the ability to skip table or index blocks that do not contain data that the database needs to satisfy the query. This reduction can significantly reduce the I/O and CPU cost of table scans.
In the SAP system, you can manage materials in different units of measure. You must always specify a base unit of measure for the material. Alternative units of measure are then defined using a conversion factor with reference to this base unit of measure. The system automatically converts all quantities that you enter in an alternative unit of measure during a goods movement into the base unit of measure.
Rounding differences may occur during these conversions. Especially in the following cases, rounding effects may occur:
The base unit of measure is a metric unit of measure (such as meter, liter, and kilogram) and goods movements are posted in non-metric units (such as yard, gallon, and pound) (or vice versa).
The base unit of measure is not the smallest unit of measure, unlike the alternative unit of measure.
List of Stock Values (MB5L Transaction) / Display List of Stock Values App
With the reportList of Stock Values, you can display the total stock quantity and the total stock value for a material (or several materials) at company code and valuation area level. In addition, the stock account to which the specified material is posted during goods movements is determined.
You can also display the balance values per company code and G/L account. For each G/L account, the balance is compared with the sum of the stock values of the associated materials. The difference between the two totals is displayed.
You can restrict the search options in the List of Stock Values report with the following fields:
Material
Company code
Valuation area
Valuation type
Valuation class
G/L account
In addition, you can choose the period for the evaluation (current period, previous period, or previous year) and specify the scope of the list, for example, totals or negative stocks only.
If you start the report without specifying a material, valuation area, and valuation class, the balance for each stock account must match the total of all stock values of the materials for the relevant valuation class. A variance can have the following reasons:
You made manual postings to the stock account.
Other postings besides stock postings are made to the stock account. In this case, you need to check the account determination in the Customizing settings for valuation. Ensure that the stock accounts are used only for transaction key BSX (stock postings).
You changed the account determination for stock accounts (transaction and event key BSX) in the current operation, but you did not debit the relevant stocks from the old account (movement type 562) and posted to the new account (movement type 561).
If you place the cursor on the G/L account in the output list and choose Environment→Stocks, the system displays an overview of all materials that are managed in this G/L account. The system also displays the total value and total stock for each material.
Special Procurement Process of Subcontracting – Overview
During subcontracting, your company orders material from an external supplier. Unlike a normal external procurement process, your company makes the components for the production of the materials partially or completely available to the supplier (subcontractor).
Inventory management supports the following functions within subcontracting:
Special stock type “Stock of Material Provided to Supplier”
Provision of components
Goods receipt for finished product with consumption of components
Subsequent adjustment in the case of excess or under-consumption of components
Transfer postings (material to material, stock to stock, and plant to plant)
For the stock of material provided to suppliers, the available stock types are unrestricted-use stock and stock in quality inspection. The quantities consumed by the subcontractor can be withdrawn only from the unrestricted-use stock. A physical inventory is to be carried out for the stock of material provided to the supplier.
To analyze the stocks of material provided, you can use various stock evaluations, for example:
Display Stock Overview app and corresponding transaction Stock Overview (MMBE):In the stock overview, at plant level, the total of all stocks of material provided for this material in this plant is displayed. With a double-click on this special stock line you see a detailed overview of the stock of material provided per subcontractor.To start the transaction from the SAP Easy Access menu, choose Logistics→Materials Management→Inventory Management→Environment→Stock→Stock Overview (MMBE).
Stocks at Subcontractor app and corresponding transaction Stocks at Subcontractor (MBLB):With this function, you can display stocks provided for several materials and several subcontractors (supplier) at the same time.To start the transaction from the SAP Easy Access menu, choose Logistics→Materials Management→Inventory Management→Environment→Stock→Stock with Subcontractor (MBLB).
Stock – Multiple Material app:In the app, to select only stocks of material provided, select the predefined view Stock Value for Subcontracting Stock (O). In this view, the filter for the special stock type is already preset with O (Subcontracting Stock).
Display Subcontracting Stocks by Supplier app and corresponding transaction Subcontracting Cockpit (ME2ON):To start the transaction from the SAP Easy Access menu, choose Logistics→Materials Management→Purchasing→Purchase Order→Reporting→Subcontracting Cockpit (ME2ON).
From the point of view of inventory management, the posting of a provision of components corresponds to a transfer posting from unrestricted-use stock to the stock of material provided to vendor. This transfer posting can be carried out using the one-step procedure (MvT 541), or the two-step procedure (MvT 30A and 30C).
There are different options available for posting the provision of components.
In Inventory Management, you can post a provision of components using transaction MIGO and the corresponding SAP Fiori App Post Goods Movement. You can refer to the subcontracting item (SC item) of the purchase order when entering this transfer posting. The components and their quantities are then proposed from the SC item. You can change the proposed quantities of the materials to be provided.Provision of components with reference to a purchase order is not updated in the purchase order history because the stock of the material provided is not purchase-order based.
In transaction MIGO and the corresponding SAP Fiori App Post Goods Movement, you can also provide components without reference to a purchase order. To do this, you enter another transfer posting and specify the required materials and quantities as well as the subcontractor manually.
In Purchasing, you can use the Subcontracting Cockpit (transaction ME2ON) or the corresponding app Display Subcontracting Stocks by Supplier to post a goods issue for the components. As the selection criteria, you enter, for example, the supplier, the component to be provided, or the material to be produced. A list containing the requirements and stocks of the components, and the pegged requirements is then displayed. To post a provision for a component, select the relevant line and choose Edit→Post goods issue.
In addition, in SAP S/4HANA there is the Subcontracting Cockpit Fiori app. Its functions are similar to those of transaction ME2ON. The SAP Fiori app provides you with an overview of open and completed purchasing documents (purchase orders and scheduling agreements). As filter criteria, you are offered subcontractor (supplier), component, assembly (material to be produced), plant and shipping status, among others. You can execute the following functions for the selected subcontracting documents and the associated components:
Post goods issue on purchasing document item level.
Create the delivery document for the component delivery.
Calculate the stock balance at component, plant, and supplier level. If you click on the calculated stock balance for a component, you get a detailed overview of the stock and requirements situation of the component.
If you have to procure a component from a second supplier, you can have it delivered directly to the subcontractor. To do this, you must specify the following in the delivery address of the purchase order item:
the supplier number of the subcontractor
the Subcontractor supplier (SC Supp) indicator
When your subcontractor informs you that the component has arrived, you enter the goods receipt for the component purchase order. At this goods receipt, the component is posted directly to the stock of material provided which is assigned to the subcontractor. You cannot do without this goods receipt posting, since it is the only way to update the stock of material provided and thus also your own, usually valuated stock.This direct delivery of a component is taken into account in transaction ME2ON during selection if you set the With external receipts from purchase orders/purchase requisitions indicators. In the Subcontracting Cockpit Fiori app, the direct delivery is also taken into account when calculating the stock balance for a component.
Subcontracting: Transfer Postings of the Stock of Material Provided
The following transfer postings are allowed for the stock of material provided to supplier special stock:
Plant to plant in the one-step procedure (movement type 301)
Material to material (movement type 309)
Stock in quality inspection to unrestricted-use stock (movement type 321)
To execute these transfer postings for the stock of material provided to supplier, you must specify the special stock indicator O; otherwise, the transfer postings do not differ from normal transfer postings.
Subcontracting: Subsequent Adjustment
If the subcontractor reports excess or under-consumption of subcontracting components after the delivery of the finished product, you must post a subsequent adjustment to correct the component consumption.
The following functions are available for posting a subsequent adjustment:
Transaction MIGO or the corresponding Post Goods Movements app
Post Subsequent Adjustment Fiori app, since SAP S/4HANA 2021
Enter a subsequent adjustment using transaction MIGO:
In the list of business transactions, choose Subsequent Adjustment. As a reference document, the system automatically enters Purchase Order. After you have specified the purchase order item, you can enter the difference quantity for each component. If a component has excess consumption, you only need to enter the additional quantity consumed. If less was consumed by a component than was posted at goods receipt, you also set the Underconsumption / Short Receipt indicator. This increases the stock provided of this component by the difference quantity (the consumption is reversed).
Enter a subsequent adjustment using Post Subsequent Adjustment app:
On the initial screen, enter the document number of the purchasing document for which you want to enter a subsequent adjustment. To enter the overconsumption or underconsumption of a component, open the item details. In the Components section, in the Goods Movement field, select whether there is Overconsumption or Underconsumption for the individual component and enter the quantity to be posted. For components for which no subsequent adjustment needs to be posted, leave the default No Posting for the goods movement unchanged. Finally, choose Apply for the component data and post the subsequent adjustment.
Swashbuckle is a NuGet package that provides a way to automatically generate Swagger documentation for ASP.NET Web API projects. Swagger is a tool that helps developers design, build, document, and consume RESTful APIs. With Swashbuckle, you can easily add Swagger documentation to your Web API project by annotating your code with attributes that describe your API endpoints, parameters, and responses. Swashbuckle then uses this information to generate a Swagger JSON file, which can be used to generate interactive API documentation, client SDKs, and more.
Swashbuckle.AspNetCore.Swagger: a Swagger object model and middleware to expose SwaggerDocument objects as JSON endpoints.
Swashbuckle.AspNetCore.SwaggerGen: a Swagger generator that builds SwaggerDocument objects directly from your routes, controllers, and models. It’s typically combined with the Swagger endpoint middleware to automatically expose Swagger JSON.
Swashbuckle.AspNetCore.SwaggerUI: an embedded version of the Swagger UI tool. It interprets Swagger JSON to build a rich, customizable experience for describing the web API functionality. It includes built-in test harnesses for the public methods.
The following dotnet add command installs the Swashbuckle NuGet package:
The call to AddEndpointsApiExplorer shown in the previous example is only required for minimal APIs.
Enable the middleware for serving the generated JSON document and the Swagger UI, also in Program.cs:
C#
app.UseSwagger();
if (app.Environment.IsDevelopment())
{
app.UseSwaggerUI();
}
The default endpoint for the Swagger UI is http:<hostname>:<port>/swagger.
Note
SwaggerUI is very useful in your development environment. It can be enabled in production, but you should consider any security requirements for your specific application before doing so.
Swagger provides options for documenting the object model and customizing the UI. The configuration action passed to the AddSwaggerGen method can include additional information through the OpenApiInfo class.
The following code sample shows how to add information to display in the API documentation.
C#
// Add using statement for the OpenApiInfo class
using Microsoft.OpenApi.Models;
builder.Services.AddSwaggerGen(options =>
{
options.SwaggerDoc("v1", new OpenApiInfo
{
Version = "v1",
Title = "Fruit API",
Description = "API for managing a list of fruit their stock status.",
TermsOfService = new Uri("https://example.com/terms")
});
});
The Swagger UI displays the version and the added information:
You can group operations in your API with the .WithTags option. You can also add descriptive text describing the operation with the .WithSummary option. The following sample code shows using .WithTags to group the POST operation into both a post group, and a fruit group. It also adds the summary specified in the .WithSummary option to the operation.
The Hypertext Transfer Protocol (or HTTP) is used to request resources from a web server. Many types of resources are available on the web, and HTTP defines a set of request methods for accessing these resources. In .NET Core, those requests are made through an instance of the HttpClient.
There are two options for implementing HttpClient in your app and the recommendation is to choose the implementation based on the clients lifetime management needs:
Long-lived clients: create a static or singleton instance using the HttpClient class and set PooledConnectionLifetime
Short-lived clients: use clients created by IHttpClientFactory
Implement with the HttpClient class
The System.Net.Http.HttpClient class sends HTTP requests and receives HTTP responses from a resource identified by a URI. An HttpClient instance is a collection of settings applied to all requests executed by that instance, and each instance uses its own connection pool, which isolates its requests from others. Beginning with .NET Core 2.1, the SocketsHttpHandler class provides the implementation, making behavior consistent across all platforms.
HttpClient only resolves DNS entries when a connection is created. It doesn’t track time to live (TTL) durations specified by the DNS server. If DNS entries change regularly the client is unaware those updates. To solve this issue, you can limit the lifetime of the connection by setting the PooledConnectionLifetime property, so that DNS lookup is repeated when the connection is replaced.
In the following example, HttpClient is configured to reuse connections for 15 minutes. After the TimeSpan specified by PooledConnectionLifetime elapses, the connection is closed and a new one is created.
C#
var handler = new SocketsHttpHandler
{
PooledConnectionLifetime = TimeSpan.FromMinutes(15) // Recreate every 15 minutes
};
var sharedClient = new HttpClient(handler);
Implement with IHttpClientFactory
The IHttpClientFactory serves as a factory abstraction that can create HttpClient instances with custom configurations. IHttpClientFactory was introduced in .NET Core 2.1. Common HTTP-based .NET workloads can take advantage of middleware with ease.
When you call any of the AddHttpClient extension methods, you’re adding the IHttpClientFactory and related services to the IServiceCollection. The IHttpClientFactory type offers the following benefits:
Exposes the HttpClient class as a dependency injection-ready type.
Provides a central location for naming and configuring logical HttpClient instances.
Codifies the concept of outgoing middleware via delegating handlers in HttpClient.
Provides extension methods for Polly based middleware to take advantage of delegating handlers in HttpClient.
Manages the caching and lifetime of underlying HttpClientHandler instances. Automatic management avoids common Domain Name System (DNS) problems that occur when manually managing HttpClient lifetimes.
Adds a configurable logging experience for all requests sent through clients created by the factory.
You should let HttpClientFactory and the framework manage the lifetimes and instantiation of HttpClient instances. The lifetime management helps avoid common issues such as DNS (Domain Name System) problems that can occur when manually managing HttpClient lifetimes.
