Submit Oracle Questions to Computer Education Techniques CETi

Submit Oracle Questions to Computer Education Techniques

Submit Oracle Questions

Home

Submit Oracle Questions

Technology Update

Knowledge Base


Submit Oracle Questions to Computer Education Techniques CETi Advanced search
     

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?
A 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?
A 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.
  Step 1:
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.

Step 2:
Use a DDL statement for defining the table as its association with the external table.

CREATE TABLE countries_ext (
   state_code        VARCHAR2(2),
   state_name        VARCHAR2(50)
)
ORGANIZATION EXTERNAL (  
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY externaldata
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (       state_code      CHAR(2),
            state_name      CHAR(50)
     )
   )  
   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.  

Step 3:
Query the external table.

For example:

SELECT * from countries_ext

Q Referential integrity applies to using multiple tables.  Can a list of constraints used with a specific table be generated?
A Use the table all_constraints within SQL code to retrieve a list of constraints for a specific table.  

For example:

SELECT *
FROM all_constraints
WHERE r_constraint_name IN
   (SELECT constraint_name
      FROM all_constraints
      WHERE table_name='tablename')

Change tablename to the name of the specific table.
Q What are the differences between SHUTDOWN, SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE AND SHUTDOWN ABORT?
A

SHUTOWN NORMAL is the same as SHUTDOWN. It waits for all sessions to end, without allowing new connections.

SHUTDOWN IMMEDIATE will rollback current transactions and terminate every session.

SHUTDOWN ABORT will abort all the sessions, leaving the database in an inconsistent state. Although the fastest method, it can lead to database corruption.

Q

How many categories of data types does Oracle have?

A

Oracle supports the following categories of data types:

  • Oracle built-in datatypes

  • User-defined types

  • ANSI, DB2, and SQL/DS datatypes

  • Oracle-supplied types


Q

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.

A1

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.

Code:

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

A2

 

This code will help in addressing the uptime database issue.

SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"

   FROM   sys.v_$instance;


Q

What are the differences between CHAR and NCHAR?

A

Both CHAR and NCHAR are fixed length character data types.

The differences are:

  • CHAR's size is specified in bytes by default.
     

  • NCHAR's size is specified in characters by default. A character could be 1 byte to 4 bytes long depending on the character set used. NCHAR stores characters in Unicode.

Guideline:

With the Oracle database,dates can be converted to characters using the TO_CHAR() function:

Example:

           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.

Q

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.

Schedule