Sp_help for Oracle?

  • g.britton

    SSChampion

    Points: 13682

    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

    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

    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

    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

    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

    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

    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

    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

    have you tried transparent gateway between oracle and sql server?

    Cheers,John Esraelo

  • This was removed by the editor as SPAM

  • SGT_squeequal

    SSCertifiable

    Points: 7060

    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 *******

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply