Oracle tablespace export expdp - Tips and Restrictions

Introduction

In a previous post, we have explained about Oracle tablespace export using the export data pump utility (expdp).  In this post, we will add more details to provide information on some restrictions and tips using the tablespace export data pump option.

Oracle does not export tablespace metadata

If you unload the tablespace using the tablespace export mode, Oracle does not export the metadata about the tablespace even if the expdp was run by a user with the DATAPUMP_EXP_FULL_DATABASE role.  This is unlike a schema mode export which exports even the schema metadata (schema creation definition, system and role grants to the schema etc.) if you perform such an export using a database user with the DATAPUMP_EXP_FULL_DATABASE role. But there is a workaround to obtain the tablespace metadata as well using the DBMS_METADATA package.

For example, if you want to perform a tablespace export data pump operation on HRTS tablespace, you can save its metadata (tablespace creation definitions) first using the below command.

select dbms_metadata.get_ddl('TABLESPACE','HRTS') from dual;

(Tip: This functions returns output as CLOB datatype, so if you use in SQL*Plus , it may truncate the returned output. One option is to use a bigger output buffer option such as "set long 100000")

Oracle does not export PL/SQL procedures dependent on tables in the tablespace

Tablespace export data pump operation only unloads tables only. Though Oracle mentions in  the utilities guide that the table's dependent objects are also unloaded, this seems to be only for indexes.  If you have a procedure which refers these tables in the tablespace, they are not exported. This could be because procedures are stored in the SYSTEM tablespace and not in the tablespace in question that is being exported out of the database.

Oracle expects tablespaces created already during import data pump operation.

Because Oracle does not export tablespace metadata (tablespace creation definitions), you have to create the tablespaces with adequate size (atleast equal to the source tablespace size) in your destination database.  If your plan is to use different tablespace names, you can use the REMAP_TABLESPACE option in the import data pump (impdp) command. The below example import data pump (impdp) command demonstrates how to import the EMP table only into a HRTS_NEW tablespace in the destination database.

impdp tables=EMP remap_tablespace=(HRTS:HRTS_NEW) dumpfile=DATA_PUMP_DIR:ts_dumps_%U.dmp logfile=DATA_PUMP_DIR:impdp_orders_log.log

Conclusion

Use the tablespace export mode if your sole aim is to only export all tables and their dependent indexes in a tablespace. A related use case for a tablespace export is for taking  a backup of tables and indexes in the tablespace prior to dropping the tablespace.  The examples used in this post are tested on a 11g R2 Oracle database and should work on 12c R1 and 11g R1 databases as well.

ORA-01501: CREATE DATABASE failed ORA-01519 sql.bsq

Introduction

If you are still supporting Oracle 10g R2 databases and are encountering the error ORA-01092: ORACLE instance terminated. Disconnection forced instance termination while running the CREATE DATABASE statement using the Oracle provided syntax in the administrator's guide, then you may be hitting this bug. After further investigation of the alert log file, you may notice the below errors.

ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/sql.bsq' near line 6268
ORA-00604: error occurred at recursive SQL level 1
ORA-02236: invalid file name

Problem

Most Oracle DBAs use the Oracle's example CREATE DATABASE command in the administrator's guide.

There is a documentation bug in Oracle 10g R2 administrator's guide (Part No B14231-02).  Specifically, the CREATE DATABASE statement in Page 77 of the PDF

If you observe closely, these errors are caused by the line "DEFAULT TABLESPACE tbs_1". There is no data file specified for this tablespace in the CREATE DATABASE command.


CREATE DATABASE mynewdb
USER SYS IDENTIFIED BY pz6r58
USER SYSTEM IDENTIFIED BY y1tz5p
LOGFILE GROUP 1 ('/u01/oracle/oradata/mynewdb/redo01.log') SIZE 100M,
GROUP 2 ('/u01/oracle/oradata/mynewdb/redo02.log') SIZE 100M,
GROUP 3 ('/u01/oracle/oradata/mynewdb/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/u01/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE tbs_1
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/oracle/oradata/mynewdb/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/oracle/oradata/mynewdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Solution

Just remove the line DEFAULT TABLESPACE tbs_1 in the CREATE DATABASE statement and retry. This time your database will be created successfully.

How to configure and start VNCServer on Linux

How to configure and start VNCServer on Linux 6

Please follow the below guidelines to successfully configure and start VNCServer service on Linux operating system versions 6.x (Red Hat Enterprise Linux, Oracle Enterprise Linux, CentOS etc.). If your operating system has version 7.x, then please follow this article instead.

1) As root, edit /etc/sysconfig/vncservers and add the below line at the end of the file.
VNCSERVERS=”1:root”

This is to start the vncserver process automatically after a server reboot using root as the vncserver process owner. If you want a different operating system user, then replace the user root with the other operating system user name

2) As root, run the command chkconfig vncserver on


3) Run the vncpasswd command to set the password to connect to the vncserver service.


4) If you want to terminate the vncserver process, execute the vncserver -kill :1.0 command. The :1.0 is the display setting.

alter table set unused versus drop column

Introduction

You may be asked to drop a column from a database table during a business day (or) to be marked as unusable immediately due to a security concern. Once you have approval to drop a column, it may be wise to first set it as unused and only drop it later. A drop column command incurs a lot of database I/O and hence should be avoided during a business day or peak business hour for the database.

Explanation with an example

Let us create 2 tables named test_unused and dropped respectively with 1 million rows and 3 columns named a,b,c in each of them. So these two tables are identical in number of columns and number of rows.

Table creation scripts:

