AWS Test

The article demonstrate how to interaction with AWS service by using CLI application

This command is to list the security group, displaying security group name and group ID

c:\>aws ec2 --profile testec2 describe-security-groups --query “SecurityGroups[*].{Name:GroupName,ID:GroupId}”

Setup User in Oracle 19c

Since Oracle 12c, The user can create the database called, pluggable database. The idea is to allow user to create several database for specific purposes. For example, we can have the database for HR department and another for Sale department. After finishing creating PDB, pluggable database, we can create the user. In this article, we will create user to local PDB. 

1. Login as SYS user

2. Switch to pluggable data as 

sql > alter session set container = pdb01;

Check current database we are in

sql > select name, pdb from all_services;

sql > create user tommy01 identified by xxxxx container=current;

Then, we need to grant necessary privileges for this new user. In this case, this user shall have enough privileges to perform tasks, such as creating table, creating view and other common tasks. Run below grant statements 

sql > grant create session to tommy01 container=current;

sql > grant connect to tommy01 container=current;

sql > grant CREATE ANY TABLE to tommy01 container=current;

sql > grant INSERT ANY TABLE to tommy01 container=current;

sql > grant resource to tommy01 container=current;

sql > grant create tablespace to tommy01 container=current;

sql > grant create datafile to tommy01 container=current;

this allow user tommy01 to use data file in the current 

sql > grant unlimited tablespace to tommy01 ;

sql > alter user tommy01 default tablespace data_01 container=current;

sql > grant CREATE ANY VIEW to tommy01 container=current;

sql > grant create PROCEDURE to tommy01 container=current;

sql > grant EXECUTE ANY PROCEDURE to tommy01 container=current;

Check all privileges, granted to user TOMMY01.

SELECT * FROM DBA_SYS_PRIVS where grantee in ('TOMMY01') order by 1, 2;

If any SQL command run with error, we need to grant more privileges to the user. Your comment is welcome. 

Divide and Conquer Very Big Table

Index is not the ultimate to the fast table access

Most of the time when people dealing with the very large table which is more than billion rows, people tend to think that creating index for the this big table shall help data access. However, there are several techniques which database designer can exploit to speed up data access from the big table.

1. First, we can use table partition technique. This will divide the table into small logical table. Nevertheless, the Oracle partition feature costs more money. The organization would have to pay for separate license for the partition feature.
2. If the license fee is the limitation, we can divide the the big table to small ones. 
3. Applying the second approach with other Oracle capabilities, such data compression and index, we can accomplish the following tasks
• Administration task, when the the big table is slighted into several small tables, we can remove old data more quickly by dropping unneeded table.
• Oracle data compression can help reducing the amount disk space to be consume. In case for table scan, it will reduce I/O as well.
• Small table mean small data file and small index help table access and reduce I/O.

I have worked with three approaches. The total records test = 88,054,450 rows

1. Doing table partition by month - table: transaction_data
2. Doing sample table with transaction data for quarter one table: transaction_data_q1
3. Divide transaction data by month
• transaction_data_jan
• transaction_data_feb 
• transaction_date_mar

When query the table for row count by date as

select count(*) from transaction_data 
where tran_dt = to_date('15-JAN-2016','DD-MON-YYY');

select count(*) from transaction_data_jan
where tran_dt = to_date('15-JAN-2016','DD-MON-YYY');

The above two queries consume about 00:01:18. They are very close result. The other query

select count(*) from transaction_data_q1
where tran_dt = to_date('15-JAN-2016','DD-MON-YYY');

This consumes about 00:03:27 query time.

Then, the index is created on tran_dt colume for table transaction_data_q1. Query time is extreamly improved. With same query from table transaction_data_q1. It takes only a fraction of second to yield the result.

In the actual scenario, we would not divide transaction table to month level. This would be too small and too many tables. Divide transaction table to quarter level seems to be reasonable. There are after all four tables per year.

When old data is not needed, table can be dropped easily. Image if the transaction table has data from year 2012 to 2016. This is a very one big fat table to managed. Building index for one very large table is time and resource consuming. When there is a need to delete data in year 2012, it is still taking a lot of time to do delete. This will impact on undo log as well. Moreover, the delete data result in index fragment.

Therefore, dividing the to big transaction table to several manageable can help in many aspects.

Install Oracle 11gR2 on Linux

Step Installing Oracle 11gR2 on Linux 

Installing Oracle requires prerequisite component packages to installed. If the computer is connected with the internet, run the following command on Linux prompt.

[root@hostname]# yum install oracle-rdbms-server-11gR2-preinstall

The above line install the additional packages onto Linux.

It is probably worth doing a full update as well, but this is not strictly speaking necessary. The update process takes over an hour to complete.

[root@hostname]# yum update

Then, check other configuration values in "etc/sysclt.conf"

[root@hostname]# cat /etc/sysctl.conf

If you run the preinstallation package, the content in the sysctl.conf file shall be updated. To have new kernal parameter values take effect, run the following command

[root@hostname]# /sbin/sysctl -p

Add the following lines to "/etc/securty/limits.conf" file

oracle soft nproc 16384
oracle hard nproc 16384
oracle soft nofile 4096
oracle hard nofile 65536
oracle soft stack 10240

Again, these lines are automatically added if you have run oracle-rdbms-server-11gR2-preinstall package.

Next create the new groups and uses.

[root@hostname]# groupadd -g 54321 oinstall
[root@hostname]# groupadd -g 54322 dba
[root@hostname]# groupadd -g 54323 oper
[root@hostname]# groupadd -g 54324 backupdba
[root@hostname]# groupadd -g 54325 dgdba
[root@hostname]# groupadd -g 54326 kmdba
[root@hostname]# groupadd -g 54327 asmdba
[root@hostname]# groupadd -g 54328 asmoper
[root@hostname]# groupadd -g 54329 asmadmin

Strangely enough, when adding group of "oinstall", dba group is also added, and new user "oracle" is created in Linux. After adding all group, run the following command to create user and assign groups to user. In this case, the user is oracle

[root@hostname]# useradd -u 54321 -g oinstall -G dba,oper oracle

Be sure to type in command as appeared above. Unnecessary space causes error. Then, set the password for user "oracle"

[root@hostname]# passwd oracle

Then, type new password and confirm the password.
Set secure Linux by edit file "/etc/selinux/config" file. The value inside the file = SELINUX=permissive
Once the value is change. Run the following command at prompt
[root@hostname]# setenforce Permissive

Then, disable the Linux firewall with following commands

[root@hostname]# service iptables stop
[root@hostname]# chkconfig iptables off

Create directory for Oracle software installation
[root@hostname]# mkdir -p /u01/app/oracle/product/11.2.0.1/db_1
[root@hostname]# chown -R oracle:oinstall /u01
[root@hostname]# chmod -R 775 /u01

Before running the installer, edit bash_profile for user "oracle". Use gedit to edit file
[root@hostname]# gedit /home/oracle/.bash_profile

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=localhost.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.1/db_1; export ORACLE_HOME
ORACLE_SID=DB11G; export ORACLE_SID

PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

Save and exit. Note that ORACLE_BASE and ORACLE_HOME is the directory, created earlier step

If installation is performed directly on Linux server, issue this xhost command

[root@hostname]# xhost +SI:localuser:oracle

Switch to user oracle

[root@hostname]# su oracle

Run the installer command where the runInstaller file is saved.

[root@hostname]# ./runInstaller

Then follow onscreen instruction until installation is complete.

Convert Rows to Columns in SQL Server

This is the simple code to convert records rows to columns:
Let create simple two column table:

 CREATE TABLE [dbo].[LotNo]( [IDNo] [bigint], [LotNo] [varchar](50) NULL )

Now insert some records to the table:
insert into LotNo ([IDNo], LotNo)
select 1, 'A' union all
select 2, 'A' union all
select 3, 'A' union all
select 4, 'A' union all
select 5, 'A' union all
select 6, 'A' union all
select 1, 'B' union all
select 2, 'B' union all
select 3, 'B' union all
select 1, 'C' union all
select 2, 'C' union all
select 3, 'C' union all
select 4, 'C' union all
select 1, 'D' union all
select 2, 'D' union all
select 1, 'E' union all
select 2, 'E' union all
select 3, 'E' union all
select 1, 'F' union all
select 2, 'F' union all
select 3, 'F' union all
select 4, 'F' union all
select 5, 'F' union all
select 6, 'F' union all
select 1, 'G' union all
select 2, 'H' union all
select 3, 'I' union all
select 4, 'J' union all
select 6, 'K'

Now, this is the sql statement to show the the result in columns

select LotNO, max(case when IDNo = 1 then IDNo else '' end) as Col1,
       max(case when IDNo = 2 then IDNo else '' end) as Col2,
       max(case when IDNo = 3 then IDNo else '' end) as Col3,
       max(case when IDNo = 4 then IDNo else '' end) as Col4,
       max(case when IDNo = 5 then IDNo else '' end) as Col5,
       max(case when IDNo = 6 then IDNo else '' end) as Col6
from LotNo
group by LotNO

You can use this approach to produce permutation table.

Grant Select Privilege on SELECT_CATALOG_ROLE to User

In Oracle, after the new user or schema is created, the user cannot query the data dictionary. For example, if you run the below query:

Select * from dba_data_files

you'll see error message as ORA-00942: table or view does not exist

To allow the user to select table from Oracle data dictionary, you have to grant SELECT_CATALOG_ROLE privilege the that users. Use the following statement while logging in as sysdba

sql> grand SELECT_CATALOG_ROLE to user_name;

Amazon Product List Page

Click here to check out product list

Shop Amazon Books - New J.K. Rowling - The Casual Vacancy