Table or vision?

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
marquesjr
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 77
Joined: Wed, 05 Dec 2007 10:51 am
Location: Fortaleza - CE

Speaks galera,

I would like to import data but I am in doubt if the "table" I made the query is a table or a vision. Can you identify if a "table" is a table or a vision?
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Brother, beleza?

uses this script below, exchanging the name in quotation marks, by the name of your object:

Select all

 
SELECT OBJECT_TYPE  
  FROM USER_OBJECTS   -- OU ALL_OBJECTS 
 WHERE OBJECT_NAME = 'TEU_OBJETO'; 
whatever, send it there.
marquesjr
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 77
Joined: Wed, 05 Dec 2007 10:51 am
Location: Fortaleza - CE

The result was Synonym

means that it is a table?
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

No, Synonym works as if it were a "nickname" to some other object, object that can be or not from another owner of your base ..
to know the origin of this object consulting his data ..

Select all

 
SELECT * 
  FROM all_synonyms 
 WHERE synonym_name = 'SUA_TABELA' 
He will tell you who is the owner and the name of the original object, there with this data verify that the original is a table or a view using the example that Trevisolli passed ..

[] 's
marquesjr
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 77
Joined: Wed, 05 Dec 2007 10:51 am
Location: Fortaleza - CE

So I made the query and appeared as follows

owner, synonym_name, table_owner, table_name, db_link

in case the object I consulted 'supplies' is with the same name for both Synonym_Name and Table_Name.

The following reasoning is correct:

Every object that is related in the TABLE_NAME field is a table? And never a view will appear on Table_Name?

Thanks for help.
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

And the object's owner, is it the same?

About the All_Synonyms Table_Name field, although the name of the field is TABLE_NAME it is possible to have in this field the name of a tb view.

[] 's
marquesjr
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 77
Joined: Wed, 05 Dec 2007 10:51 am
Location: Fortaleza - CE

OPA, in Owner Related All system users, in Table_Owner is the system name.
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

The select that you ran by searching this object was in user_objects or at all_objects ??

[] 's
marquesjr
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 77
Joined: Wed, 05 Dec 2007 10:51 am
Location: Fortaleza - CE

The Select I used is this:

Select all

SELECT * FROM USER_OBJECTS WHERE OBJECT_NAME = 'FORNEC'
The result was this:

Select all

From: <Salvo pelo Microsoft Internet Explorer 7> 
Subject:  
Date: Wed, 5 Dec 2007 14:48:29 -0300 
MIME-Version: 1.0 
Content-Type: text/html; 
	charset="Windows-1252" 
Content-Transfer-Encoding: 7bit 
Content-Location: =?Windows-1252?Q?file://C:\Documents_and_Settings\u012215.M01AUD0149\Conf?= 
	=?Windows-1252?Q?igura=E7=F5es_locais\Temp\SQLT0005.HTM?= 
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198 
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> 
<HTML><HEAD> 
<META http-equiv=Content-Type content="text/html; charset=windows-1252"> 
<META content="MSHTML 6.00.6000.16544" name=GENERATOR></HEAD> 
<BODY> 
<TABLE cellSpacing=0 cellPadding=1 border=1> 
  <TBODY> 
  <TR> 
    <TH>OBJECT_NAME</TH> 
    <TH>SUBOBJECT_NAME</TH> 
    <TH>OBJECT_ID</TH> 
    <TH>DATA_OBJECT_ID</TH> 
    <TH>OBJECT_TYPE</TH> 
    <TH>CREATED</TH> 
    <TH>LAST_DDL_TIME</TH> 
    <TH>TIMESTAMP</TH> 
    <TH>STATUS</TH> 
    <TH>TEMPORARY</TH> 
    <TH>GENERATED</TH> 
    <TH>SECONDARY</TH></TR> 
  <TR> 
    <TD>FORNEC</TD> 
    <TD>&nbsp;</TD> 
    <TD align=right>5089671</TD> 
    <TD align=right>&nbsp;</TD> 
    <TD>SYNONYM</TD> 
    <TD>27/08/07</TD> 
    <TD>17/09/07</TD> 
    <TD>2007-09-17:10:35:56</TD> 
    <TD>VALID</TD> 
    <TD>N</TD> 
    <TD>N</TD> 
    <TD>N</TD></TR></TBODY></TABLE></BODY></HTML>
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

It makes a select on all_objects, is to appear another object that is not the synonym that you found in user_objects.

[] 's
marquesjr
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 77
Joined: Wed, 05 Dec 2007 10:51 am
Location: Fortaleza - CE

marquesjr
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 77
Joined: Wed, 05 Dec 2007 10:51 am
Location: Fortaleza - CE

The result was as follows:

[IMG http://geocities.yahoo.com.br/fa7tributario/1020.JPG[/img]
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

This object is a table, and her owner is the pyramid ..

[] 's
marquesjr
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 77
Joined: Wed, 05 Dec 2007 10:51 am
Location: Fortaleza - CE

Thanks even for the help, now you can identify the tables.

Taking advantage ...

How can I identify which select was used in a certain view by the consultation in the bank?
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

Oops, yes.
take a look at the Text field of the All_Views table.

Select all

 
SELECT * FROM all_views WHERE VIEW_NAME = 'SUA_VIEW' 
[] 's
marquesjr
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 77
Joined: Wed, 05 Dec 2007 10:51 am
Location: Fortaleza - CE

Boy, once again, it was worth it, very important table that: -The

I generated the consultation and appeared in the text field the following:

I just got the Next I did not find the balance_atual object specified in Select below;

Select all

Select Saldo_Atual.cd_contagem 
     , Saldo_Atual.dt_contagem 
     , Saldo_Atual.hr_contagem 
     , Saldo_Atual.dt_geracao 
     , Saldo_Atual.hr_geracao 
     , Saldo_Atual.cd_produto 
     , Saldo_Atual.Qt_estoque 
     , Saldo_Atual.Qt_estoque_doado 
     , Saldo_Atual.vl_custo_medio 
     , Nvl(Saldo_Anterior.qt_anterior, 0) Qt_Anterior 
     , Nvl(Saldo_Anterior.qt_anterior_doado, 0) Qt_Anteior_Doado 
     , Saldo_Atual.ds_unidade 
  From ( 
Select contagem.cd_contagem 
     , contagem.dt_contagem 
     , contagem.hr_contagem 
     , contagem.dt_geracao 
     , contagem.hr_geracao 
     , itcontagem.cd_produto 
     , Sum(itcontagem.qt_estoque * uni_pro.vl_Fator) qt_estoque 
     , Sum(itcontagem.qt_estoque_doado * uni_pro.vl_fator) qt_estoque_doado 
     , avg(itcontagem.vl_custo_medio / uni_pro.vl_fator ) vl_custo_medio 
     , 0 qt_anterior 
     , 0 qt_anterior_doado 
     , uni_pro.ds_unidade 
  From Dbamv.Contagem 
     , Dbamv.ItContagem 
     , Dbamv.Uni_Pro 
 Where Contagem.Cd_Contagem = ItContagem.Cd_Contagem 
   And ItContagem.Cd_Uni_Pro = Uni_Pro.Cd_Uni_Pro 
Group By Contagem.Cd_Contagem 
       , contagem.Dt_Contagem 
       , contagem.Hr_Contagem 
       , contagem.Dt_Geracao 
       , contagem.Hr_Geracao 
       , itcontagem.Cd_Produto 
       , Uni_Pro.Ds_Unidade 
       ) Saldo_Atual 
     , ( 
Select Copia_Estoque.Cd_Contagem 
     , Null          dt_contagem 
     , Null          hr_contagem 
     , Null          dt_geracao 
     , Null          hr_geracao 
     , Copia_Estoque.cd_produto 
     , 0 qt_estoque 
     , 0 qt_estoque_doado 
     , 0 vl_custo_medio 
     , Sum(Copia_Estoque.Qt_Estoque) qt_anterior 
     , Sum(Copia_Estoque.Qt_Estoque_Doado) qt_anterior_doado 
     , Null  ds_unidade 
  From Dbamv.Copia_Estoque 
Group By Copia_Estoque.Cd_Contagem 
       , Copia_Estoque.cd_produto 
       ) Saldo_Anterior 
Where Saldo_Atual.Cd_Contagem = Saldo_Anterior.Cd_Contagem(+) 
  And Saldo_Atual.Cd_Produto = Saldo_Anterior.Cd_Produto(+)
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

In this case the "balance_atual" is not an object of your bank, it is an "alias" for an excerpt from your select ... goes an example of what was done in your query.

Select all

 
SELECT Saldo_Atual.*  
  FROM (SELECT SYSDATE  
          FROM DUAL) Saldo_Atual 
As you can see Clausua from is not a table in specific, but the result of a select, result that can have alias you want ..

99] [] 's
marquesjr
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 77
Joined: Wed, 05 Dec 2007 10:51 am
Location: Fortaleza - CE

I pulled out, I'vê sacked. Perfect, thanks immensely for the help, was and is (since it is posted on the forum) of Great Serventia.
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 15 guests