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

Retrieve Table Information in MS SQL Server

Quite often there is a need to check number of records in the table. It can be done with ease with the following SQL statement

Select Count(*) from TableName

However, it would have been better if we can retrieve number of records from the table with other information related to that table. SQL Server provides this store procedure to accomplish this task

sp_spaceused 'Table_Name'

more detail inside this book

The statement above shall return result in the table format. This SQL statement only works with one table in the chosen database. If we want to loop through all tables in the database to display table information, we can perform this step.

• First, create the temporary table to store result from store procedure
• Second, run the store procedure
• Last, use the select statement to display the result

Use the below SQL statement to create the temporary table in SQL Server

CREATE TABLE #tableSize(
TAB_NAME NVARCHAR(128),
RowsNO VARCHAR(30),
sizereserved VARCHAR(30),
sizedata VARCHAR(30),
index_size VARCHAR(30),
unused VARCHAR(30) )

Then, execute the store procedure below

INSERT #tableSize EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

After the the procedure finish running, simply run the select statement.

SELECT * FROM #tableSize order by TAB_NAME

Find more books related to MS SQL Server

How to access table in different schema in Store Procedure

On one Oracle database, there are several users or schemas. Each schema contains tables. These table belong to each user. It is possible for a user to access table in other schema. For instant, if there are three users in Oracle database, such as USER1, USER2, and USER3.

In event that the USER3 needs to access the table which belongs to the USER2. SQL select statement is written as

Select Col1, Col2, Col3 from USER2.TableName

This statement works fine when the SQL statement is run in Toad or SQL-Plus. However, if the SQL statement above is put into the store procedure, it will not work. The store procedure will not compiled correctly.

To correct this problem, the USER2 has to grant select on table privilege the USER3. Follow the step below:

1. Login to the Oracle database as USER2
2. Run this statement Grant Select on USER2.TableName to USER3;

Now the USER3 can put the select statement of granted table in the store procedure.

Rename DATA FILE for Oracle

The data file is created when the Oracle DBA created the table space. If the Oracle DBA wants to rename the data file after the it has been created, he or she can do so by following these steps

1. The table space has to be taken offline; use the following command ALTER TABLESPACE tablespace_name OFFLINE NORMAL;
2. Then data file is rename using the operating system.
3. After that the Oracle DBA renames the data file in SQL Plus enviroment. Supposed that the old data file name is 'USER1.DBF'. The new data file name is 'NEW_USER.DBF'. Following the step 2, issue this SQL comment ALTER TABLESPACE 'tablespace name' RENAME DATAFILE 'C:\USER1.DBF' to 'C:\NEW_USER.DBF'; This assumes that the data file is located in drive C
4. After rename the data file is performed in the Oracle environment, mount the tablespace online again, using this command: ALTER TABLESPACE tablespace_name ONLINE;

Now, the table space contain the data file with the new name.

How to undrop a table in Oracle

When a table in the Oracle databaseis dropped, table is not actually dropped. The dropped table is renamed to other name which starts with BIN$.... The table is stored in recycle area, so the table space is still occupied by the dropped table. This is the new feature in Oracle version 10G. We will try to create the new table, insert data, drop and undrop the table.

SQL> create table testing (col varchar2(10), row_chng_dt date);

Table created...

SQL> insert into testing values ('Version1', sysdate);

1 row created..

SQL> drop table testing;

Table dropped.

Now the deleted table is stored in the recycle area. To see the dropped table in the recycle area, issue this SQL commend.

SQL> select object_name, original_name, type, droptime

from recyclebin Where original_name = 'TESTING';

the result is shown below

OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME

------------------------------ ------------- ----- ---------------

BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TESTING TABLE 2006-09-01:16:10:12

If the user makes the select statement again, the user shall use BIN$HGnc55/7rRPgQPeM/qQoRw==$0 for table name

Select * from BIN$HGnc55/7rRPgQPeM/qQoRw==$0;

Now the user can restore the table by using this SQL statement

SQL> flashback table testing to before drop;

Flashback complete.

In case that we want to completely delete table Testing from database. We have to purge table from the recycle bin area.

SQL> purge table "BIN$HGnc55/7rRPgQPeM/qQoRw==$0";

Table purged.

After purging operation is complete, the table space in the Oracle is released.
Hone your skill on the Oracle database with this book

Creating DB User and Grant Option - Oracle

When the new user has been created in Oracle database, the new user cannot do anything with the database. The database admin has to grant privilege to that user. First privilege is

SQL> grant connect to Tommy;

Grant succeed.

The privilege allow the user Tommy to be able to connect to the database. In order for user Tommy to use the resource, granting resource must be follow:

SQL> grant resource to TOMMY;

Grant succeeded.

