Error:
[oracle@vmxora9i stage]$ ./Disk1/runInstaller
[oracle@vmxora9i stage]$ Initializing Java Virtual Machine from /tmp/OraInstall2011-02-25_07-15-40PM/jre/bin/java. Please wait...
Error occurred during initialization of VM
Unable to load native library: /tmp/OraInstall2011-02-25_07-15-40PM/jre/lib/i386/libjava.so: symbol __libc_wait, version GLIBC_2.0 not defined in file libc.so.6 with link time reference
Solution:
Download patch 4198954(COMPATIBILITY PACKAGES FOR ORACLE ON RHEL 4) from metalink
>>>> Compatibility files for running Oracle on RHEL 4
>>>>
>>>> compat-oracle-rhel4-1.0-5.i386.rpm
>>>> compat-libcwait-2.1-1.i386.rpm
[root@vmxora9i oracle]# yum install ./compat-oracle-rhel4-1.0-5.i386.rpm ./compat-libcwait-2.1-1.i386.rpm
Setting up Install Process
Setting up repositories
Reading repository metadata in from local files
Parsing package install arguments
Examining ./compat-oracle-rhel4-1.0-5.i386.rpm: compat-oracle-rhel4 - 1.0-5.i386
Marking ./compat-oracle-rhel4-1.0-5.i386.rpm to be installed
Examining ./compat-libcwait-2.1-1.i386.rpm: compat-libcwait - 2.1-1.i386
Marking ./compat-libcwait-2.1-1.i386.rpm to be installed
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Package compat-oracle-rhel4.i386 0:1.0-5 set to be updated
---> Package compat-libcwait.i386 0:2.1-1 set to be updated
--> Running transaction check
--> Processing Dependency: compat-gcc-32 for package: compat-oracle-rhel4
--> Processing Dependency: xorg-x11-deprecated-libs-devel for package: compat-oracle-rhel4
--> Processing Dependency: compat-gcc-32-c++ for package: compat-oracle-rhel4
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Package compat-gcc-32-c++.i386 0:3.2.3-47.3 set to be updated
---> Package xorg-x11-deprecated-libs-devel.i386 0:6.8.2-1.0.1.EL.63 set to be updated
---> Package compat-gcc-32.i386 0:3.2.3-47.3 set to be updated
--> Running transaction check
--> Processing Dependency: xorg-x11-devel = 6.8.2-1.0.1.EL.63 for package: xorg-x11-deprecated-libs-devel
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Package xorg-x11-devel.i386 0:6.8.2-1.0.1.EL.63 set to be updated
--> Running transaction check
--> Processing Dependency: fontconfig-devel >= 2.1 for package: xorg-x11-devel
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Package fontconfig-devel.i386 0:2.2.3-13.el4 set to be updated
--> Running transaction check
--> Processing Dependency: freetype-devel >= 2.1.4 for package: fontconfig-devel
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Package freetype-devel.i386 0:2.1.9-8.el4.6 set to be updated
--> Running transaction check
--> Processing Dependency: zlib-devel for package: freetype-devel
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Package zlib-devel.i386 0:1.2.1.2-1.2 set to be updated
--> Running transaction check
Dependencies Resolved
=============================================================================
Package Arch Version Repository Size
=============================================================================
Installing:
compat-libcwait i386 2.1-1 ./compat-libcwait-2.1-1.i386.rpm 2.7 k
compat-oracle-rhel4 i386 1.0-5 ./compat-oracle-rhel4-1.0-5.i386.rpm 1.8 k
Installing for dependencies:
compat-gcc-32 i386 3.2.3-47.3 el4_u8_base 3.0 M
compat-gcc-32-c++ i386 3.2.3-47.3 el4_u8_base 3.7 M
fontconfig-devel i386 2.2.3-13.el4 el4_u8_base 233 k
freetype-devel i386 2.1.9-8.el4.6 el4_u8_base 533 k
xorg-x11-deprecated-libs-devel i386 6.8.2-1.0.1.EL.63 el4_u8_base 285 k
xorg-x11-devel i386 6.8.2-1.0.1.EL.63 el4_u8_base 5.2 M
zlib-devel i386 1.2.1.2-1.2 el4_u8_base 91 k
Transaction Summary
=============================================================================
Install 9 Package(s)
Update 0 Package(s)
Remove 0 Package(s)
Total download size: 13 M
Is this ok [y/N]: y
Downloading Packages:
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing: compat-gcc-32 ######################### [1/9]
Installing: compat-gcc-32-c++ ######################### [2/9]
Installing: zlib-devel ######################### [3/9]
Installing: freetype-devel ######################### [4/9]
Installing: fontconfig-devel ######################### [5/9]
Installing: xorg-x11-devel ######################### [6/9]
Installing: xorg-x11-deprecated-libs-dev ######################### [7/9]
Installing: compat-oracle-rhel4 ######################### [8/9]
cat: /etc/ld.so.preload: No such file or directory
Installing: compat-libcwait ######################### [9/9]
Installed: compat-libcwait.i386 0:2.1-1 compat-oracle-rhel4.i386 0:1.0-5
Dependency Installed: compat-gcc-32.i386 0:3.2.3-47.3 compat-gcc-32-c++.i386 0:3.2.3-47.3 fontconfig-devel.i386 0:2.2.3-13.el4 freetype-devel.i386 0:2.1.9-8.el4.6 xorg-x11-deprecated-libs-devel.i386 0:6.8.2-1.0.1.EL.63 xorg-x11-devel.i386 0:6.8.2-1.0.1.EL.63 zlib-devel.i386 0:1.2.1.2-1.2
Complete!
[root@vmxora9i oracle]#
Friday, February 25, 2011
Tuesday, February 22, 2011
Demostrate how to concatenate a column value based on another column(s) using wmsys.wm_concat
SQL> desc wmsys.wm_concat
FUNCTION wmsys.wm_concat RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P1 VARCHAR2 IN
SQL> create table t1 (id number, title varchar2(20));
Table created.
SQL> insert into t1 values(1,'Consultant');
1 row created.
SQL> insert into t1 values (1,'Project Manager');
1 row created.
SQL> insert into t1 values (2,'DBA');
1 row created.
SQL> commit;
Commit complete.
SQL> col concat_title for a60
SQL> select id,
2 wmsys.wm_concat(title) concat_title
3 from t1
4 group by id;
ID CONCAT_TITLE
---------- ------------------------------------------------------------
1 Consultant,Project Manager
2 DBA
SQL> select comp_name, version, schema from dba_registry;
COMP_NAME VERSION SCHEMA
--------------------------------------------- ----------- ---------------
OWB 11.2.0.2.0 OWBSYS
Oracle Application Express 3.2.1.00.12 APEX_030200
Oracle Enterprise Manager 11.2.0.2.0 SYSMAN
Spatial 11.2.0.2.0 MDSYS
OLAP Catalog 11.2.0.2.0 OLAPSYS
Oracle OLAP API 11.2.0.2.0 SYS
OLAP Analytic Workspace 11.2.0.2.0 SYS
Oracle Multimedia 11.2.0.2.0 ORDSYS
Oracle Rules Manager 11.2.0.2.0 EXFSYS
Oracle XML Database 11.2.0.2.0 XDB
Oracle Text 11.2.0.2.0 CTXSYS
Oracle Expression Filter 11.2.0.2.0 EXFSYS
Oracle Database Java Packages 11.2.0.2.0 SYS
Oracle XDK 11.2.0.2.0 SYS
JServer JAVA Virtual Machine 11.2.0.2.0 SYS
Oracle Workspace Manager 11.2.0.2.0 WMSYS
Oracle Database Packages and Types 11.2.0.2.0 SYS
Oracle Database Catalog Views 11.2.0.2.0 SYS
18 rows selected.
Sunday, February 20, 2011
ORA-04043: object XDB_DATASTORE_PROC does not exist
Sympton:
This error occurred during upgrade from 11.2.0.1 to 11.2.0.2, according ly MOS note 360907.1, it applies to upgrade from 10.2.0.x, 11.1.0.x and 11.2.0.1 upgrading to 11.2.0.2.
Causes:
XML Database (XDB) installed, but Oracle Text was not installed.
Impact:
It can be ignored if Oracle Text is not in use, otherwise configure Oracle Text and execute script "dbmsxdbt.sql" as sysdba.
This error occurred during upgrade from 11.2.0.1 to 11.2.0.2, according ly MOS note 360907.1, it applies to upgrade from 10.2.0.x, 11.1.0.x and 11.2.0.1 upgrading to 11.2.0.2.
Causes:
XML Database (XDB) installed, but Oracle Text was not installed.
Impact:
It can be ignored if Oracle Text is not in use, otherwise configure Oracle Text and execute script "dbmsxdbt.sql" as sysdba.
Saturday, February 19, 2011
Installing un-supported Linux on VMware (I was trying to install RHEL 6, which is too new to VMware)
The installation of VMware Tools 8.4.5 build-324285 for Linux completed
successfully. You can decide to remove this software from your system at any
time by invoking the following command: "/usr/bin/vmware-uninstall-tools.pl".
Before running VMware Tools for the first time, you need to configure it by
invoking the following command: "/usr/bin/vmware-config-tools.pl". Do you want
this program to invoke the command for you now? [yes]
Initializing...
Making sure services for VMware Tools are stopped.
The module vmmemctl has already been installed on this system by another
installer or package and will not be modified by this installer. Use the flag
--clobber-kernel-modules=vmmemctl to override.
Before you can compile modules, you need to have the following installed...
make
gcc
kernel headers of the running kernel
Searching for GCC...
Detected GCC binary at "/usr/bin/gcc".
The path "/usr/bin/gcc" appears to be a valid path to the gcc binary.
Would you like to change it? [no]
Searching for a valid kernel header path...
The path "" is not valid.
Would you like to change it? [yes]
What is the location of the directory of C header files that match your running
kernel?
The path "" is not valid.
Would you like to change it? [yes]
[root@vmxdb01 ~]# rpm -Uhv /media/OL6.0\ i386\ Disc\ 1\ 20110203/Packages/kernel-devel-2.6.32-71.el6.i686.rpm
Preparing... ########################################### [100%]
1:kernel-devel ########################################### [100%]
[root@vmxdb01 ~]#
[root@vmxdb01 vmware-tools-distrib]# /usr/bin/vmware-config-tools.pl
Initializing...
Making sure services for VMware Tools are stopped.
vmware-tools stop/waiting
The module vmmemctl has already been installed on this system by another
installer or package and will not be modified by this installer. Use the flag
--clobber-kernel-modules=vmmemctl to override.
Before you can compile modules, you need to have the following installed...
make
gcc
kernel headers of the running kernel
Searching for GCC...
Detected GCC binary at "/usr/bin/gcc".
The path "/usr/bin/gcc" appears to be a valid path to the gcc binary.
Would you like to change it? [no]
Searching for a valid kernel header path...
Detected the kernel headers of the running kernel at
"/lib/modules/2.6.32-71.el6.i686/build/include".
The path "/lib/modules/2.6.32-71.el6.i686/build/include" appears to be a valid
path to the kernel headers of the running kernel.
Would you like to change it? [no]
Using 2.6.x kernel build system.
make: Entering directory `/tmp/vmware-root/modules/vmhgfs-only'
make -C /lib/modules/2.6.32-71.el6.i686/build/include/.. SUBDIRS=$PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= modules
make[1]: Entering directory `/usr/src/kernels/2.6.32-71.el6.i686'
CC [M] /tmp/vmware-root/modules/vmhgfs-only/backdoor.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/backdoorGcc32.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/bdhandler.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/cpName.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/cpNameLinux.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/cpNameLite.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/dentry.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/dir.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/file.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/filesystem.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/fsutil.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/hgfsBd.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/hgfsEscape.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/hgfsUtil.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/inode.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/kernelStubsLinux.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/link.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/message.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/messageBackdoor.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/module.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/page.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/request.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/rpcout.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/stubs.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/super.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/tcp.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/transport.o
LD [M] /tmp/vmware-root/modules/vmhgfs-only/vmhgfs.o
Building modules, stage 2.
MODPOST 1 modules
CC /tmp/vmware-root/modules/vmhgfs-only/vmhgfs.mod.o
LD [M] /tmp/vmware-root/modules/vmhgfs-only/vmhgfs.ko.unsigned
NO SIGN [M] /tmp/vmware-root/modules/vmhgfs-only/vmhgfs.ko
make[1]: Leaving directory `/usr/src/kernels/2.6.32-71.el6.i686'
make -C $PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= postbuild
make[1]: Entering directory `/tmp/vmware-root/modules/vmhgfs-only'
make[1]: `postbuild' is up to date.
make[1]: Leaving directory `/tmp/vmware-root/modules/vmhgfs-only'
cp -f vmhgfs.ko ./../vmhgfs.o
make: Leaving directory `/tmp/vmware-root/modules/vmhgfs-only'
Using 2.6.x kernel build system.
make: Entering directory `/tmp/vmware-root/modules/vmxnet-only'
make -C /lib/modules/2.6.32-71.el6.i686/build/include/.. SUBDIRS=$PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= modules
make[1]: Entering directory `/usr/src/kernels/2.6.32-71.el6.i686'
CC [M] /tmp/vmware-root/modules/vmxnet-only/vmxnet.o
Building modules, stage 2.
MODPOST 1 modules
CC /tmp/vmware-root/modules/vmxnet-only/vmxnet.mod.o
LD [M] /tmp/vmware-root/modules/vmxnet-only/vmxnet.ko.unsigned
NO SIGN [M] /tmp/vmware-root/modules/vmxnet-only/vmxnet.ko
make[1]: Leaving directory `/usr/src/kernels/2.6.32-71.el6.i686'
make -C $PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= postbuild
make[1]: Entering directory `/tmp/vmware-root/modules/vmxnet-only'
make[1]: `postbuild' is up to date.
make[1]: Leaving directory `/tmp/vmware-root/modules/vmxnet-only'
cp -f vmxnet.ko ./../vmxnet.o
make: Leaving directory `/tmp/vmware-root/modules/vmxnet-only'
Using 2.6.x kernel build system.
make: Entering directory `/tmp/vmware-root/modules/vmblock-only'
make -C /lib/modules/2.6.32-71.el6.i686/build/include/.. SUBDIRS=$PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= modules
make[1]: Entering directory `/usr/src/kernels/2.6.32-71.el6.i686'
CC [M] /tmp/vmware-root/modules/vmblock-only/linux/block.o
CC [M] /tmp/vmware-root/modules/vmblock-only/linux/control.o
CC [M] /tmp/vmware-root/modules/vmblock-only/linux/dbllnklst.o
CC [M] /tmp/vmware-root/modules/vmblock-only/linux/dentry.o
CC [M] /tmp/vmware-root/modules/vmblock-only/linux/file.o
CC [M] /tmp/vmware-root/modules/vmblock-only/linux/filesystem.o
CC [M] /tmp/vmware-root/modules/vmblock-only/linux/inode.o
CC [M] /tmp/vmware-root/modules/vmblock-only/linux/module.o
CC [M] /tmp/vmware-root/modules/vmblock-only/linux/stubs.o
CC [M] /tmp/vmware-root/modules/vmblock-only/linux/super.o
LD [M] /tmp/vmware-root/modules/vmblock-only/vmblock.o
Building modules, stage 2.
MODPOST 1 modules
CC /tmp/vmware-root/modules/vmblock-only/vmblock.mod.o
LD [M] /tmp/vmware-root/modules/vmblock-only/vmblock.ko.unsigned
NO SIGN [M] /tmp/vmware-root/modules/vmblock-only/vmblock.ko
make[1]: Leaving directory `/usr/src/kernels/2.6.32-71.el6.i686'
make -C $PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= postbuild
make[1]: Entering directory `/tmp/vmware-root/modules/vmblock-only'
make[1]: `postbuild' is up to date.
make[1]: Leaving directory `/tmp/vmware-root/modules/vmblock-only'
cp -f vmblock.ko ./../vmblock.o
make: Leaving directory `/tmp/vmware-root/modules/vmblock-only'
Using 2.6.x kernel build system.
make: Entering directory `/tmp/vmware-root/modules/vmci-only'
make -C /lib/modules/2.6.32-71.el6.i686/build/include/.. SUBDIRS=$PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= modules
make[1]: Entering directory `/usr/src/kernels/2.6.32-71.el6.i686'
CC [M] /tmp/vmware-root/modules/vmci-only/kernelStubsLinux.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciDatagram.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciEvent.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciGuestDs.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciGuestKernelIf.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciKernelIf.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciProcess.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciQueuePair.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciUtil.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmci_drv.o
LD [M] /tmp/vmware-root/modules/vmci-only/vmci.o
Building modules, stage 2.
MODPOST 1 modules
CC /tmp/vmware-root/modules/vmci-only/vmci.mod.o
LD [M] /tmp/vmware-root/modules/vmci-only/vmci.ko.unsigned
NO SIGN [M] /tmp/vmware-root/modules/vmci-only/vmci.ko
make[1]: Leaving directory `/usr/src/kernels/2.6.32-71.el6.i686'
make -C $PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= postbuild
make[1]: Entering directory `/tmp/vmware-root/modules/vmci-only'
make[1]: `postbuild' is up to date.
make[1]: Leaving directory `/tmp/vmware-root/modules/vmci-only'
cp -f vmci.ko ./../vmci.o
make: Leaving directory `/tmp/vmware-root/modules/vmci-only'
Using 2.6.x kernel build system.
make: Entering directory `/tmp/vmware-root/modules/vmci-only'
make -C /lib/modules/2.6.32-71.el6.i686/build/include/.. SUBDIRS=$PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= modules
make[1]: Entering directory `/usr/src/kernels/2.6.32-71.el6.i686'
CC [M] /tmp/vmware-root/modules/vmci-only/kernelStubsLinux.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciDatagram.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciEvent.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciGuestDs.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciGuestKernelIf.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciKernelIf.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciProcess.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciQueuePair.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciUtil.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmci_drv.o
LD [M] /tmp/vmware-root/modules/vmci-only/vmci.o
Building modules, stage 2.
MODPOST 1 modules
CC /tmp/vmware-root/modules/vmci-only/vmci.mod.o
LD [M] /tmp/vmware-root/modules/vmci-only/vmci.ko.unsigned
NO SIGN [M] /tmp/vmware-root/modules/vmci-only/vmci.ko
make[1]: Leaving directory `/usr/src/kernels/2.6.32-71.el6.i686'
make -C $PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= postbuild
make[1]: Entering directory `/tmp/vmware-root/modules/vmci-only'
make[1]: `postbuild' is up to date.
make[1]: Leaving directory `/tmp/vmware-root/modules/vmci-only'
cp -f vmci.ko ./../vmci.o
make: Leaving directory `/tmp/vmware-root/modules/vmci-only'
Using 2.6.x kernel build system.
make: Entering directory `/tmp/vmware-root/modules/vsock-only'
make -C /lib/modules/2.6.32-71.el6.i686/build/include/.. SUBDIRS=$PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= modules
make[1]: Entering directory `/usr/src/kernels/2.6.32-71.el6.i686'
CC [M] /tmp/vmware-root/modules/vsock-only/linux/af_vsock.o
CC [M] /tmp/vmware-root/modules/vsock-only/linux/notify.o
CC [M] /tmp/vmware-root/modules/vsock-only/linux/stats.o
CC [M] /tmp/vmware-root/modules/vsock-only/linux/util.o
CC [M] /tmp/vmware-root/modules/vsock-only/linux/vsockAddr.o
CC [M] /tmp/vmware-root/modules/vsock-only/driverLog.o
LD [M] /tmp/vmware-root/modules/vsock-only/vsock.o
Building modules, stage 2.
MODPOST 1 modules
CC /tmp/vmware-root/modules/vsock-only/vsock.mod.o
LD [M] /tmp/vmware-root/modules/vsock-only/vsock.ko.unsigned
NO SIGN [M] /tmp/vmware-root/modules/vsock-only/vsock.ko
make[1]: Leaving directory `/usr/src/kernels/2.6.32-71.el6.i686'
make -C $PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= postbuild
make[1]: Entering directory `/tmp/vmware-root/modules/vsock-only'
make[1]: `postbuild' is up to date.
make[1]: Leaving directory `/tmp/vmware-root/modules/vsock-only'
cp -f vsock.ko ./../vsock.o
make: Leaving directory `/tmp/vmware-root/modules/vsock-only'
The module vmxnet3 has already been installed on this system by another
installer or package and will not be modified by this installer. Use the flag
--clobber-kernel-modules=vmxnet3 to override.
The module pvscsi has already been installed on this system by another
installer or package and will not be modified by this installer. Use the flag
--clobber-kernel-modules=pvscsi to override.
Disabling timer-based audio scheduling in pulseaudio.
Detected Xorg X server version 1.7.7.
This program previously created the file /usr/libexec/hal-probe-vmmouse, and
was about to remove it. Somebody else apparently did it already.
hal: unrecognized service
X.Org X Server 1.7.7
Release Date: 2010-05-04
X Protocol Version 11, Revision 0
Build Operating System: ca-build19 2.6.31.5-127.fc12.x86_64
Current Operating System: Linux vmxdb01.lab.dbaglobe.com 2.6.32-71.el6.i686 #1 SMP Wed Dec 15 09:50:18 EST 2010 i686
Kernel command line: ro root=/dev/mapper/vg_vmxdb01-lv_root rd_LVM_LV=vg_vmxdb01/lv_root rd_LVM_LV=vg_vmxdb01/lv_swap rd_NO_LUKS rd_NO_MD rd_NO_DM LANG=en_US.UTF-8 SYSFONT=latarcyrheb-sun16 KEYBOARDTYPE=pc KEYTABLE=us rhgb quiet
Build Date: 20 November 2010 04:27:20PM
Build ID: xorg-x11-server 1.7.7-26.el6
Current version of pixman: 0.16.6
Before reporting problems, check https://www.redhat.com/apps/support/
to make sure that you have the latest version.
Markers: (--) probed, (**) from config file, (==) default setting,
(++) from command line, (!!) notice, (II) informational,
(WW) warning, (EE) error, (NI) not implemented, (??) unknown.
(++) Log file: "/tmp/vmware-config0/XF86ConfigLog.17402", Time: Sat Feb 19 21:42:25 2011
(==) Using default built-in configuration (30 lines)
(EE) Failed to load module "vmwgfx" (module does not exist, 0)
(EE) vmware: Please ignore the above warnings about not being able to to load module/driver vmwgfx
(EE) open /dev/fb0: No such device
error setting MTRR (base = 0xd0000000, size = 0x01600000, type = 1) Invalid argument (22)
X is running fine with the new config file.
error setting MTRR (base = 0xd0000000, size = 0x01600000, type = 1) Invalid argument (22)
Creating a new initrd boot image for the kernel.
vmware-tools start/running
The configuration of VMware Tools 8.4.5 build-324285 for Linux for this running
kernel completed successfully.
You must restart your X session before any mouse or graphics changes take
effect.
You can now run VMware Tools by invoking "/usr/bin/vmware-toolbox-cmd" from the
command line or by invoking "/usr/bin/vmware-toolbox" from the command line
during an X server session.
To enable advanced X features (e.g., guest resolution fit, drag and drop, and
file and text copy/paste), you will need to do one (or more) of the following:
1. Manually start /usr/bin/vmware-user
2. Log out and log back into your desktop session; and,
3. Restart your X session.
To use the vmxnet driver, restart networking using the following commands:
/etc/init.d/network stop
rmmod pcnet32
rmmod vmxnet
modprobe vmxnet
/etc/init.d/network start
Enjoy,
--the VMware team
[root@vmxdb01 vmware-tools-distrib]#
successfully. You can decide to remove this software from your system at any
time by invoking the following command: "/usr/bin/vmware-uninstall-tools.pl".
Before running VMware Tools for the first time, you need to configure it by
invoking the following command: "/usr/bin/vmware-config-tools.pl". Do you want
this program to invoke the command for you now? [yes]
Initializing...
Making sure services for VMware Tools are stopped.
The module vmmemctl has already been installed on this system by another
installer or package and will not be modified by this installer. Use the flag
--clobber-kernel-modules=vmmemctl to override.
Before you can compile modules, you need to have the following installed...
make
gcc
kernel headers of the running kernel
Searching for GCC...
Detected GCC binary at "/usr/bin/gcc".
The path "/usr/bin/gcc" appears to be a valid path to the gcc binary.
Would you like to change it? [no]
Searching for a valid kernel header path...
The path "" is not valid.
Would you like to change it? [yes]
What is the location of the directory of C header files that match your running
kernel?
The path "" is not valid.
Would you like to change it? [yes]
[root@vmxdb01 ~]# rpm -Uhv /media/OL6.0\ i386\ Disc\ 1\ 20110203/Packages/kernel-devel-2.6.32-71.el6.i686.rpm
Preparing... ########################################### [100%]
1:kernel-devel ########################################### [100%]
[root@vmxdb01 ~]#
[root@vmxdb01 vmware-tools-distrib]# /usr/bin/vmware-config-tools.pl
Initializing...
Making sure services for VMware Tools are stopped.
vmware-tools stop/waiting
The module vmmemctl has already been installed on this system by another
installer or package and will not be modified by this installer. Use the flag
--clobber-kernel-modules=vmmemctl to override.
Before you can compile modules, you need to have the following installed...
make
gcc
kernel headers of the running kernel
Searching for GCC...
Detected GCC binary at "/usr/bin/gcc".
The path "/usr/bin/gcc" appears to be a valid path to the gcc binary.
Would you like to change it? [no]
Searching for a valid kernel header path...
Detected the kernel headers of the running kernel at
"/lib/modules/2.6.32-71.el6.i686/build/include".
The path "/lib/modules/2.6.32-71.el6.i686/build/include" appears to be a valid
path to the kernel headers of the running kernel.
Would you like to change it? [no]
Using 2.6.x kernel build system.
make: Entering directory `/tmp/vmware-root/modules/vmhgfs-only'
make -C /lib/modules/2.6.32-71.el6.i686/build/include/.. SUBDIRS=$PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= modules
make[1]: Entering directory `/usr/src/kernels/2.6.32-71.el6.i686'
CC [M] /tmp/vmware-root/modules/vmhgfs-only/backdoor.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/backdoorGcc32.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/bdhandler.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/cpName.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/cpNameLinux.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/cpNameLite.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/dentry.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/dir.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/file.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/filesystem.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/fsutil.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/hgfsBd.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/hgfsEscape.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/hgfsUtil.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/inode.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/kernelStubsLinux.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/link.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/message.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/messageBackdoor.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/module.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/page.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/request.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/rpcout.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/stubs.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/super.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/tcp.o
CC [M] /tmp/vmware-root/modules/vmhgfs-only/transport.o
LD [M] /tmp/vmware-root/modules/vmhgfs-only/vmhgfs.o
Building modules, stage 2.
MODPOST 1 modules
CC /tmp/vmware-root/modules/vmhgfs-only/vmhgfs.mod.o
LD [M] /tmp/vmware-root/modules/vmhgfs-only/vmhgfs.ko.unsigned
NO SIGN [M] /tmp/vmware-root/modules/vmhgfs-only/vmhgfs.ko
make[1]: Leaving directory `/usr/src/kernels/2.6.32-71.el6.i686'
make -C $PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= postbuild
make[1]: Entering directory `/tmp/vmware-root/modules/vmhgfs-only'
make[1]: `postbuild' is up to date.
make[1]: Leaving directory `/tmp/vmware-root/modules/vmhgfs-only'
cp -f vmhgfs.ko ./../vmhgfs.o
make: Leaving directory `/tmp/vmware-root/modules/vmhgfs-only'
Using 2.6.x kernel build system.
make: Entering directory `/tmp/vmware-root/modules/vmxnet-only'
make -C /lib/modules/2.6.32-71.el6.i686/build/include/.. SUBDIRS=$PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= modules
make[1]: Entering directory `/usr/src/kernels/2.6.32-71.el6.i686'
CC [M] /tmp/vmware-root/modules/vmxnet-only/vmxnet.o
Building modules, stage 2.
MODPOST 1 modules
CC /tmp/vmware-root/modules/vmxnet-only/vmxnet.mod.o
LD [M] /tmp/vmware-root/modules/vmxnet-only/vmxnet.ko.unsigned
NO SIGN [M] /tmp/vmware-root/modules/vmxnet-only/vmxnet.ko
make[1]: Leaving directory `/usr/src/kernels/2.6.32-71.el6.i686'
make -C $PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= postbuild
make[1]: Entering directory `/tmp/vmware-root/modules/vmxnet-only'
make[1]: `postbuild' is up to date.
make[1]: Leaving directory `/tmp/vmware-root/modules/vmxnet-only'
cp -f vmxnet.ko ./../vmxnet.o
make: Leaving directory `/tmp/vmware-root/modules/vmxnet-only'
Using 2.6.x kernel build system.
make: Entering directory `/tmp/vmware-root/modules/vmblock-only'
make -C /lib/modules/2.6.32-71.el6.i686/build/include/.. SUBDIRS=$PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= modules
make[1]: Entering directory `/usr/src/kernels/2.6.32-71.el6.i686'
CC [M] /tmp/vmware-root/modules/vmblock-only/linux/block.o
CC [M] /tmp/vmware-root/modules/vmblock-only/linux/control.o
CC [M] /tmp/vmware-root/modules/vmblock-only/linux/dbllnklst.o
CC [M] /tmp/vmware-root/modules/vmblock-only/linux/dentry.o
CC [M] /tmp/vmware-root/modules/vmblock-only/linux/file.o
CC [M] /tmp/vmware-root/modules/vmblock-only/linux/filesystem.o
CC [M] /tmp/vmware-root/modules/vmblock-only/linux/inode.o
CC [M] /tmp/vmware-root/modules/vmblock-only/linux/module.o
CC [M] /tmp/vmware-root/modules/vmblock-only/linux/stubs.o
CC [M] /tmp/vmware-root/modules/vmblock-only/linux/super.o
LD [M] /tmp/vmware-root/modules/vmblock-only/vmblock.o
Building modules, stage 2.
MODPOST 1 modules
CC /tmp/vmware-root/modules/vmblock-only/vmblock.mod.o
LD [M] /tmp/vmware-root/modules/vmblock-only/vmblock.ko.unsigned
NO SIGN [M] /tmp/vmware-root/modules/vmblock-only/vmblock.ko
make[1]: Leaving directory `/usr/src/kernels/2.6.32-71.el6.i686'
make -C $PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= postbuild
make[1]: Entering directory `/tmp/vmware-root/modules/vmblock-only'
make[1]: `postbuild' is up to date.
make[1]: Leaving directory `/tmp/vmware-root/modules/vmblock-only'
cp -f vmblock.ko ./../vmblock.o
make: Leaving directory `/tmp/vmware-root/modules/vmblock-only'
Using 2.6.x kernel build system.
make: Entering directory `/tmp/vmware-root/modules/vmci-only'
make -C /lib/modules/2.6.32-71.el6.i686/build/include/.. SUBDIRS=$PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= modules
make[1]: Entering directory `/usr/src/kernels/2.6.32-71.el6.i686'
CC [M] /tmp/vmware-root/modules/vmci-only/kernelStubsLinux.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciDatagram.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciEvent.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciGuestDs.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciGuestKernelIf.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciKernelIf.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciProcess.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciQueuePair.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciUtil.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmci_drv.o
LD [M] /tmp/vmware-root/modules/vmci-only/vmci.o
Building modules, stage 2.
MODPOST 1 modules
CC /tmp/vmware-root/modules/vmci-only/vmci.mod.o
LD [M] /tmp/vmware-root/modules/vmci-only/vmci.ko.unsigned
NO SIGN [M] /tmp/vmware-root/modules/vmci-only/vmci.ko
make[1]: Leaving directory `/usr/src/kernels/2.6.32-71.el6.i686'
make -C $PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= postbuild
make[1]: Entering directory `/tmp/vmware-root/modules/vmci-only'
make[1]: `postbuild' is up to date.
make[1]: Leaving directory `/tmp/vmware-root/modules/vmci-only'
cp -f vmci.ko ./../vmci.o
make: Leaving directory `/tmp/vmware-root/modules/vmci-only'
Using 2.6.x kernel build system.
make: Entering directory `/tmp/vmware-root/modules/vmci-only'
make -C /lib/modules/2.6.32-71.el6.i686/build/include/.. SUBDIRS=$PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= modules
make[1]: Entering directory `/usr/src/kernels/2.6.32-71.el6.i686'
CC [M] /tmp/vmware-root/modules/vmci-only/kernelStubsLinux.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciDatagram.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciEvent.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciGuestDs.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciGuestKernelIf.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciKernelIf.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciProcess.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciQueuePair.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmciUtil.o
CC [M] /tmp/vmware-root/modules/vmci-only/vmci_drv.o
LD [M] /tmp/vmware-root/modules/vmci-only/vmci.o
Building modules, stage 2.
MODPOST 1 modules
CC /tmp/vmware-root/modules/vmci-only/vmci.mod.o
LD [M] /tmp/vmware-root/modules/vmci-only/vmci.ko.unsigned
NO SIGN [M] /tmp/vmware-root/modules/vmci-only/vmci.ko
make[1]: Leaving directory `/usr/src/kernels/2.6.32-71.el6.i686'
make -C $PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= postbuild
make[1]: Entering directory `/tmp/vmware-root/modules/vmci-only'
make[1]: `postbuild' is up to date.
make[1]: Leaving directory `/tmp/vmware-root/modules/vmci-only'
cp -f vmci.ko ./../vmci.o
make: Leaving directory `/tmp/vmware-root/modules/vmci-only'
Using 2.6.x kernel build system.
make: Entering directory `/tmp/vmware-root/modules/vsock-only'
make -C /lib/modules/2.6.32-71.el6.i686/build/include/.. SUBDIRS=$PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= modules
make[1]: Entering directory `/usr/src/kernels/2.6.32-71.el6.i686'
CC [M] /tmp/vmware-root/modules/vsock-only/linux/af_vsock.o
CC [M] /tmp/vmware-root/modules/vsock-only/linux/notify.o
CC [M] /tmp/vmware-root/modules/vsock-only/linux/stats.o
CC [M] /tmp/vmware-root/modules/vsock-only/linux/util.o
CC [M] /tmp/vmware-root/modules/vsock-only/linux/vsockAddr.o
CC [M] /tmp/vmware-root/modules/vsock-only/driverLog.o
LD [M] /tmp/vmware-root/modules/vsock-only/vsock.o
Building modules, stage 2.
MODPOST 1 modules
CC /tmp/vmware-root/modules/vsock-only/vsock.mod.o
LD [M] /tmp/vmware-root/modules/vsock-only/vsock.ko.unsigned
NO SIGN [M] /tmp/vmware-root/modules/vsock-only/vsock.ko
make[1]: Leaving directory `/usr/src/kernels/2.6.32-71.el6.i686'
make -C $PWD SRCROOT=$PWD/. \
MODULEBUILDDIR= postbuild
make[1]: Entering directory `/tmp/vmware-root/modules/vsock-only'
make[1]: `postbuild' is up to date.
make[1]: Leaving directory `/tmp/vmware-root/modules/vsock-only'
cp -f vsock.ko ./../vsock.o
make: Leaving directory `/tmp/vmware-root/modules/vsock-only'
The module vmxnet3 has already been installed on this system by another
installer or package and will not be modified by this installer. Use the flag
--clobber-kernel-modules=vmxnet3 to override.
The module pvscsi has already been installed on this system by another
installer or package and will not be modified by this installer. Use the flag
--clobber-kernel-modules=pvscsi to override.
Disabling timer-based audio scheduling in pulseaudio.
Detected Xorg X server version 1.7.7.
This program previously created the file /usr/libexec/hal-probe-vmmouse, and
was about to remove it. Somebody else apparently did it already.
hal: unrecognized service
X.Org X Server 1.7.7
Release Date: 2010-05-04
X Protocol Version 11, Revision 0
Build Operating System: ca-build19 2.6.31.5-127.fc12.x86_64
Current Operating System: Linux vmxdb01.lab.dbaglobe.com 2.6.32-71.el6.i686 #1 SMP Wed Dec 15 09:50:18 EST 2010 i686
Kernel command line: ro root=/dev/mapper/vg_vmxdb01-lv_root rd_LVM_LV=vg_vmxdb01/lv_root rd_LVM_LV=vg_vmxdb01/lv_swap rd_NO_LUKS rd_NO_MD rd_NO_DM LANG=en_US.UTF-8 SYSFONT=latarcyrheb-sun16 KEYBOARDTYPE=pc KEYTABLE=us rhgb quiet
Build Date: 20 November 2010 04:27:20PM
Build ID: xorg-x11-server 1.7.7-26.el6
Current version of pixman: 0.16.6
Before reporting problems, check https://www.redhat.com/apps/support/
to make sure that you have the latest version.
Markers: (--) probed, (**) from config file, (==) default setting,
(++) from command line, (!!) notice, (II) informational,
(WW) warning, (EE) error, (NI) not implemented, (??) unknown.
(++) Log file: "/tmp/vmware-config0/XF86ConfigLog.17402", Time: Sat Feb 19 21:42:25 2011
(==) Using default built-in configuration (30 lines)
(EE) Failed to load module "vmwgfx" (module does not exist, 0)
(EE) vmware: Please ignore the above warnings about not being able to to load module/driver vmwgfx
(EE) open /dev/fb0: No such device
error setting MTRR (base = 0xd0000000, size = 0x01600000, type = 1) Invalid argument (22)
X is running fine with the new config file.
error setting MTRR (base = 0xd0000000, size = 0x01600000, type = 1) Invalid argument (22)
Creating a new initrd boot image for the kernel.
vmware-tools start/running
The configuration of VMware Tools 8.4.5 build-324285 for Linux for this running
kernel completed successfully.
You must restart your X session before any mouse or graphics changes take
effect.
You can now run VMware Tools by invoking "/usr/bin/vmware-toolbox-cmd" from the
command line or by invoking "/usr/bin/vmware-toolbox" from the command line
during an X server session.
To enable advanced X features (e.g., guest resolution fit, drag and drop, and
file and text copy/paste), you will need to do one (or more) of the following:
1. Manually start /usr/bin/vmware-user
2. Log out and log back into your desktop session; and,
3. Restart your X session.
To use the vmxnet driver, restart networking using the following commands:
/etc/init.d/network stop
rmmod pcnet32
rmmod vmxnet
modprobe vmxnet
/etc/init.d/network start
Enjoy,
--the VMware team
[root@vmxdb01 vmware-tools-distrib]#
Wednesday, February 16, 2011
Oracle: Local partitioned index and partition pruning (Performance Tuning)
drop table tbl_txn purge;
create table tbl_txn (
txn_id number,
txn_type char(20),
col1 char(20),
col2 char(20),
col3 char(20),
txn_tms timestamp)
partition by range (txn_tms) (
partition p20110215 values less than (to_date('20110216','yyyymmdd')) tablespace parttbs,
partition p20110216 values less than (to_date('20110217','yyyymmdd')) tablespace parttbs,
partition p20110217 values less than (to_date('20110218','yyyymmdd')) tablespace parttbs,
partition p20110218 values less than (to_date('20110219','yyyymmdd')) tablespace parttbs,
partition p20110219 values less than (to_date('20110220','yyyymmdd')) tablespace parttbs)
/
declare
cdate date;
c number;
cchar char(20);
begin
cdate := to_date('20110215','yyyymmdd');
c := 1;
while cdate < to_date('20110220','yyyymmdd')
loop
select case mod(c,3) when 1 then 'A' when 2 then 'B' when 3 then 'C' else 'D' end
into cchar
from dual;
insert into tbl_txn values (c,cchar,'dummy','dummy','dummy',cdate);
if mod(c,1000) = 0 then
commit;
end if;
c := c+1;
cdate := cdate+(1/(60*60*24));
end loop;
end;
/
alter table tbl_txn add constraint tbl_txn_pk primary key (txn_id);
create index tbl_txn_n1 on tbl_txn(txn_type) local;
exec dbms_stats.gather_table_stats(null,'TBL_TXN')
SQL> drop table tbl_txn purge;
Table dropped.
Elapsed: 00:00:00.24
SQL>
SQL> create table tbl_txn (
2 txn_id number,
3 txn_type char(20),
4 col1 char(20),
5 col2 char(20),
6 col3 char(20),
7 txn_tms timestamp)
8 partition by range (txn_tms) (
9 partition p20110215 values less than (to_date('20110216','yyyymmdd')) tablespace parttbs,
10 partition p20110216 values less than (to_date('20110217','yyyymmdd')) tablespace parttbs,
11 partition p20110217 values less than (to_date('20110218','yyyymmdd')) tablespace parttbs,
12 partition p20110218 values less than (to_date('20110219','yyyymmdd')) tablespace parttbs,
13 partition p20110219 values less than (to_date('20110220','yyyymmdd')) tablespace parttbs)
14 /
Table created.
Elapsed: 00:00:00.06
SQL>
SQL>
SQL> declare
2 cdate date;
3 c number;
4 cchar char(20);
5 begin
6 cdate := to_date('20110215','yyyymmdd');
7 c := 1;
8 while cdate < to_date('20110220','yyyymmdd')
9 loop
10 select case mod(c,3) when 1 then 'A' when 2 then 'B' when 3 then 'C' else 'D' end
11 into cchar
12 from dual;
13 insert into tbl_txn values (c,cchar,'dummy','dummy','dummy',cdate);
14 if mod(c,1000) = 0 then
15 commit;
16 end if;
17 c := c+1;
18 cdate := cdate+(1/(60*60*24));
19 end loop;
20 end;
21 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:46.61
SQL>
SQL> alter table tbl_txn add constraint tbl_txn_pk primary key (txn_id);
Table altered.
Elapsed: 00:00:01.08
SQL>
SQL> create index tbl_txn_n1 on tbl_txn(txn_type) local;
Index created.
Elapsed: 00:00:03.48
SQL>
SQL> exec dbms_stats.gather_table_stats(null,'TBL_TXN')
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.07
SQL> select segment_name,partition_name,bytes from user_segments where segment_name='TBL_TXN';
SEGMENT_NAME PARTITION_NAME BYTES
-------------------- -------------------- ----------
TBL_TXN P20110215 10485760
TBL_TXN P20110216 10485760
TBL_TXN P20110217 10485760
TBL_TXN P20110218 10485760
TBL_TXN P20110219 10485760
Elapsed: 00:00:00.07
SQL> set lin 120
SQL> select txn_type, count(*) from tbl_txn
2 where txn_tms >=to_date('20110219','yyyymmdd')
3 group by txn_type;
Execution Plan
----------------------------------------------------------
Plan hash value: 820060006
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 96 | 291 (6)| 00:00:04 | | |
| 1 | HASH GROUP BY | | 3 | 96 | 291 (6)| 00:00:04 | | |
| 2 | PARTITION RANGE SINGLE| | 86400 | 2700K| 281 (2)| 00:00:04 | 5 | 5 |
| 3 | TABLE ACCESS FULL | TBL_TXN | 86400 | 2700K| 281 (2)| 00:00:04 | 5 | 5 |
---------------------------------------------------------------------------------------------------
SQL> select txn_type, count(*) from tbl_txn
2 where txn_tms >=to_date('20110218','yyyymmdd')
3 group by txn_type;
Execution Plan
----------------------------------------------------------
Plan hash value: 898817563
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 96 | 579 (6)| 00:00:07 | | |
| 1 | HASH GROUP BY | | 3 | 96 | 579 (6)| 00:00:07 | | |
| 2 | PARTITION RANGE ITERATOR| | 174K| 5437K| 559 (2)| 00:00:07 | 4 | 5 |
| 3 | TABLE ACCESS FULL | TBL_TXN | 174K| 5437K| 559 (2)| 00:00:07 | 4 | 5 |
-----------------------------------------------------------------------------------------------------
SQL> alter table tbl_txn modify (txn_tms not null);
Table altered.
SQL> select txn_type, count(*) from tbl_txn
2 where txn_tms >=to_date('20110219','yyyymmdd')
3 group by txn_type;
Execution Plan
----------------------------------------------------------
Plan hash value: 820060006
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 96 | 291 (6)| 00:00:04 | | |
| 1 | HASH GROUP BY | | 3 | 96 | 291 (6)| 00:00:04 | | |
| 2 | PARTITION RANGE SINGLE| | 86400 | 2700K| 281 (2)| 00:00:04 | 5 | 5 |
| 3 | TABLE ACCESS FULL | TBL_TXN | 86400 | 2700K| 281 (2)| 00:00:04 | 5 | 5 |
---------------------------------------------------------------------------------------------------
SQL> select txn_type, count(*) from tbl_txn
2 where txn_tms >=to_date('20110218','yyyymmdd')
3 group by txn_type;
Execution Plan
----------------------------------------------------------
Plan hash value: 898817563
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 96 | 579 (6)| 00:00:07 | | |
| 1 | HASH GROUP BY | | 3 | 96 | 579 (6)| 00:00:07 | | |
| 2 | PARTITION RANGE ITERATOR| | 174K| 5437K| 559 (2)| 00:00:07 | 4 | 5 |
| 3 | TABLE ACCESS FULL | TBL_TXN | 174K| 5437K| 559 (2)| 00:00:07 | 4 | 5 |
-----------------------------------------------------------------------------------------------------
SQL> create index tbl_txn_n2 on tbl_txn(txn_tms,txn_type) local;
Index created.
SQL> select txn_type, count(*) from tbl_txn
2 where txn_tms >=to_date('20110219','yyyymmdd')
3 group by txn_type;
Execution Plan
----------------------------------------------------------
Plan hash value: 3005629620
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 96 | 120 (11)| 00:00:02 | | |
| 1 | HASH GROUP BY | | 3 | 96 | 120 (11)| 00:00:02 | | |
| 2 | PARTITION RANGE SINGLE| | 86400 | 2700K| 110 (3)| 00:00:02 | 5 | 5 |
| 3 | INDEX FAST FULL SCAN | TBL_TXN_N2 | 86400 | 2700K| 110 (3)| 00:00:02 | 5 | 5 |
------------------------------------------------------------------------------------------------------
SQL> select txn_type, count(*) from tbl_txn
2 where txn_tms >=to_date('20110218','yyyymmdd')
3 group by txn_type;
Execution Plan
----------------------------------------------------------
Plan hash value: 2408720091
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 96 | 563 (6)| 00:00:07 | | |
| 1 | HASH GROUP BY | | 3 | 96 | 563 (6)| 00:00:07 | | |
| 2 | PARTITION RANGE ITERATOR| | 174K| 5437K| 543 (3)| 00:00:07 | 4 | 5 |
| 3 | INDEX FAST FULL SCAN | TBL_TXN_N2 | 174K| 5437K| 543 (3)| 00:00:07 | 4 | 5 |
--------------------------------------------------------------------------------------------------------
SQL> select txn_type, count(*) from tbl_txn
2 where txn_tms < to_date('20110216','yyyymmdd')
3 group by txn_type;
Execution Plan
----------------------------------------------------------
Plan hash value: 3005629620
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 96 | 120 (11)| 00:00:02 | | |
| 1 | HASH GROUP BY | | 3 | 96 | 120 (11)| 00:00:02 | | |
| 2 | PARTITION RANGE SINGLE| | 86400 | 2700K| 110 (3)| 00:00:02 | 1 | 1 |
| 3 | INDEX FAST FULL SCAN | TBL_TXN_N2 | 86400 | 2700K| 110 (3)| 00:00:02 | 1 | 1 |
------------------------------------------------------------------------------------------------------
SQL> drop index tbl_txn_n2;
Index dropped.
SQL> select txn_type, count(*) from tbl_txn
2 where txn_tms < to_date('20110216','yyyymmdd')
3 group by txn_type;
Execution Plan
----------------------------------------------------------
Plan hash value: 820060006
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 96 | 291 (6)| 00:00:04 | | |
| 1 | HASH GROUP BY | | 3 | 96 | 291 (6)| 00:00:04 | | |
| 2 | PARTITION RANGE SINGLE| | 86400 | 2700K| 281 (2)| 00:00:04 | 1 | 1 |
| 3 | TABLE ACCESS FULL | TBL_TXN | 86400 | 2700K| 281 (2)| 00:00:04 | 1 | 1 |
---------------------------------------------------------------------------------------------------
Monday, February 14, 2011
Oracle ASO: TDE encryption wallet operation and key management
$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Feb 14 04:31:18 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> set echo on
SQL> @/home/oracle/aso_scripts/show_encryption_wallet_status.sql
SQL> desc v$encryption_wallet;
Name Null? Type
----------------------------------------- -------- ----------------------------
WRL_TYPE VARCHAR2(20)
WRL_PARAMETER VARCHAR2(4000)
STATUS VARCHAR2(9)
SQL>
SQL> col wallet format a10
SQL> col status format a10
SQL> col wallet_location format a20
SQL> set echo on
SQL> select wrl_type wallet,status,wrl_parameter wallet_location
2 from v$encryption_wallet;
WALLET STATUS WALLET_LOCATION
---------- ---------- --------------------
file OPEN /home/oracle/wallet
SQL> set echo off
$ $OH/bin/sqlplus system/oracle1
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Feb 14 04:33:08 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> alter system set encryption wallet close;
System altered.
$ sqlplus jbrinson/jbrinson
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Feb 14 04:34:43 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> select * from banking.customer;
select * from banking.customer
*
ERROR at line 1:
ORA-28365: wallet is not open
SQL> desc banking.customer
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTOMER_ID NUMBER
CUSTOMER_NAME VARCHAR2(30) ENCRYPT
CUSTOMER_TYPE VARCHAR2(3)
CUSTOMER_CITY VARCHAR2(20) ENCRYPT
CUSTOMER_COUNTRY VARCHAR2(2)
SQL> select CUSTOMER_ID, CUSTOMER_TYPE, CUSTOMER_COUNTRY from banking.customer;
select CUSTOMER_ID, CUSTOMER_TYPE, CUSTOMER_COUNTRY from banking.customer
*
ERROR at line 1:
ORA-28365: wallet is not open
SQL> alter system set encryption wallet open identified by "abcdefg12#";
alter system set encryption wallet open identified by "abcdefg12#"
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn system/oracle1
Connected.
SQL>
SQL> alter system set encryption wallet open identified by "abcdefg12#";
System altered.
SQL> conn jbrinson/jbrinson
Connected.
SQL> select * from banking.customer;
CUSTOMER_ID CUSTOMER_NAME CUS CUSTOMER_CITY CU
----------- ------------------------------ --- -------------------- --
101 HERTZ CORPORATION LO BERLIN DE
102 SUNGARD DATA SYSTEMS GL NEW YORK US
103 TEMASEK HOLDINGS GL SINGAPORE SG
104 NORDIC TELEPHONE GL STOCKHOLM SE
105 ORACLE CORPORATION GL REDWOOD SHORES US
106 QWEST COMMUNICATIONS GL DENVER US
107 OLD MUTUAL PRC GL LONDON UK
108 FRESENIUS MED CARE GL LONDON UK
109 EMI GERMANY CORPORATION LO FRANKFURT DE
110 DAIMLER GL STUTTGART DE
10 rows selected.
SQL> alter system set encryption wallet close;
alter system set encryption wallet close
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn system/oracle1
Connected.
SQL> alter system set encryption wallet close;
System altered.
SQL> alter system set encryption wallet open identified by "abcdefg12#";
System altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
$ cp /home/oracle/wallet/ewallet.p12 /home/oracle/wallet/ewallet.p12.$(date +%y%m%d).old
$ ls -l /home/oracle/wallet/ewallet.p12*
-rw-rw-r-- 1 oracle dba 1573 Feb 14 03:04 /home/oracle/wallet/ewallet.p12
-rw-rw-r-- 1 oracle oracle 1573 Feb 14 04:43 /home/oracle/wallet/ewallet.p12.110214.old
$ sqlplus system/oracle1
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Feb 14 04:43:29 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> alter system set encryption key identified by "abcdefg12#";
System altered.
SQL> ! ls -l /home/oracle/wallet/ewallet.p12*
-rw-rw-r-- 1 oracle dba 1837 Feb 14 04:43 /home/oracle/wallet/ewallet.p12
-rw-rw-r-- 1 oracle oracle 1573 Feb 14 04:43 /home/oracle/wallet/ewallet.p12.110214.old
SQL> alter table banking.customer rekey;
Table altered.
SQL> exit
m set encryption key identified by "abcdefg12#";
System altered.
SQL> ! ls -l /home/oracle/wallet/ewallet.p12*
-rw-rw-r-- 1 oracle dba 1837 Feb 14 04:43 /home/oracle/wallet/ewallet.p12
-rw-rw-r-- 1 oracle oracle 1573 Feb 14 04:43 /home/oracle/wallet/ewallet.p12.110214.old
SQL> alter table banking.customer rekey;
Table altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
$ /u01/oracle/product/11.1.0/db_1/bin/owm &
[1] 7934
$ Done.
$ /u01/oracle/product/11.1.0/sqlplus system/oracle1
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Feb 14 04:52:21 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> ! ls -l /home/oracle/wallet/ewallet.p12*
-rw------- 1 oracle dba 1838 Feb 14 04:52 /home/oracle/wallet/ewallet.p12
-rw-rw-r-- 1 oracle oracle 1573 Feb 14 04:43 /home/oracle/wallet/ewallet.p12.110214.old
SQL> alter system set encryption wallet open identified by "hijklmnop12#";
alter system set encryption wallet open identified by "hijklmnop12#"
*
ERROR at line 1:
ORA-28354: wallet already open
SQL> alter system set encryption wallet close;
System altered.
SQL> alter system set encryption wallet open identified by "hijklmno1#";
alter system set encryption wallet open identified by "hijklmno1#"
*
ERROR at line 1:
ORA-28353: failed to open wallet
SQL> alter system set encryption wallet open identified by "hijklmnop12#";
System altered.
SQL> alter system set encryption wallet close;
System altered.
SQL> exit
$ orapki wallet change_pwd -wallet /home/oracle/wallet
Enter wallet password: <- old password here
New password:
Enter wallet password: <- new password here
Oracle ASO: TDE with tablespace level encryption
$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Feb 14 03:37:24 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> set echo on
SQL> @/home/oracle/aso_scripts/create_enc_tablespace.sql
SQL> set echo on
SQL> spool create_enc_tablespace.out
SQL>
SQL> drop tablespace example_11g_enc_tablespace including contents and datafiles;
Tablespace dropped.
SQL>
SQL> create tablespace example_11g_enc_tablespace
2 datafile '/u01/oracle/oradata/db01/example_11g_enc.dbf'
3 size 50m
4 encryption using 'AES192'
5 default storage(encrypt)
6 /
Tablespace created.
SQL> set echo off
SQL> @@/home/oracle/aso_scripts/create_banking_schema.sql
SQL>
SQL> connect dvacctmgr/oracle12#
Connected.
SQL>
SQL> drop user banking cascade;
User dropped.
SQL> drop user banking_dba_sr;
User dropped.
SQL> drop user banking_dba_jr;
User dropped.
SQL>
SQL> connect / as sysdba
Connected.
SQL>
SQL> drop tablespace banking02 including contents and datafiles;
Tablespace dropped.
SQL> create tablespace banking02
2 datafile '/u01/oracle/oradata/db01/banking02.dbf' size 1m;
Tablespace created.
SQL>
SQL> drop tablespace banking01 including contents and datafiles;
Tablespace dropped.
SQL> create tablespace banking01
2 datafile '/u01/oracle/oradata/db01/banking01.dbf'
3 size 1m;
Tablespace created.
SQL>
SQL> connect dvacctmgr/oracle12#
Connected.
SQL>
SQL> create user banking identified by oracle1
2 default tablespace banking01;
User created.
SQL> create user banking_dba_sr identified by oracle1;
User created.
SQL> create user banking_dba_jr identified by oracle1;
User created.
SQL>
SQL> connect / as sysdba
Connected.
SQL> grant dba to banking,banking_dba_sr,banking_dba_jr;
Grant succeeded.
SQL>
SQL> connect banking/oracle1
Connected.
SQL> alter session set nls_date_format = 'dd/mon/yyyy';
Session altered.
SQL> create table banking.customer (
2 customer_id number,
3 customer_name varchar2(30),
4 customer_type varchar2(3),
5 customer_city varchar2(20),
6 customer_country varchar2(2)
7 );
Table created.
SQL> create table account (
2 account_id number,
3 account_type_code varchar(4),
4 customer_id number ,
5 block_credit char(1),
6 block_debit char(1),
7 date_opened date,
8 date_closed date
9 );
Table created.
SQL> create table account_balance (
2 account_id number,
3 account_bal_date date,
4 bal_avail_adj number,
5 bal_avail_closing number
6 );
Table created.
SQL>
SQL> insert into customer values(101,'HERTZ CORPORATION','LO','BERLIN','DE');
1 row created.
SQL> insert into customer values(102,'SUNGARD DATA SYSTEMS','GL','NEW YORK','US');
1 row created.
SQL> insert into customer values(103,'TEMASEK HOLDINGS','GL','SINGAPORE','SG');
1 row created.
SQL> insert into customer values(104,'NORDIC TELEPHONE','GL','STOCKHOLM','SE');
1 row created.
SQL> insert into customer values(105,'ORACLE CORPORATION','GL','REDWOOD SHORES','US');
1 row created.
SQL> insert into customer values(106,'QWEST COMMUNICATIONS','GL','DENVER','US');
1 row created.
SQL> insert into customer values(107,'OLD MUTUAL PRC','GL','LONDON','UK');
1 row created.
SQL> insert into customer values(108,'FRESENIUS MED CARE','GL','LONDON','UK');
1 row created.
SQL> insert into customer values(109,'EMI GERMANY CORPORATION','LO','FRANKFURT','DE');
1 row created.
SQL> insert into customer values(110,'DAIMLER','GL','STUTTGART','DE');
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> insert into account values(1001,'CORP',101,'N','N','11/JAN/1987',NULL);
1 row created.
SQL> insert into account values(1002,'CORP',102,'N','N','23/FEB/1992',NULL);
1 row created.
SQL> insert into account values(1003,'CORP',103,'Y','Y','01/JUL/1983','27/JUL/1984');
1 row created.
SQL> insert into account values(1004,'CORP',104,'N','N','30/JUL/1981',NULL);
1 row created.
SQL> insert into account values(1005,'CORP',105,'N','N','16/OCT/1999',NULL);
1 row created.
SQL> insert into account values(1006,'CORP',106,'N','N','04/DEC/1995',NULL);
1 row created.
SQL> insert into account values(1017,'ADJ',106,'N','N','20/JAN/2009',NULL);
1 row created.
SQL> insert into account values(1018,'CORP',107,'N','Y','21/AUG/2000','03/SEP/2002');
1 row created.
SQL> insert into account values(1221,'CORP',108,'N','N','28/SEP/2008',NULL);
1 row created.
SQL> insert into account values(1932,'CORP',109,'Y','N','03/NOV/2003',NULL);
1 row created.
SQL> insert into account values(2135,'CORP',102,'Y','Y','10/OCT/2006',NULL);
1 row created.
SQL> insert into account values(2218,'ADJ',102,'N','N','27/MAR/1988','10/DEC/2001');
1 row created.
SQL> insert into account values(6127,'ADJ',102,'Y','N','01/MAY/1996','22/APR/2005');
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> insert into account_balance values(1001,'01-MAY-2006',121455.90,101440.01);
1 row created.
SQL> insert into account_balance values(1002,'01-JUL-2008',782000.00,780211.23);
1 row created.
SQL> insert into account_balance values(1003,'15-JUN-2003',978332.90,765232.00);
1 row created.
SQL> insert into account_balance values(1004,'19-APR-2009',850200.18,850200.18);
1 row created.
SQL> insert into account_balance values(1005,'28-OCT-2004',232900.10,120918.75);
1 row created.
SQL> insert into account_balance values(1006,'03-SEP-2007',496039.88,490190.59);
1 row created.
SQL> insert into account_balance values(1017,'22-JAN-1992',101900.00,100248.95);
1 row created.
SQL> insert into account_balance values(1018,'01-SEP-2008',768950.00,569122.30);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> set echo on
SQL>
SQL> spool off
SQL> alter system flush buffer_cache;
System altered.
SQL> ! strings /u01/oracle/oradata/db01/banking01.dbf|grep TEMASEK
TEMASEK HOLDINGS
SQL> @/home/oracle/aso_scripts/show_banking_tables.sql
SQL> set echo on
SQL>
SQL> select substr(a.table_name,1,28) "TABLE",substr(b.tablespace_name,1,13) "TABLESPACE",
2 substr(a.owner,1,10) "OWNER",
3 b.encrypted "ENC?"
4 from dba_tables a, dba_tablespaces b
5 where a.tablespace_name=b.tablespace_name
6 and owner in ('BANKING')
7 order by 3,1,2
8 /
TABLE TABLESPACE OWNER ENC
---------------------------- ------------- ---------- ---
ACCOUNT BANKING01 BANKING NO
ACCOUNT_BALANCE BANKING01 BANKING NO
CUSTOMER BANKING01 BANKING NO
SQL> set echo off
SQL> @/home/oracle/aso_scripts/move_banking_tables_to_11g_encrypted_tablespace.sql
SQL>
SQL> connect / as sysdba
Connected.
SQL>
SQL> alter table banking.account move tablespace example_11g_enc_tablespace;
Table altered.
SQL> alter table banking.account_balance move tablespace example_11g_enc_tablespace;
Table altered.
SQL> alter table banking.customer move tablespace example_11g_enc_tablespace;
Table altered.
SQL>
SQL> set echo off
SQL> alter system flush buffer_cache;
System altered.
SQL> @/home/oracle/aso_scripts/show_banking_tables.sql
SQL>
SQL> select substr(a.table_name,1,28) "TABLE",substr(b.tablespace_name,1,13) "TABLESPACE",
2 substr(a.owner,1,10) "OWNER",
3 b.encrypted "ENC?"
4 from dba_tables a, dba_tablespaces b
5 where a.tablespace_name=b.tablespace_name
6 and owner in ('BANKING')
7 order by 3,1,2
8 /
TABLE TABLESPACE OWNER ENC
---------------------------- ------------- ---------- ---
ACCOUNT EXAMPLE_11G_E BANKING YES
ACCOUNT_BALANCE EXAMPLE_11G_E BANKING YES
CUSTOMER EXAMPLE_11G_E BANKING YES
SQL> set echo off
SQL> ! strings /u01/oracle/oradata/db01/example_11g_enc.dbf|grep TEMASEK
SQL> set echo on
SQL> @/home/oracle/aso_scripts/show_encrypted_tablespaces.sql
SQL> desc v$encrypted_tablespaces;
Name Null? Type
----------------------------------------- -------- ----------------------------
TS# NUMBER
ENCRYPTIONALG VARCHAR2(7)
ENCRYPTEDTS VARCHAR2(3)
SQL>
SQL> select t.name "TSName", e.encryptionalg "Algorithm", d.file_name "File Name"
2 FROM v$tablespace t, v$encrypted_tablespaces e, dba_data_files d
3 WHERE t.ts# = e.ts# and t.name = d.tablespace_name;
TSName Algorit
------------------------------ -------
File Name
--------------------------------------------------------------------------------
EXAMPLE_11G_ENC_TABLESPACE AES192
/u01/oracle/oradata/db01/example_11g_enc.dbf
SQL>
SQL> select a.owner "Owner", a.table_name "Table Name", e.encryptionalg "Algorithm"
2 FROM dba_tables a, v$encrypted_tablespaces e
3 WHERE a.tablespace_name in (select t.name from v$tablespace t,
4 v$encrypted_tablespaces e where t.ts# = e.ts#);
Owner Table Name Algorit
------------------------------ ------------------------------ -------
BANKING ACCOUNT AES192
BANKING CUSTOMER AES192
BANKING ACCOUNT_BALANCE AES192
Oracle ASO: TDE with column level encryption
$ORACLE_HOME/network/admin/sqlnet.ora
==================================================
ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=/home/oracle/wallet)))
SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.ENCRYPTION_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=(MD5)
SQLNET.ENCRYPTION_TYPES_SERVER=(DES40,RC4_40)
SQLNET.CRYPTO_SEED="Between Ten and Seventy Random Characters"
SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.ENCRYPTION_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT=(MD5)
SQLNET.ENCRYPTION_TYPES_CLIENT=(DES40,RC4_40)
$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Feb 14 03:04:01 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> alter system set encryption key identified by "abcdefg12#";
System altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
$ ls -l /home/oracle/wallet/
total 4
-rw-rw-r-- 1 oracle dba 1573 Feb 14 03:04 ewallet.p12
$ sqlplus system/oracle1
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Feb 14 03:08:04 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> alter table banking.customer modify (customer_name encrypt);
Table altered.
SQL> alter table banking.customer modify (customer_city encrypt);
Table altered.
SQL> desc banking.customer
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTOMER_ID NUMBER
CUSTOMER_NAME VARCHAR2(30) ENCRYPT
CUSTOMER_TYPE VARCHAR2(3)
CUSTOMER_CITY VARCHAR2(20) ENCRYPT
CUSTOMER_COUNTRY VARCHAR2(2)
SQL> ! strings /u01/oracle/oradata/db01/banking01.dbf|grep TEMASEK
TEMASEK HOLDINGS
SQL> alter table BANKING.CUSTOMER move;
Table altered.
SQL> ! strings /u01/oracle/oradata/db01/banking01.dbf|grep TEMASEK
TEMASEK HOLDINGS
SQL> alter system flush buffer_cache;
System altered.
SQL> ! strings /u01/oracle/oradata/db01/banking01.dbf|grep TEMASEK
TEMASEK HOLDINGS
SQL> select tablespace_name from dba_tables where table_name='CUSTOMER';
TABLESPACE_NAME
------------------------------
BANKING01
SQL> ! strings /u01/oracle/oradata/db01/banking01.dbf|grep TEMASEK
TEMASEK HOLDINGS
SQL> create table banking.customer_2 tablespace banking01
2 as select * from banking.customer;
Table created.
SQL>
SQL> alter table banking.customer_2 modify (customer_name encrypt);
Table altered.
SQL> alter table banking.customer_2 modify (customer_city encrypt);
Table altered.
SQL> ! strings /u01/oracle/oradata/db01/banking01.dbf|grep TEMASEK
TEMASEK HOLDINGS
TEMASEK HOLDINGS
SQL> alter table banking.customer_2 move tablespace banking02;
Table altered.
SQL> desc banking.customer_2;
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTOMER_ID NUMBER
CUSTOMER_NAME VARCHAR2(30) ENCRYPT
CUSTOMER_TYPE VARCHAR2(3)
CUSTOMER_CITY VARCHAR2(20) ENCRYPT
CUSTOMER_COUNTRY VARCHAR2(2)
SQL> ! strings /u01/oracle/oradata/db01/banking01.dbf|grep TEMASEK
TEMASEK HOLDINGS
TEMASEK HOLDINGS
SQL> ! strings /u01/oracle/oradata/db01/banking02.dbf|grep TEMASEK
SQL>
SQL> select * from dba_encrypted_columns;
OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL
------------------------------ ------------------------------ ------------------------------ ----------------------------- ---
BANKING CUSTOMER_2 CUSTOMER_NAME AES 192 bits key YES
BANKING CUSTOMER_2 CUSTOMER_CITY AES 192 bits key YES
BANKING CUSTOMER CUSTOMER_NAME AES 192 bits key YES
BANKING CUSTOMER CUSTOMER_CITY AES 192 bits key YES
Subscribe to:
Posts (Atom)