Sql

Oracle Sql output format

I am using the following form for regular sql output


SET
LINESIZE 32000;
SET PAGESIZE 40000;
SET LONG 50000; 

for example show Oracle Asm disks status :

SQL> select GROUP_NUMBER,MOUNT_STATUS,STATE,REDUNDANCY,NAME,PATH from v$asm_disk;
output like this.

er

Advertisements
Standard
Sql

How to find table size?

Hello,

if you want to see all table sizes in oracle database you can use this code.

SELECT owner, table_name, ROUND(sum(bytes)/1024/1024/1024) GB
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND   s.owner = i.owner
AND   s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
and   s.owner = l.owner
AND   s.segment_type in ('LOBSEGMENT', 'LOB PARTITION', 'LOB SUBPARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND   s.owner = l.owner
AND   s.segment_type = 'LOBINDEX')
GROUP BY table_name, owner
order by sum(bytes) desc;
Standard
Sql

The Backdoor Entry to Oracle Database

The backdoor entry of the Oracle database! You can enter to the database through the back door using SQL * Plus tool with “Prelim” parameter  Prelim, directly connects to the SGA but it does not open a session.

You can connect to the database with Prelim as following.

[oracle@maxerman bin]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 18 10:42:55 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> set_prelim on
SQL> conn / as sysdba
Prelim connection established
SQL>

Standard
Oracle, Plsql, Sql

ORA 28040 no machine authentication protocol Error.

I am trying to connect with Plsql Developer to Oracle databse Oracle 12c in Oracle Linux 7.3 system. However, whenever i run my application i get following exception. Error messages summary : “connect error, ORA-28040: No matching authentication protocol”

Bug number -> 14575666

Screen Shot 2017-07-13 at 20.14.27

Solutions 1 : 

1. Connect to system with oracle user
2. set SQLNET.ALLOWED_LOGON_VERSION=8 in the sqlnet.ora file.

Solutions 2 :

Plsql developer may have stayed on the old version. Update the Plsql Developer 

ORA 28040 no machine authentication protocol Error.

Aside
Linux, Oracle, Sql

infoFence, is a 3rd. party real time security database software for Oracle databases.

SCOPE

  • Login Control
  • DDL/DCL (Database Definition & Control Language) Control
  • DML (Data Manipulation Language)/QUERY Control
  • Database and Application Error Detection System
  • Conditional Table Access Logging System.
    It is installed into Oracle Databases (10g,11g,12c) and runs using Database Triggers and optionally be integrated with Oracle Database Vault Option

Oracle Database Vault Option Integration

Oracle Database Vault is a security platform running in database. Segregation of duty is enforced by limiting privileges of DBAs having unlimited privileges. This option requires an additional licence from Oracle Corp. infoFence with its unique design and Oracle Database Vault integration, creates an enourmous difference in database security and management.

Maximum Security

  • infoFence is a security layer on Oracle Databases.
  • Each client logging into database are redefined with their client session values. Existing privileges are never altered but reauthorized in a new layer.
  • User access, DDL, data change, update, query, mask and redaction operations are easily controlled and logged.
  • infoFence provides ability to test before blocking. (Try, analyze then apply)
  • infoFence prevents any security breaches and backdoor access to the database.

Login Control

  • Any user knowing the right user-password can not login to the database unless defined by infoFence explicitly.
  • Even the most powerful user SYS (as sysdba) can not login unless defined in infoFence. No Oracle privileges are revoked at all to provide this restriction.

DDL/DCL Control

  • Users with same database user-pasword may have different privileges and may execute DDL/DCL commands like ALTER, DROP, CREATE, GRANT, REVOKE on specified objects only if defined by infoFence.
  • Even the most powerful user sys (as sysdba) can not execute DDL/DCL commands unless defined by infoFence.
  • All DDL/DCL executions are logged and source code of database object versions are kept historically with their state and error.
  • Specified database Schema and objects are protected against DDL/DCL operations.

DML/Query Control

  • Critical data are now secured. Even the DBAs and the most powerful user SYS are prevented to query and access critical data. Unauthorized accesses are blocked and logged.
  • The selected clients are prevented to access selected tables.
  • The selected clients are prevented to see critical data with Masking or Data Redaction. Data Redaction requires additional Licence from Oracle Corp.
  • In production environment, it is possible to test and analyze the consequences of blocking a table access before blocking. First define in SNIFFMODE, analyze it, then switch to GUARDMODE later on.

Error Detection Detector

  • Any database errors occuring in Oracle database are logged in alertSID.log file. The errors arisen from both database and application are detected and logged by infoFence.
  • When explicitly defined, any database and application error can automatically run an event action by infoFence. The user can write his/her own PL/SQL code for the automatic action alarms.

