in homework, there is a question that wants me to display all objects from my account(wants to display all tables and views), but the problem is i didn't use Oracle to know how to do it due to corona virus. thank you
Asked
Active
Viewed 92 times
-2
-
Does this answer your question? [Display all views on oracle database](https://stackoverflow.com/questions/13742717/display-all-views-on-oracle-database) – Isaac Mar 23 '20 at 16:20
-
unfortunately no, my view name is test, so i should write "SELECT test FROM all_view" but i don't know what "all view" represent ? btw i'm running W3school website to try the solution you gave me, thank you. – N.J Mar 23 '20 at 17:04
-
I don't understand what the corona virus has to do with ability to access an oracle database. Unless the db can only be accessed from on-campus and said campus is shut down. In any event, since you don't know what "all_view" represents, it's high time you got familiar with the documentation. Look up 'all_views' in the Database Reference. https://docs.oracle.com/database/121/REFRN/toc.htm – EdStevens Mar 23 '20 at 18:51
-
about Corona, yes that's true. And thanks for the reference, it helped me. – N.J Mar 23 '20 at 20:48
-
@EdStevens - these days I find myself confused by the meaning of phrases like "antivirus software". Could such software be used to fight corona virus? I view the op's reference in the same light. – Mar 24 '20 at 00:38
2 Answers
0
You want the information_schema. Since it's homework I'll stop there and leave it to you to explore the rest.
Joel Coehoorn
- 399,467
- 113
- 570
- 794
-
4
-
as i said, i can't use Oracle due to Corona virus and other problems, the homework must be submit via MS Word and there i must write the statement. – N.J Mar 23 '20 at 16:27
-
@N.J You can download a free edition of Oracle to run on your own computer. – Joel Coehoorn Mar 23 '20 at 16:29
-
i did but i countered problem where i can't find the file of the oracle and when i download it again it says that the file is already in my computer. – N.J Mar 23 '20 at 16:33
0
Objects that belong to your "account" - as you call it; it is "schema" in an Oracle database, i.e. "user" with all its objects - can be fetched from user_objects. Something like this:
I'm connected as user scott:
SQL> show user
USER is "SCOTT"
What do I have?
SQL> desc user_objects
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
SQL> select object_type, count(*)
2 from user_objects
3 group by object_type
4 order by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
FUNCTION 3
INDEX 6
PROCEDURE 4
SEQUENCE 1
TABLE 14
TRIGGER 2
TYPE 2
VIEW 2
8 rows selected.
How to list all those objects?
SQL> select object_name, object_type
2 from user_objects
3 order by object_type, object_name;
OBJECT_NAME OBJECT_TYPE
-------------------- -------------------
EMPLOYEE_FUNCTION FUNCTION
F_SAL FUNCTION
F_TEST FUNCTION
PKA INDEX
PKB INDEX
PK_DEPT INDEX
SYS_C0077379 INDEX
SYS_C0077382 INDEX
SYS_C0077385 INDEX
CHALLAN_TEST PROCEDURE
P_CRV PROCEDURE
P_MAIL PROCEDURE
PRINT_TABLE PROCEDURE
SEQA SEQUENCE
A TABLE
ALLOCATION TABLE
B TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
EMPLOYEES TABLE
HSTT_SR_MST TABLE
MASTER_TABLE TABLE
NURSE TABLE
ROOM TABLE
SALGRADE TABLE
TEST TABLE
TEST_DEFVAL TABLE
TRG_BI_DEF TRIGGER
TRG_BI_TEST TRIGGER
EMP_OBJ_TYPE TYPE
EMP_TBL_TYPE TYPE
VIEW_EXEC_DATA VIEW
V_MASTER_TABLE VIEW
34 rows selected.
SQL>
I guess that it answers your question.
Alternatively, if you had to display all objects you have access to, you'd query all_objects instead. Result is then very different:
SQL> select object_type, count(*)
2 from all_objects
3 group by object_type
4 order by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
CONSUMER GROUP 2
DESTINATION 2
DIRECTORY 1
EDITION 1
EVALUATION CONTEXT 1
FUNCTION 217
INDEX 6
INDEXTYPE 9
JAVA CLASS 28751
JAVA RESOURCE 938
JOB CLASS 2
OPERATOR 55
PACKAGE 553
PROCEDURE 74
PROGRAM 11
SCHEDULE 3
SCHEDULER GROUP 4
SEQUENCE 18
SYNONYM 33399
TABLE 119
TRIGGER 2
TYPE 1569
VIEW 2257
WINDOW 9
XML SCHEMA 53
25 rows selected.
SQL>
I think you don't want to list all those tens of thousands of objects; that would be useless. Also, that view (all_objects) contains additional column name: owner which shows who owns those objects. Some of them are owned by you (and you already saw them in user_objects), while most of them don't. Include it into the query for more info.
Furthermore, if you were a DBA (or had appropriate privileges), you'd query dba_objects which contain objects within the whole database. It also contains the owner column.
Littlefoot
- 131,892
- 15
- 35
- 57