Table or view?

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Wed, 05 Dec 2007 10:54 am

Hey everybody, I would like to import some data but I'm wondering if the \"table\" that performed the query is a table or a view. Has how to identify if a \"table\" is a table or a view?
marquesjr
Location: Fortaleza - CE

Poston Wed, 05 Dec 2007 11:43 am

Brother, beleza?

Uses this script below, changing the name in quotation marks, the name of your object:
Code: Select all
SELECT OBJECT_TYPE
  FROM USER_OBJECTS   -- OU ALL_OBJECTS
WHERE OBJECT_NAME = 'TEU_OBJETO';
qualquer thing, says ai.
Trevisolli
Location: Araraquara - SP

Abraço,

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

Poston Wed, 05 Dec 2007 11:58 am

the result was SYNONYM means that it is a table?
marquesjr
Location: Fortaleza - CE

Poston Wed, 05 Dec 2007 12:46 pm

No, the synonym works as if it were a \"nickname\" for some other object, that object may or may not be from another owner of its base.
to know the origin of this object see his information..
Code: Select all
SELECT *
  FROM all_synonyms
WHERE synonym_name = 'SUA_TABELA'
he will let you know who is the owner and the original object name, ai with these data make sure that the original is a table or a view, using the example that the cheezburger network?.

[] 's
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Wed, 05 Dec 2007 1:46 pm

so I made the appointment and showed up the following OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME, DB_LINK in case the object that I consulted ' VENDOR ' with the same name for both the SYNONYM_NAME as TABLE_NAME.

the following reasoning is correct: All object that is listed in TABLE_NAME field is a table? and never a vision appears in TABLE_NAME.

Thanks for the help.
marquesjr
Location: Fortaleza - CE

Poston Wed, 05 Dec 2007 2:07 pm

And the owner of the object, is the same?

On the field the table_name all_synonyms, despite the name of the field being TABLE_NAME is possible you have in this field the name of a view também.

[] 's
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Wed, 05 Dec 2007 2:21 pm

Oops, in OWNER is related to all users of the system, is the system name TABLE_OWNER.
marquesjr
Location: Fortaleza - CE

Poston Wed, 05 Dec 2007 2:34 pm

The select q você rode searching this object was in the USER_OBJECTS or ALL_OBJECTS??

[] 's
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Wed, 05 Dec 2007 2:54 pm

the select that I used is this:
Code: Select all
SELECT * FROM USER_OBJECTS WHERE OBJECT_NAME = 'FORNEC'
the result was this: From: < Saved by Microsoft Internet Explorer 7 > Subject: Date: Wed, December 5, 2007 14:48:29-0300 MIME-Version: 1.0 Content-Type: text/html;
u0009charset = \"Windows-1252\" Content-Transfer-Encoding: 7 bit Content-Location: =?Windows-1252?Q? file://C: Documents_and_Settingsu012215.M01AUD0149Conf? = =?Windows-1252?Q? = igura E7 = F5es_locaisTempSQLT0005.HTM? = X-MimeOLE: Produced By Microsoft MimeOLE 6.00.2900.3198 V <!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 = </HEAD> > <BODY> < GENERATOR 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>SYNONYM</TD> <TD>17/09/07</TD> <TD>27/08/07</TD> </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>
marquesjr
Location: Fortaleza - CE

Poston Wed, 05 Dec 2007 3:01 pm

Do a select on ALL_OBJECTS is another object that is not synonym that you found in the user_objects.

[] 's
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Wed, 05 Dec 2007 3:04 pm

[img]http%20://geocities%20.%20yahoo%20.%20with%20.%20fa7tributario/br/imagem121355%20.%20JPG[/img]
marquesjr
Location: Fortaleza - CE

Poston Wed, 05 Dec 2007 3:08 pm

the result was as follows: [img]http%20://geocities%20.%20yahoo%20.%20with%20.%20fa7tributario/1020/.%20JPG[/img]
marquesjr
Location: Fortaleza - CE

Poston Wed, 05 Dec 2007 3:21 pm

This object is a table, and the owner is the Pyramid ...

[] 's
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Wed, 05 Dec 2007 3:28 pm

Thanks a lot for your help, now I can identify the tables.

leveraging ... how to identify which select was used in certain vision for the query in the?
marquesjr
Location: Fortaleza - CE

Poston Wed, 05 Dec 2007 4:13 pm

Oops, Yes.
take a look at the text field of the all_views table.

Code: Select all
SELECT * FROM all_views WHERE VIEW_NAME = 'SUA_VIEW'


[]'s
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Wed, 05 Dec 2007 5:04 pm

Boy, one more time thanks a lot, very important this table:-generated the query and appeared in the TEXT field the following: Just got the following question I couldn't find the Saldo_Atual object specified in the select below;

Code: 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(+)
marquesjr
Location: Fortaleza - CE

Poston Wed, 05 Dec 2007 5:31 pm

In this case the \"Saldo_Atual\" is not an object of his seat, he is an \"alias\" for a snippet of your select ... get an example of what was done in your query.
Code: Select all
SELECT Saldo_Atual.*
  FROM (SELECT SYSDATE
          FROM DUAL) Saldo_Atual
as you can see from clausua is not a table in specific, and rather the result of a select, a result that may have the alias that you want ...

[] 's
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Wed, 05 Dec 2007 5:38 pm

get it, get it. perfect, immensely for the help agradesço, was and is (as is posted in the forum) of great service.
marquesjr
Location: Fortaleza - CE



Return to SQL

Who is online

Users browsing this forum: No registered users and 3 guests