Retrieve the table space name for specific user or table

use this syntax to retrive the data about oracle table or user or even by owner.

select *
FROM ALL_TABLES;

Users and Schemas

First, two definitions. In Oracle parlance, a database user is a person who can log on to the database. A database schema is all the objects in the database owned by one user. The two terms can often be used interchangeably, as there is a one-to-one relationship between users and schemas. Note that while there is in fact a CREATE SCHEMA command, this does not actually create a schema—it is only a quick way of  creating objects in a schema. A schema is initially created empty, when a user is created with the CREATE USER command.

Schemas are used for storing objects. These may be data objects such as tables or programmatic objects such as PL/SQL stored procedures. User logons are used to connect to the database and access these objects. By default, users have access to the objects in their own schema and to no others, but most applications change this. Typically, one schema may be used for storing data that is accessed by other users who have been given permission to use the objects, even though they do not own them. In practice, very few users will ever have objects in their own schema, or permission to create them: they will have access rights (which will be strictly controlled) only to objects in another schema. These objects will be used by all users who run the application whose data that schema stores. Conversely, the users who own the data-storing schemas may never in fact log on: the only purpose of their schemas is to contain data used by others. 

It is impossible for a data object to exist independently of a schema. Or in other words, all tables must have  an owner. The owner is the user in whose schema the table resides. The unique identifier for a table (or any other schema object) is the username, followed by the object name. It follows that it is not possible for two  tables with the same name to exist in the same schema, but that two tables with the same name (though possibly different structures or contents) can exist in different schemas. If an object does not exist in one’s own schema, to access it one must qualify its name with the name of the schema in which it resides. For example, HR.EMPLOYEES is the table called EMPLOYEES in user HR’s schema. Only a user connected as HR could get to the table by referring to EMPLOYEES without a schema name qualifier.

HR Schema

The HR demonstration schema consists of seven tables, linked by primary key to foreign key relationships.

The tables are:

• REGIONS has rows for major geographical areas.
• COUNTRIES has rows for each country, which are optionally assigned to a region.
• LOCATIONS includes individual addresses, which are optionally assigned to a country.
• DEPARTMENTS has a row for each department, optionally assigned to a location and optionally with a manager (who must exist as an employee).
• EMPLOYEES has a row for every employee, each of whom must be assigned to a job and optionally to a department and to a manager. The managers must themselves be employees.
• JOBS lists all possible jobs in the organization. It is possible for many employees to have the same job.
• JOB_HISTORY lists previous jobs held by employees, uniquely identified by employee_id and start_date; it is not possible for an employee to hold two jobs concurrently. Each job history record will refer to one employee, who will have had one job at that time and may have been a member of one department.

These commands, which could be issued from SQL*Plus or SQL Developer, will make it possible to log on as users HR and OE using the passwords HR:

alter user hr account unlock identified by hr;

These alter user commands can only be issued when connected to the database as a user with DBA privileges, such as the user SYSTEM.