Logging

  • Login attempts, DDL, DCL, DML (Access & Block), QUERY access operations and error detections are logged.
  • Table/view accesses for desired user groups can be logged. E.g. You can log table/view accesses for direct user connections but not log application server clients.
  • Checksums are kept for each log. Any log can be checked if it is modified, hacked or not. The encrypted logs may logged explicitly.

LDAP OID Support

  • Client defined in Oracle Internet Directory (OID) may login to database with his/her username via Enterprise User Security (EUS). Client authentication definition can be done in infoFence using Client’s username (LDAP user), Ldap path, proxy user and mapped schema name.

Identity Management Support

  • It is easy to integrate with Identity Management Systems.
  • There are fields in client definition for identity definition. Using the APIs at INFOFENCE_PANEL package from infoFence, integration can be done easily.

Reports

  • infoFence user interface panel has generic reports to be generated.

Performance

  • infoFence is highly tuned. It has no top query or wait event on 10000 (ten thousand) concurrent user system and a very high transaction rate.

Web Site : http://infofence.com/en/

What is infoFence?

Aside
Oracle, Sql

Wget: command not found error on OEL 6.8

error => bash : wget command not found.

Use the wget utility to download the repository configuration file that is appropriate for your system.


[root@ermankara ~]# cd /etc/yum.repos.d/
[root@ermankara yum.repos.d]# wget <a href="http://yum.oracle.com/public-yum-ol6.repo" target="_blank" rel="noopener" data-saferedirecturl="https://www.google.com/url?hl=tr&q=http://yum.oracle.com/public-yum-ol6.repo&source=gmail&ust=1499859882238000&usg=AFQjCNFg0USF9JXI2Gidw2bd18vnBiKbxA">http://yum.oracle.com/public-<wbr />yum-ol6.repo</a>
bash: wget: command not found

Is wget installed?

[root@ermankara yum.repos.d]# rpm -q wget
package wget is not installed

If not, install it:

[root@ermankara yum.repos.d]# yum list | grep wget
wget.x86_64                   <wbr />       1.12-10.el6              public_ol6_latest

[root@ermankara yum.repos.d]# yum install wget.x86_64
Loaded plugins: refresh-packagekit, ulninfo
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package wget.x86_64 0:1.12-10.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================<wbr />==============================<wbr />====================
Package     Arch          Version               Repository                Size
==============================<wbr />==============================<wbr />====================
Installing:
wget        x86_64        1.12-10.el6           public_ol6_latest        483 k

Transaction Summary
==============================<wbr />==============================<wbr />====================
Install       1 Package(s)

Total download size: 483 k
Installed size: 1.8 M
Is this ok [y/N]: y
Downloading Packages:
wget-1.12-10.el6.x86_64.rpm   <wbr />                           | 483 kB     00:00
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : wget-1.12-10.el6.x86_64       <wbr />                               1/1
Verifying  : wget-1.12-10.el6.x86_64       <wbr />                               1/1

Installed:
wget.x86_64 0:1.12-10.el6                 <wbr />                              <wbr />

Complete!

[root@ermankara yum.repos.d]# wget <a href="http://yum.oracle.com/public-yum-ol6.repo" target="_blank" rel="noopener" data-saferedirecturl="https://www.google.com/url?hl=tr&q=http://yum.oracle.com/public-yum-ol6.repo&source=gmail&ust=1499859882238000&usg=AFQjCNFg0USF9JXI2Gidw2bd18vnBiKbxA">http://yum.oracle.com/public-<wbr />yum-ol6.repo</a>
--2017-07-11 14:40:19--  <a href="http://yum.oracle.com/public-yum-ol6.repo" target="_blank" rel="noopener" data-saferedirecturl="https://www.google.com/url?hl=tr&q=http://yum.oracle.com/public-yum-ol6.repo&source=gmail&ust=1499859882238000&usg=AFQjCNFg0USF9JXI2Gidw2bd18vnBiKbxA">http://yum.oracle.com/public-<wbr />yum-ol6.repo</a>
Resolving yum.oracle.com... 195.175.114.178, 195.175.114.170
Connecting to <a href="http://yum.oracle.com/" target="_blank" rel="noopener" data-saferedirecturl="https://www.google.com/url?hl=tr&q=http://yum.oracle.com&source=gmail&ust=1499859882238000&usg=AFQjCNEaZQtqjnaNju4HpKwaWk41osjbZQ">yum.oracle.com</a>|195.175.114.<wbr />178|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 7530 (7.4K) [text/plain]
Saving to: “public-yum-ol6.repo.1”

100%[=========================<wbr />=============>] 7,530       --.-K/s   in 0s

2017-07-11 14:40:20 (618 MB/s) - “public-yum-ol6.repo.1” saved [7530/7530]

[root@ermankara yum.repos.d]#

 

Standard
Sql

 


root@erman02]#vi /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=erman02
NETWORKING=yes
HOSTNAME=production
Reboot Ediyoruz.
[root@erman02]# reboot
[root@erman02]# hostname
production

How to change the Hostname for Linux

Aside