Query to display all tablespaces in a database and datafiles

In oracle, generally speaking, there are number of facts that I will mention in following section:

  • Each database can have many Schema/User (Logical division).
  • Each database can have many tablespaces (Logical division).
  • A schema is the set of objects (tables, indexes, views, etc) that belong to a user.
  • In Oracle, a user can be considered the same as a schema.
  • A database is divided into logical storage units called tablespaces, which group related logical structures together. For example, tablespaces commonly group all of an application’s objects to simplify some administrative operations. You may have a tablespace for application data and an additional one for application indexes.

Therefore, your question, “to see all tablespaces and datafiles belong to SCOTT” is s bit wrong.

However, there are some DBA views encompass information about all database objects, regardless of the owner. Only users with DBA privileges can access these views: DBA_DATA_FILES, DBA_TABLESPACES, DBA_FREE_SPACE, DBA_SEGMENTS.

So, connect to your DB as sysdba and run query through these helpful views. For example this query can help you to find all tablespaces and their data files that objects of your user are located:

SELECT DISTINCT sgm.TABLESPACE_NAME , dtf.FILE_NAME
FROM DBA_SEGMENTS sgm
JOIN DBA_DATA_FILES dtf ON (sgm.TABLESPACE_NAME = dtf.TABLESPACE_NAME)
WHERE sgm.OWNER = 'SCOTT'

Related Posts:

  1. ORA-12514 TNS:listener does not currently know of service requested in connect descriptor
  2. ORA-01017 Invalid Username/Password when connecting to 11g database from 9i client
  3. SQL Error: ORA-00942 table or view does not exist
  4. ORA-00984: column not allowed here [duplicate]
  5. How to resolve ORA 00936 Missing Expression Error?
  6. ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist
  7. ORA-01461: can bind a LONG value only for insert into a LONG column-Occurs when querying
  8. Oracle SQL Developer: Failure – Test failed: The Network Adapter could not establish the connection?
  9. How to display databases in Oracle 11g using SQL*Plus
  10. Hibernate dialect for Oracle Database 11g?
  11. SQL error “ORA-01722: invalid number”
  12. how to fix oracle ORA-01722 invalid number error
  13. QL Error: ORA-00933: SQL command not properly ended
  14. ORA-28040: No matching authentication protocol exception
  15. ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
  16. SQL not a single-group group function
  17. PLS-00201 – identifier must be declared
  18. issue ORA-00001: unique constraint violated coming in INSERT/UPDATE
  19. ORA-30926: unable to get a stable set of rows in the source tables
  20. PLS-00103: Encountered the symbol “CREATE”
  21. Oracle client ORA-12541: TNS:no listener
  22. SQL error “ORA-01722: invalid number”
  23. No more data to read from socket error
  24. ORA-12899: value too large for column
  25. Oracle startup not possible – ORA-00845: MEMORY_TARGET not supported on this system – but memory size seems to be fine
  26. Oracle date format picture ends before converting entire input string
  27. Oracle – ORA-01489: result of string concatenation is too long [duplicate]
  28. SQLPLUS error:ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
  29. SQL Error: ORA-00913: too many values
  30. UTL_FILE.FOPEN() procedure not accepting path for directory?
  31. ORA-01861: literal does not match format string
  32. ORA-01653: unable to extend table by in tablespace ORA-06512
  33. SQL Error: ORA-01861: literal does not match format string 01861
  34. ORA-28001: The password has expired
  35. Oracle “SQL Error: Missing IN or OUT parameter at index:: 1”
  36. How to insert date values into table
  37. Oracle SQL query for Date format
  38. sqldeveloper error : no ocijdbc11 in java.library.path on windows 7
  39. How to execute an oracle stored procedure?
  40. ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
  41. ORA-01747: invalid user.table.column, table.column, or column specification
  42. SQL Queries “00904. 00000 – “%s: invalid identifier”
  43. Convert timestamp to date in Oracle SQL
  44. grant select on view
  45. ‘NOT LIKE’ in an SQL query
  46. How do I use CREATE OR REPLACE?
  47. ORA-20002: YOU ARE NOT ALLOWED TO CHANGE THE PASSWORD FOR CRITICAL SCHEMAS
  48. How can I set a custom date time format in Oracle SQL Developer?
  49. How to copy files into a virtual box oracle box
  50. Find out the history of SQL queries
  51. ora-06553 pls-306 wrong number or types of arguments in call to ‘ogc_x’
  52. ORA-00904: invalid identifier
  53. ORA-00979 not a group by expression
  54. ORA-00907: missing right parenthesis
  55. error: ORA-65096: invalid common user or role name in oracle
  56. ORA-12154 could not resolve the connect identifier specified
  57. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
  58. ORA-01843 not a valid month- Comparing Dates
  59. ORA-00918: column ambiguously defined in SELECT *
  60. Error ORA-00054 resource busy and acquire with NOWAIT specified or timeout expired in oracle11g
  61. Oracle error : ORA-00905: Missing keyword
  62. How do I limit the number of rows returned by an Oracle query after ordering?
  63. Oracle SELECT TOP 10 records
  64. Can a foreign key be NULL and/or duplicate?
  65. How to Select Top 100 rows in Oracle?
  66. ORA-12560: TNS:protocol adaptor error
  67. What does the following Oracle error mean: invalid column index
  68. ORA-00972 identifier is too long alias column name
  69. ORA-01830: date format picture ends before converting entire input string / Select sum where date query
  70. What does the following Oracle error mean: invalid column index
  71. Oracle Apex column link from LOV values
  72. PLS-00103: Encountered the symbol when expecting one of the following:
  73. What’s the difference between RANK() and DENSE_RANK() functions in oracle?
  74. ORA-00918: column ambiguously defined in SELECT *
  75. How to resolve ORA-011033: ORACLE initialization or shutdown in progress
  76. Is there a combination of “LIKE” and “IN” in SQL?
  77. What is it exactly a BLOB in a DBMS context
  78. SQLRecoverableException: I/O Exception: Connection reset
  79. “ORA-01438: value larger than specified precision allowed for this column” when inserting 3
  80. How do you create a temporary table in an Oracle database?
  81. Oracle “Partition By” Keyword
  82. Oracle SQL: Update a table with data from another table
  83. PCI\VEN_10EC&DEV_522A&SUBSYS_837D103C&REV_01
  84. ORA-12518, TNS:listener could not hand off client connection
  85. What is the Oracle equivalent of SQL Server’s IsNull() function?
  86. Error while trying to retrieve text for error ORA-01019
  87. IO Error: The Network Adapter could not establish the connection
  88. How SID is different from Service name in Oracle tnsnames.ora
  89. ORA-06502: PL/SQL: numeric or value error: character string buffer too small
  90. How do I reset a sequence in Oracle?
  91. Update statement with inner join on Oracle
  92. MySQL equivalent of DECODE function in Oracle
  93. Oracle: If Table Exists
  94. DATEDIFF function in Oracle
  95. How to create id with AUTO_INCREMENT on Oracle?
  96. LEFT function in Oracle
  97. How do I view the Explain Plan in Oracle Sql developer?
  98. ORA-01779: cannot modify a column which maps to a non key-preserved table
  99. PLS-00049 bad bind variable While using :OLD
  100. Run Oracle SQL script and exit from sqlplus.exe via command prompt

Leave a Comment