Submit Oracle Questions
Interrelated Information Technology
|Database Questions||WebLogic Questions|
|WebSphere MQ Questions||Web Server Questions|
|Java Questions||XML Questions|
The Computer Education Techniques knowledge base is a service for answering questions, inclusive of the research and validation of the accuracy of information in the public domain. Citation of source documentation and examples are used to provide answers to the questions. Utilization of the information of this service and reliance on the answers, information or other materials received through this web site is done at your own risk.
|Q||What is the Oracle Block Size and what value should be used?|
The value of the block size is set in the parameter DB_BLOCK_SIZE. The
default value is 8192 bytes and once set it cannot be modified. The
database will have to be recreated in order to change the block size. The
value specified must be a multiple of the device block size. The value 8K is
a sensible value for general use. For a warehouse environment, a database
administrator can increase this value.
The smaller block sizes are well suited for small rows with random access. The larger block size allows several rows to be read in with a single I/O. Larger row sizes are better for sequential access and large rows. Larger block size may waste space in the buffer cache with small rows in a random environment.
In the Linux operating system, the physical sector size can be ascertained
with this command:
sudo hdparm -I /dev/sda | grep -i physical or lsblk -o NAME,PHY-SeC
In the MS Windows operating system, the physical sector size can be
ascertained with this command:
fsutil fsinfo ntfsinfo c:
|Command execution requires administrative mode.|
|Q||How can the database version be determined at run-time?|
This can be achieved using this query:
SELECT * FROM V$VERSION
This query would provide similar information:
SELECT version, edition FROM V$INSTANCE
|Q||There is a requirement to read a sequential file in a MS Windows directory as an external table. What steps are needed to create and use an external table.|
|A||The three steps are: 1. Create a directory object point to the data using CREATE DIRECTORY command. 2. Create the external table using the CREATE TABLE command 3. Query the table using the SELECT statement.|
Use the DDL statement for creating the directory object.
CREATE OR REPLACE DIRECTORY externaldata AS 'c:/temp/data';
The name of the directory object is externaldata and the location of the data is in the directory c:/temp/data. The name does not include the actual data file name; only the directory name.
Use a DDL statement for defining the table as its association with the external table.
CREATE TABLE countries_ext (
ORGANIZATION EXTERNAL (
DEFAULT DIRECTORY externaldata
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
( state_code CHAR(2),
LOCATION ('states.dat') ) REJECT LIMIT UNLIMITED;
The ORGANIZATION EXTERNAL clause provides a definition as an external table. LOCATION is the name of the file which exits in the directory object externaldata. ACCESS PARAMETERS define the fields and attributes in the external file. Views can be created on the table; however data modification operations are not permitted.
Query the external table.
SELECT * from countries_ext
|Q||Referential integrity applies to using multiple tables. Can a list of constraints used with a specific table be generated?|
Use the table all_constraints within SQL code to
retrieve a list of constraints for a specific table.
WHERE r_constraint_name IN
Change tablename to the name of the specific table.
|Q||What are the differences between SHUTDOWN, SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE AND SHUTDOWN ABORT?|
How many categories of data types does Oracle have?
Oracle supports the following categories of data types:
Our organization utilizes the Oracle database; business operations require careful monitoring of the current redo log file? Do you have any recommendations for efficiently performing these tasks? There also is a requiment to monitor database uptime.
A query can be used to provide this information. It will indicate capacity of the current redo log file and a projection as to when the next log file will be archived out.
SELECT le.leseq "Current log sequence No",
100*cp.cpodr_bno/le.lesiz "Percent Full",
cp.cpodr_bno "Current Block No",
le.lesiz "Size of Log in Blocks"
FROM x$kcccp cp, x$kccle le
WHERE le.leseq =cp.cpodr_seq
AND bitand(le.leflg,24) = 8
This code will help in addressing the uptime database issue.
SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
What are the differences between CHAR and NCHAR?
Both CHAR and NCHAR are fixed length character data types.
The differences are:
With the Oracle database,dates can be converted to characters using the TO_CHAR() function:
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') FROM DUAL;
SYSDATE returns the current date 17-MAY-2013
|Q||Do SYS-ED Oracle courses teach migration and interoperability with other databases?|
|A||Our Oracle courses reflect the full range of issues regarding migration and interoperability with other databases: Oracle8 up through Oracle 12c, and DB2 to Oracle. SYS-EDís Oracle training programs can be mixed and matched based upon the existing investment in code and specific operational objectives. There is an examination of the Oracle development tools with the Java standard.|
Will SYS-ED courses help in addressing specific issues relating to multi-vendor / multi-platform issues?
|A||Our Oracle courses teach the database foundation not only for Oracle WLS and OAS, but all the ODBC options - IBM DB2, MS SQL Server, and SAP Sybase.|
|Q||Do we have to take all the courses in a curriculum?|
|A||No. We invite you to review the technology updates, technology assessments, learning paths, and course hand-outs. Our staff will provide a course outline and lesson plan specific to your organization's project specifications and operational objectives. Optionally, we can provide pre and post course validation procedures.|
|Q||We are not a Fortune 1000 company; and it is important that we leverage our existing investment in web server and Internet technologies.|
|A||Since we are vendor-independent; we can provide unbiased information and hands-on demonstrations as part of your assessing whether or not you should take a course. And we offer the just-in-time training to make your operational requirements a reality within a realistic budget.|
|Q||What if I have specific performance optimization and troubleshooting questions which need to be addressed?|
|A||Our Oracle PL/SQL programming courses and training programs incorporate performance optimization. We have the benchmarks, tools, and utilities to resolve your bottlenecks.|