-> show all records
$ last
-> show all records page by page
$ last | more
-> show first 10 records
$ last | head
-> show first 100 records
$ last | head -100
-> show last 10 records
$ last | tail -10
-> show last 100 records
$ last | tail -100
Monday 30 July 2018
Wednesday 18 July 2018
How to change service name?
e.g.
current service name: postgresql.service
desired service name: postgresql-9.1.service
Run following commands to change service name;
Step 1: Stop current service
# systemctl stop postgresql.service
# systemctl daemon-reload
Step 2: Change service name
# cd /usr/lib/systemd/system
# mv postgresql.service postgresql-9.1.service
Step 3: Start and enable new service
# systemctl start postgresql-9.1.service
# systemctl enable postgresql-9.1.service
Step 4: Check service status:
# systemctl status postgresql-9.1.service
Step 5: Make sure that service runs properly
# journalctl -u postgresql-9.1.service
current service name: postgresql.service
desired service name: postgresql-9.1.service
Run following commands to change service name;
Step 1: Stop current service
# systemctl stop postgresql.service
# systemctl daemon-reload
Step 2: Change service name
# cd /usr/lib/systemd/system
# mv postgresql.service postgresql-9.1.service
Step 3: Start and enable new service
# systemctl start postgresql-9.1.service
# systemctl enable postgresql-9.1.service
Step 4: Check service status:
# systemctl status postgresql-9.1.service
Step 5: Make sure that service runs properly
# journalctl -u postgresql-9.1.service
Friday 13 July 2018
How to solve remote connection error cause of iptables/firewalld service?
Case
Machine A(192.168.1.2) wants to connect on Machine B(192.168.1.5) by database port.
Action
In Machine A;
# su - postgres
$ psql -h 192.168.1.5 -d alper -U alper -p 5433
Error
psql: could not connect to server: No route to host
Is the server running on host "192.168.1.5" and accepting
TCP/IP connections on port 5433?
Solution
In Machine B;
For iptables service:
# iptables -I INPUT -p tcp --dport 5433 -j ACCEPT
# iptables-save
For firewalld service:
# firewall-cmd --zone=public --add-port=5433/tcp --permanent
Machine A(192.168.1.2) wants to connect on Machine B(192.168.1.5) by database port.
Action
In Machine A;
# su - postgres
$ psql -h 192.168.1.5 -d alper -U alper -p 5433
Error
psql: could not connect to server: No route to host
Is the server running on host "192.168.1.5" and accepting
TCP/IP connections on port 5433?
Solution
In Machine B;
For iptables service:
# iptables -I INPUT -p tcp --dport 5433 -j ACCEPT
# iptables-save
For firewalld service:
# firewall-cmd --zone=public --add-port=5433/tcp --permanent
# firewall-cmd --reload
Check
In Machine A;
In Machine A;
# nc -vz 192.168.1.5 5433
Ncat: Connected to 192.168.1.5:5433
How to solve database module error?
Action
# ansible-playbook -i myhosts example.yml -v
Error
TASK [example role : example task] *******************
fatal: [local]: FAILED! => {"changed": false, "msg": "The MySQL-python module is required."}
Solution
# yum install MySQL-python
The other solutions related to database tasks:
# yum install python-redis (Redisdb)
# yum install python-psycopg2 (PostgreSQL)
Check it for up to date list of database modules:
https://docs.ansible.com/ansible/latest/modules/list_of_database_modules.html
# ansible-playbook -i myhosts example.yml -v
Error
TASK [example role : example task] *******************
fatal: [local]: FAILED! => {"changed": false, "msg": "The MySQL-python module is required."}
Solution
# yum install MySQL-python
The other solutions related to database tasks:
# yum install python-redis (Redisdb)
# yum install python-psycopg2 (PostgreSQL)
Check it for up to date list of database modules:
https://docs.ansible.com/ansible/latest/modules/list_of_database_modules.html
Friday 6 July 2018
How to prevent sleep mode on laptop when lid closes?
Press Window Key then type "Power Options" and click on it -> Click "Choose what closing the lid does" -> Set "When I close the lid" to "Do nothing" -> Click "Save changes" -> Done!
Friday 29 June 2018
How to run a SQL query/statement in a task?
You are able to run a SQL query/statement with "echo" command by shell module for the ansible task.
# an example task for sql query/statement
- name: set default schema for user
shell: echo "ALTER ROLE alper SET SEARCH_PATH TO myschema" | psql
become: yes
become_user: postgres
# an example task for sql query/statement
- name: set default schema for user
shell: echo "ALTER ROLE alper SET SEARCH_PATH TO myschema" | psql
become: yes
become_user: postgres
benefit of "gather_facts: no" setting
If you do not need any facts for your variables, you can run your playbook more fast via gather_facts setting.
For this, just add following setting in your playbook:
# example playbook
- name: Run the role for me
hosts: my_nodes
gather_facts: no
roles:
- example-role
For this, just add following setting in your playbook:
# example playbook
- name: Run the role for me
hosts: my_nodes
gather_facts: no
roles:
- example-role
Friday 18 May 2018
"database "example" is being accessed by other users" solution
Action
postgres=# drop database example;
Error
ERROR: database "example" is being accessed by other users
DETAIL: There is 1 other session using the database.
Solution
Run following sql code;
postgres=#
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'example' AND pid <> pg_backend_pid();
All sessions should be terminated where belong to "example" database except backend sessions.
Lastly, try to drop it again.
postgres=# drop database example;
Error
ERROR: database "example" is being accessed by other users
DETAIL: There is 1 other session using the database.
Solution
Run following sql code;
postgres=#
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'example' AND pid <> pg_backend_pid();
All sessions should be terminated where belong to "example" database except backend sessions.
Lastly, try to drop it again.
Sunday 6 May 2018
How to backup database in XE?
It is very simple to backup your database in Oracle XE with already prepared .bat file quickly.
Steps;
Press Windows Key -> Type "Backup Database" -> Click "Backup Database" link -> .bat file "Backup.bat" runs -> Follow the steps -> Done
Finally, check the backup path;
...\oracle\fast_recovery_area\XE
Steps;
Press Windows Key -> Type "Backup Database" -> Click "Backup Database" link -> .bat file "Backup.bat" runs -> Follow the steps -> Done
Finally, check the backup path;
...\oracle\fast_recovery_area\XE
Friday 27 April 2018
e.g. truncate table cascade
Preparation of Test Data
test=#
create table employees (id integer, name varchar, department_id integer);
test=#
create table departments (id int, department_name varchar);
test=#
ALTER TABLE DEPARTMENTS
ADD CONSTRAINT DEPT_PK
PRIMARY KEY (ID);
test=#
ALTER TABLE EMPLOYEES
ADD CONSTRAINT EMP_DEPT_FK
FOREIGN KEY (DEPARTMENT_ID)
REFERENCES DEPARTMENTS(ID);
test=#
insert into departments
values (1,'IT');
test=#
insert into departments
values (2,'Sales');
test=#
insert into departments
values (3,'Marketing');
test=#
insert into employees
values (1,'John',1);
test=#
insert into employees
values (2,'Alex',1);
test=#
insert into employees
values (3,'Guru',2);
test=#
insert into employees
values (4,'Maximus',3);
test=#
commit;
Data Check
-- select all rows of parent table
test=#
select * from departments;
-- select all rows of child table
test=#
select * from employees;
Action
-- by using truncate table cascade, parent table and child table data will be removed.
test=#
truncate table departments cascade;
Last Data Check
-- select all rows of parent table
test=#
select * from departments;
-- select all rows of child table
test=#
select * from employees;
Info: In Oracle 12c, you are able to use "cascade" clause with "truncate table" statement as well.
test=#
create table employees (id integer, name varchar, department_id integer);
test=#
create table departments (id int, department_name varchar);
test=#
ALTER TABLE DEPARTMENTS
ADD CONSTRAINT DEPT_PK
PRIMARY KEY (ID);
test=#
ALTER TABLE EMPLOYEES
ADD CONSTRAINT EMP_DEPT_FK
FOREIGN KEY (DEPARTMENT_ID)
REFERENCES DEPARTMENTS(ID);
test=#
insert into departments
values (1,'IT');
test=#
insert into departments
values (2,'Sales');
test=#
insert into departments
values (3,'Marketing');
test=#
insert into employees
values (1,'John',1);
test=#
insert into employees
values (2,'Alex',1);
test=#
insert into employees
values (3,'Guru',2);
test=#
insert into employees
values (4,'Maximus',3);
test=#
commit;
Data Check
-- select all rows of parent table
test=#
select * from departments;
-- select all rows of child table
test=#
select * from employees;
Action
-- by using truncate table cascade, parent table and child table data will be removed.
test=#
truncate table departments cascade;
Last Data Check
-- select all rows of parent table
test=#
select * from departments;
-- select all rows of child table
test=#
select * from employees;
Info: In Oracle 12c, you are able to use "cascade" clause with "truncate table" statement as well.
Thursday 26 April 2018
e.g. fetching JSON data
Preparation of Test Data
test=#
create table T_JSON_DATA (id INTEGER, body JSON);
test=#
insert into T_JSON_DATA
values (1, '{ "name":"John", "age":30, "city":"New York" }');
test=#
insert into T_JSON_DATA
values (2, '{ "name":"Alex", "age":25, "city":"China" }');
test=#
insert into T_JSON_DATA
values (3, '{ "name":"Guru", "age":21, "city":"Russia" }');
test=#
insert into T_JSON_DATA
values (4, '{ "name":"Maximus", "age":25, "city":"Somewhere" }');
test=# commit;
Examples for populating test data
test=#
select * from T_JSON_DATA;
-- to retrieve specific field data
test=#
select body->>'name' from T_JSON_DATA;
-- with aliases
test=#
select body->'name' AS NAME, body->'city' AS CITY from T_JSON_DATA;
-- use with where clause
test=#
select body->'name' AS NAME, body->'city' AS CITY from T_JSON_DATA where id = 1;
test=#
create table T_JSON_DATA (id INTEGER, body JSON);
test=#
insert into T_JSON_DATA
values (1, '{ "name":"John", "age":30, "city":"New York" }');
test=#
insert into T_JSON_DATA
values (2, '{ "name":"Alex", "age":25, "city":"China" }');
test=#
insert into T_JSON_DATA
values (3, '{ "name":"Guru", "age":21, "city":"Russia" }');
test=#
insert into T_JSON_DATA
values (4, '{ "name":"Maximus", "age":25, "city":"Somewhere" }');
test=# commit;
Examples for populating test data
test=#
select * from T_JSON_DATA;
-- to retrieve specific field data
test=#
select body->>'name' from T_JSON_DATA;
-- with aliases
test=#
select body->'name' AS NAME, body->'city' AS CITY from T_JSON_DATA;
-- use with where clause
test=#
select body->'name' AS NAME, body->'city' AS CITY from T_JSON_DATA where id = 1;
Wednesday 25 April 2018
ORA-02292 solution
Preparation
-- Insert test data into DEPARTMENT and EMPLOYEES tables.
SQL>
INSERT INTO DEPARTMENTS
VALUES (280,'New Department',100,1700);
SQL>
INSERT INTO EMPLOYEES
VALUES (207,'John','Mathew','JMATHEW','111.111.111',TO_DATE('25.04.2018','DD.MM.YYYY'),'IT_PROG',10000,0.5,100,280);
SQL>
INSERT INTO EMPLOYEES
VALUES (208,'Jim','Parker','JPARKER','111.111.111',TO_DATE('25.05.2018','DD.MM.YYYY'),'IT_PROG',20000,0.6,100,280);
SQL>
INSERT INTO EMPLOYEES
VALUES (209,'Sophia','Ran','SRAN','111.111.111',TO_DATE('25.06.2018','DD.MM.YYYY'),'IT_PROG',30000,0.7,100,280);
SQL>
INSERT INTO EMPLOYEES
VALUES (210,'Wendi','Blake','WBLAKE','111.111.111',TO_DATE('25.07.2018','DD.MM.YYYY'),'IT_PROG',40000,0.8,100,280);
SQL>
COMMIT;
Action
SQL>
DELETE FROM DEPARTMENTS
WHERE DEPARTMENT_ID = 280;
Error
ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found
Solution
-- Find referencial integrity constraint between DEPARTMENT and EMPLOYEES tables for this error.
SQL>
SELECT *
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME IN ('EMPLOYEES', 'DEPARTMENTS') AND CONSTRAINT_TYPE = 'R';
-- Drop constraint
SQL>
ALTER TABLE EMPLOYEES DROP CONSTRAINT EMP_DEPT_FK;
-- Re-define constraint with "ON DELETE CASCADE" option
SQL>
ALTER TABLE EMPLOYEES
ADD CONSTRAINT EMP_DEPT_FK
FOREIGN KEY (DEPARTMENT_ID)
REFERENCES DEPARTMENTS(DEPARTMENT_ID)
ON DELETE CASCADE;
-- Try again
SQL>
DELETE FROM DEPARTMENTS
WHERE DEPARTMENT_ID = 280;
SQL>
COMMIT;
-- Insert test data into DEPARTMENT and EMPLOYEES tables.
SQL>
INSERT INTO DEPARTMENTS
VALUES (280,'New Department',100,1700);
SQL>
INSERT INTO EMPLOYEES
VALUES (207,'John','Mathew','JMATHEW','111.111.111',TO_DATE('25.04.2018','DD.MM.YYYY'),'IT_PROG',10000,0.5,100,280);
SQL>
INSERT INTO EMPLOYEES
VALUES (208,'Jim','Parker','JPARKER','111.111.111',TO_DATE('25.05.2018','DD.MM.YYYY'),'IT_PROG',20000,0.6,100,280);
SQL>
INSERT INTO EMPLOYEES
VALUES (209,'Sophia','Ran','SRAN','111.111.111',TO_DATE('25.06.2018','DD.MM.YYYY'),'IT_PROG',30000,0.7,100,280);
SQL>
INSERT INTO EMPLOYEES
VALUES (210,'Wendi','Blake','WBLAKE','111.111.111',TO_DATE('25.07.2018','DD.MM.YYYY'),'IT_PROG',40000,0.8,100,280);
SQL>
COMMIT;
Action
SQL>
DELETE FROM DEPARTMENTS
WHERE DEPARTMENT_ID = 280;
Error
ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found
Solution
-- Find referencial integrity constraint between DEPARTMENT and EMPLOYEES tables for this error.
SQL>
SELECT *
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME IN ('EMPLOYEES', 'DEPARTMENTS') AND CONSTRAINT_TYPE = 'R';
-- Drop constraint
SQL>
ALTER TABLE EMPLOYEES DROP CONSTRAINT EMP_DEPT_FK;
-- Re-define constraint with "ON DELETE CASCADE" option
SQL>
ALTER TABLE EMPLOYEES
ADD CONSTRAINT EMP_DEPT_FK
FOREIGN KEY (DEPARTMENT_ID)
REFERENCES DEPARTMENTS(DEPARTMENT_ID)
ON DELETE CASCADE;
-- Try again
SQL>
DELETE FROM DEPARTMENTS
WHERE DEPARTMENT_ID = 280;
SQL>
COMMIT;
Thursday 19 April 2018
e.g. count and distinct aggregate functions
examples; count (1) - count(*) - count(column_name) - count(distinct (column_name))
e.g. count aggregate function
SQL>
SELECT
COUNT(*)
FROM
employees;
COUNT(*)
----------
107
SQL>
SELECT
COUNT(1)
FROM
employees;
COUNT(*)
----------
107
SQL>
SELECT
COUNT(commission_pct)
FROM
employees;
COUNT(COMMISSION_PCT)
---------------------
35
Info: COUNT(1) and COUNT(*) calculate all rows even all columns data are NULL. However, COUNT(COLUMN_NAME) ignores NULL data.
e.g. distinct aggregate function
SQL>
SELECT
distinct(commission_pct)
FROM
employees;
COMMISSION_PCT
--------------
NULL
,15
,35
,4
,3
,2
,25
,1
8 rows selected.
Info: DISTINCT(COLUMN_NAME) function uses not to see duplicated rows and also it shows NULL column if it exists.
e.g. both of count and distinct aggregate functions
SQL>
SELECT
COUNT(DISTINCT(commıssıon_pct) )
FROM
employees;
COUNT(DISTINCT(COMMISSION_PCT))
-------------------------------
7
Info: count(column_name) ignores NULL values, even if distinct function is used.
e.g. count aggregate function
SQL>
SELECT
COUNT(*)
FROM
employees;
COUNT(*)
----------
107
SQL>
SELECT
COUNT(1)
FROM
employees;
COUNT(*)
----------
107
SQL>
SELECT
COUNT(commission_pct)
FROM
employees;
COUNT(COMMISSION_PCT)
---------------------
35
Info: COUNT(1) and COUNT(*) calculate all rows even all columns data are NULL. However, COUNT(COLUMN_NAME) ignores NULL data.
e.g. distinct aggregate function
SQL>
SELECT
distinct(commission_pct)
FROM
employees;
COMMISSION_PCT
--------------
NULL
,15
,35
,4
,3
,2
,25
,1
8 rows selected.
Info: DISTINCT(COLUMN_NAME) function uses not to see duplicated rows and also it shows NULL column if it exists.
e.g. both of count and distinct aggregate functions
SQL>
SELECT
COUNT(DISTINCT(commıssıon_pct) )
FROM
employees;
COUNT(DISTINCT(COMMISSION_PCT))
-------------------------------
7
Info: count(column_name) ignores NULL values, even if distinct function is used.
Wednesday 11 April 2018
ORA-02449 solution
Preparation
SQL>
CREATE TABLESPACE EXAMPLE_TS DATAFILE
'C:\ORACLEXE_XE\APP\ORACLE\ORADATA\XE\example_01.dbf' SIZE 512K REUSE
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
SQL>
CREATE TABLESPACE EXAMPLE_TS_2 DATAFILE
'C:\ORACLEXE_XE\APP\ORACLE\ORADATA\XE\example_02.dbf' SIZE 512K REUSE
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
SQL>
CREATE TABLE T_EXAMPLE (ID NUMBER)
TABLESPACE EXAMPLE_TS;
SQL>
CREATE TABLE T_EXAMPLE_CHILD (ID NUMBER, FID NUMBER)
TABLESPACE EXAMPLE_TS_2;
SQL>
ALTER TABLE T_EXAMPLE
ADD CONSTRAINT ID_EXAMPLE_PK
PRIMARY KEY (ID);
SQL>
ALTER TABLE T_EXAMPLE_CHILD
ADD CONSTRAINT FID_EXAMPLE_FK
FOREIGN KEY (FID)
REFERENCES T_EXAMPLE (ID);
Action
SQL>
DROP TABLESPACE EXAMPLE_TS;
Error
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
Next Action
SQL>
DROP TABLESPACE EXAMPLE_TS INCLUDING CONTENTS AND DATAFILES;
Next Error
SQL>
ORA-02449: unique/primary keys in table referenced by foreign keys
Solid Solution
SQL>
DROP TABLESPACE EXAMPLE_TS INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
SQL>
CREATE TABLESPACE EXAMPLE_TS DATAFILE
'C:\ORACLEXE_XE\APP\ORACLE\ORADATA\XE\example_01.dbf' SIZE 512K REUSE
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
SQL>
CREATE TABLESPACE EXAMPLE_TS_2 DATAFILE
'C:\ORACLEXE_XE\APP\ORACLE\ORADATA\XE\example_02.dbf' SIZE 512K REUSE
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
SQL>
CREATE TABLE T_EXAMPLE (ID NUMBER)
TABLESPACE EXAMPLE_TS;
SQL>
CREATE TABLE T_EXAMPLE_CHILD (ID NUMBER, FID NUMBER)
TABLESPACE EXAMPLE_TS_2;
SQL>
ALTER TABLE T_EXAMPLE
ADD CONSTRAINT ID_EXAMPLE_PK
PRIMARY KEY (ID);
SQL>
ALTER TABLE T_EXAMPLE_CHILD
ADD CONSTRAINT FID_EXAMPLE_FK
FOREIGN KEY (FID)
REFERENCES T_EXAMPLE (ID);
Action
SQL>
DROP TABLESPACE EXAMPLE_TS;
Error
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
Next Action
SQL>
DROP TABLESPACE EXAMPLE_TS INCLUDING CONTENTS AND DATAFILES;
Next Error
SQL>
ORA-02449: unique/primary keys in table referenced by foreign keys
Solid Solution
SQL>
DROP TABLESPACE EXAMPLE_TS INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
ORA-01549 solution
Preparation
SQL>
CREATE TABLESPACE EXAMPLE_TS DATAFILE
'C:\ORACLE_XE\APP\ORACLE\ORADATA\XE\example_01.dbf' SIZE 512K REUSE
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
SQL>
CREATE TABLE T_EXAMPLE (ID NUMBER)
TABLESPACE EXAMPLE_TS;
Action
SQL>
DROP TABLESPACE EXAMPLE_TS;
Error
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
Solution
SQL>
DROP TABLESPACE EXAMPLE_TS INCLUDING CONTENTS AND DATAFILES;
SQL>
CREATE TABLESPACE EXAMPLE_TS DATAFILE
'C:\ORACLE_XE\APP\ORACLE\ORADATA\XE\example_01.dbf' SIZE 512K REUSE
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
SQL>
CREATE TABLE T_EXAMPLE (ID NUMBER)
TABLESPACE EXAMPLE_TS;
Action
SQL>
DROP TABLESPACE EXAMPLE_TS;
Error
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
Solution
SQL>
DROP TABLESPACE EXAMPLE_TS INCLUDING CONTENTS AND DATAFILES;
Monday 9 April 2018
e.g. pivot clause
Preparation
* Define column names for pivot table
SQL>
SELECT LISTAGG (
'''' || JOB_ID || '''' || ' AS ' || '"' || JOB_TITLE || '"' || ', ')
WITHIN GROUP (ORDER BY JOB_TITLE)
FROM JOBS;
Pivot example
* Show that how many jobs are set to persons according to department names.
SQL>
SELECT *
FROM (SELECT JOB_ID, DEPARTMENT_NAME
FROM EMPLOYEES A, DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID) PIVOT (
COUNT (JOB_ID)
FOR JOB_ID
IN ('AD_VP' AS "ADMIN",
'FI_ACCOUNT' AS "Accountant",
'AC_MGR' AS "Accounting Manager",
'AD_ASST' AS "Administration Assistant",
'FI_MGR' AS "Finance Manager",
'HR_REP' AS "Human Resources Representative",
'MK_MAN' AS "Marketing Manager",
'MK_REP' AS "Marketing Representative",
'AD_PRES' AS "Presidentt",
'IT_PROG' AS "Programmer",
'AC_ACCOUNT' AS "Public Accountant",
'PR_REP' AS "Public Relations Represent.",
'PU_CLERK' AS "Purchasing Clerk",
'PU_MAN' AS "Purchasing Manager",
'SA_MAN' AS "Sales Manager",
'SA_REP' AS "Sales Representative",
'SH_CLERK' AS "Shipping Clerk",
'ST_CLERK' AS "Stock Clerk",
'ST_MAN' AS "Stock Manager"))
ORDER BY DEPARTMENT_NAME DESC;
* Define column names for pivot table
SQL>
SELECT LISTAGG (
'''' || JOB_ID || '''' || ' AS ' || '"' || JOB_TITLE || '"' || ', ')
WITHIN GROUP (ORDER BY JOB_TITLE)
FROM JOBS;
Pivot example
* Show that how many jobs are set to persons according to department names.
SQL>
SELECT *
FROM (SELECT JOB_ID, DEPARTMENT_NAME
FROM EMPLOYEES A, DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID) PIVOT (
COUNT (JOB_ID)
FOR JOB_ID
IN ('AD_VP' AS "ADMIN",
'FI_ACCOUNT' AS "Accountant",
'AC_MGR' AS "Accounting Manager",
'AD_ASST' AS "Administration Assistant",
'FI_MGR' AS "Finance Manager",
'HR_REP' AS "Human Resources Representative",
'MK_MAN' AS "Marketing Manager",
'MK_REP' AS "Marketing Representative",
'AD_PRES' AS "Presidentt",
'IT_PROG' AS "Programmer",
'AC_ACCOUNT' AS "Public Accountant",
'PR_REP' AS "Public Relations Represent.",
'PU_CLERK' AS "Purchasing Clerk",
'PU_MAN' AS "Purchasing Manager",
'SA_MAN' AS "Sales Manager",
'SA_REP' AS "Sales Representative",
'SH_CLERK' AS "Shipping Clerk",
'ST_CLERK' AS "Stock Clerk",
'ST_MAN' AS "Stock Manager"))
ORDER BY DEPARTMENT_NAME DESC;
Friday 6 April 2018
How to use SPOOL command in Oracle?
WINDOWS
e.g.
SQL>
sqlplus / as sysdba;
SQL>
SET LINESIZE 200;
SET PAGESIZE 100;
-- define a output file for writing
SPOOL 'C:\output_SQL.txt';
SELECT owner,
object_name,
created,
timestamp
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
ORDER BY OBJECT_NAME;
-- stop to write
SPOOL OFF;
LINUX
e.g.
SQL>
sqlplus / as sysdba;
SQL>
SET LINESIZE 200;
SET PAGESIZE 100;
--define a output file for writing
SPOOL '/tmp/output_SQL.txt';
SELECT owner,
object_name,
created,
timestamp
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
ORDER BY OBJECT_NAME;
-- stop to write
SPOOL OFF;
-> to see output file
$ cat /tmp/output_SQL.txt
e.g.
SQL>
sqlplus / as sysdba;
SQL>
SET LINESIZE 200;
SET PAGESIZE 100;
-- define a output file for writing
SPOOL 'C:\output_SQL.txt';
SELECT owner,
object_name,
created,
timestamp
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
ORDER BY OBJECT_NAME;
-- stop to write
SPOOL OFF;
LINUX
e.g.
SQL>
sqlplus / as sysdba;
SQL>
SET LINESIZE 200;
SET PAGESIZE 100;
--define a output file for writing
SPOOL '/tmp/output_SQL.txt';
SELECT owner,
object_name,
created,
timestamp
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
ORDER BY OBJECT_NAME;
-- stop to write
SPOOL OFF;
-> to see output file
$ cat /tmp/output_SQL.txt
Tuesday 3 April 2018
What are pencil hardness grading scales?
2H -> H -> F -> HB -> B -> 2B
H means Hard (hard-tipped)
B means Bold (soft-tipped)
F means Fine
Info: from left to right, writing is thicker on the paper.
H means Hard (hard-tipped)
B means Bold (soft-tipped)
F means Fine
Info: from left to right, writing is thicker on the paper.
Generally, it would say that H (Hard) is designed for drafting and S (Soft) is designed for writing.
Friday 30 March 2018
What is implicit commit?
After every DDL operation, implicit commit occurs such as while defining a new table.
e.g.
CREATE TABLE HR.EXAMPLE(ABC char(1));
Also we call it as "auto commit" operation.
e.g.
CREATE TABLE HR.EXAMPLE(ABC char(1));
Also we call it as "auto commit" operation.
blob vs bfile data types
BLOB (binary large objects) stores unstructured binary large objects. It is often used for graphic images, video clips, and sounds. The BLOB data type stores the content inside the Oracle database.
BFILE (binary file) is also used to large binary file stored outside the database. Oracle can read the file only, not modify it. Oracle requires appropriate operating system level read permissions on the file.
Ref: Oracle SQL Interactive Workbook (Alice Rischert)
BFILE (binary file) is also used to large binary file stored outside the database. Oracle can read the file only, not modify it. Oracle requires appropriate operating system level read permissions on the file.
Ref: Oracle SQL Interactive Workbook (Alice Rischert)
Subscribe to:
Posts (Atom)