select a.column_name, a.data_type, a.data_length, a.data_scale,a.data_precision, b.column_name, b.data_type, b.data_length, b.data_scale,b.data_precision from user_tab_columns a, user_tab_columns b where a.table_name = upper(‘&table1’) and b.table_name = upper(‘&table2’) and a.column_name = b.column_name and ( a.data_type b.data_type or a.data_length b.data_length or a.data_scale b.data_scale or a.data_precision b.data_precision );
Monthly Archives: February 2017
Query shows clients connected to all the Flex ASM instances
col client_instance_name format a21 col host_name format a9 col status format a9 select distinct i.host_name, i.instance_name asm_instance_name, c.instance_name client_instance_name, c.db_name, c.status from gv$instance i, gv$asm_client c where i.inst_id=c.inst_id;
Displays contents of the registry history
SELECT TO_CHAR(action_time, ‘DD-MON-YYYY’) AS TIME, action, namespace, version, id, comments, bundle_series FROM sys.registry$history ORDER by 1;
Connect to Oracle Cloud Database with SQL Developer using SSH
Let’s turn off the 1521 port if we can already open so that to make sure our connection using SSH works. It will confirm us that SSH is enabled. Moreover SSH connection is more secure. In SQL Developer menu, click on View –> SSH. Then right click and choose “Create New SSH Host”. Provide the […]
Connect to Oracle Cloud Database using SQL Developer
By default, the Oracle DBaaS Cloud Service blocks access port 1521 used by tnsping. You have to open the port before you can connect using SQL Developer or Connecting to another instance via sqlplus. Log in to the Oracle Database Cloud Service and click on the service name which you already created. Note down the […]
Top Sql in Exadata
SELECT * FROM ( SELECT sql_id, executions, physical_read_bytes FROM v$sqlstats WHERE io_cell_offload_eligible_bytes = 0 ORDER BY physical_read_bytes DESC ) WHERE ROWNUM <= 10 /
Show Exadata cell versions
SELECT cellname cv_cellname , CAST(extract(xmltype(confval), ‘/cli-output/interdatabaseplan/objective/text()’) AS VARCHAR2(20)) objective , CAST(extract(xmltype(confval), ‘/cli-output/interdatabaseplan/status/text()’) AS VARCHAR2(15)) status , CAST(extract(xmltype(confval), ‘/cli-output/interdatabaseplan/name/text()’) AS VARCHAR2(30)) interdb_plan , CAST(extract(xmltype(confval), ‘/cli-output/interdatabaseplan/catPlan/text()’) AS VARCHAR2(30)) cat_plan , CAST(extract(xmltype(confval), ‘/cli-output/interdatabaseplan/dbPlan/text()’) AS VARCHAR2(30)) db_plan FROM v$cell_config WHERE conftype = ‘IORM’ ORDER BY cv_cellname /
SQL*Plus History In Oracle 12.2
In Oracle 12.2, SQL*Plus can keep the history of the commands executed. You can enable or disable the HISTORY command in the current SQL*Plus session by using the SET HISTORY command. The history feature is not enabled by default, so after you start SQL*Plus, you need to enable history by running “set hist[ory] on” SQL> […]
Create Database on Oracle Cloud
We will show you how to create database on oracl cloud database. Log into your Oracle Cloud Services account Go to the “Oracle Database Cloud Service” and create a new service Select “Oracle Database Cloud Service” as the type of subscription from the Dropdown Select the SSH Public Key as shown in the attached figure […]
How to make a table read only in Oracle
Oracle 11g allows tables to be marked as read-only using the ALTER TABLE command. ALTER TABLE table_name READ ONLY; ALTER TABLE table_name READ WRITE; Let’s create a table and make it read-only. CREATE TABLE Skant (id NUMBER); INSERT INTO Skant VALUES (1); ALTER TABLE Skant READ ONLY; Any DML statements that affect the table data […]