create table scott.test_unused as
select level a,  level+1||'b' b, rpad('c-',mod(level,100),'*') c
from dual
connect by level <= 1e6;

create table scott.dropped as
select level a,  level+1||'b' b, rpad('c-',mod(level,100),'*') c
from dual
connect by level <= 1e6;

Case 1: Set the column as unused first

alter table test_unused set unused (B,C)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.01          0         98          1           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          0        100          1           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 60

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************

Case 2: Drop the column right away

alter table dropped drop (B,C)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     19.96      76.81       9597      14486    2099302           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     19.96      76.81       9597      14486    2099302           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 60

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file scattered read                        235        0.32         22.15
  log buffer space                              126        0.70         21.34
  reliable message                               46        0.99          3.06
  rdbms ipc reply                                62        0.00          0.00
  log file switch completion                      9        0.25          1.52
  db file sequential read                        91        0.28          6.67
  latch: cache buffers chains                     1        0.00          0.00
  log file sync                                   1        0.36          0.36
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       19.39         19.39
********************************************************************************

As you can see, setting a column as UNUSED just marks a column for logical deletion and the columns are no longer available for DML, DDL or SELECT commands. But the data in the columns is still intact in the segment and not dropped.

UNUSED  is much faster and as it doesn't update the data file with the changes. The actual DROP will take place after issuing DROP UNUSED columns. This is quite evident from the elapsed time of 0.1 seconds and wait events.

DROP will drop the column data from the segment right away and therefore you can notice the high elapsed time of 76.81  seconds.

Conclusion

Therefore, if the intent  is to cut-off access to columns without I/O latency in production during peak business hours, then first mark it as UNUSED and then during the weekend or off peak hours, complete the physical drop of the column using DROP UNUSED.

Core Java Programming Training Course

Core Java programming training course

We are excited to inform you that we are all set to start Java training courses.  Melvin Jackson will be the faculty for the core Java Programming training course.  Melvin has 11+ years of strong Java programming experience in the IT industry in USA. He also has prior teaching experience in community colleges in Austin, Texas. This training course will be conducted online (remote lab access will be provided and training will be conducted using whiteboard technology).
 

Also this should be the first course to attend if you want to become a Java developer.  This course will start from fundamentals of core Java programming and takes to advance level of core Java programming.

Click here to watch Melvin Jackson's video profile.

Course Highlights

1) About 40 hours course  for 5 weekends.

2) Training Lab environment will be on Windows 2012 server with JDeveloper tool

3) Saturday and Sunday  - 9:00 am Central Time to 1:30 pm Central Time (with a 30 minute break).

4) DBA University's Training material will be provided.

5) Click here for a FREE DEMO session  (Name and Email only)

6) Click here to register for the online training course

Lab work

1) Each student will get a remote lab computer to practice the course work.  

2) The lab computer will be using Windows 2012 server and JDeveloper tool.

3) 10 hours Remote LAB access on Saturday, Sunday.

4) 5 hours Remote LAB access on weekdays.

Links and Resources

1) Click here to download the core Java programming training course topics and brochure

2) Click here for a FREE DEMO session  (Name and Email only)

3) Click here to register for the online training course

MS SQL Server Training Video Review

We are posting one of the video reviews of our students on MS SQL Server DBA Training in DBA University.  For more videos about DBA University faculty and students, please visit http://video.dbauniversity.com

Computer Science Faculty Jobs DBA University

Introduction

 

We are looking for IT professionals in the USA who have a strong passion for teaching and motivating students with at least 4 years of professional work experience. DBA University is well positioned for fast growth in USA.

Qualifications

• Weekly 10 hour commitment. (Weekends or Weekday evenings)
• Must be a resident in USA or Canada. 
• We have faculty positions open in Microsoft .NET, Sharepoint, Java, Software Testing Tools (Manual, Automation), Oracle E-Business Suite, Big Data,  TIBCO and Oracle OBIEE etc.
• Good opportunity to get experience in innovative e-learning technologies and advanced higher education technology systems. Minimum of 4 years of strong professional work experience the related field

Benefits

• Competitive Pay 
• Long term positions. 
• Good opportunity to get experience in innovative e-learning technologies and advanced higher education technology systems.

Contact Us

Please send us a brief cover letter and resume to info@DBAuniversity.com

Oracle RAC Training

Oracle RAC, ASM and Data Guard Training

We have some exciting news for you. Michelle Malcher will be conducting the Oracle RAC, ASM and Data Guard course. This course will now be offered in the latest Oracle 12c database release from Mar 15th 2014.  Michelle is a well experience Oracle Database Administrator. She is a well known author of several Oracle Press books. Also, she is an Oracle ACE Director (only a few in the world are recognized as ACE Director by Oracle Corporation) and a noted contributor to Oracle Magazine.

Click here to watch Michelle's video about this course.

Course Highlights

1) About 40 hours course  for 5 weeks.

2) Training will contain Oracle RAC, ASM and Data Guard topics in Oracle 12c version.

3) Evenings - Sun, Mon, Tue and Thu  - 7 pm Central Time to 9 pm Central Time

4) DBA University's e-Book will be provided.

5) Click here for a FREE DEMO session  (Name and Email only)

6) Click here to register for the online training course

Lab work

1) For majority of the topics, each student will get a remote lab computer to practice the course work.  

2) The training will be on Oracle Linux 64 bit as the operating system.

3) 10 hours Remote LAB access on Saturday, Sunday

4) 5 hours Remote LAB access on weekdays

Links and Resources

1) Click here to download Oracle RAC, ASM and Data Guard course brochure

2) Click here for a FREE DEMO session  (Name and Email only)

3) Click here to register for the online training course