Sp_help for Oracle?

• g.britton

  SSChampion

  Points: 13682

  May 15, 2015 at 6:11 am

  #317501

  I'm building a set of SQL Server views that use OPENQUERY to run queries against an Oracle server.

  I want to see the metadata of the query from Oracle's perspective.

  Is there an equivalent to SQL Server's sp_help for Oracle that I could use?

  Gerald Britton, MCSE-DP, MVPToronto PASS Chapter[/url]

• Lowell

  SSC Guru

  Points: 323354

  May 15, 2015 at 6:51 am

  #1798179

  you can use the two built in procedures which are used specifically for interrogating linked servers.

  EXEC sp_tables_ex [OracleLinkedServerName] will get you started, but from there, you need to find a database/schema/table to really dig into:

  EXECUTE sp_tables_ex

  @table_server ='CPULinked',

  @table_schema='BOOFILES',

  @table_name = 'DOCTR'

  EXECUTE sp_columns_ex

  @table_server ='CPULinked',

  @table_schema='BOOFILES',

  @table_name = 'DOCTR',

  @Column_Name ='DRTYPE'

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• g.britton

  SSChampion

  Points: 13682

  May 15, 2015 at 6:59 am

  #1798182

  Thanks for the tip!

  Unfortunately, these queries returned empty result sets for me, though the server, schema and tables/views most certainly exist (and I can query them using OPENQUERY)

  Actually what I was looking for was an equivalent to sp_help that I can run on Oracle. So something like:

  select * from openquery(oracle, 'exec sp_help ''schema.table''')

  replacing 'exec sp_help ''schema.table''' with whatever the equivalent is on Oracle.

  Gerald Britton, MCSE-DP, MVPToronto PASS Chapter[/url]

• Lowell

  SSC Guru

  Points: 323354

  May 15, 2015 at 8:12 am

  #1798204

  ahh, sorry.

  DESC or DESCRIBE TableName will get you some information, not sure how pretty it will be over a linked server, but that's what you want to try.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• g.britton

  SSChampion

  Points: 13682

  May 15, 2015 at 8:47 am

  #1798218

  Alas, I don't think you can run DESC over OPENQUERY, since it is not a SQL command. I think you can only do that with the SQLPLUS command.

  Could be wrong though!

  Gerald Britton, MCSE-DP, MVPToronto PASS Chapter[/url]

• Joie Andrew

  One Orange Chip

  Points: 27271

  May 20, 2015 at 4:29 pm

  #1799505

  Doesn't Oracle have metadata views like ALL_TABLES, ALL_TAB_COLUMNS and ALL_USER_TABLES? Can you query those?

  Joie Andrew
  "Since 1982"

• kevaburg

  SSCoach

  Points: 17910

  November 14, 2016 at 7:00 am

  #1912246

  g.britton (5/15/2015)

  ---

  Alas, I don't think you can run DESC over OPENQUERY, since it is not a SQL command. I think you can only do that with the SQLPLUS command.

  Could be wrong though!

  You are right.....it is an internal command that only works with SQLPlus.....shame though....

• knareshseo92

  SSC Rookie

  Points: 33

  February 2, 2018 at 2:56 am

  #1978318

  What is the business process management?

  Business process management (BPM) is a systematic approach to making an organization’s workflow more effective, more efficient and more capable of adapting to an ever-changing environment. A business process is an activity or set of activities that will accomplish a specific organizational goal.What is the definition of process management?

  Process management is the ensemble of activities of planning and monitoring the performance of a business process. The term usually refers to the management of business processes and manufacturing processes. Business process management (BPM) and business process re engineering are interrelated, but not identical.

  What is BPM industry?

  Business process management (BPM) is a field Oracle Interview Questions in operations management that focuses on improving corporate performance by managing and optimizing a company’s business processes. It can, therefore, be described as a “process optimization process.”

  Visit and Checkout Oracle Endeca Training

• John Esraelo-498130

  SSCertifiable

  Points: 5894

  May 11, 2018 at 2:58 pm

  #1990250

  have you tried transparent gateway between oracle and sql server?

  Cheers,John Esraelo

• March 25, 2019 at 5:44 am

  This was removed by the editor as SPAM

• SGT_squeequal

  SSCertifiable

  Points: 7060

  June 8, 2019 at 12:25 pm

  #3651377

  oracle has equivalent information_schema

  Select * from all_tables

  select * from all_views

  Select * from all_tab_columns

  ***The first step is always the hardest *******