Member Avatar for Junaid.mcsd

i am a new bee to oracle and database world, i need your help,

as i am a student i learnt some commands in class, how to retrieve the tables and its contents in 10g, my question is that, is there any builtin tables in oracle 11g as well if yes then how i enable and view it,
also let me know how can i install SQLplus for oracle 11g

thanks

  • 2 Contributors
  • 1 Reply
  • 351 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by davidcairns

Dani AI

Generated

As indicated, the built-in metadata is exposed through the data-dictionary views rather than by querying SYS tables directly. USER* views show objects owned by the connected account, ALL shows objects the account can access (own + granted), and DBA_ shows everything (DBA privilege required). The practical workflow is: list tables from the appropriate view, inspect columns, then check constraints/indexes/objects when needed.

Practical queries:

-- list tables owned by the current user
SELECT table_name
FROM user_tables
ORDER BY table_name;

-- list accessible tables (owner + table)
SELECT owner, table_name
FROM all_tables
ORDER BY owner, table_name;

-- list columns for a table (dictionary values are uppercase)
SELECT column_id, column_name, data_type, nullable
FROM user_tab_columns
WHERE table_name = 'MYTABLE'
ORDER BY column_id;

Notes on sample schemas and accounts: XE sometimes ships without demo schemas or leaves them locked. To enable a classic example account (SCOTT) connect locally as SYSDBA and unlock/set a password:

sqlplus / as sysdba
ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

If a sample schema is not present, the distribution includes install scripts (version-dependent) under the Oracle home; run those as SYS to create demo users.

SQLPlus and connections: Oracle Database 11g XE normally installs SQLPlus (sqlplus executable in ORACLE_HOME/bin and a Start Menu shortcut on Windows). Typical connection forms:

-- local admin
sqlplus / as sysdba

-- remote or normal connect (XE service)
sqlplus system/PASSWORD@//localhost:1521/XE

If SQLPlus is missing, the Oracle Instant Client + SQLPlus package can be installed separately. Common troubleshooting: dictionary names are uppercase (use UPPER() when filtering), insufficient privileges will return no rows (try a higher-privilege account to confirm), and ensure the listener/DB service is running before remote connects.

Member Avatar for davidcairns

The important things for the noob are

ALL_TABLES
ALL_TAB_COLUMNS
ALL_OBJECTS
ALL_VIEWS

note all of these have a version for the user you are logged in as

USER_TABLES
etc.

There are quite a few more (ALL or USER is required), _CONSTRAINTS, _SOURCE, _INDEXES to name a few.

Have a nose around and get to know them, I have never stopped using them in over 20 years

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.