Now, the user Tommy can use resource like table space. There are other options in privilege to be granted. From my practice, after I create the new user, I also grant EXP_FULL_DATABASE to the user.

SQL> grant EXP_FULL_DATABASE to TOMMY;

This option allows the user to create and drop tables and other objects.

Oracle Password Management

Each user account is protected by the their password. When the user is created, the password is assigned to the user. By default, user password is set to expired by 180 days. After the password expires, the user can no longer access his or her account. To see the expiry date for each open user, user following commands.

SQL> Select username, Account_Status, Expiry_Date from DBA_USERS;

The result shall display after query statement execution. The database administrator can switch off password expiry option by issuing the following command

SQL> Alter Profile Default limit PASSWORD_LIFE_TIME unlimited;

Profile altered.

Now running the same query below, the expiry date value shall be null

SQL> Select username, Account_Status, Expiry_Date from DBA_USERS;

However, this is dangerous for security reason because all users under PROFILE default have non-expiry date for password. To limit the password expiry again,

SQL> alter profile default limit PASSWORD_LIFE_TIME 180;

Profile altered.

180 means password expire after 180 days

Oracle Password Restriction & Solution

What are valid characters in password for Oracle? The Oracle recommends using the combination of letters and numbers. Symbols of "#", "_", and "$" sign are also valid. There are rules that you should follow when creating the password.

First Rule: The first letter of the password must start with the letter, Example

SQL> alter user tommy identified by tommy1234;

User altered.

SQL> alter user tommy identified by 1234tommy;

ERROR at line 1:

ORA-00988 missing or invalid password(s)

As you can see, the password cannot start with the number. However if you want to start password with the number, you can use the double quotation mark on the password;

SQL> alter user tommy identified by "1234tommy";

User altered.

My note: some blogs say password is not case sensitive. I do some testing on the testing database. The result is password is case sensitive. If your user password is Tommy1234, typing password as tommy1234 gives the error message while logging in.

Good luck........

Alter User Password - Reset User Password in Oracle

When the database administrator creates a new user in the Oracle database, password for the new user is set to expire. Password expiry occurs, for it is the default in setting inside Oracle. You can query the Oracle database to see the detail of the database profile as:

Select Profile, Resource_Name, Resource_Type, Limit

from dba_Profiles

where Resource_Name like 'PASS%';

Then, look for highlight line. The password expire after 180 days.

Then, you can query the DBA_USERS table to see the expiry date for the user. Using the follow SQL statement:

Select UserName, Account_Status, Expiry_Date

from dba_Users

Where UserName = 'TEST';

The above query will show the account status and password expiry date for user TEST.

The password for the user TEST shall expire on Sep 03, 2011. What happen when your user password expires. The user cannot log in. Let's try by altering the user TEST account status to expire. Log in the Oracle database as SYSDBA in SQL Plus, and issue alter statement as follow:

Alter User TEST Password Expire;

Now the user TEST shall not be able to connect to the database. Here is the result after altering password to expired

You can unlock the account for user TEST by resetting the new password. At the SQL Plus command prompt, issue the following command to reset password

Alter User TEST identified by NewPassword;

The user TEST now can access the database again.....

Oracle PFILE vs. SPFILE Parameter File to Start up Oracle Instance

When an Oracle Instance is started, the characteristics of the Instance are established by parameters specified within the initialization parameter file. These initialization parameters are either stored in a PFILE or SPFILE. SPFILEs are available in Oracle 9i and above. All prior releases of Oracle are using PFILEs.

SPFILEs provide the following advantages over PFILEs:

• An SPFILE can be backed-up with RMAN (RMAN cannot backup PFILEs)
• Reduce human errors. The SPFILE is maintained by the server. Parameters are checked before changes are accepted.
• Eliminate configuration problems (no need to have a local PFILE if you want to start Oracle from a remote machine)
• Easy to find - stored in a central location

What is the difference between a PFILE and SPFILE:

A PFILE is a static, client-side text file that must be updated with a standard text editor like "notepad" or "vi". This file normally reside on the server, however, you need a local copy if you want to start Oracle from a remote machine. DBA's commonly refer to this file as the INIT.ORA file.

An SPFILE (Server Parameter File), on the other hand, is a persistent server-side binary file that can only be modified with the "ALTER SYSTEM SET" command. This means you no longer need a local copy of the pfile to start the database from a remote machine. Editing an SPFILE will corrupt it, and you will not be able to start your database anymore.

How will I know if my database is using a PFILE or SPFILE:

Execute the following query to see if your database was started with a PFILE or SPFILE:

SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"

FROM sys.v_$parameter WHERE name = 'spfile';

You can also use the V$SPPARAMETER view to check if you are using a PFILE or not: if the "value" column is NULL for all parameters, you are using a PFILE.

Starting a database with a PFILE or SPFILE:

continue with this URL http://www.orafaq.com/node/5